Querying Geometry with ST_ Functions

The last lesson, why spatial databases, put a geometry column inside an ordinary Postgres table: one row per park, one column holding coordinates, the rest holding names and other attributes. That column sits there the same way any other column does. The difference shows up the moment a query tries to do something with it, because a geometry cannot be summed or averaged the way a price or a count can. PostGIS answers that with a set of SQL functions built specifically for geometry, and every one of them starts with the same two letters.

Every spatial function starts with ST_

Open the PostGIS documentation and the function names all follow one pattern: ST_Area, ST_Length, ST_Transform, ST_Buffer, dozens more. The prefix stands for spatial type, and it comes from the OGC Simple Features specification, the industry standard that defines how geometry should be stored and queried in a database. PostGIS did not invent the prefix. It implements a spec that several other spatial databases also follow, so the same ST_Area call works, with only small differences, in Oracle Spatial or SQL Server's geometry type.

That consistency matters less for portability and more for a simpler reason: once the prefix is familiar, a new function is guessable. A geometry's boundary length is ST_Perimeter. The distance between two geometries is ST_Distance. A query that needs the shortest path is unusual. A query that needs the area of a polygon, the length of a line, or the distance between two points comes up constantly, and PostGIS groups its answers into a few clear jobs: measuring a geometry, inspecting what it actually is, transforming it into a different coordinate system, and constructing a new geometry out of an old one.

Inspecting a geometry before trusting it

Before measuring anything, it helps to see what is actually stored in a column. Three functions answer that: ST_AsText prints the geometry as readable text, ST_GeometryType names the kind of shape, and ST_SRID reports which coordinate reference system the geometry claims to use.

Here is a parks table with three small polygons, all stored in EPSG 4326, the longitude and latitude system most data arrives in.

A query against that table can check the type and reference system of every row at once:

SELECT
  name,
  ST_GeometryType(geom) AS geom_type,
  ST_SRID(geom) AS srid
FROM parks;

The srid column is the useful one to build a habit around. An SRID, short for spatial reference identifier, is just the number a coordinate system is registered under. 4326 always means WGS84 longitude and latitude, the same system GPS and GeoJSON use. Trusting that number instead of guessing it is what makes every later step, especially reprojection, safe to automate.

ST_AsText goes further and prints the actual coordinates as well known text, the same plain-text format most spatial tools can read and write.

SELECT ST_AsText(geom) FROM parks WHERE name = 'Liberty Common';

Five points, the first and last identical, closing the ring into a rectangle. Longitude comes before latitude in that text, matching the column order GeoJSON and most GIS tools expect. A row that came back with SRID 0, or a geometry type of ST_LineString where a polygon was expected, is a sign the data loaded wrong before a single measurement is even attempted.

Why ST_Area lies on a 4326 geometry

Measuring is where PostGIS earns its keep. ST_Area returns a polygon's area, ST_Length returns a line's length, ST_Perimeter returns a polygon's boundary length, and ST_Distance returns how far apart two geometries sit. All four take a geometry and hand back a plain number.

Running ST_Area straight on the parks table looks like it should just work.

SELECT name, ST_Area(geom) AS area_raw
FROM parks
WHERE name = 'Liberty Common';

That number is not wrong. It is honest about what it measured: square degrees, because the geometry's coordinates are degrees of longitude and latitude, and ST_Area multiplied them exactly as given. The trouble is that a degree of longitude is not a fixed distance. Near the equator it spans about 111 kilometers. Near a pole it shrinks toward zero, because meridians converge. A degree of latitude stays close to constant, but a degree of longitude does not, so squaring the two together produces a figure with no real-world meaning at all.

The fix is ST_Transform, which reprojects a geometry into a different coordinate system inside the query itself, without touching the stored data. Feeding its result into ST_Area produces a number in the new system's units. UTM, the Universal Transverse Mercator system, splits the globe into zones and gives each one a metric, meters-based projection accurate over a few hundred kilometers. These three parks sit in UTM zone 18N, EPSG code 32618.

