Expedition. Free, on-demand.

Technical tracks for practitioners, outcomes for leaders.

PostGIS is the spatial extension for Postgres. It adds geographic data types, spatial indexing, and hundreds of functions for querying location data. It's the standard other spatial databases get measured against.

With PostGIS you can:

  • Store points, lines, polygons, and complex geometries
  • Calculate distances, areas, intersections, and containment
  • Find nearest neighbors quickly with spatial indexes
  • Transform between coordinate systems
  • Process raster (grid) data alongside vector geometries

PostGIS powers mapping applications, logistics platforms, delivery routing, land-use analysis, and anything else that asks "what's near what?"

CREATE EXTENSION postgis;

-- Confirm the version (current major release is PostGIS 3.6)
SELECT PostGIS_Full_Version();

Geometry vs. Geography

PostGIS has two spatial types, and picking the right one matters.

Geometry

Geometry works on a flat Cartesian plane. Coordinates are plain X/Y numbers with no awareness of the Earth's curvature.

  • Fast calculations (simple math)
  • Results are in the coordinate system's units (degrees for EPSG:4326, usually meters for projected systems)
  • Best for small areas where curvature doesn't matter, projected coordinate systems, or when you need the full set of PostGIS functions
-- Geometry column: coordinates are just numbers
CREATE TABLE parcels (
    id SERIAL PRIMARY KEY,
    name TEXT,
    boundary GEOMETRY(Polygon, 4326)
);

Geography

Geography works on a spheroid. It knows the Earth is round and calculates true great-circle distances.

  • Results are in meters
  • More accurate over large distances
  • Fewer functions available (but the common ones work)
  • Best for points spread across the globe, or when you want real-world distances in meters
-- Geography column: calculations account for Earth's curvature
CREATE TABLE airports (
    id SERIAL PRIMARY KEY,
    code CHAR(3),
    name TEXT,
    location GEOGRAPHY(Point, 4326)
);

When to Use Which

ScenarioUse
Points spread across the globeGeography
Need distances in metersGeography
Small area (single city/state)Either works
Need advanced functions (ST_Union, ST_Difference)Geometry
Working with a projected coordinate system (UTM, State Plane)Geometry
Storing routes/polygons for large regionsGeometry with ST_Transform

Rule of thumb: if your data is lat/lon points and you mostly need distance and containment queries, use Geography. If you need the full spatial toolkit or a projected coordinate system, use Geometry.

Creating Spatial Tables

Point Data

The most common case is storing locations:

CREATE TABLE coffee_shops (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    address TEXT,
    location GEOGRAPHY(Point, 4326)
);

-- Insert using longitude, latitude (note: lon first, lat second!)
INSERT INTO coffee_shops (name, address, location) VALUES
    ('Blue Bottle', '315 Linden St, SF', ST_Point(-122.4194, 37.7749)),
    ('Stumptown', '128 SW 3rd Ave, Portland', ST_Point(-122.6587, 45.5155)),
    ('Intelligentsia', '53 E Randolph St, Chicago', ST_Point(-87.6298, 41.8781));

aside negative A common gotcha: PostGIS uses longitude, latitude order (X, Y), not the lat, lon order you see on Google Maps. Get it backwards and your points end up in the ocean.

Line Data

For roads, routes, or paths:

CREATE TABLE trails (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    difficulty TEXT,
    path GEOMETRY(LineString, 4326)
);

INSERT INTO trails (name, difficulty, path) VALUES
    ('Forest Loop', 'moderate',
     ST_GeomFromText('LINESTRING(-122.68 45.52, -122.69 45.53, -122.68 45.54)', 4326));

Polygon Data

For boundaries, zones, or areas:

CREATE TABLE neighborhoods (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    boundary GEOMETRY(Polygon, 4326)
);

INSERT INTO neighborhoods (name, boundary) VALUES
    ('Pearl District',
     ST_GeomFromText('POLYGON((-122.685 45.525, -122.675 45.525, -122.675 45.530, -122.685 45.530, -122.685 45.525))', 4326));

