SQL 110 🛢️ Spatial Data Processing and PostGIS / Geospatial Extensions - KandZ

⏱️ Estimated read: 13 min | |

Spatial data represents information about the physical location and shape of geometric objects—points, lines, polygons, and the relationships between them . A database that can store coordinates is not automatically a spatial database. A spatial database must understand the geometry’s structure, support the operations that answer spatial questions (does this point fall inside that polygon? how far apart are these two shapes?), and index the data so those questions can be answered without scanning every row. PostgreSQL’s PostGIS is the most complete open-source implementation of this idea, and it is the reference against which the other engines’ spatial capabilities are measured .

The core of PostGIS is the geometry and geography types, the spatial functions that operate on them, and the GiST index that makes the operations fast. The geometry type models shapes on a flat Cartesian plane, and the geography type models shapes on a sphere or spheroid, which is what you want when the distances and areas need to be accurate across large regions of the Earth’s surface . The distinction is not cosmetic: a distance calculated in geometry is a Cartesian distance, while a distance calculated in geography is a great-circle distance in meters.

Key point: PostGIS adds the geometry and geography types, hundreds of spatial functions, and GiST-based spatial indexing to PostgreSQL. The geometry type uses a planar coordinate system, and the geography type uses a spherical one; the choice between them determines whether the distance calculations are planar or great-circle.

Why spatial data needs its own processing model

The coordinate problem. A point is two numbers—longitude and latitude, or easting and northing. A B-tree can index one of those numbers efficiently, but a query that asks “which points are inside this rectangle” needs to filter on both. A composite B-tree index on (x, y) helps, but it is still a range scan, not a spatial search. The GiST index solves the problem by indexing the bounding boxes of the geometries and descending only into the boxes that can overlap the query region .

The coordinate system problem. The same point on the Earth has different coordinates depending on the projection. A point in New York is (-74.006, 40.7128) in WGS 84 longitude/latitude, but it has a different pair of numbers in a UTM projection, and a different pair again in a state-plane projection. The SRID is the identifier that tells PostGIS which coordinate system the numbers belong to, and the functions that convert between systems use the SRID to do the transformation . Without the SRID, the database cannot know whether two geometries are in the same system, and the distance calculations would be meaningless.

The topology problem. Spatial queries are not just about coordinates. They are about relationships: does polygon A contain point B, do line C and polygon D intersect, is shape E within 500 meters of shape F. These are topological predicates, and they require an understanding of the geometry’s interior, boundary, and exterior . The PostGIS functions ST_Contains, ST_Intersects, and ST_Within implement these predicates using the DE-9IM model, which is the OGC standard for describing the spatial relationship between two geometries .

The index selectivity problem. A spatial index on a table of points can narrow the candidate set, but the final predicate still needs to evaluate the exact geometry. The GiST index provides the coarse filter—the bounding boxes that overlap the query—and the exact function evaluates the survivors. The index-aware functions like ST_DWithin are designed to use the index for the coarse filter and the exact computation for the refinement . A function that does not use the index, like ST_Distance in a WHERE clause, evaluates the exact distance on every row.

a. The geometry and geography types

PostGIS adds two spatial types. The geometry type stores shapes in a planar coordinate system, and the geography type stores shapes in a spherical coordinate system . Both support the OGC geometry subtypes: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon, and GeometryCollection .

-- A geometry column with a type modifier and an SRID
-- The modifier restricts the column to Point geometries
-- The SRID 4326 identifies the WGS 84 longitude/latitude system
CREATE TABLE places (
  id   serial PRIMARY KEY,
  name text,
  geom geometry(Point, 4326)
);

-- A geography column with the default SRID 4326
-- The geography type calculates distances in meters
CREATE TABLE cities (
  id   serial PRIMARY KEY,
  name text,
  geog geography(Point)
);

The type modifier geometry(Point, 4326) restricts the column to points in the WGS 84 system. The modifier is optional, but it catches errors at insert time: a LineString inserted into a Point column is rejected, and a geometry with a different SRID is rejected if the SRID is specified . The geography type defaults to SRID 4326, because the spherical calculations only make sense in longitude/latitude coordinates .

