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

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - Spatial Data Support » 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.


This lesson assumes you have completed all preceding lessons. See Lesson 1: Install additional units of measure and spatial reference systems.

This lesson assumes you have the roles and privileges listed in the Privileges section at the start of this tutorial: Tutorial: Experimenting with the spatial features.

Context and remarks

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.

  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 ) );
  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. 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 » SQL Anywhere, and set Truncation Length to a higher number. Execute your query again and view the geometry.


You have finished the tutorial.


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

 See also