SRID and Coordinate Reference Systems

Every spatial column has an SRID (Spatial Reference System Identifier) that tells PostGIS how to interpret the coordinates.

The SRIDs you'll see most:

SRIDNameUnitsUse Case
4326WGS 84DegreesGPS data, web mapping, global datasets
3857Web MercatorMetersWeb map tiles (Google Maps, Mapbox)
32610UTM Zone 10NMetersPacific Northwest US
5070NAD83 / Conus AlbersMetersContinental US equal-area
-- Check what SRIDs are available
SELECT srid, proj4text FROM spatial_ref_sys WHERE srid IN (4326, 3857, 32610);

-- Transform between coordinate systems
SELECT ST_Transform(
    ST_SetSRID(ST_Point(-122.4194, 37.7749), 4326),
    3857
) AS mercator_point;

Always specify the SRID when you create columns. PostGIS will then reject data in the wrong SRID and transform correctly. A typed column like GEOGRAPHY(Point, 4326) already enforces this. If you have an untyped column, you can add a check:

-- This enforces SRID 4326 on all data in the column
ALTER TABLE coffee_shops
    ADD CONSTRAINT enforce_srid
    CHECK (ST_SRID(location::geometry) = 4326);

Core Spatial Functions

Distance

-- Distance between two points (geography = meters)
SELECT ST_Distance(
    ST_Point(-122.4194, 37.7749)::geography,
    ST_Point(-122.6587, 45.5155)::geography
) AS distance_meters;
-- Returns roughly 860,000 meters (SF to Portland)

-- Find all shops within 2km of a location
SELECT name, round(ST_Distance(
    location, ST_Point(-122.4194, 37.7749)::geography
)::numeric) AS distance_m
FROM coffee_shops
WHERE ST_DWithin(
    location,
    ST_Point(-122.4194, 37.7749)::geography,
    2000  -- 2km radius
)
ORDER BY distance_m;

Containment and Intersection

-- Is a point inside a polygon?
SELECT ST_Contains(
    boundary,
    ST_SetSRID(ST_Point(-122.681, 45.527), 4326)
) AS is_inside
FROM neighborhoods
WHERE name = 'Pearl District';

-- Find all coffee shops in a neighborhood
SELECT c.name AS shop, n.name AS neighborhood
FROM coffee_shops c
JOIN neighborhoods n
    ON ST_Contains(n.boundary, c.location::geometry);

-- Do two geometries overlap?
SELECT ST_Intersects(
    a.boundary, b.boundary
) AS overlaps
FROM neighborhoods a, neighborhoods b
WHERE a.name = 'Pearl District' AND b.name = 'Old Town';

Area and Length

-- Area of a polygon (geography = square meters)
SELECT name, round(ST_Area(boundary::geography)::numeric) AS area_sqm
FROM neighborhoods;

-- Length of a line (geography = meters)
SELECT name, round(ST_Length(path::geography)::numeric) AS length_m
FROM trails;

Buffer and Envelope

-- Create a 1km buffer zone around a point
SELECT ST_Buffer(
    ST_Point(-122.4194, 37.7749)::geography,
    1000  -- 1km
) AS buffer_zone;

-- Bounding box of a geometry
SELECT ST_Envelope(boundary) AS bbox
FROM neighborhoods
WHERE name = 'Pearl District';

Nearest Neighbor

The K-nearest-neighbor (KNN) operator uses the spatial index for fast lookups:

-- Find 5 nearest coffee shops to a location (uses index!)
SELECT name, round(ST_Distance(
    location, ST_Point(-122.68, 45.52)::geography
)::numeric) AS distance_m
FROM coffee_shops
ORDER BY location <-> ST_Point(-122.68, 45.52)::geography
LIMIT 5;

The <-> operator does an index-assisted nearest-neighbor search. It's much faster than computing every distance and sorting. Use it with ORDER BY ... LIMIT for KNN queries.

Spatial Indexing