The choice between the two types is determined by the scope of the data. If the data is confined to a small area—a city, a county, a state—a planar projection with geometry is faster and supports more functions. If the data is global or continental, geography avoids the projection complexity and produces accurate great-circle distances . The geography type has fewer functions and they are slower to execute, because the spherical calculations are more expensive than the Cartesian ones.

b. The spatial functions and the GiST index

The spatial functions are the vocabulary of the queries. The most common are the measurement functions (ST_Distance, ST_Area, ST_Length), the relationship predicates (ST_Contains, ST_Intersects, ST_Within, ST_DWithin), and the constructor functions (ST_Point, ST_GeomFromText, ST_MakePolygon).

-- Create a GiST index on the geometry column
-- The index accelerates the bounding-box operators
CREATE INDEX idx_places_geom ON places USING GiST (geom);

-- Find the places within 500 meters of a point
-- ST_DWithin is index-aware: it uses the GiST index for the coarse filter
-- The distance is in meters because the column is geography
SELECT name
FROM cities
WHERE ST_DWithin(
  geog,
  ST_SetSRID(ST_MakePoint(-74.006, 40.7128), 4326)::geography,
  500
);

-- The same query using geometry with a projected SRID
-- The SRID 3857 uses meters, so the distance is in meters
SELECT name
FROM places
WHERE ST_DWithin(
  geom,
  ST_SetSRID(ST_MakePoint(-74.006, 40.7128), 3857),
  500
);

The ST_DWithin function is the index-aware form of “is this shape within a distance.” It uses the index to narrow the candidates to those whose bounding boxes are within the distance, then evaluates the exact distance on the survivors . The ST_Distance function computes the exact distance and does not use the index when it appears in a WHERE clause, which is why the guidance is to use ST_DWithin for the filter and ST_Distance only for the final sort or the precise measurement.

The spatial join is the pattern that combines two tables using a spatial relationship rather than a shared key. The PostGIS documentation uses the example of joining roads to municipalities with ST_Contains to compute the length of roads within each municipality .

-- A spatial join: for each municipality, sum the length of the roads it contains
-- ST_Contains is the join condition
SELECT m.name, sum(ST_Length(r.geom)) / 1000 AS roads_km
FROM bc_roads r
JOIN bc_municipality m ON ST_Contains(m.geom, r.geom)
GROUP BY m.name
ORDER BY roads_km DESC;

The join condition is a spatial predicate, not an equality on a foreign key. The GiST index on both geometry columns is what makes the join practical: without it, the predicate evaluates the containment check for every pair of rows .

c. The other engines and their spatial extensions

PostGIS is the most complete spatial database, but it is not the only one. The other engines implement subsets of the same ideas with different trade-offs.

SQL Server has two spatial types, geometry and geography, implemented as .NET CLR types. The geometry type is planar, and the geography type is round-earth . The spatial functions use the ST prefix and follow the OGC conventions, and the spatial index is built on a different internal structure than GiST but serves the same purpose.

MySQL 8.0 has spatial types (POINT, LINESTRING, POLYGON) and the ST_ functions. The SRID is part of the geometry value, and the ST_Distance_Sphere function computes great-circle distances . The spatial index in MySQL is an R-tree, and the MBRContains function uses it for the coarse filter. The axis order in MySQL is a known trap: the ST_GeomFromText function with SRID 4326 defaults to latitude/longitude order, while the POINT() constructor uses longitude/latitude, and swapping them silently produces wrong results .

SQLite has SpatiaLite, a loadable extension that adds spatial types and functions. It is the lightweight option: a single file database, no server, and OGC-compliant geometry support . It is not as complete as PostGIS, but for a mobile app or an embedded system it is the practical choice.

Complete Example Session

The following session creates a spatial table, inserts geometries, creates a GiST index, and runs a proximity query and a spatial join.

