📐

PostgreSQL Geometric Functions

Complete reference for PostgreSQL geometric functions covering point, line, circle, polygon, and path operations. Includes area, distance, perimeter, intersection, containment, and geometric type constructor functions. Updated for PostgreSQL 16.

7 functions

Jump to function

area center perimeter <-> (distance operator)point box circle

What are PostgreSQL Geometric Functions?

PostgreSQL geometric functions are built-in functions for working with geometric data types such as point, line, lseg, box, path, polygon, and circle. They support distance calculations, containment tests, intersection detection, and coordinate transformations. These functions are useful for spatial queries in applications that do not require the full PostGIS extension.

area

PG 7.4+→ double precision

Computes the area of a geometric object (box, circle, or path).

DeveloperData Eng

Signature

area ( geometric_type ) → double precision

Parameters

Parameter	Type	Description
shape	box, circle, or path	Geometric shape

Examples

sql

SELECT area('((0,0),(1,1))'::box);

→1

sql

SELECT area('<(0,0),1>'::circle);

→3.141592653589793

sql

SELECT area('((0,0),(1,0),(1,1),(0,1))'::path);

→1 (closed path = polygon)

sql

SELECT id, name, area(footprint) AS sq_units FROM buildings ORDER BY area(footprint) DESC LIMIT 10;

→Top 10 buildings by footprint area

⚠Anti-Pattern— Using built-in area() for real-world geographic area

area() operates in pure Euclidean 2D space with no concept of Earth's curvature or coordinate projections. Storing (longitude, latitude) in a point column and calling area() on a polygon built from those coordinates will produce results in degrees-squared, which is meaningless and varies by latitude.

✓ Instead: Use PostGIS: `ST_Area(ST_Transform(geom, 3857))` for metric area, or `ST_Area(geog)` on a geography column for geodetic area in square metres.

PostgreSQL's built-in geometric types are for 2D Euclidean geometry only — no coordinate projections. For geographic area (square kilometers on Earth's surface), use PostGIS with `ST_Area(ST_Transform(geom, 3857))` or similar.

example

SELECT area('<(0,0),5>'::circle) AS euclidean_area;

→78.5398 (πr²)

center

PG 7.4+→ point

Computes the center point of a box, circle, or polygon.

DeveloperData Eng

Signature

center ( geometric_type ) → point

Parameters

Parameter	Type	Description
shape	box or circle	Geometric shape

Examples

sql

SELECT center('((0,0),(4,4))'::box);

→(2,2)

sql

SELECT center('<(3,4),5>'::circle);

→(3,4)

sql

SELECT id, center(bounding_box) AS centroid FROM regions;

→Centroid of each region's bounding box

sql

SELECT center(box(point(0,0), point(10,6)));

→(5,3)

⚠Anti-Pattern— Treating center() of a box as the true centroid of a polygon

center() applied to a box returns the midpoint of the bounding box, not the true geometric centroid of an arbitrary polygon. For concave or irregular shapes the bounding-box center can be far outside the shape itself.

✓ Instead: For true polygon centroids, use PostGIS `ST_Centroid(geom)` which computes the actual centre of mass of the geometry.

Use `center(box(point_col, point_col))` to find the centroid of a group of points for geographic clustering — without PostGIS.

example

SELECT center(box(min(location), max(location))) AS cluster_center FROM pois WHERE cluster_id = 1;

→Centroid of a cluster of points

perimeter

PG 7.4+→ double precision

Computes the perimeter of a box or polygon path.

DeveloperData Eng

Signature

perimeter ( geometric_type ) → double precision

Parameters

Parameter	Type	Description
shape	box or path	Geometric shape

Examples

sql

SELECT perimeter('((0,0),(1,1))'::box);

→4

sql

SELECT perimeter('((0,0),(1,0),(1,1),(0,1))'::path);

→4

sql

SELECT id, perimeter(boundary) AS perim FROM regions ORDER BY perimeter(boundary) DESC;