Without an index, every spatial query is a sequential scan that compares your query geometry against every row. With millions of features, that gets painful fast.

GiST Indexes

GiST (Generalized Search Tree) is the standard spatial index. It uses bounding boxes to quickly throw out rows that can't possibly match.

-- Create a spatial index on the geography column
CREATE INDEX idx_shops_location
ON coffee_shops USING GIST (location);

-- For geometry columns
CREATE INDEX idx_neighborhoods_boundary
ON neighborhoods USING GIST (boundary);

BRIN Indexes (for sorted data)

If your spatial data has a natural physical ordering (say, it was loaded in geographic clusters), a BRIN index uses far less space:

-- BRIN index: compact, but only useful when data is physically sorted
CREATE INDEX idx_points_location_brin
ON large_point_table USING BRIN (geom);

Index Performance Tips

  • Run ANALYZE after bulk loads so the planner has fresh statistics and picks the index
  • Use ST_DWithin instead of ST_Distance < X. The former can use the index; the latter can't
  • For KNN queries, use the <-> operator, which the GiST index accelerates
  • Check with EXPLAIN ANALYZE that your queries actually use the index
EXPLAIN ANALYZE
SELECT name FROM coffee_shops
WHERE ST_DWithin(location, ST_Point(-122.68, 45.52)::geography, 5000);

Look for an Index Scan or Bitmap Index Scan on your GiST index. A Seq Scan on a big table means something's off.

Common Patterns

Geofencing

Figure out which zone a point falls in. Handy for pricing zones, delivery areas, or jurisdictions:

-- Which delivery zone is this address in?
SELECT z.name AS zone, z.delivery_fee
FROM delivery_zones z
WHERE ST_Contains(z.boundary, ST_SetSRID(ST_Point(-122.43, 37.77), 4326));

Clustering Points

Group nearby points together:

-- Cluster coffee shops near each other
-- eps is in the units of the geometry (degrees for 4326);
-- 0.005 degrees is roughly 500m in latitude
SELECT
    ST_ClusterDBSCAN(location::geometry, eps := 0.005, minpoints := 2)
        OVER () AS cluster_id,
    name
FROM coffee_shops;

Route Length and Bounding

-- Total length of a multi-segment route
SELECT ST_Length(
    ST_MakeLine(ARRAY[
        ST_Point(-122.68, 45.52),
        ST_Point(-122.69, 45.53),
        ST_Point(-122.67, 45.54)
    ])::geography
) AS route_length_m;

Simplifying Complex Geometries

When you're rendering on a map, you don't need millimeter precision:

-- Simplify a complex boundary for display
SELECT ST_Simplify(boundary, 0.001) AS simplified
FROM neighborhoods;

Routing with pgRouting

pgRouting extends PostGIS with routing and network analysis. It adds graph algorithms on top of your spatial data, so a road network stored in PostGIS becomes a routable graph without leaving the database.

Core Algorithms

AlgorithmFunctionUse Case
Dijkstrapgr_dijkstra()Shortest path between two nodes
A*pgr_aStar()Heuristic shortest path (faster for large graphs)
K-Shortest Pathspgr_ksp()Multiple alternative routes
Driving Distancepgr_drivingDistance()Service areas / isochrones
TSPpgr_TSP()Optimal visit order for multiple stops

Basic Shortest Path

-- Enable pgRouting
CREATE EXTENSION pgrouting;

-- Find shortest path between nodes 1 and 42
SELECT seq, node, edge, cost, agg_cost
FROM pgr_dijkstra(
    'SELECT id, source, target, cost, reverse_cost FROM roads',
    1,    -- start node
    42,   -- end node
    directed := true
);

Building a Routable Network from OpenStreetMap

The osm2pgrouting tool imports OpenStreetMap data into a pgRouting-compatible schema:

osm2pgrouting \
    --file portland.osm \
    --dbname routing_db \
    --conf mapconfig.xml

Once it's loaded, your road network has source and target node IDs for pgRouting's algorithms to traverse.

