Lesson 4: Query spatial data

This lesson shows you how to use some of the spatial methods to query the data in a meaningful context. You will also learn how to calculate distances, which requires you to add units of measurement to your database.

1. In Interactive SQL, create a variable named @Mass_01775 to hold the associated geometry for the zip code region 01775.

   CREATE VARIABLE @Mass_01775 ST_Geometry;
   SELECT geometry INTO @Mass_01775
   FROM Massdata
   WHERE ZIP = '01775';

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

   SELECT c.Surname, c.GivenName, c.Street, c.City, c.PostalCode, z.geometry
   FROM Massdata z, GROUPO.SpatialContacts c
   WHERE
   c.PostalCode = z.ZIP
   AND z.geometry.ST_Intersects( @Mass_01775 ) = 1;

3. 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 a 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;

4. 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 zip code 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 in Interactive SQL:

   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. You will learn about projection later in this tutorial.

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

   SELECT c.PostalCode, c.City,  
          z.CenterPoint.ST_Distance( ( SELECT CenterPoint 
                               FROM Massdata WHERE ZIP = '01775' ),
                             'Statute mile' ) dist,
          z.CenterPoint
   FROM Massdata z, GROUPO.SpatialContacts c
   WHERE c.PostalCode = z.ZIP
     AND dist <= 100
   ORDER BY dist;

6. 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, GROUPO.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;