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, use Interactive SQL to query the ST_SPATIAL_REFERENCE_SYSTEMS consolidated view for a SRID containing the word Massachusetts, as follows:

SELECT * FROM ST_SPATIAL_REFERENCE_SYSTEMS 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.proj_geometry, into which you will project the geometries into 3586 using the ST_Transform method. To do so, execute the following statement in Interactive SQL:

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

See ST_Transform method for type ST_Geometry.

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

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 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 ST_UnionAggr method for type ST_Geometry.

You have now finished the Experimenting with the spatial features tutorial.

To restore the sample database (demo.db) to its original state, follow the steps in Recreate the sample database (demo.db).