Service Areas (Isochrones)

Calculate everything reachable within a cost threshold, for "how far can I drive in 10 minutes?" style questions:

-- All nodes reachable within cost 15 from node 1
SELECT node, edge, cost, agg_cost
FROM pgr_drivingDistance(
    'SELECT id, source, target, cost FROM roads',
    1,      -- start node
    15.0    -- max cost
);

When to Use pgRouting vs. PostGIS Alone

PostGIS gives you straight-line distances and spatial relationships. pgRouting adds:

  • Path traversal along a network (roads follow curves, not straight lines)
  • Cost modeling (traffic, speed limits, road class)
  • Turn restrictions
  • One-way streets
  • Vehicle constraints (weight limits, road access)

If your question is "what's the shortest path along the roads?" rather than "what's the straight-line distance?", you want pgRouting.

Serving Spatial Data: pg_tileserv and pg_featureserv

Crunchy Data built two lightweight Go microservices that turn PostGIS into a spatial web backend with no extra middleware.

pg_tileserv: Vector Tiles from PostGIS

pg_tileserv is a thin tile server. It takes HTTP tile requests, runs SQL against PostGIS, and returns Mapbox Vector Tiles (MVT). Any table with a geometry column gets published as a tile layer automatically.

It serves MVT tiles to map clients like Mapbox GL, MapLibre, and Leaflet. Table layers come from auto-discovering geometry columns, and function layers let you expose your own SQL functions as dynamic tile sources. Access control uses the database's existing roles and grants, and the server itself is stateless.

-- Create a function layer for pg_tileserv
CREATE OR REPLACE FUNCTION public.points_in_radius(
    z integer, x integer, y integer,
    center_lon float8 DEFAULT -122.4,
    center_lat float8 DEFAULT 37.8,
    radius float8 DEFAULT 1000
)
RETURNS bytea AS $$
DECLARE
    result bytea;
    bounds geometry;
BEGIN
    bounds := ST_TileEnvelope(z, x, y);
    SELECT ST_AsMVT(q, 'points_in_radius', 4096, 'geom')
    INTO result
    FROM (
        SELECT ST_AsMVTGeom(geom, bounds) AS geom, name
        FROM my_points
        WHERE ST_DWithin(geom::geography,
              ST_Point(center_lon, center_lat)::geography, radius)
          AND ST_Intersects(geom, bounds)
    ) q;
    RETURN result;
END;
$$ LANGUAGE plpgsql STABLE;

pg_featureserv: GeoJSON Features via REST

pg_featureserv implements the OGC API - Features standard and exposes PostGIS tables as GeoJSON collections over HTTP. You get a REST API that returns GeoJSON, CQL filtering for attribute and spatial queries, and the ability to publish your own PostGIS functions as endpoints. Configuration is minimal: point it at a database and it discovers the tables.

GET /collections/coffee_shops/items?filter=name LIKE 'Blue%'
GET /collections/coffee_shops/items?bbox=-122.5,37.7,-122.3,37.9
GET /functions/points_in_radius/items?center_lon=-122.4&center_lat=37.8&radius=500

Architecture Philosophy

Both services are database-centric. The database is the source of truth and the coordination layer. The services are stateless HTTP frontends: no business logic, no cached state, and all spatial processing happens in PostGIS. Change your data model, add a column, or create a new function, and it shows up in the API without a redeploy.

Advanced Spatial Topics

H3 Hexagonal Indexing