-- ============================================
-- PART 1: THE TABLE AND THE INDEX
-- ============================================
CREATE TABLE restaurants (
  id   serial PRIMARY KEY,
  name text,
  location geography(Point)
);

CREATE INDEX idx_restaurants_location ON restaurants USING GiST (location);

-- ============================================
-- PART 2: INSERT GEOMETRIES
-- ============================================
-- Insert three restaurants with WGS 84 coordinates
INSERT INTO restaurants (name, location) VALUES
  ('Pizza Place', 'SRID=4326;POINT(-74.006 40.7128)'),
  ('Sushi Bar',   'SRID=4326;POINT(-74.005 40.7130)'),
  ('Taco Stand',  'SRID=4326;POINT(-74.010 40.7100)');

-- ============================================
-- PART 3: THE PROXIMITY QUERY
-- ============================================
-- Find restaurants within 500 meters of a point
-- ST_DWithin uses the GiST index
SELECT name
FROM restaurants
WHERE ST_DWithin(
  location,
  'SRID=4326;POINT(-74.006 40.7128)'::geography,
  500
);

-- ============================================
-- PART 4: THE SPATIAL JOIN
-- ============================================
-- Create a delivery zone polygon
CREATE TABLE delivery_zones (
  id   serial PRIMARY KEY,
  name text,
  geom geometry(Polygon, 4326)
);

INSERT INTO delivery_zones (name, geom) VALUES
  ('Downtown', 'SRID=4326;POLYGON((-74.02 40.70, -74.00 40.70, -74.00 40.72, -74.02 40.72, -74.02 40.70))');

-- Find restaurants inside the Downtown zone
-- ST_Contains is the join condition
SELECT r.name
FROM restaurants r
JOIN delivery_zones z ON ST_Contains(z.geom, r.location::geometry)
WHERE z.name = 'Downtown';

The four parts cover the table and index, the inserts, the proximity query with ST_DWithin, and the spatial join with ST_Contains. What the session demonstrates is that the index is the GiST index, the proximity query uses the index-aware function, and the join uses the spatial predicate as the join condition.

Quick Reference

PostGIS Types

Type	Coordinate System	Distance Units
`geometry`	Planar (projected)	Units of the SRID
`geography`	Spherical	Meters

Common Spatial Functions

Function	Purpose
`ST_Distance`	Exact distance
`ST_DWithin`	Within distance (index-aware)
`ST_Contains`	A contains B
`ST_Within`	A is within B
`ST_Intersects`	A and B intersect
`ST_Area`	Area of a polygon
`ST_Length`	Length of a line

Spatial Index

Index	Engine	Purpose
GiST	PostGIS	Bounding-box tree
SP-GiST	PostGIS	Space-partitioned tree
BRIN	PostGIS	Block-range summary
R-tree	MySQL	Bounding-box tree

SRID Values

SRID	System
4326	WGS 84 longitude/latitude
3857	Web Mercator (meters)
26910	NAD83 UTM zone 10N

Best Practices

✅ Do This:

-- Use ST_DWithin for the filter
WHERE ST_DWithin(location, point, 500)

-- Use the geography type for global data
location geography(Point)

-- Create a GiST index
CREATE INDEX ON places USING GiST (geom);

❌ Don’t Do This:

-- Use ST_Distance in WHERE without a prefilter
WHERE ST_Distance(location, point) < 500 -- full scan

-- Mix SRIDs in a query
ST_Distance(geom_4326, geom_3857) -- meaningless

-- Assume the axis order
ST_GeomFromText('POINT(lon lat)', 4326) -- might be lat/lon

Common Pitfalls

Pitfall	Why It Happens	Fix
Distance in degrees	Used geometry without a projected SRID	Use geography or a meter-based SRID
Index not used	Used ST_Distance in WHERE	Use ST_DWithin
Axis order wrong	ST_GeomFromText uses lat/lon for 4326	Check the engine’s convention
Slow spatial join	No GiST index on the join column	Create the index

Real-World Examples

1. Nearest restaurants

SELECT name FROM restaurants
WHERE ST_DWithin(location, point, 500);

