Using Interactive SQL, start and connect to the sample database (demo.db) as user DBA, or as a member of the SYS_SPATIAL_ADMIN_ROLE group.

The sample database is located in your /samples directory. For the default location of your /samples directory, see SQLANYSAMP12 environment variable.

Execute the following statement:

CALL sa_install_feature( 'st_geometry_predefined_srs' );

When the statement finishes, the additional units of measure and spatial reference systems have been installed.

See also: sa_install_feature system procedure and CREATE SPATIAL REFERENCE SYSTEM statement.

To determine the units of measure installed in your database, you can execute the following query:

SELECT * FROM SYSUNITOFMEASURE;

See also: SYSUNITOFMEASURE system view.

To determine the spatial reference systems installed in your database, you can look in the Spatial Reference Systems folder in Sybase Central, or execute the following query:

SELECT * FROM SYSSPATIALREFERENCESYSTEM;

See also: SYSSPATIALREFERENCESYSTEM system view.

Create a local directory called c:\temp\massdata.

Go to the following URL:  http://www2.census.gov/cgi-bin/shapefiles2009/national-files

On the right-hand side of the page, in the State- and County-based Shapefiles dropdown, select Massachusetts, and then click Submit.

On the left-hand side of the page, select 5-Digit ZIP Code Tabulation Area (2002), and then click Download Selected Files.

When prompted, save the zip file, multiple_tiger_files.zip, to c:\temp\massdata, and extract its contents. This creates a subdirectory called 25_MASSACHUSETTS containing another zip file called tl_2009_25_zcta5.zip.

Extract the contents of tl_2009_25_zcta5.zip to C:\temp\massdata.

This unpacks five files, including an ESRI shape file (.shp) you will use to load the spatial data into the database.

Since spatial data is associated with a specific spatial reference system, when you load data into the database, you must load it into the same spatial reference system, or at least one with an equivalent definition. To find out the spatial reference system information for the ESRI shapefile, open the project file, c:\temp\massdata\tl_2009_25_zcta5.prj, in a text editor. This file contains the spatial reference system information you need.

GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",
SPHEROID["GRS_1980",6378137,298.257222101]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]]

The string GCS_North_American_1983 is the name of the spatial reference system associated with the data.

A quick query of the SYSSPATIALREFERENCESYSTEM view, SELECT * FROM SYSSPATIALREFERENCESYSTEM WHERE srs_name='GCS_North_American_1983';, reveals that this name is not present in the list of predefined SRSs. However, you can query for a spatial reference system with the same definition and use it instead:

SELECT *
FROM SYSSPATIALREFERENCESYSTEM
WHERE definition LIKE '%1983%' 
AND definition LIKE 'GEOGCS%';

The query returns a single spatial reference system, NAD83 with SRID 4269, that has the same definition and will be suitable for loading the data into.

Next, you need to create a table to load the spatial data into. To do this, you must first determine the columns in your ESRI shapefile. The following statement returns a description of the columns. It also adds some formatting to the output that will help prepare the result set for inclusion in a CREATE TABLE statement. Note the use of the SRID you found in the previous step when calling the sa_describe_shapefile system procedure:

SELECT name || ' ' || domain_name_with_size || ', ' 
FROM sa_describe_shapefile('C:\temp\massdata\tl_2009_25_zcta5.shp', 4269)
ORDER BY column_number;

Select all rows in the result set, then right-click and select Copy Data » Cells.