H3 (Uber's hierarchical hexagonal grid system) pairs well with PostGIS for rolling point data up into uniform cells:

-- Requires the h3-pg extensions; h3_postgis adds PostGIS type support
CREATE EXTENSION h3;
CREATE EXTENSION h3_postgis;

-- Index points into H3 cells at resolution 9
SELECT h3_lat_lng_to_cell(location::geometry, 9) AS h3_index,
       count(*) AS point_count
FROM coffee_shops
GROUP BY 1;

H3 cells are good for heatmaps, density analysis, and consistent spatial binning without the distortion square grids get at high latitudes.

Topology

PostGIS Topology stores data as faces, edges, and nodes with shared boundaries, which lets the database enforce topological rules like no gaps and no overlaps:

CREATE EXTENSION postgis_topology;

-- Create a topology with roughly 1-meter tolerance (in degrees)
SELECT topology.CreateTopology('city_topo', 4326, 0.00001);

-- Add a layer
SELECT topology.AddTopoGeometryColumn('city_topo', 'public', 'neighborhoods', 'topo_geom', 'POLYGON');

Use topology for cadastral data, administrative boundaries, and any dataset where shared borders have to stay consistent.

3D and SFCGAL

PostGIS supports 3D operations through the SFCGAL library:

CREATE EXTENSION postgis_sfcgal;

-- 3D intersection
SELECT ST_3DIntersection(geom_a, geom_b) FROM buildings;

-- Extrude a 2D polygon to 3D
SELECT ST_Extrude(boundary, 0, 0, building_height)
FROM footprints;

Raster Data

PostGIS can store and analyze gridded (raster) data alongside vector geometries. Since PostGIS 3.0, raster support lives in its own postgis_raster extension:

CREATE EXTENSION postgis_raster;

-- Query raster values at point locations
SELECT ST_Value(r.rast, p.geom) AS elevation
FROM elevation_raster r
JOIN trail_points p ON ST_Intersects(r.rast, p.geom);

This is how you do elevation analysis, land cover classification, or combine satellite imagery with vector boundaries.

The PostGIS Community

PostGIS is governed by a Project Steering Committee (PSC) that sets the project's direction and release cycle. It's an Open Source Geospatial Foundation (OSGeo) project and is run separately from Postgres itself. Paul Ramsey, one of the original PostGIS developers and a PSC member, is a Snowflake engineer.

PostGIS Day

PostGIS Day is a free online event held every November, the day after GIS Day, hosted here at Snowflake. Developers, users, and enthusiasts get together for a full day of 30-minute talks, from beginner tutorials to advanced spatial work.

PostGIS Day 2025 was hosted by Paul Ramsey and Elizabeth Christensen and supported by Snowflake. Talks covered AI agents writing spatial SQL, lakehouse architectures with GeoParquet, large disaster response platforms, and high school students building isochrone analysis for emergency services. Read the full PostGIS Day 2025 recap on the Snowflake blog.

PostGIS on Snowflake Postgres

PostGIS is available on Snowflake Postgres, along with postgis_raster, postgis_topology, postgis_sfcgal, postgis_tiger_geocoder, pgRouting, and H3. Enable it with CREATE EXTENSION postgis;. See the Snowflake Postgres extensions documentation for the full list.

On Snowflake Postgres, PostGIS works like it does on any other Postgres deployment. The extension is already installed on the server, so you enable it, build your spatial tables, and query them.

-- Enable PostGIS on Snowflake Postgres
CREATE EXTENSION IF NOT EXISTS postgis;

-- Verify
SELECT PostGIS_Version();

Conclusion

PostGIS is the most mature spatial extension around. It handles everything from simple "find nearby" queries to complex geometric operations across millions of features. Between the spatial types, the function library, and GiST indexing, you rarely need a separate GIS system. Postgres can be your spatial database.

Related Resources

Complete function reference and usage guides for all PostGIS versions.


Routing algorithms, network analysis, and shortest-path functions for PostGIS.


Lightweight vector tile server that serves Mapbox Vector Tiles directly from PostGIS.


RESTful GeoJSON feature server implementing OGC API - Features for PostGIS.


Annual free online event for the PostGIS community, with talks and demos.


Full playlist of PostGIS Day 2025 talks hosted by Snowflake.


Previous years' PostGIS Day recordings from Crunchy Data.


Interactive browser-based playground for learning PostGIS fundamentals hands-on.


Tutorials on spatial performance, tile serving, raster analysis, and more.

Updated Oct 27, 2026

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances