PostGIS Spatial Functions and Operators

This document covers PostGIS spatial functions and operators available in this library for working with geometry and geography data types.

📖 See also: Spatial Types for geometry/geography types and Common Use Cases and Examples for practical spatial query examples

PostGIS Spatial Operators

⚠️ Important: Some operators have dual meanings for different data types. Use the specific DQL function names to avoid conflicts:

• @: Use CONTAINS for arrays/JSON, SPATIAL_CONTAINED_BY for geometry/geography
• ~: Use REGEXP for text patterns, SPATIAL_CONTAINS for geometry/geography
• &&: Use OVERLAPS for arrays/JSON, spatial overlaps work automatically with geometry/geography

📝 Compatibility Notes:

• Most bounding box operators work primarily with geometry types
• Geography types have limited operator support (mainly &&, <->, <@>)
• 3D/n-dimensional operators may require explicit type casting: ST_GeomFromText('POINT Z(0 0 0)')
• Some advanced operators (&&&, <<#>>) may not be available in all PostGIS versions

Bounding Box Operators

These operators work with geometry and geography bounding boxes. All return boolean values and should be used with = TRUE or = FALSE in DQL.

Distance Operators

These operators calculate distances between geometries. All return numeric values.

PostGIS Spatial Relationship Functions

These functions determine spatial relationships between geometries. Most return boolean values and should be used with = TRUE or = FALSE in DQL, but there are exceptions: ST_Relate(geom, geom) returns text (intersection matrix) and ST_LineCrossingDirection returns integer (crossing behavior).

PostGIS Geometry Accessor Functions

These functions return properties and information about geometries.

PostGIS Measurement Functions

These functions calculate various measurements of geometries including lengths, areas, distances, and angles.

PostGIS Overlay Functions

These functions perform geometric operations between geometries including intersection, union, difference, and splitting.

PostGIS Coverage Functions

These functions work with polygonal coverages (sets of non-overlapping polygons that share edges).

PostGIS GeoJSON functions

PostGIS Geometry Processing Functions

These functions modify and transform geometries including buffering, simplification, coordinate system changes, and geometric transformations.

Usage Examples

-- Bounding box operations
-- Find geometries to the left of a reference point
SELECT e FROM Entity e WHERE STRICTLY_LEFT(e.geometry, 'POINT(0 0)') = TRUE

-- Find overlapping polygons
SELECT e FROM Entity e WHERE SPATIAL_CONTAINS(e.polygon, e.point) = TRUE

-- 3D spatial relationships
SELECT e FROM Entity e WHERE ND_OVERLAPS(e.geometry3d, 'POLYGON Z((0 0 0, 1 1 1, 2 2 2, 0 0 0))') = TRUE

-- Distance calculations
-- Find nearest geometries
SELECT e, GEOMETRY_DISTANCE(e.geometry, 'POINT(0 0)') as distance
FROM Entity e ORDER BY distance LIMIT 10

-- Bounding box distance for index optimization
SELECT e FROM Entity e WHERE BOUNDING_BOX_DISTANCE(e.geometry, 'POINT(0 0)') < 1000

-- 3D distance calculations
SELECT ND_CENTROID_DISTANCE(e.geometry3d1, e.geometry3d2) as distance FROM Entity e

-- Spatial relationship tests
-- Test if geometries intersect
SELECT e FROM Entity e WHERE ST_Intersects(e.geometry, 'POINT(0 0)') = TRUE

-- Test if one geometry contains another
SELECT e FROM Entity e WHERE ST_Contains(e.polygon, e.point) = TRUE

-- Test if geometries are within a distance
SELECT e FROM Entity e WHERE ST_DWithin(e.geometry, 'POINT(0 0)', 1000) = TRUE

-- Test topological relationships with intersection matrix
SELECT e FROM Entity e WHERE ST_Relate(e.geometry1, e.geometry2, 'T*T***T**') = TRUE

-- Get intersection matrix between two geometries
SELECT e, ST_Relate(e.geometry1, e.geometry2) as matrix FROM Entity e

-- Test if point is inside circle
SELECT e FROM Entity e WHERE ST_PointInsideCircle(e.point, 0, 0, 1000) = TRUE

-- Analyze line crossing behavior
SELECT e, ST_LineCrossingDirection(e.line1, e.line2) as crossing FROM Entity e
WHERE ST_LineCrossingDirection(e.line1, e.line2) != 0

-- Measurements
-- Calculate areas and perimeters
SELECT e, ST_Area(e.polygon) as area, ST_Perimeter(e.polygon) as perimeter
FROM Entity e WHERE e.polygon IS NOT NULL

-- Calculate 3D measurements
SELECT e, ST_3DLength(e.line3d) as length3d, ST_3DPerimeter(e.polygon3d) as perimeter3d
FROM Entity e WHERE e.line3d IS NOT NULL OR e.polygon3d IS NOT NULL

-- Find geometries within distance
SELECT e, ST_Distance(e.geometry, 'POINT(0 0)') as distance
FROM Entity e ORDER BY distance LIMIT 10

-- Calculate azimuth between points
SELECT e, ST_Azimuth(e.point1, e.point2) as azimuth_radians,
       DEGREES(ST_Azimuth(e.point1, e.point2)) as azimuth_degrees
FROM Entity e WHERE e.point1 IS NOT NULL AND e.point2 IS NOT NULL

-- GeoJSON conversion
-- Convert geometry to GeoJSON
SELECT e, ST_AsGeoJSON(e.geometry) as geojson FROM Entity e

-- Convert geometry to GeoJSON with limited decimal precision
SELECT e, ST_AsGeoJSON(e.geometry...