2. Point in polygon

SELECT name FROM zones WHERE ST_Contains(geom, point);

3. Road length in a city

SELECT sum(ST_Length(r.geom)) FROM roads r
JOIN city c ON ST_Contains(c.geom, r.geom);

4. Create a point

ST_SetSRID(ST_MakePoint(-74.006, 40.7128), 4326)

5. Create a polygon from text

ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 0))', 4326)

6. Buffer around a point

ST_Buffer(point, 500)

7. Area of a polygon

ST_Area(geom)

8. Intersecting roads

SELECT * FROM roads WHERE ST_Intersects(geom, city_geom);

9. GiST index

CREATE INDEX ON places USING GiST (geom);

10. Geography distance

ST_Distance(geog1, geog2)

Visual

Spatial Query Flow

┌──────────────────────────────────────────────┐
│  Query: ST_DWithin(geom, point, 500)         │
│         │                                    │
│         ▼                                    │
│  GiST index: bounding-box filter             │
│  → candidate geometries                      │
│         │                                    │
│         ▼                                    │
│  Exact distance evaluation                   │
│  → surviving geometries                      │
└──────────────────────────────────────────────┘

Geometry vs Geography

┌──────────────────────────────────────────────┐
│  GEOMETRY (planar)                           │
│  → Cartesian distance                        │
│  → units of the SRID                         │
│  → faster, more functions                    │
│                                              │
│  GEOGRAPHY (spherical)                       │
│  → great-circle distance                     │
│  → meters                                    │
│  → slower, fewer functions                   │
└──────────────────────────────────────────────┘

Spatial Join

┌──────────────────────────────────────────────┐
│  Roads table          Municipalities table   │
│  ┌─────────┐          ┌─────────────────┐    │
│  │ road 1  │          │ municipality A  │    │
│  │ road 2  │          │ municipality B  │    │
│  └────┬────┘          └────────┬────────┘    │
│       │                        │             │
│       └────────┬───────────────┘             │
│                ▼                             │
│  ST_Contains(m.geom, r.geom)                 │
│  → roads grouped by municipality             │
└──────────────────────────────────────────────┘

Summary

Item	Value
Spatial extension	PostGIS for PostgreSQL
Planar type	`geometry`
Spherical type	`geography`
Coordinate system ID	SRID
Default geographic SRID	4326
Index type	GiST
Proximity function	`ST_DWithin`
Containment predicate	`ST_Contains`
Spatial join	Join on a spatial predicate

Key takeaways:

PostGIS adds spatial types, functions, and indexing to PostgreSQL. The geometry type is planar, and the geography type is spherical; the choice determines whether distances are Cartesian or great-circle .
The SRID identifies the coordinate system. Without it, the distance and area calculations are meaningless. The type modifier geometry(Point, 4326) enforces the type and the SRID at insert time .
The GiST index is the spatial index. It indexes bounding boxes and descends only into the candidates that can match. The index-aware functions like ST_DWithin use it; ST_Distance in a WHERE clause does not .
ST_DWithin is the correct proximity filter. It uses the index for the coarse filter and the exact distance for the refinement. ST_Distance is for the final sort or the exact measurement .
The spatial join uses a predicate as the join condition. ST_Contains(m.geom, r.geom) joins the roads to the municipalities that contain them, and the GiST index makes the predicate practical .
SQL Server, MySQL, and SQLite have spatial extensions. SQL Server has geometry and geography, MySQL has ST_ functions and an R-tree, and SQLite has SpatiaLite. Each is a subset of PostGIS’s capability with different trade-offs .

Remember: Spatial data processing is not a column type; it is a set of operations, a coordinate system, and an index. The geometry type stores the shapes, the SRID defines the coordinate system, the GiST index makes the queries fast, and the ST_ functions answer the questions. The choice between geometry and geography is the choice between a flat map and a round Earth, and the distance unit is the consequence of that choice. The PostGIS documentation is the reference for the function semantics, and the GiST index is the reference for the performance.

Was this helpful?

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!