GEOG 897D
Spatial Database Management

Spatial Relationship and Measurement Functions

PrintPrint

The functions in this category are the big ones in terms of providing the true power of a GIS.  (So pay attention!)

A. Spatial relationship functions

ST_Contains()
This function takes two geometries as input and determines whether the first geometry contains the other. The example below selects each city in the state of New York and checks to see if it is contained by a bounding box, the box representing the bounds of Pennsylvania which we created earlier using MakeEnvelope. This query should return True values for the border cities of Binghamton, Elmira and Jamestown; and False for all other cities.

SELECT name, ST_Contains(ST_MakeEnvelope(-80.52, 39.72, -74.70, 42.27, 4269),geom) 
    FROM usa.cities
    WHERE stateabb = 'US-NY';

ST_Within()
The converse of the ST_Contains() function is ST_Within(), which determines whether the first geometry is within the other. Thus you could obtain the same results returned by ST_Contains() by reversing the geometries:

SELECT name, ST_Within(geom,ST_MakeEnvelope(-80.52, 39.72, -74.70, 42.27, 4269)) 
    FROM usa.cities
    WHERE stateabb = 'US-NY';

ST_Covers()
This function will return the same results as ST_Contains() in most cases. To illustrate the difference between the two functions imagine a road segment that is exactly coincident with a county boundary (i.e., the road forms the boundary between two counties). If the road segment and county geometries were fed to the ST_Contains() function it would return False. The ST_Covers() function, on the other hand would return True.

ST_CoveredBy()
This function is to ST_Covers() as ST_Within() is to ST_Contains().

ST_Intersects()
This function determines whether two geometries share the same space in any way. Unlike ST_Contains(), which tests whether one geometry is fully within another, ST_Intersects() looks for intersection between any parts of the geometries. Returning to the road/county example, a road segment that is partially within a county and partially outside of it would return False using ST_Contains(), but True using ST_Intersects().

ST_Disjoint()
This function is the converse of ST_Intersects(). It returns True if the two geometries share no space and False if they intersect.

ST_Overlaps()
This function is quite similar to ST_Intersects with a couple of exceptions: a. the geometries must be of the same dimension (i.e., two lines or two polygons), and b. one geometry cannot completely contain the other.

ST_Touches()
This function returns True if the two geometries are tangent to one another but do not share any interior space. If the geometries are disjoint or overlapping the function returns False. Two neighboring land parcels would return True when fed to ST_Touches(); a county and its parent state would yield a return value of False.

ST_DWithin()
This function performs "within a distance of" logic, accepting two geometries and a distance as inputs. It returns True if the geometries are within the specified distance of one another and False if they are not. The example below reports on whether features in the NYC pts table are within a distance of 2 miles (5280 feet x 2) of the Empire State Building.

SELECT ptsA.name, ptsB.name, 
    ST_DWithin(ST_Transform(ptsA.geom,2260),ST_Transform(ptsB.geom,2260),5280*2) 
    FROM pts AS ptsA, pts AS ptsB
    WHERE ptsA.name = 'Empire State Building';

Some important aspects of this query are:

  1. The geometries (stored in the NAD83 latitude/longitude coordinates) are transformed to the New York East State Plane coordinate system before being passed to ST_DWithin(). This avoids measuring distance in decimal degrees.
  2. A cross join is used to join the pts table to itself. As we saw in Lesson 1, a cross join produces the cross product of two tables; i.e., it joins every row in the first table to every row in the second table.
  3. The WHERE clause restricts the query to showing just the Empire State Building records, if that clause were omitted the query would output every combination of features from the pts table.

ST_DFullyWithin()
This function is similar to ST_DWithin(), with the difference being that ST_DFullyWithin() requires each point that makes up the two geometries to be within the search distance, whereas ST_DWithin() is satisfied if any of the points comprising the geometries are within the search distance. The example below demonstrates the difference by performing a cross join between the NYC pts and polys.

SELECT pts.name, polys.name, 
    ST_DWithin(ST_Transform(pts.geom,2260),ST_Transform(polys.geom,2260),5280*2),
    ST_DFullyWithin(ST_Transform(pts.geom,2260),ST_Transform(polys.geom,2260),5280*2) 
    FROM pts CROSS JOIN polys
    WHERE pts.name = 'Empire State Building';

