丁久

Posted on May 16 • Originally published at dingjiu1989-hue.github.io

Geospatial Data with PostGIS: Geometry, Geography, and Spatial Queries

#database #backend #data

This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.

Geospatial Data with PostGIS: Geometry, Geography, and Spatial Queries

PostGIS transforms PostgreSQL into a full-featured spatial database. It adds support for geographic objects, spatial indexing, and hundreds of spatial functions. This article covers fundamental concepts and practical query patterns.

Setting Up PostGIS

CREATE EXTENSION postgis;

CREATE EXTENSION postgis_topology;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Verify installation

SELECT postgis_version();

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- 3.4.0 or similar

Geometry vs Geography

The most important design decision in PostGIS is choosing between the geometry and geography data types.

Geometry treats coordinates as Cartesian points on a flat plane. It is appropriate for:

Local datasets where Earth curvature is negligible.
Data in projected coordinate systems (UTM, State Plane).
Operations that require planar spatial functions.

CREATE TABLE buildings (

id BIGSERIAL PRIMARY KEY,

name TEXT,

location GEOMETRY(Point, 4326), -- SRID 4326 = WGS84 lon/lat

footprint GEOMETRY(Polygon, 4326)

);

INSERT INTO buildings (name, location) VALUES

('City Hall', ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326));

Geography treats coordinates on a sphere (or spheroid). It accounts for Earth curvature, making it appropriate for:

Global datasets spanning large distances.
Accurate distance calculations in degrees.
Queries like "find all points within 10 km."

CREATE TABLE pois (

id BIGSERIAL PRIMARY KEY,

name TEXT,

location GEOGRAPHY(Point, 4326)

);

INSERT INTO pois (name, location) VALUES

('Statue of Liberty', ST_SetSRID(ST_MakePoint(-74.0445, 40.6892), 4326));

Performance note : Geography calculations are 2-3x slower than geometry because they use more complex spherical math. For local datasets, cast geography to geometry when precision is not critical.

Spatial Indexes

PostGIS uses GiST (Generalized Search Tree) indexes for spatial data:

CREATE INDEX idx_buildings_location ON buildings USING GIST (location);

CREATE INDEX idx_pois_location ON pois USING GIST (location);

GiST indexes enable indexed spatial operators: ST_DWithin, ST_Intersects, ST_Contains, ST_Within, and bounding box comparisons.

Common Spatial Queries

Distance Queries

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Find all POIs within 1000 meters of a point

SELECT name, ST_Distance(location, ST_SetSRID(ST_MakePoint(-74.006, 40.7128), 4326)) AS dist_meters

FROM pois

WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(-74.006, 40.7128), 4326), 1000)

ORDER BY dist_meters;

ST_DWithin uses the index and is far faster than filtering by ST_Distance in a WHERE clause.

Spatial Joins

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Find all buildings within each neighborhood

SELECT n.name AS neighborhood, COUNT(b.id) AS building_count

FROM neighborhoods n

JOIN buildings b ON ST_Contains(n.geom, b.location)

GROUP BY n.name

ORDER BY building_count DESC;

Area Calculations

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.

Found this useful? Check out more developer guides and tool comparisons on AI Study Room.

DEV Community

Geospatial Data with PostGIS: Geometry, Geography, and Spatial Queries

Geospatial Data with PostGIS: Geometry, Geography, and Spatial Queries