→Regions ranked by perimeter length

sql

SELECT perimeter(box('(0,0)'::point,'(3,4)'::point));

→14

⚠Anti-Pattern— Calling perimeter() on an open path expecting a closed-polygon result

An open path (first and last point not joined) does not have a closed perimeter. perimeter() on an open path returns the sum of segment lengths but does NOT add the closing edge, so the result is shorter than the true perimeter of the implied polygon.

✓ Instead: Ensure the path is closed — either use the closed-path literal syntax starting with `(` or explicitly close it by repeating the first point at the end before calling perimeter().

Use `perimeter(polygon_col)` to find the boundary length of 2D polygons stored in a path or polygon column. For real-world geographic perimeters, use PostGIS `ST_Perimeter`.

example

SELECT id, perimeter(boundary) AS perimeter_units FROM regions;

→Perimeter of each region in coordinate units

<-> (distance operator)

Operator available since PG 8.0+; KNN index scan support via GiST added in PG 9.1+→ double precision

Returns the Euclidean distance between two geometric objects (points, segments, boxes, etc.).

DeveloperData Eng

Signature

geometric_type <-> geometric_type → double precision

Parameters

Parameter	Type	Description
shape1	geometric type	First object
shape2	geometric type	Second object

Examples

sql

SELECT '(0,0)'::point <-> '(3,4)'::point;

→5

sql

SELECT * FROM pois ORDER BY location <-> '(10,10)'::point LIMIT 5;

→5 closest points to (10,10)

sql

SELECT id, name, coords <-> '(51.5,-0.1)'::point AS dist FROM venues ORDER BY dist LIMIT 3;

→3 nearest venues to reference point

sql

SELECT '((0,0),(1,1))'::box <-> '((3,3),(4,4))'::box;

→2.8284... (distance between closest box corners)

⚠Anti-Pattern— Running distance-ordered queries without a GiST index

Without a GiST index, `ORDER BY coords <-> target LIMIT k` performs a full sequential scan computing the distance to every row before sorting — this is O(n) and catastrophically slow on large tables.

✓ Instead: Always create `CREATE INDEX ON tbl USING GIST (coords)` before running KNN queries. Confirm index use with EXPLAIN — look for 'Index Scan using … (order: <->)'.

Create a GiST index on point columns and use `ORDER BY location <-> target LIMIT k` for fast K-nearest-neighbor queries. The planner uses the index to avoid scanning all rows.

example

CREATE INDEX ON locations USING GIST (coords);
SELECT id, name FROM locations ORDER BY coords <-> '(40.7,-74.0)'::point LIMIT 10;

→10 nearest locations to a point — index-accelerated

point

PG 7.4+→ point

Creates a point from x and y coordinates.

DeveloperData Eng

Signature

point ( x double precision, y double precision ) → point

Parameters

Parameter	Type	Description
x	double precision	X coordinate
y	double precision	Y coordinate

Examples

sql

SELECT point(1.5, 2.5);

→(1.5,2.5)

sql

SELECT point(longitude, latitude) FROM addresses;

→Point from coordinate columns

sql

SELECT point(3, 4) <-> point(0, 0);

→5

sql

SELECT id, point(lng, lat) AS coords FROM stores WHERE point(lng, lat) <@ box('(-74.1,40.6)'::point,'(-73.9,40.8)'::point);

→Stores inside a bounding box

⚠Anti-Pattern— Confusing point(x,y) coordinate ordering when storing lat/long

PostgreSQL's point type uses (x, y) order — mathematically (horizontal, vertical). Geographic convention is latitude (vertical/N-S) first, longitude (horizontal/E-W) second, which is the opposite. Calling `point(latitude, longitude)` silently stores coordinates in the wrong order, causing all distance calculations and spatial queries to be incorrect.

