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.

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 » SAP Sybase IQ, 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 SAP Sybase IQ Spatial Viewer and execute the following query in Interactive SQL:

   SELECT geometry FROM Massdata
   UNION ALL SELECT CenterPoint FROM Massdata;