Spatial Queries

Proximity Search

SELECT name, ST_Distance(location, ST_Point(-73.990, 40.750)) AS dist
FROM restaurants
WHERE ST_DWithin(location, ST_Point(-73.990, 40.750), 1000)
ORDER BY dist;

Geofencing (Point-in-Polygon)

SELECT name FROM restaurants
WHERE ST_Within(location, ST_GeomFromGeoJSON('{
    "type": "Polygon",
    "coordinates": [[[-74.0, 40.7], [-73.9, 40.7], [-73.9, 40.8], [-74.0, 40.8], [-74.0, 40.7]]]
}'));

OGC Predicates

Function	Description
`ST_Contains`	Geometry A contains geometry B
`ST_Within`	Geometry A is within geometry B
`ST_Intersects`	Geometries share any space
`ST_Disjoint`	Geometries share no space
`ST_DWithin`	Within a given distance in meters: geodesic for `4326`, planar for a projected SRID
`ST_IsValid`	Whether a geometry is well-formed

Constructors

Function	Result
`ST_Point(lng, lat)`	Point
`ST_MakePoint(x, y [, z])`	Point
`ST_GeomFromText(wkt [, srid])`	Geometry from WKT
`ST_GeomFromGeoJSON(json)`	Geometry from GeoJSON
`ST_GeomFromWKB(bytes [, srid])`	Geometry from WKB; accepts `X'...'` or its hex string
`ST_MakeLine(point, point, ...)`	LineString
`ST_MakePolygon(ring, ...)`	Polygon from arrays of `[lng, lat]` pairs
`ST_MakeEnvelope(min_lng, min_lat, max_lng, max_lat)`	Rectangular Polygon

Accessors and Measures

Measures are in meters, and square meters for area. A WGS 84 (4326) geometry is measured geodesically. A geometry in a projected SRID is measured in planar grid meters. ST_Distance needs both operands in the same SRID.

Function	Result
`ST_AsText(geom)`	WKT rendering
`ST_AsGeoJSON(geom)`	GeoJSON rendering
`ST_X(geom)` / `ST_Y(geom)`	Ordinate of a Point; NULL for any other geometry
`ST_GeometryType(geom)`	Type name, e.g. `Point`
`ST_NPoints(geom)`	Total vertex count
`ST_SRID(geom)`	SRID of the geometry; `4326` unless the geometry carries another SRID. See Spatial Reference Systems
`ST_Distance(a, b)`	Distance between two geometries
`ST_Length(geom)`	Length of linear components
`ST_Perimeter(geom)`	Boundary length of areal components
`ST_Area(geom)`	Area of areal components, less holes

Spatial Reference Systems

Every geometry carries an SRID. The default is 4326 (WGS 84, longitude and latitude in degrees). GeoJSON reads and writes a crs member for any SRID other than 4326.

SRID	System	Unit
`4326`	WGS 84 geographic	degrees
`32647`	UTM zone 47N on WGS 84	metres
`32648`	UTM zone 48N on WGS 84	metres
`3375`	GDM2000 Peninsula RSO	metres
`3376`	GDM2000 East Malaysia (Borneo) RSO	metres

The set is closed. Any other SRID is refused.

Function	Result
`ST_SRID(geom)`	SRID of the geometry
`ST_SetSRID(geom, srid)`	Geometry relabeled with `srid`. Coordinates stay as they are.
`ST_Transform(geom, srid)`	Geometry with every coordinate reprojected into `srid`

ST_GeomFromText and ST_GeomFromWKB take an optional srid argument. ST_SetSRID and ST_Transform return NULL when srid is NULL.

SELECT ST_Transform(ST_Point(101.7117, 3.1579), 32647);

Geometry functions read the SRID of each operand. They never transform implicitly. These cases raise a data exception with SQLSTATE 22000:

Two operands in different SRIDs. The error reads operands have different SRIDs <left> and <right>; use ST_Transform.
An SRID outside the supported set. The error lists the supported codes.
A coordinate outside the region the target projection is defined on.

Call ST_Transform on one operand to bring both into the same SRID.

A spatial index fixes its SRID at its first entry. It refuses geometries in any other SRID.

Geometry Operations

Function	Result
`ST_Buffer(geom, meters [, segments])`	Geometry grown by a distance
`ST_Envelope(geom)`	Bounding box
`ST_Centroid(geom)`	Centroid, weighted by highest dimension
`ST_Union(a, b)`	Union of geometries
`ST_Intersection(a, b)`	Intersection geometry

Reading Stored Geometry

Every function above works in every position a geometry expression may appear — an INSERT value, a SELECT projection, and a predicate's query-geometry argument. Constructors nest, so a buffered point is a valid search area, and a bare string literal is read as WKT or GeoJSON.

SELECT name, ST_AsText(location), ST_X(location), ST_Y(location)
FROM restaurants;

SELECT name FROM restaurants
WHERE ST_Within(location, ST_Buffer(ST_Point(-73.990, 40.750), 1000));

SELECT name FROM restaurants
WHERE ST_DWithin(location, 'POINT(-73.990 40.750)', 1000);

Spatial Join

SELECT r.name, z.zone_name FROM restaurants r, delivery_zones z
WHERE ST_Contains(z.boundary, r.location);

H3 Hexagonal Indexing

SELECT h3_latlngtocell(40.748, -73.985, 9) AS hex;

Hybrid Spatial-Vector

SELECT name, vector_distance(embedding, $query_vec) AS similarity
FROM restaurants
WHERE ST_DWithin(location, ST_Point(-73.990, 40.750), 2000) AND embedding <-> $query_vec
LIMIT 10;