This tutorial shows allows you to experiment with some of the spatial features in SQL Anywhere. To do so, you will first load an ESRI shapefile into your sample database (demo.db) to give you some valid spatial data to experiment with.
The tutorial is broken into the following parts:
This part of the tutorial shows you how to use the sa_install_feature system procedure to install many predefined units of measure and spatial reference systems you will need later in this tutorial.
Using Interactive SQL, start and connect to the sample database (demo.db) as user DBA, or as a member of the SYS_SPATIAL_ADMIN_ROLE group.
The sample database is located in your /samples directory. For the default location of your /samples directory, see SQLANYSAMP12 environment variable.
Execute the following statement:
CALL sa_install_feature( 'st_geometry_predefined_srs' );
When the statement finishes, the additional units of measure and spatial reference systems have been installed.
To determine the units of measure installed in your database, you can execute the following query:
SELECT * FROM SYSUNITOFMEASURE;
See also: SYSUNITOFMEASURE system view.
To determine the spatial reference systems installed in your database, you can look in the Spatial Reference Systems folder in Sybase Central, or execute the following query:
SELECT * FROM SYSSPATIALREFERENCESYSTEM;
See also: SYSSPATIALREFERENCESYSTEM system view.
In this part of the tutorial, you will download an ESRI shapefile from the US Census website ( www2.census.gov). The shapefile you download represents the Massachusetts 5-digit code zip code information used during the 2002 census tabulation. Each zip code area is treated as either a polygon or multipolygon.
Create a local directory called c:\temp\massdata.
Go to the following URL: http://www2.census.gov/cgi-bin/shapefiles2009/national-files
On the right-hand side of the page, in the State- and County-based Shapefiles dropdown, select Massachusetts, and then click Submit.
On the left-hand side of the page, select 5-Digit ZIP Code Tabulation Area (2002), and then click Download Selected Files.
When prompted, save the zip file, multiple_tiger_files.zip, to c:\temp\massdata, and extract its contents. This creates a subdirectory called 25_MASSACHUSETTS containing another zip file called tl_2009_25_zcta5.zip.
Extract the contents of tl_2009_25_zcta5.zip to C:\temp\massdata.
This unpacks five files, including an ESRI shape file (.shp) you will use to load the spatial data into the database.
This part of the tutorial shows you how to find out the columns in the ESRI shapefile and use that information to create a table that you will load the data into.
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.
The string GCS_North_American_1983 is the name of the spatial reference system associated with the data.
A quick query of the SYSSPATIALREFERENCESYSTEM view,
SELECT * FROM SYSSPATIALREFERENCESYSTEM 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 SYSSPATIALREFERENCESYSTEM 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 and will be suitable for loading the data into.
Next, you need to create a table to load the spatial data into. To do this, you must first determine the columns in your ESRI shapefile. The following statement returns a description of the columns. It also adds some formatting to the output that will help prepare the result set for inclusion in a CREATE TABLE statement. Note the use of the SRID you found in the previous step when calling the sa_describe_shapefile system procedure:
SELECT name || ' ' || domain_name_with_size || ', ' FROM sa_describe_shapefile('C:\temp\massdata\tl_2009_25_zcta5.shp', 4269) ORDER BY column_number;
Select all rows in the result set, then right-click and select Copy Data » Cells.
In the top pane in Interactive SQL, remove the SELECT statement you executed and type CREATE TABLE Massdata(, and then paste the cells you copied.
Change the definition for the record_number column to be a PRIMARY KEY (that is, change
record_number int, to
record_number int PRIMARY KEY,).
Change column name ZCTA5CE to be ZIP.
For the last column in the list, remove the trailing comma and add a closing bracket followed by a semicolon.
Your CREATE TABLE statement should look as follows:
CREATE TABLE Massdata( record_number int PRIMARY KEY, geometry ST_Geometry(SRID=4269), ZIP varchar(5), CLASSFP varchar(2), MTFCC varchar(5), FUNCSTAT varchar(1), ALAND bigint, AWATER bigint, INTPTLAT varchar(11), INTPTLON varchar(12) );
Execute the CREATE TABLE statement to create the table.
Load the spatial data in the ESRI shapefile into Massdata using the following statement. This may take several minutes to complete.
LOAD TABLE Massdata USING FILE 'C:\temp\massdata\tl_2009_25_zcta5.shp' FORMAT SHAPEFILE;
In the Massdata table, the two columns INTPTLON and INTPTLAT represent the X and Y coordinates for the center of the zip code region. In this step, you combine the values into an ST_Point column called CenterPoint. Each value in the CenterPoint column (in WKT) is the center point of the zip code region represented in the geometry column. This column will be useful in some of the tutorial examples later on.
To create the column, execute the following statement:
ALTER TABLE Massdata ADD CenterPoint AS ST_Point(SRID=4269) COMPUTE( new ST_Point( CAST( INTPTLON AS DOUBLE ), CAST( INTPTLAT AS DOUBLE ), 4269 ) );
You can view the data by executing the following statement in Interactive SQL:
SELECT * FROM Massdata;
Each row in the results represents a zip code region. Massdata.geometry holds the shape information of the zip code region as either a polygon (one area) or multipolygon (two or more incontiguous areas).
To view an individual geometry (a zip code region) as a shape, double-click any value in Massdata.geometry and 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.
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:
SELECT geometry FROM Massdata UNION ALL SELECT centerpoint FROM Massdata;
This part of the tutorial shows you how to use some of the spatial methods to query the data in a meaningful context.
The queries are performed on one or both of the SpatialContacts table, which holds names and contact information for people--many of whom live in Massachusetts, and on the Massdata table you created. You will also learn how to calculate distances, which requires you to add units of measurement to your database.
In the following steps, you will work with the zip code area 01775.
Create a variable named
@Mass_01775 to hold the associated geometry.
CREATE VARIABLE @Mass_01775 ST_Geometry; SELECT geometry INTO @Mass_01775 FROM Massdata WHERE ZIP = '01775';
Suppose you want to find all contacts in SpatialContacts in the zip code area 01775 and surrounding zip code areas. For this, you can use the ST_Intersects method, which returns geometries that intersects with, or are the same as, the specified geometry. You would execute the following statement:
SELECT c.Surname, c.GivenName, c.Street, c.City, c.PostalCode, z.geometry FROM Massdata z, SpatialContacts c WHERE c.PostalCode = z.ZIP AND z.geometry.ST_Intersects( @Mass_01775 ) = 1;
All rows in Massdata.geometry are associated with the same spatial reference system (SRID 4269) because you assigned SRID 4269 when you created the geometry column and loaded data into it.
However, it is also possible to create an undeclared ST_Geometry column (that is, without assigning a SRID to it). This may be necessary if you intend store spatial values that have different SRSs associated to them in a single column. When operations are performed on these values, the spatial reference system associated with each value is used.
One danger of having an undeclared column, is that the database server does not prevent you from changing an spatial reference system that is associated with data in an undeclared column.
If the column has a declared SRID, however, the database server does not allow you to modify the spatial reference system associated with the data. You must first unload and then truncate the data in the declared column, change the spatial reference system, and then reload the data.
You can use the ST_SRID method to determine the SRID associated with values in a column, regardless of whether it is declared or not. For example, the following statement shows the SRID assigned to each row in the Massdata.geometry column:
SELECT geometry.ST_SRID() FROM Massdata;
See also: ST_SRID method for type ST_Geometry
You can use the ST_CoveredBy method to check that a geometry is completely contained within another geometry. For example, Massdata.CenterPoint (ST_Point type) contains the latitude/longitude coordinates of the center of the zipcode area, while Massdata.geometry contains the polygon reflecting the zip code area. You can do a quick check to make sure that no CenterPoint value has been set outside its zip code area by executing the following query:
SELECT * FROM Massdata WHERE NOT(CenterPoint.ST_CoveredBy(geometry) = 1);
No rows are returned, indicating that all CenterPoint values are contained within their associated geometries in Massdata.geometry. This check does not validate that they are the true center, of course. You would need to project the data to a flat-Earth spatial reference system and check the CenterPoint values using the ST_Centroid method. For information on how to project data to another spatial reference system, see Part 6: Project spatial data.
See also: ST_CoveredBy method for type ST_Geometry
You can use the ST_Distance method to measure the distance between the center point of the zip code areas. For example, suppose you want the list of zip code within 100 miles of zip code area 01775. You could execute the following query:
SELECT c.PostalCode, c.City, z.CenterPoint.ST_Distance( ( SELECT CenterPoint FROM Massdata WHERE ZIP = '01775' ), 'Statute mile' ) dist, z.CenterPoint FROM Massdata z, SpatialContacts c WHERE c.PostalCode = z.ZIP AND dist <= 100 ORDER BY dist;
See also: ST_Distance method for type ST_Geometry
If knowing the exact distance is not important, you could construct the query using the ST_WithinDistance method instead, which can offer better performance for certain datasets (in particular, for large geometries):
SELECT c.PostalCode, c.City, z.CenterPoint FROM Massdata z, SpatialContacts c WHERE c.PostalCode = z.ZIP AND z.CenterPoint.ST_WithinDistance( ( SELECT CenterPoint FROM Massdata WHERE ZIP = '01775' ), 100, 'Statute mile' ) = 1 ORDER BY c.PostalCode;
You can export geometries to SVG format for viewing in Interactive SQL or in an SVG-compatible application. In the following procedure, you create an SVG document to view a multipolygon expressed in WKT.
In Interactive SQL, execute the following statement to create a variable with an example geometry:
CREATE OR REPLACE VARIABLE @svg_geom ST_Polygon = (NEW ST_Polygon('Polygon ((1 1, 5 1, 5 5, 1 5, 1 1), (2 2, 2 3, 3 3, 3 2, 2 2))'));
In Interactive SQL, execute the following SELECT statement to call the ST_AsSVG method:
SELECT @svg_geom.ST_AsSVG() AS svg;
The result set has a single row that is an SVG image. You can view the image using the SVG Preview feature in Interactive SQL. To do this, double-click the result row, and select the SVG Preview tab.
If you receive an error saying that the full value could not be read from the database, you need to change the Truncation Length setting for Interactive SQL. To do this, in Interactive SQL click Tools » Options » SQL Anywhere, and set Truncation Length to a high number such as 100000. Execute your query again and view the geometry.
The previous step described how to preview an SVG image within Interactive SQL. However, it may be more useful to write the resulting SVG to a file so that it can be read by an external application. You could use the xp_write_file system procedure or the WRITE_CLIENT_FILE function [String] to write to a file relative to either the database server or the client computer. In this example, you will use the OUTPUT statement [Interactive SQL].
In Interactive SQL, execute the following SELECT statement to call the ST_AsSVG method and output the geometry to a file named myPolygon.svg:
SELECT @svg_geom.ST_AsSVG(); OUTPUT TO 'c:\\myPolygon.svg' QUOTE '' ESCAPES OFF FORMAT TEXT
You must include the
QUOTE '' and
ESCAPES OFFclauses, otherwise line return characters and single quotes are inserted in the XML to preserve whitespace, causing the output
to be an invalid SVG file.
Open the SVG in a web browser or application that supports viewing SVG images. Alternatively, you can open the SVG in a text editor to view the XML for the geometry.
The ST_AsSVG method generates an SVG image from a single geometry. In some cases, you want to generate an SVG image including all of the shapes in a group. The ST_AsSVGAggr method is an aggregate function that combines multiple geometries into a single SVG image. First, create a variable to hold the SVG image and generate it using the ST_AsSVGAggr method.
CREATE OR REPLACE VARIABLE @svg XML; SELECT ST_Geometry::ST_AsSVGAggr( geometry, 'attribute=fill="black"' ) INTO @svg FROM Massdata;
@svg variable now holds an SVG image representing all of the zip code regions in the Massdata table. The
'attribute=fill="black"' specifies the fill color that is used for the generated image. If not specified, the database server chooses a random fill
color. Now that you have a variable containing the SVG image you are interested in, you can write it to a file for viewing
by other applications. Execute the following statement to write the SVG image to a file relative to the database server.
CALL xp_write_file( 'c:\\temp\\Massdata.svg', @svg );
The WRITE_CLIENT_FILE function could also be used to write a file relative to the client application, but additional steps may be required to ensure appropriate permissions are enabled. If you open the SVG image in an application that supports SVG data, you should see an image like the following:
You will notice that the image is not uniformly black; there are small gaps between the borders of adjacent zip code regions. These are actually white lines between the geometries and is characteristic of the way the SVG is rendered. There are not really any gaps in the data. Larger white lines are rivers and lakes.
This part of the tutorial shows you how to project data into an 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.
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, query the SYSSPATIALREFERENCESYSTEM system view for a SRID containing the word Massachusetts, as follows:
SELECT * FROM SYSSPATIALREFERENCESYSTEM 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.
You must now create a column, Massdata.geometry_flat, into which you will project the geometries into 3586 using the ST_Transform method:
ALTER TABLE Massdata ADD proj_geometry AS ST_Geometry(SRID=3586) COMPUTE( geometry.ST_Transform( 3586 ) );
See also: ST_Transform method for type ST_Geometry
You can compute the area using the Massdata.proj_geometry. For example, execute the following statement:
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.
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;
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 also: ST_Touches method for type ST_Geometry
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 also: ST_UnionAggr method for type ST_Geometry.
Restore the sample database (demo.db) to its original state by following the steps found here: Recreate the sample database (demo.db).
Discuss this page in DocCommentXchange.
|Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0|