ST_DWithin() reports that the Empire State Building and Central Park are within 2 miles of each other, whereas ST_DFullyWithin() reports that they are not (because part of the Central Park polygon is greater than 2 miles away). Note that this query shows an alternative syntax for specifying a cross join in Postgres.

B. Measurement functions

ST_Area()
The key point to remember with this function is to use it on a geometry that is suitable for measuring areas. As we saw in Lesson 3, the ST_Transform() function can be used to re-project data on the fly if it is not stored in an appropriate projection.

ST_Area() can be used on both geometry and geography data types. Though geography objects are in latitude/longitude coordinates by definition, ST_Area() is programmed to return area values in square meters when a geography object is passed to it. By default the area will be calculated using the WGS84 spheroid. This can be costly in terms of performance so the function has an optional use_spheroid parameter. Setting that parameter to false, causes the function to use a much simpler but less accurate sphere.

ST_Centroid()
See Lesson 3 for example usages of this function.

ST_Distance()
This function calculates the 2D (Cartesian) distance between two geometries. It should only be used at a local or regional scale when the curvature of the earth's surface is not a significant factor. The example below again uses a cross join between the NYC pts table and itself to compute the distance in miles between the Empire State Building and the other features in the table:

SELECT ptsA.name, ptsB.name, 
    ST_Distance(ST_Transform(ptsA.geom,2260),ST_Transform(ptsB.geom,2260))/5280 
    FROM pts AS ptsA CROSS JOIN pts AS ptsB
    WHERE ptsA.name = 'Empire State Building';

The ST_Distance() function can also be used to calculate distances between geography data types. If only geography objects are supplied in the call to the function, the distance will be calculated based on a simple sphere. For a more accurate calculation an optional use_spheroid argument can be set to True, as we saw with ST_Area().

ST_Distance_Spheroid() and ST_Distance_Sphere()
These functions exist to provide for high-accuracy distance measurement when the data are stored using the geometry data type (rather than geography) and the distance is large enough for the earth's curvature to have an impact. They essentially eliminate the need to transform lat/long data stored as geometries prior to using ST_Distance(). The example below illustrates the use of both functions to calculate the distance between Los Angeles and New York.

SELECT cityA.name, cityB.name, 
    ST_Distance_Sphere(cityA.geom,cityB.geom)/1000 AS dist_sphere,
    ST_Distance_Spheroid(cityA.geom,cityB.geom,'SPHEROID["GRS 1980",6378137,298.257222101]')/1000 AS dist_spheroid
    FROM cities AS cityA CROSS JOIN cities AS cityB
    WHERE cityA.name = 'Los Angeles' AND cityB.name = 'New York';

Note that the Spheroid function requires specification of a spheroid. In this case the GRS80 spheroid is used because it is associated with the NAD83 GCS. Other spheroid specifications can be found in the spatial_ref_sys table in the public schema. You can query that table like so:

SELECT srtext FROM spatial_ref_sys WHERE srid = 4326;

The query above returns the description of the WGS84 GCS, including its spheroid parameters. These parameters could be copied for use in the ST_Distance_Spheroid() function as in the example above.

ST_Length()
This function returns the length of a linestring. The length of polygon outlines is provided by ST_Perimeter(); see below. As with measuring distance, be sure to use an appropriate spatial reference. Here we get the length of the features in our NYC lines table in feet:

SELECT name, ST_Length(ST_Transform(geom,2260)) FROM lines;

As with the ST_Distance() function, ST_Length() accepts the geography data type as an input and can calculate length using either a sphere or spheroid.

ST_Length_Spheroid()
Like the ST_Distance_Spheroid() function, this function is intended for measuring the lengths of lat/long geometries without having to transform to a different spatial reference.

ST_Length3D()
This function is used to measure the lengths of linestrings that have a Z dimension.

ST_Length3D_Spheroid()
This function is to ST_Length3D() as the ST_Length_Spheroid() function is to ST_Length(). It makes it possible to measure the lengths of lines with a Z component using a spheroid rather than a 2D approach.

ST_Perimeter()
This function is used to measure the length of the exterior ring of a polygon. Here we obtain the perimeter of Central Park:

SELECT name, ST_Perimeter(ST_Transform(geom,2260)) FROM polys;

ST_Perimeter3D()
This function is used to measure the perimeter of polygons whose boundaries include a Z dimension.