Geospatial Databases and Queries: Complete Guide

In this tutorial, you'll learn about Geospatial Databases and Queries: Complete Guide. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

Geospatial databases extend traditional database systems with spatial data types and functions -- enabling storage of points, lines, and polygons, execution of proximity queries, geofencing, spatial joins, and coordinate system transformations for location-based applications.

What You'll Learn

You will install and configure PostGIS, store and index geospatial data using geometry and geography types, execute proximity and containment queries with ST_ functions, convert between coordinate systems (SRID), and build location-based features.

Why Geospatial Databases Matter

Location data is everywhere. Doda Browser stores user locations for nearby place recommendations. Without spatial indexes, a proximity query scanning all locations sequentially takes 5 seconds. With PostGIS and GIST indexes, the same query completes in 5ms.

Geospatial Learning Path

flowchart LR
  A[PostgreSQL] --> B[PostGIS Extension]
  B --> C[Geospatial Queries]
  C:::current
  classDef current fill:#f90,color:#fff,stroke:#333,stroke-width:2px

PostGIS Installation

# Ubuntu/Debian
sudo apt-get install postgis postgresql-16-postgis-3

# Connect to database and enable extension
psql -U postgres -d mydb

-- Create the extension
CREATE EXTENSION IF NOT EXISTS postgis;

-- Verify installation
SELECT postgis_full_version();

Expected output:

POSTGIS="3.4.1" [EXTENSION] PGSQL="160" GEOS="3.12.0" PROJ="9.2.0"

Spatial Data Types

Creating Spatial Tables

-- Table with GEOMETRY type (projected coordinates)
CREATE TABLE venues (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    location GEOMETRY(Point, 4326),  -- SRID 4326 = WGS84 (GPS)
    capacity INT
);

-- Table with GEOGRAPHY type (for distance calculations on globe)
CREATE TABLE places (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    location GEOGRAPHY(Point, 4326),  -- Use for ST_DWithin with meters
    category VARCHAR(50)
);

Spatial Indexes

-- GIST index for spatial queries
CREATE INDEX idx_venues_location ON venues USING GIST (location);
CREATE INDEX idx_places_location ON places USING GIST (location);

Without a GIST index, every spatial query performs a full table scan. With the index, PostgreSQL uses R-tree index for fast bounding-box searches.

Inserting Spatial Data

-- Insert with ST_GeomFromText (Well-Known Text)
INSERT INTO venues (name, location, capacity)
VALUES (
    'DodaTech HQ',
    ST_GeomFromText('POINT(-73.9857 40.7484)', 4326),  -- Longitude, Latitude
    200
);

-- Insert with ST_MakePoint
INSERT INTO places (name, location, category)
VALUES (
    'Central Park',
    ST_MakePoint(-73.9654, 40.7829)::GEOGRAPHY,  -- Lon, Lat order!
    'park'
);

Important: PostGIS uses longitude-latitude (X-Y) order, not latitude-longitude.

Geospatial Query Functions

Proximity (Nearby Points)

-- Find venues within 1km of a point (using GEOGRAPHY)
SELECT id, name, 
       ST_Distance(location, ST_MakePoint(-73.9857, 40.7484)::GEOGRAPHY) AS distance_m
FROM places
WHERE ST_DWithin(
    location,
    ST_MakePoint(-73.9857, 40.7484)::GEOGRAPHY,
    1000  -- 1km in meters
)
ORDER BY distance_m;

Distance Calculation

-- Distance between two points (returns meters for GEOGRAPHY)
SELECT ST_Distance(
    ST_MakePoint(-73.9857, 40.7484)::GEOGRAPHY,    -- NYC
    ST_MakePoint(-118.2437, 34.0522)::GEOGRAPHY     -- LA
) AS distance_meters;

Expected output: ~3,944,000 meters (3,944 km)

Point-in-Polygon

-- Find venues within a polygon (neighborhood boundary)
SELECT v.name
FROM venues v
JOIN neighborhoods n ON ST_Within(v.location, n.boundary)
WHERE n.name = 'Manhattan';

-- Check if a point is in a polygon
SELECT ST_Within(
    ST_GeomFromText('POINT(-73.9857 40.7484)', 4326),
    ST_GeomFromText('POLYGON((-74.0 40.7, -73.9 40.7, -73.9 40.8, -74.0 40.8, -74.0 40.7))', 4326)
) AS is_inside;

Finding Nearest Neighbors

-- K-Nearest Neighbors (KNN) using <-> operator
SELECT id, name,
       location <-> ST_MakePoint(-73.9857, 40.7484)::GEOMETRY AS distance
FROM venues
ORDER BY location <-> ST_MakePoint(-73.9857, 40.7484)::GEOMETRY
LIMIT 5;

The <-> operator uses the GIST index for distance ordering without calculating exact distances for every row.

