Example: 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