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 3: Load the ESRI shapefile data

This lesson shows you how to determine the columns in the ESRI shapefile and use that information to create a table that you will load the data into.

Prerequisites

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

If you have difficulty running any of the steps due to privilege problems, ask your administrator what value the -gl database option is set to, and then read the privilege section of the st_geometry_load_shapefile system procedure to determine the corresponding privileges you need.

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

  2. A quick query of the ST_SPATIAL_REFERENCE_SYSTEMS view, SELECT * FROM ST_SPATIAL_REFERENCE_SYSTEMS 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 ST_SPATIAL_REFERENCE_SYSTEMS
    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. This is the SRID you will assign to the data you load from the shapefile.

  3. In Interactive SQL, execute the following statement to create a table called Massdata, load the shapefile into the table, and assign SRID 4269 to the data. he load may take a minute.

    CALL st_geometry_load_shapefile ( 'c:\\temp\\massdata\\tl_2009_25_zcta5.shp', 
    4269, 
    'Massdata' );
    Note

    The Import Wizard also supports loading data from shapefiles.

  4. In Interactive SQL, query the table to view the data that was in the shapefile:

    SELECT * FROM Massdata;

    Each row in the results represents data for a zip code region.

    The geometry column holds the shape information of the zip code region as either a polygon (one area) or multipolygon (two or more noncontiguous areas).

  5. The ZCTA5CE column holds zip codes. To make it easier to refer to this column later in the tutorial, execute the following ALTER TABLE statement n Interactive SQL to change the column name to ZIP:

    ALTER TABLE Massdata
    RENAME ZCTA5CE TO ZIP;
  6. The two columns, INTPTLON and INTPTLAT, represent the X and Y coordinates for the center points of the zip code regions. Execute the following ALTER TABLE statement in Interactive SQL to create a column called CenterPoint of type ST_Point, and to turn each X and Y set into a value in CenterPoint.

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

    Now, each ST_Point value in Massdata.CenterPoint represents the center point of the zip code region stored in Massdata.geometry.

  7. To view an individual geometry (a zip code region) as a shape, double-click any value except the first one in Massdata.geometry and then 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.

  8. 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 in Interactive SQL:

    SELECT geometry FROM Massdata
    UNION ALL SELECT CenterPoint FROM Massdata;
    The state of Massachusetts displayed as colored polygons, each representing a zip code region. A dot in each polygon marks its center point.

Results

The ESRI shapefile data is loaded.

Next

Proceed to Lesson 4: Query spatial data.

 See also