In the top pane in Interactive SQL, remove the SELECT statement you executed and type CREATE TABLE Massdata(, and then paste the cells you copied.

Change the definition for the record_number column to be a PRIMARY KEY (that is, change record_number int, to record_number int PRIMARY KEY,).

Change column name ZCTA5CE to be ZIP.

For the last column in the list, remove the trailing comma and add a closing bracket followed by a semicolon.

Your CREATE TABLE statement should look as follows:

CREATE TABLE Massdata(
record_number int PRIMARY KEY, 
geometry ST_Geometry(SRID=4269), 
ZIP varchar(5), 
CLASSFP varchar(2), 
MTFCC varchar(5), 
FUNCSTAT varchar(1), 
ALAND bigint, 
AWATER bigint, 
INTPTLAT varchar(11), 
INTPTLON varchar(12) 
);

Execute the CREATE TABLE statement to create the table.

Load the spatial data in the ESRI shapefile into Massdata using the following statement. This may take several minutes to complete.

LOAD TABLE Massdata 
USING FILE 'C:\temp\massdata\tl_2009_25_zcta5.shp' 
FORMAT SHAPEFILE;

In the Massdata table, the two columns INTPTLON and INTPTLAT represent the X and Y coordinates for the center of the zip code region. In this step, you combine the values into an ST_Point column called CenterPoint. Each value in the CenterPoint column (in WKT) is the center point of the zip code region represented in the geometry column. This column will be useful in some of the tutorial examples later on.

To create the column, execute the following statement:

ALTER TABLE Massdata
ADD CenterPoint AS ST_Point(SRID=4269)
COMPUTE( new ST_Point( CAST( INTPTLON AS DOUBLE ), CAST( INTPTLAT AS DOUBLE ), 4269 ) );

You can view the data by executing the following statement in Interactive SQL:

SELECT * FROM Massdata;

Each row in the results represents a zip code region. Massdata.geometry holds the shape information of the zip code region as either a polygon (one area) or multipolygon (two or more incontiguous areas).

To view an individual geometry (a zip code region) as a shape, double-click any value in Massdata.geometry and click the Spatial Preview tab of the Value Of Column window.

If you receive an error saying the value is to large, or suggesting you include a primary key in the results, it is because the value has been truncated for display purposes in Interactive SQL. To fix this, you can either modify the query to include the primary key column in the results, or adjust the Truncation Length setting for Interactive SQL. Changing the setting is recommended if you don't want to have to include the primary key each time you query for geometries with the intent to view them in Interactive SQL.

To change the Truncation Length setting for Interactive SQL, click Tools » Options » SQL Anywhere, set Truncation Length to a high number such as 100000.

To view the entire data set as one shape, click Tools » Spatial Viewer to open the SQL Anywhere Spatial Viewer and execute the following query:

SELECT geometry FROM Massdata
UNION ALL SELECT centerpoint FROM Massdata;

In the following steps, you will work with the zip code area 01775.

Create a variable named @Mass_01775 to hold the associated geometry.

CREATE VARIABLE @Mass_01775 ST_Geometry;
SELECT geometry INTO @Mass_01775
FROM Massdata
WHERE ZIP = '01775';

Suppose you want to find all contacts in SpatialContacts in the zip code area 01775 and surrounding zip code areas. For this, you can use the ST_Intersects method, which returns geometries that intersects with, or are the same as, the specified geometry. You would execute the following statement:

SELECT c.Surname, c.GivenName, c.Street, c.City, c.PostalCode, z.geometry
FROM Massdata z, SpatialContacts c
WHERE
c.PostalCode = z.ZIP
AND z.geometry.ST_Intersects( @Mass_01775 ) = 1;

See also: ST_Intersects method for type ST_Geometry

All rows in Massdata.geometry are associated with the same spatial reference system (SRID 4269) because you assigned SRID 4269 when you created the geometry column and loaded data into it.

However, it is also possible to create an undeclared ST_Geometry column (that is, without assigning a SRID to it). This may be necessary if you intend store spatial values that have different SRSs associated to them in a single column. When operations are performed on these values, the spatial reference system associated with each value is used.

One danger of having an undeclared column, is that the database server does not prevent you from changing an spatial reference system that is associated with data in an undeclared column.

If the column has a declared SRID, however, the database server does not allow you to modify the spatial reference system associated with the data. You must first unload and then truncate the data in the declared column, change the spatial reference system, and then reload the data.

You can use the ST_SRID method to determine the SRID associated with values in a column, regardless of whether it is declared or not. For example, the following statement shows the SRID assigned to each row in the Massdata.geometry column:

SELECT geometry.ST_SRID()
FROM Massdata;

See also: ST_SRID method for type ST_Geometry

You can use the ST_CoveredBy method to check that a geometry is completely contained within another geometry. For example, Massdata.CenterPoint (ST_Point type) contains the latitude/longitude coordinates of the center of the zipcode area, while Massdata.geometry contains the polygon reflecting the zip code area. You can do a quick check to make sure that no CenterPoint value has been set outside its zip code area by executing the following query:

SELECT * FROM Massdata
WHERE NOT(CenterPoint.ST_CoveredBy(geometry) = 1);

No rows are returned, indicating that all CenterPoint values are contained within their associated geometries in Massdata.geometry. This check does not validate that they are the true center, of course. You would need to project the data to a flat-Earth spatial reference system and check the CenterPoint values using the ST_Centroid method. For information on how to project data to another spatial reference system, see Part 6: Project spatial data.

See also: ST_CoveredBy method for type ST_Geometry

You can use the ST_Distance method to measure the distance between the center point of the zip code areas. For example, suppose you want the list of zip code within 100 miles of zip code area 01775. You could execute the following query:

SELECT c.PostalCode, c.City,  
       z.CenterPoint.ST_Distance( ( SELECT CenterPoint 
                            FROM Massdata WHERE ZIP = '01775' ),
                          'Statute mile' ) dist,
       z.CenterPoint
FROM Massdata z, SpatialContacts c
WHERE c.PostalCode = z.ZIP
  AND dist <= 100
ORDER BY dist;

See also: ST_Distance method for type ST_Geometry

If knowing the exact distance is not important, you could construct the query using the ST_WithinDistance method instead, which can offer better performance for certain datasets (in particular, for large geometries):

SELECT c.PostalCode, c.City, z.CenterPoint
FROM Massdata z, SpatialContacts c
WHERE c.PostalCode = z.ZIP
  AND z.CenterPoint.ST_WithinDistance( ( SELECT CenterPoint
                            FROM Massdata WHERE ZIP = '01775' ),
                          100, 'Statute mile' ) = 1
ORDER BY c.PostalCode;

See also: ST_WithinDistance method for type ST_Geometry.

In Interactive SQL, execute the following statement to create a variable with an example geometry:

CREATE OR REPLACE VARIABLE @svg_geom 
ST_Polygon = (NEW ST_Polygon('Polygon ((1 1, 5 1, 5 5, 1 5, 1 1), (2 2, 2 3, 3 3, 3 2, 2 2))'));

In Interactive SQL, execute the following SELECT statement to call the ST_AsSVG method:

SELECT @svg_geom.ST_AsSVG() AS svg;

The result set has a single row that is an SVG image. You can view the image using the SVG Preview feature in Interactive SQL. To do this, double-click the result row, and select the SVG Preview tab.

If you receive an error saying that the full value could not be read from the database, you need to change the Truncation Length setting for Interactive SQL. To do this, in Interactive SQL click Tools » Options » SQL Anywhere, and set Truncation Length to a high number such as 100000. Execute your query again and view the geometry.

The previous step described how to preview an SVG image within Interactive SQL. However, it may be more useful to write the resulting SVG to a file so that it can be read by an external application. You could use the xp_write_file system procedure or the WRITE_CLIENT_FILE function [String] to write to a file relative to either the database server or the client computer. In this example, you will use the OUTPUT statement [Interactive SQL].

In Interactive SQL, execute the following SELECT statement to call the ST_AsSVG method and output the geometry to a file named myPolygon.svg:

SELECT @svg_geom.ST_AsSVG();
OUTPUT TO 'c:\\myPolygon.svg' 
QUOTE '' 
ESCAPES OFF 
FORMAT TEXT

You must include the QUOTE '' and ESCAPES OFFclauses, otherwise line return characters and single quotes are inserted in the XML to preserve whitespace, causing the output to be an invalid SVG file.

Open the SVG in a web browser or application that supports viewing SVG images. Alternatively, you can open the SVG in a text editor to view the XML for the geometry.

The ST_AsSVG method generates an SVG image from a single geometry. In some cases, you want to generate an SVG image including all of the shapes in a group. The ST_AsSVGAggr method is an aggregate function that combines multiple geometries into a single SVG image. First, create a variable to hold the SVG image and generate it using the ST_AsSVGAggr method.

CREATE OR REPLACE VARIABLE @svg XML;
SELECT ST_Geometry::ST_AsSVGAggr( geometry, 'attribute=fill="black"' )
INTO @svg
FROM Massdata;

The @svg variable now holds an SVG image representing all of the zip code regions in the Massdata table. The 'attribute=fill="black"' specifies the fill color that is used for the generated image. If not specified, the database server chooses a random fill color. Now that you have a variable containing the SVG image you are interested in, you can write it to a file for viewing by other applications. Execute the following statement to write the SVG image to a file relative to the database server.

CALL xp_write_file( 'c:\\temp\\Massdata.svg', @svg );

The WRITE_CLIENT_FILE function could also be used to write a file relative to the client application, but additional steps may be required to ensure appropriate permissions are enabled. If you open the SVG image in an application that supports SVG data, you should see an image like the following:

You will notice that the image is not uniformly black; there are small gaps between the borders of adjacent zip code regions. These are actually white lines between the geometries and is characteristic of the way the SVG is rendered. There are not really any gaps in the data. Larger white lines are rivers and lakes.

To measure the area of polygons representing the zip code areas, you must project the data in Massdata.geometry to a flat-Earth spatial reference system.

To select an appropriate SRID to project the data in Massdata.geometry into, query the SYSSPATIALREFERENCESYSTEM system view for a SRID containing the word Massachusetts, as follows:

SELECT * FROM SYSSPATIALREFERENCESYSTEM WHERE srs_name LIKE '%massachusetts%';

This returns several SRIDs suitable for use with the Massachusetts data. For the purpose of this tutorial, 3586 will be used.

You must now create a column, Massdata.geometry_flat, into which you will project the geometries into 3586 using the ST_Transform method:

ALTER TABLE Massdata 
ADD proj_geometry 
 AS ST_Geometry(SRID=3586)
 COMPUTE( geometry.ST_Transform( 3586 ) );

See also: ST_Transform method for type ST_Geometry

You can compute the area using the Massdata.proj_geometry. For example, execute the following statement:

SELECT zip, proj_geometry.ST_ToMultiPolygon().ST_Area('Statute Mile') AS area
FROM Massdata
ORDER BY area DESC;

To see the impact that projecting to another spatial reference system has on calculations of distance, you can use the following query to compute the distance between the center points of the zip codes using the round-Earth model (more precise) or the projected flat-Earth model. Both models agree fairly well for this data because the projection selected is suitable for the data set.

SELECT M1.zip, M2.zip,
       M1.CenterPoint.ST_Distance( M2.CenterPoint, 'Statute Mile' ) dist_round_earth, 
       M1.CenterPoint.ST_Transform( 3586 ).ST_Distance( M2.CenterPoint.ST_Transform( 3586 ), 'Statute Mile' ) dist_flat_earth
FROM Massdata M1, Massdata M2
WHERE M1.ZIP = '01775'
ORDER BY dist_round_earth DESC;

Suppose you want to find neighboring zip code areas that border the zip code area 01775. To do this, you would use the ST_Touches method. The ST_Touches method compares geometries to see if one geometry touches another geometry without overlapping in any way. Note that the results for ST_Touches do not include the row for zip code 01775 (unlike the ST_Intersects method).

DROP VARIABLE @Mass_01775;
CREATE VARIABLE @Mass_01775 ST_Geometry;
SELECT geometry INTO @Mass_01775
FROM Massdata
WHERE ZIP = '01775';

SELECT record_number, proj_geometry 
FROM Massdata 
WHERE proj_geometry.ST_Touches( @Mass_01775.ST_Transform( 3586 ) ) = 1;

See also: ST_Touches method for type ST_Geometry

You can use the ST_UnionAggr method to return a geometry that represents the union of a group of zip code areas. For example, suppose you want a geometry reflecting the union of the zip code areas neighboring, but not including, 01775.

In Interactive SQL, click Tools » Spatial Viewer and execute the following query:

SELECT ST_Geometry::ST_UnionAggr(proj_geometry) 
FROM Massdata
WHERE proj_geometry.ST_Touches( @Mass_01775.ST_Transform( 3586 ) ) = 1;

Double-click the result to view it.

If you receive an error saying the full column could not be read from the database, increase the Truncation Length setting for Interactive SQL. To do this, in Interactive SQL click Tools » Options » SQL Anywhere, and set Truncation Length to a higher number. Execute your query again and view the geometry.

See also: ST_UnionAggr method for type ST_Geometry.