OpenGeo

Introduction to PostGIS

Table Of Contents

Previous topic

Section 6: Accessor Functions

Next topic

Section 8: Spatial Indexing

Section 7: Measurement

In this section, we introduce you to measurement functions, the first of the proper geometry processing functions you will learn about in this workshop. The three measurement functions we will discuss are length, distance, and area.

Length

The basic concept of length is simple enough to start with, but can become complicated with the introduction of multiple dimensions and geometry types. At its simplest, a length query uses the ST_Length function, which measures the length of a line (summing all of its line segments) and will look like this:

SELECT ST_Length(ST_GeomFromText('LINESTRING(0 0,5 0,5 10)'));

Note

The function ST_GeomFromText takes a string and converts it into a geometry so that spatial functions can act on the string.

_images/measure01.png

The result is 15, which can be easily verified. (The three coordinates in the above example are (0,0),(5,0),(5,10).) Now take a look at the 3-dimensional case.

SELECT ST_Length(ST_GeomFromEWKT('LINESTRING(0 0 0,5 0 3,5 10 5)'));
_images/measure02.png

The same answer is returned, as ST_Length discards any coordinates in dimensions greater than 2. For this case, the function ST_Length3D is used to generate the correct answer.

SELECT ST_Length3D(ST_GeomFromEWKT('LINESTRING(0 0 0,5 0 3,5 10 5)'));
_images/measure03.png

The length returned by these functions are calculated using a Cartesian coordinate system (x,y,z) and returned in the units of the spatial reference system. This is perfectly sensible, unless the coordinates are in lat/long. This is because degrees are not a natural unit of length, since length per degree varies with latitude and orientation. For this case, two additional functions are provided, ST_Length2D and ST_Length3D:

SELECT
  ST_Length2D_Spheroid(g, 'SPHEROID["GRS 1980",6378137,298.257222101]'),
  ST_Length3D_Spheroid(g, 'SPHEROID["GRS 1980",6378137,298.257222101]')
FROM (
  VALUES (
    ST_GeomFromEWKT('LINESTRING(151.1205 -33.7145 0,151.1218 -33.7087 54)')
  ) ) AS query(g);
_images/measure04.png

These functions take an additional parameter to the previous functions, a Spheroid object, and return a distance in meters. This spheroid defines the shape of the globe that the lat/long points are measured on. Spheroids are discussed further in the next section.

Sensibly, the Length functions work only on LINESTRING and MULTILINESTRING types, returning 0 for all others. While the length of a POINT is a nonsensical notion, POLYGONs have a perimeter that can be measured using the ST_Perimeter function.

SELECT ST_Perimeter(ST_GeomFromEWKT(g))
  FROM (
    VALUES
      ('POLYGON((-2 -2 0,2 -2 1,2 2 2,-2 2 1,-2 -2 0))'),
      ('POLYGON((-2 -2,2 -2,2 2,-2 2,-2 -2),(1 1,-1 1,-1 -1,1 -1,1 1))')
   ) AS query(g);
_images/measure05.png

The first polygon in the example is 3-dimensional. The second is 2-dimensional, but contains an inner ring (the second grouping of ordered pairs). Further, the calculated perimeter is not restricted to the exterior ring but includes (sums) all interior rings as well. To ensure you are only operating on the exterior ring, use the ST_ExteriorRing function along with ST_Length:

SELECT
  ST_Length(
    ST_ExteriorRing(
      ST_GeomFromEWKT(
        'POLYGON((-2 -2,2 -2,2 2,-2 2,-2 -2),(1 1,-1 1,-1 -1,1 -1,1 1))'
      )
    )
  );
_images/measure06.png

Area

Compared to the length and distance functions, the area function is very straightforward. There is only one function, ST_Area that operates on the 2-dimensional nature of a given geometry, returning a result in the spatial reference system of the input geometry (units square).

SELECT ST_Area(ST_GeomFromEWKT(g))
  FROM (
    VALUES
      ('POLYGON((-2 -2 0,2 -2 1,2 2 2,-2 2 1,-2 -2 0))'),
      ('POLYGON((-2 -2,2 -2,2 2,-2 2,-2 -2),(1 1,-1 1,-1 -1,1 -1,1 1))')
     ) AS query(g);
_images/measure09.png

Interior rings are not counted as part of the area measurement.

Distance

An analogue of length are the distance functions. ST_Distance provides the minimum distance between two geometries (of any type). Note that ST_Distance operates on 2-dimensional geometries only.

SELECT ST_Distance(ST_GeomFromEWKT('POINT(0 5)'),
  ST_GeomFromEWKT('LINESTRING(-2 2,2 2)'));
_images/measure07.png

The above example looks for the minimum distance between a line and a point. The closest the line (-2,2),(2,2) comes to the point at (0,5) is at (0,2), which is 3 units away from the point.

As with the length functions, the comparison is performed in Cartesian space, and the result is in the projection units of the data. Again, there are options available for calculating the distance between lat/long geometries as well, but these only work against point geometries. The relevant functions used are ST_Distance_Sphere and ST_Distance_Spheroid, for spheres and spheroids, respectively.

SELECT
  ST_Distance_Sphere(a, b),
  ST_Distance_Spheroid(a, b, 'SPHEROID["GRS 1980",6378137,298.257222101]')
FROM (
  VALUES (
    ST_GeomFromText('POINT(151.1205 -33.7145)'),
    ST_GeomFromText('POINT(151.1218 -33.7087)')
    ) ) AS query (a, b);
_images/measure08.png

Within a Distance

One of the most common inefficiencies in using PostGIS is done trying to locate items in proximity to something. For example, consider the problem of finding all roads within 5km of “Hawthorne Park / Pool”. It seems simple doesn’t it?

SELECT namelow
  FROM jacksonco_streets, medford_parks
  WHERE ST_Distance(medford_parks.the_geom,
    jacksonco_streets.the_geom) < 5000 / 0.3048
  AND medford_parks.name = 'Hawthorne Park / Pool';
_images/dwithin01.png

Note that the data is stored in feet not metres, so a conversion value (dividing by 0.3048) is required. Also notice how slow the query runs. This query earns far more points for clarity than it does for speed. Fortunately there is a faster solution with the ST_DWithin function.

SELECT namelow
  FROM jacksonco_streets, medford_parks
  WHERE ST_DWithin(medford_parks.the_geom,
    jacksonco_streets.the_geom, 5000 / 0.3048)
  AND medford_parks.name = 'Hawthorne Park / Pool';
_images/dwithin02.png

As you can see, the revised query is somewhat faster than the original. The ST_DWithin function includes the bounding box comparison && when it is executed. && is an operator that very quickly determines if the bounding box of the two geometries overlap. In this case the bounding box of the park is expanded by five kilometers and used for comparison with the street bounding boxes. This allows geometries that are clearly not within 5km of one another to be eliminated from the query using a very fast bounding box comparison, eliminating the need for a relatively expensive distance calculation.