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';

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, SpatialContacts c
WHERE
c.PostalCode = z.ZIP
AND z.geometry.ST_Intersects( @Mass_01775 ) = 1;

See ST_Intersects method for type ST_Geometry.

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;

See 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 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. For information about how to project data to another spatial reference system, see Lesson 6: Project spatial data.

See 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 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, SpatialContacts c
WHERE c.PostalCode = z.ZIP
  AND dist <= 100
ORDER BY dist;

See 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;

See ST_WithinDistance method for type ST_Geometry.

Proceed to Lesson 5: Output spatial data to SVG.