PostGIS Tutorial for Developers: Store and Query Location Data in PostgreSQL

If your app touches maps, farms, deliveries, or anything with a location, PostGIS turns PostgreSQL into a serious spatial database. I use it for plot data in compliance work. Here's the practical subset you need.

Setup

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE plots (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  producer_id uuid NOT NULL,
  geom        geometry(Geometry, 4326) NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX plots_geom_idx ON plots USING GIST (geom);

geometry(Geometry, 4326) accepts points and polygons in WGS 84, the same system as GeoJSON.

Insert GeoJSON

INSERT INTO plots (producer_id, geom)
VALUES (
  $1,
  ST_SetSRID(ST_GeomFromGeoJSON($2), 4326)
);

Export GeoJSON

SELECT json_build_object(
  'type', 'Feature',
  'properties', json_build_object('id', id),
  'geometry', ST_AsGeoJSON(geom)::json
)
FROM plots
WHERE producer_id = $1;

Geometry vs geography

• geometry: planar maths, fast, most functions. In 4326, units are degrees.
• geography: calculations on the spheroid, results in metres.

Store as geometry, cast for measurements:

SELECT id, ST_Area(geom::geography) / 10000 AS area_ha FROM plots;

Common queries

Validity:

SELECT id, ST_IsValidReason(geom) FROM plots WHERE NOT ST_IsValid(geom);

Plots within the map viewport:

SELECT id, ST_AsGeoJSON(geom)
FROM plots
WHERE geom && ST_MakeEnvelope($1, $2, $3, $4, 4326);

Plots intersecting a forest-loss layer:

SELECT p.id
FROM plots p
JOIN forest_loss f ON ST_Intersects(p.geom, f.geom);

Nearest collection point:

SELECT c.id, ST_Distance(p.geom::geography, c.geom::geography) AS metres
FROM plots p, collection_points c
WHERE p.id = $1
ORDER BY p.geom <-> c.geom
LIMIT 1;

Performance tips

• GIST index on every spatial column you query.
• Use && (bounding-box overlap) before expensive functions where possible.
• Simplify geometries for display (ST_SimplifyPreserveTopology).
• Subdivide huge polygons (ST_Subdivide) for faster intersections.

With an ORM

Most TypeScript ORMs don't model PostGIS types natively. I keep spatial queries in raw SQL (with parameters) and keep the rest in the ORM.

Related

• How to Validate GeoJSON Plot Data
• Rendering Large Geospatial Datasets in React
• Data Models for Agricultural Supply-Chain Traceability