SELECT
  name,
  round(ST_Area(ST_Transform(geom, 32618))::numeric, 1) AS area_sq_m
FROM parks
ORDER BY name;

Those figures line up with the rough hectare sizes in the earlier table: 14,105 square meters is 1.41 hectares, 22,566 is 2.26, 4,514 is 0.45. The reprojection did not change what the parks look like. It changed the ruler the query measured them with, from degrees to meters, which is the only ruler ST_Area's answer can be trusted against.

There is a second way to get meters without picking a zone by hand. A column can be declared as the geography type instead of geometry, still holding the same longitude and latitude coordinates, but telling PostGIS to run its default calculations on the sphere directly rather than on flat, projected coordinates. Casting a geometry to geography with geom::geography and calling ST_Area on that gives square meters immediately, no ST_Transform required.

SELECT
  name,
  round(ST_Area(geom::geography)::numeric, 1) AS area_sq_m_geog
FROM parks
ORDER BY name;

The numbers barely move, a difference measured in tenths of a square meter here, because the geodesic calculation on the sphere and the UTM projection agree closely at city block scale. That gap widens for features that span a wide area, like a whole state or an ocean current, which is why an analyst working at national or global scale often reaches for geography from the start, while an analyst doing detailed local work often reaches for geometry with the correct UTM zone, since it runs faster and every other spatial operation, buffering included, has clearer behavior on projected coordinates.

The same reprojection habit applies to ST_Length, ST_Perimeter, and ST_Distance. A query comparing two parks by straight-line separation reprojects both sides first:

SELECT
  round(
    ST_Distance(
      ST_Transform(a.geom, 32618),
      ST_Transform(b.geom, 32618)
    )::numeric, 1
  ) AS meters_apart
FROM parks a, parks b
WHERE a.name = 'Liberty Common' AND b.name = 'Mill Pond Park';

Constructing new geometry inside the query

Measuring and inspecting both read what is already stored. A third group of functions builds new geometry on the fly, without changing a single row in the table. ST_Centroid returns a polygon's center point, useful any time an analysis needs one representative location instead of a whole shape, for labeling a map or measuring distance from a park without dragging its full boundary along.

SELECT
  name,
  ST_AsText(ST_Centroid(geom)) AS center
FROM parks
WHERE name = 'Sparrow Corner';

ST_Buffer builds a larger polygon by expanding a geometry outward by a fixed distance, the way a compass draws a circle around every point on a shape's boundary and unions the result. It is the function behind almost every "within walking distance" or "within a mile" question a GIS answers. The distance argument is in whatever unit the geometry's current coordinate system uses, which is exactly why the reprojection habit from the last section matters here too.

SELECT
  round(
    ST_Area(
      ST_Buffer(ST_Transform(geom, 32618), 500)
    )::numeric, 1
  ) AS buffered_area_sq_m
FROM parks
WHERE name = 'Sparrow Corner';

Sparrow Corner itself covers about 4,515 square meters. Its 500 meter buffer covers over 929,000, close to 93 hectares, because the buffer distance is large relative to the park's own size. That gap is worth sitting with for a second: a walking-distance buffer around a small feature is dominated by the buffer distance, not by the feature's original shape. It is also a straight-line buffer, a proxy for actual walking distance along real streets, which almost always run a longer, bent path than a compass circle assumes.

It is still just SQL

None of this replaced anything an analyst already knows about SQL. WHERE still filters rows, ORDER BY still sorts them, JOIN still connects tables, and GROUP BY still aggregates. The only addition is a family of functions that happen to take a geometry as an argument instead of a number or a string, and that always spell their name starting with ST_. Once ST_Area, ST_Transform, and ST_Buffer feel like ordinary function calls rather than a separate language, the door opens to the more powerful class of question a spatial database exists for: not just measuring one geometry at a time, but asking how two layers relate to each other, which parks a proposed development falls inside, which sensors sit within range of a leak. That is where PostGIS spends most of its real work, and it is the next thing this module covers.