✓ Instead: Always call `point(longitude, latitude)` — x = longitude (east/west), y = latitude (north/south). Document this in column comments: `COMMENT ON COLUMN tbl.coords IS '(longitude, latitude) — x=lng, y=lat';`

If you're not using PostGIS, store geographic coordinates as `point` and use the `<->` operator with a GiST index for simple distance-based queries.

example

ALTER TABLE stores ADD COLUMN location point;
UPDATE stores SET location = point(longitude, latitude);

→Point column for simple distance queries

box

PG 7.4+→ box

Creates a rectangular box from two corner points.

DeveloperData Eng

Signature

box ( point, point ) → box

Parameters

Parameter	Type	Description
point1	point	One corner of the box
point2	point	Opposite corner of the box

Examples

sql

SELECT box('(0,0)'::point, '(1,1)'::point);

→(1,1),(0,0)

sql

SELECT box(point(xmin, ymin), point(xmax, ymax)) AS bbox FROM regions;

→Bounding boxes from coordinate columns

sql

SELECT * FROM locations WHERE coords <@ box(point(-74.1,40.6), point(-73.9,40.8));

→Points within a bounding box (NYC area)

sql

SELECT area(box(point(0,0), point(5,3)));

→15

⚠Anti-Pattern— Using box for geographic bounding-box queries near the antimeridian or poles

The box type models a simple axis-aligned rectangle in flat 2D space. Geographic bounding boxes that wrap around the antimeridian (±180° longitude) or approach the poles cannot be represented correctly as a single box, leading to queries that silently miss or incorrectly include rows.

✓ Instead: For geographic bounding-box queries, use PostGIS with ST_MakeEnvelope() and geography/geometry types, which handle antimeridian wrapping and polar regions correctly.

The `&&` operator checks whether two boxes (or other geometric types) overlap. Combined with a GiST index, this enables fast spatial overlap queries without PostGIS.

example

SELECT * FROM regions WHERE bbox && box('(0,0)'::point, '(10,10)'::point);

→Regions overlapping with the given bounding box

circle

PG 7.4+→ circle

Creates a circle from a center point and a radius.

DeveloperData Eng

Signature

circle ( point, double precision ) → circle

Parameters

Parameter	Type	Description
center	point	Center point of the circle
radius	double precision	Radius of the circle

Examples

sql

SELECT circle('(0,0)'::point, 5.0);

→<(0,0),5>

sql

SELECT area(circle('(0,0)'::point, 5.0));

→78.5398...

sql

SELECT * FROM pois WHERE location <@ circle(point(-73.99, 40.73), 0.05);

→Points within ~0.05 degree radius circle (flat-Earth approximation)

sql

SELECT circle(point(0,0), 1) @> point(0.5, 0.5);

→true (point is inside unit circle)

⚠Anti-Pattern— Using circle() radius in degrees as a geographic kilometre radius

The circle type uses Euclidean distance in whatever units the coordinates happen to be in. If coordinates are (longitude, latitude) in degrees, a radius of 1.0 means 1 degree — which is roughly 111 km at the equator but shrinks to near zero at the poles. There is no way to specify a radius in kilometres without a projection.

✓ Instead: For geographic radius queries, use PostGIS: `ST_DWithin(geog_col, ST_MakePoint(lng, lat)::geography, distance_in_metres)` which correctly handles Earth's curvature.

Use `circle @> point` to check if a point is inside a circle. Combined with a GiST index, this enables fast proximity queries.

example

SELECT * FROM pois WHERE circle(center_point, radius_km) @> location;

→Points of interest within a circle

PostgreSQL Geometric Functions

Jump to function

What are PostgreSQL Geometric Functions?

area

Signature

Parameters

Examples

center

Signature

Parameters

Examples

perimeter

Signature

Parameters

Examples

<-> (distance operator)

Signature

Parameters

Examples

point

Signature

Parameters

Examples

box

Signature

Parameters

Examples

circle

Signature

Parameters

Examples

Related PostgreSQL Categories