Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.1 » SQL Anywhere Server - Spatial Data Support » Using spatial data » Tutorial: Experimenting with the spatial features


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.

The spatial values in Massdata were assigned SRID 4269 (NAD83 spatial reference system) when you loaded the data into the database from the ESRI shapefile. SRID 4269 is a round-Earth spatial reference system. However, calculations such as the area of geometries and some spatial predicates are not supported in the round-Earth model. If your data is currently associated with a round-Earth spatial reference system, you can create a new spatial column that projects the values into a flat-Earth spatial reference system, and then perform your calculations on that column.

 Project data
  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:


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

    See ST_Transform method for type ST_Geometry.

  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;

    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.

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

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

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

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