Coordinate Systems (SRID)

-- Transform between SRIDs
SELECT ST_Transform(
    ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326),  -- WGS84 (GPS)
    3857  -- Web Mercator
) AS web_mercator_point;

-- Calculate distance in meters using geometry with projection
SELECT ST_Distance(
    ST_Transform(ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326), 3857),
    ST_Transform(ST_SetSRID(ST_MakePoint(-118.2437, 34.0522), 4326), 3857)
) AS distance_meters;

Geofencing with PostGIS

Geofencing detects when a point enters or exits a predefined boundary.

-- Create geofence zones
CREATE TABLE geofences (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    zone GEOMETRY(Polygon, 4326)
);

CREATE INDEX idx_geofences_zone ON geofences USING GIST (zone);

-- Check which geofences contain a given point
SELECT g.name
FROM geofences g
WHERE ST_Contains(
    g.zone,
    ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)
);

Spatial Joins

-- Find all venues within 500m of each subway station
SELECT v.name AS venue, s.name AS station,
       ST_Distance(v.location::GEOGRAPHY, s.location::GEOGRAPHY) AS distance_m
FROM venues v
JOIN subway_stations s
  ON ST_DWithin(v.location::GEOGRAPHY, s.location::GEOGRAPHY, 500)
ORDER BY distance_m;

Common Geospatial Errors

1. Lat/Lon vs Lon/Lat Confusion

PostGIS uses longitude-first (X, Y) order. ST_MakePoint(-73.9857, 40.7484) is (lon, lat). Transposing them returns points in the wrong ocean.

2. Mixing GEOMETRY and GEOGRAPHY Without Understanding

GEOMETRY assumes flat coordinates (degrees treated as equal units). GEOGRAPHY uses the spheroid for accurate distance in meters. Use GEOGRAPHY for GPS coordinates with distance calculations.

3. Forgetting SRID Information

A point without SRID is meaningless. Always set SRID with ST_SetSRID() or specify it in the column definition.

4. No Spatial Index

Spatial queries without a GIST index perform full table scans. A point-in-polygon query on 1M points without an index takes minutes instead of milliseconds.

5. Using ST_Distance for Filtering Instead of ST_DWithin

ST_Distance calculates exact distance for every row. ST_DWithin uses the index for bounding-box pre-filtering and is much faster.

6. Incorrect Distance Units

GEOMETRY distances are in degrees (1 degree ~ 111km). GEOGRAPHY distances are in meters. Check which type your column uses.

7. Not Validating Geometry

Invalid geometry (self-intersecting polygons, duplicate points) causes query errors. Use ST_IsValid() and ST_MakeValid() to clean data.

-- Validate and fix geometry
SELECT ST_IsValidReason(zone) FROM geofences WHERE NOT ST_IsValid(zone);
UPDATE geofences SET zone = ST_MakeValid(zone) WHERE NOT ST_IsValid(zone);

Practice Questions

1. What is the difference between GEOMETRY and GEOGRAPHY types?

GEOMETRY uses planar coordinates (cartesian math). GEOGRAPHY uses spherical coordinates (lat/lon) with accurate distance in meters. Use GEOGRAPHY for GPS data with distance calculations.

2. How does the KNN (<->) operator work?

The <-> operator returns the bounding-box distance between geometries. With a GIST index, it enables index-assisted nearest-neighbor search, avoiding full table scans.

3. What is SRID 4326?

WGS 84, the standard GPS coordinate system. Latitude and longitude in degrees. The most common SRID for global spatial data.

4. How do you find all points within 1km of a location?

Use ST_DWithin(location, target_point, 1000) with GEOGRAPHY type (meters) or project to a local coordinate system with GEOMETRY.

5. Challenge: Build a nearby venue finder.

Implement a query that returns the 10 closest coffee shops within 2km of a user's location, sorted by distance, with shop name and address. Answer:

SELECT name, address, 
       ST_Distance(location::GEOGRAPHY, ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)::GEOGRAPHY) AS distance_m
FROM coffee_shops
WHERE ST_DWithin(location::GEOGRAPHY, ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)::GEOGRAPHY, 2000)
ORDER BY location::GEOGRAPHY <-> ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)::GEOGRAPHY
LIMIT 10;

FAQ

Try It Yourself

Build a location-based query:

1. Install PostGIS extension
2. Create a table coffee_shops with a GEOGRAPHY(Point, 4326) column
3. Insert 10 sample coffee shop locations in your city
4. Add a GIST index on the location column
5. Write a query finding shops within 1km of a given point
6. Order by distance and limit to 5 results
7. Run EXPLAIN ANALYZE to verify index usage

What's Next

You have learned geospatial databases with PostGIS, spatial indexes, proximity queries, geofencing, and coordinate systems. Start by adding PostGIS to your PostgreSQL database and creating a spatial index on your location data today.