Lesson 6: Project spatial data

This lesson shows you how to project data into a spatial reference system that uses the flat-Earth model so that you can calculate area and distance measurements.

1. 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.

2. 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 ) );

3. 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;

   Note: ST_Area is not supported on round-Earth spatial reference systems and ST_Distance is supported but only between point geometries.
4. 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;

5. 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. The results for ST_Touches do not include the row for zip code 01775 (unlike the ST_Intersects method).

   CREATE OR REPLACE 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;

6. 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 » SAP Sybase IQ, and set Truncation Length to a higher number. Execute your query again and view the geometry.