This is a more detailed example of using the ST_Geometry data type to represent other geospatial data types when inserting geospatial data into tables. Create two tables in the test database: cities, which represents a list of cities and streets, which represents a list of streets. The cities are Polygons and the streets are LineStrings, and both are implemented using the ST_Geometry type as you can see from the table definitions for cities and streets. Insert some cities and streets into these tables using the well-known text format for these geospatial types and then submit a query to see if any of the streets are within any of the cities.
Here are the table definitions:
CREATE TABLE test.cities ( pkey INTEGER, CityName VARCHAR(40), CityShape ST_Geometry); CREATE TABLE test.streets ( pkey INTEGER, StreetName VARCHAR(40), StreetShape ST_Geometry);
First insert three rows into the cities table:
INSERT INTO test.cities VALUES(0, 'San Diego', 'POLYGON((1 1, 1 3, 6 3, 6 0, 1 1))' ); INSERT INTO test.cities VALUES(1, 'Los Angeles', 'POLYGON((10 10, 10 20, 20 20, 20 15, 10 10))' ); INSERT INTO test.cities VALUES(2, 'Chicago', 'POLYGON((100 100, 100 200, 300 200, 400 0, 100 100))' );
Then insert three rows into the streets table:
INSERT INTO test.streets VALUES(1, 'Lake Shore Drive', 'LINESTRING(110 180, 300 150)' ); INSERT INTO test.streets VALUES(1, 'Via Del Campo', 'LINESTRING(2 2, 3 2, 4 1)' ); INSERT INTO test.streets VALUES(1, 'Sepulveda Blvd', 'LINESTRING(12 12, 18 17)' );
Now join cities and streets on Streetshape.ST_Within(CityShape)=1 and select the StreetName and CityName column values from them:
SELECT StreetName, CityName FROM test.cities, test.streets WHERE StreetShape.ST_Within(CityShape) = 1 ORDER BY CityName;
If you use BTEQ, the result looks like this:
StreetName CityName ---------------------------------------- ---------------- Lake Shore Drive Chicago Sepulveda Blvd Los Angeles Via Del Campo San Diego