PostGIS: Spatial Data in Postgres
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
| Scenario | Use |
|---|---|
| Points spread across the globe | Geography |
| Need distances in meters | Geography |
| 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 regions | Geometry 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:
| SRID | Name | Units | Use Case |
|---|---|---|---|
| 4326 | WGS 84 | Degrees | GPS data, web mapping, global datasets |
| 3857 | Web Mercator | Meters | Web map tiles (Google Maps, Mapbox) |
| 32610 | UTM Zone 10N | Meters | Pacific Northwest US |
| 5070 | NAD83 / Conus Albers | Meters | Continental 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 withORDER BY ... LIMITfor 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
ANALYZEafter bulk loads so the planner has fresh statistics and picks the index - Use
ST_DWithininstead ofST_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 ANALYZEthat 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
| Algorithm | Function | Use Case |
|---|---|---|
| Dijkstra | pgr_dijkstra() | Shortest path between two nodes |
| A* | pgr_aStar() | Heuristic shortest path (faster for large graphs) |
| K-Shortest Paths | pgr_ksp() | Multiple alternative routes |
| Driving Distance | pgr_drivingDistance() | Service areas / isochrones |
| TSP | pgr_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¢er_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.
- When: The Thursday after GIS Day in November, every year (2025 was the 7th annual event)
- Format: Free virtual drop-in via Zoom; talks are recorded and posted to YouTube
- Length: About 11 hours (afternoon in Europe through afternoon Pacific time)
- Website: postgisday.rocks
- Videos: PostGIS Day 2025 (Snowflake YouTube) | PostGIS Day archives (Crunchy Data YouTube)
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.
This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances