CREATE EXTENSION postgis;

-- Confirm the version
SELECT PostGIS_Full_Version();

Geometry vs. Geography

Geometry

-- Geometry column — coordinates are just numbers
CREATE TABLE parcels (
    id SERIAL PRIMARY KEY,
    name TEXT,
    boundary GEOMETRY(Polygon, 4326)
);

Geography

-- 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

aside positive Rule of thumb: if your data is lat/lon points and you mainly 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

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. Getting this backwards puts your points in the ocean.

Line Data

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

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

-- 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;

-- 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 ~863,476 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

-- 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;

aside positive The <-> operator performs an index-assisted nearest-neighbor search. It's dramatically faster than computing all distances and sorting. Always use it with ORDER BY ... LIMIT for KNN queries.

Spatial Indexing

GiST Indexes

-- 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)

-- BRIN index — compact but requires physically sorted data
CREATE INDEX idx_points_location_brin
ON large_point_table USING BRIN (geom);

Index Performance Tips

EXPLAIN ANALYZE
SELECT name FROM coffee_shops
WHERE ST_DWithin(location, ST_Point(-122.68, 45.52)::geography, 5000);

Common Patterns

Geofencing

-- 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

-- Cluster coffee shops within 500m of each other
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

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

Routing with pgRouting

Core Algorithms

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

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

Service Areas (Isochrones)

-- 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

Serving Spatial Data: pg_tileserv and pg_featureserv

pg_tileserv — Vector Tiles from PostGIS

-- 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

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

Advanced Spatial Topics

H3 Hexagonal Indexing

-- Requires h3-pg extension
CREATE EXTENSION h3;

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

Topology

CREATE EXTENSION postgis_topology;

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

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

3D and SFCGAL

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

-- 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);

The PostGIS Community

PostGIS Day

PostGIS on Snowflake Postgres

aside positive PostGIS is available on Snowflake Postgres. Enable it with CREATE EXTENSION postgis;. See the Snowflake Postgres extensions documentation for version details.

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

-- Verify
SELECT PostGIS_Version();

Conclusion

Related Resources

---

---

---

---

---

---

---

---