PostGIS: Spatial Data for PostgreSQL

If your application needs to answer questions like "what is nearby?", "which points fall inside this area?", or "how far is this location from that route?", and you already use PostgreSQL, PostGIS could be a great fit.

PostGIS is an extension for PostgreSQL that adds support for geographic and geometric data. In practice, that means you can store points, lines, and polygons in your database and query them with spatial functions and indexes instead of trying to bolt location logic onto plain latitude/longitude columns.

What Is PostGIS? ¶

PostGIS turns PostgreSQL into a spatial database.

Without PostGIS, you can still store latitude and longitude as numbers, but your database only sees them as ordinary values. It cannot natively understand shapes, distances, intersections, routes, or map boundaries.

With PostGIS, PostgreSQL gains:

• spatial types such as geometry and geography
• spatial indexes such as GiST for fast location queries
• functions like ST_DWithin, ST_Distance, ST_Contains, and ST_Intersects
• support for real spatial workflows such as nearest-neighbor search, clustering, routing corridors, and polygon checks

The geometry type is a general-purpose spatial type that can represent points, lines, and polygons in any coordinate system. The geography type is specifically designed for geodetic coordinates (latitude and longitude) and provides accurate distance calculations on the Earth's surface.

Common Use Cases ¶

Some common use cases for PostGIS include:

• location-based search and filtering (e.g. finding nearby stores or services)
• geofencing and region checks (e.g. triggering events when entering or leaving a zone)
• spatial analytics and clustering (e.g. grouping locations into territories or market segments)
• route and corridor analysis (e.g. finding points near a delivery route or trip path)
• asset tracking and logistics (e.g. monitoring fleet locations and proximity to geofences)
• GIS and mapping backends (e.g. powering spatial APIs and map layers)

When to Use PostGIS ¶

PostGIS is a strong choice when:

• you already use PostgreSQL
• your product depends on location-aware queries
• you need accurate distance, containment, or intersection logic
• your dataset is large enough that indexes and database-side computation matter. For small datasets, it may be simpler to do spatial logic in application code, but as your data grows, PostGIS's indexes and optimized functions can provide significant performance benefits.
• you want SQL-based spatial analytics without introducing a separate platform

In the following sections, we will explore several PostGIS features with Go code examples. The same SQL queries and PostGIS functionality can be used from any programming language that can connect to PostgreSQL.

Setup ¶

From Go, using PostGIS is no different from using PostgreSQL in general. The demo applications use the github.com/jackc/pgx/v5 driver and connect to PostgreSQL as usual. The main difference is that your queries use PostGIS-specific SQL functions and types.

To enable the PostGIS extension in your PostgreSQL database, you run the following SQL command:

CREATE EXTENSION IF NOT EXISTS postgis;

postgis.go

The following demo application assumes a stores table with the following schema, which includes both latitude/longitude columns and PostGIS geometry/geography columns for spatial queries.

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE IF NOT EXISTS stores (
  store_number TEXT PRIMARY KEY,
  country_code TEXT NOT NULL,
  ownership_type_code TEXT,
  schedule TEXT,
  slug TEXT,
  latitude DOUBLE PRECISION NOT NULL,
  longitude DOUBLE PRECISION NOT NULL,
  street_address_line1 TEXT,
  street_address_line2 TEXT,
  street_address_line3 TEXT,
  city TEXT,
  country_subdivision_code TEXT,
  postal_code TEXT,
  current_time_offset INTEGER,
  windows_time_zone_id TEXT,
  olson_time_zone_id TEXT,
  location geometry(Point, 4326) GENERATED ALWAYS AS (
    ST_Point(longitude, latitude, 4326)
  ) STORED,
  geog geography(Point, 4326) GENERATED ALWAYS AS (
    ST_Point(longitude, latitude, 4326)::geography
  ) STORED
);

CREATE INDEX IF NOT EXISTS stores_geog_idx ON stores USING GIST (geog);
CREATE INDEX IF NOT EXISTS stores_country_code_idx ON stores (country_code);
CREATE INDEX IF NOT EXISTS stores_city_idx ON stores (city);

postgis.go

geometry(Point, 4326) stores a point in the WGS 84 coordinate system. The GENERATED ALWAYS AS clause creates a computed column that automatically populates the location field from the latitude and longitude values. The same idea is used for the geog column, which stores the point as geography(Point, 4326). The value 4326 is the SRID (Spatial Reference System Identifier) for WGS 84, the standard coordinate system used by GPS data. See the PostGIS documentation on spatial reference systems for more details.

geometry is the more flexible spatial type. It can represent points, lines, and polygons in many coordinate systems and is often used for planar calculations. geography is designed for geodetic coordinates such as longitude and latitude and provides distance calculations on the Earth's surface that account for its curvature. In this example, both columns are generated from the same latitude and longitude values, but they support different kinds of queries.

This program is used to import Starbucks location data into the stores table from this public dataset. The import program uses normal SQL INSERT statements to populate the table, there is nothing special you need to do to insert data into PostGIS columns as long as you provide the correct geometry or geography values. In this case, the location and geog columns are generated automatically from the latitude and longitude, so the import program only needs to insert those base values.

Store locator and nearby search ¶

This is one of the most common use cases. Given a latitude and longitude, find the closest stores, restaurants, warehouses, ATMs, or service providers within a radius.

This demo executes the following SQL query, which finds stores within a certain distance of a given point and orders them by proximity.

Placeholders:

• $1 - latitude of the search point
• $2 - longitude of the search point
• $3 - search radius in meters
• $4 - limit on the number of results to return

ST_Distance calculates the distance between the store's geography point and the input point.

ST_DWithin filters stores that are within the specified radius. The ORDER BY clause sorts the results by distance, and LIMIT restricts the number of returned rows.

SELECT
  store_number,
  country_code,
  COALESCE(city, ''),
  COALESCE(street_address_line1, ''),
  latitude,
  longitude,
  ROUND(ST_Distance(geog, ST_Point($2, $1, 4326)::geography))::BIGINT AS distance_meters
FROM stores
WHERE ST_DWithin(geog, ST_Point($2, $1, 4326)::geography, $3)
ORDER BY ST_Distance(geog, ST_Point($2, $1, 4326)::geography)
LIMIT $4

postgis.go

The ST_Point($2, $1, 4326) function creates a point from the input longitude and latitude. Because supplying an SRID makes ST_Point return a geometry value, the query casts it to geography so it matches the geog column and uses geodetic distance calculations.

That distinction matters. geography measures distances in meters along the Earth's curved surface, while geometry treats coordinates as planar and measures in the units of the coordinate system, which for WGS 84 means degrees. That can be acceptable for very small distances, but it becomes increasingly inaccurate as distances grow. For a store locator that needs to find nearby locations within a practical radius, geography is usually the better choice.

PostGIS functions are not limited to radius-based searches. You can also use ST_Contains to find points that fall within a polygon (e.g., stores within a city boundary) or ST_Intersects to find points that overlap with a line or area (e.g., stores along a route). The spatial functions in PostGIS are versatile and can be combined in various ways to support complex location-based queries.

Delivery, trip, and route corridors ¶

Sometimes the question is not "what is near this point?" but "what is near this route?" For example:

• coffee shops along a drive
• repair depots near a delivery corridor
• available drivers near a trip path

This demo uses the following query to find stores that are within a certain distance of a route defined by two points (start and end). In the SQL, the route is first constructed in a CTE as a line between the start and end points.

As in the previous example, the ST_DWithin function is used to filter stores that are within the specified distance from the route, and ST_Distance is used to order the results by proximity to the route.

Placeholders:

• $1 - latitude of the route start point
• $2 - longitude of the route start point
• $3 - latitude of the route end point
• $4 - longitude of the route end point
• $5 - search radius in meters from the route
• $6 - limit on the number of results to return

WITH route AS (
  SELECT ST_MakeLine(
    ST_Point($2, $1, 4326),
    ST_Point($4, $3, 4326)
  )::geography AS geog
)
SELECT
  s.store_number,
  s.country_code,
  COALESCE(s.city, ''),
  COALESCE(s.street_address_line1, ''),
  s.latitude,
  s.longitude,
  ROUND(ST_Distance(s.geog, route.geog))::BIGINT AS distance_to_route_meters
FROM stores s
CROSS JOIN route
WHERE ST_DWithin(s.geog, route.geog, $5)
ORDER BY ST_Distance(s.geog, route.geog), s.store_number
LIMIT $6

postgis.go

The CROSS JOIN route joins every store row with the single row returned by the route CTE. Because the CTE produces exactly one route geometry, the join does not multiply the result set in any meaningful way. It simply makes route.geog available in both the WHERE clause for ST_DWithin and the SELECT list for ST_Distance, which is cleaner than repeating the same scalar subquery.

Market analysis and clustering ¶

If you have thousands of locations, you often want to group them into useful regions for planning, operations, or visualization.

• rough sales territories
• service coverage planning
• map summarization at lower zoom levels
• operational zoning

The following query creates K-means clusters of store locations within a specified country and calculates the centroid for each cluster.

ST_ClusterKMeans returns a cluster ID (0 to k-1) for each row based on the spatial proximity of the location geometries. The OVER () clause indicates that the clustering is applied to the entire result set.

The final SELECT statement groups the clustered rows by cluster_id, counts the number of stores in each cluster, and calculates a centroid using ST_Centroid and ST_Collect. The latitude and longitude of the centroid are extracted with ST_Y and ST_X, respectively, and rounded to five decimal places for readability. The results are ordered by cluster size, with the largest clusters first.

Placeholders:

• $1 - country code to cluster
• $2 - number of K-means clusters

WITH clustered AS (
  SELECT
    ST_ClusterKMeans(location, $2) OVER () AS cluster_id,
    location
  FROM stores
  WHERE country_code = $1
)
SELECT
  cluster_id,
  COUNT(*)::INT,
  ROUND(ST_Y(ST_Centroid(ST_Collect(location)))::numeric, 5)::DOUBLE PRECISION AS center_latitude,
  ROUND(ST_X(ST_Centroid(ST_Collect(location)))::numeric, 5)::DOUBLE PRECISION AS center_longitude
FROM clustered
GROUP BY cluster_id
ORDER BY COUNT(*) DESC, cluster_id

postgis.go

For this kind of calculation, the geometry type is often more appropriate than geography, because the clustering algorithm operates in a planar coordinate system.

Geofencing ¶

Geofencing means defining a virtual boundary, usually as a polygon or radius, and then checking whether a point is inside it, outside it, entering it, or leaving it.

Typical geofencing examples include:

• sending an event when a driver enters a depot area
• detecting when a delivery vehicle leaves its assigned zone
• alerting when equipment moves outside an allowed region
• triggering app behavior when a user enters a venue or neighborhood

The following example uses three tables to implement a geofencing system for tracking trucks and storing events whenever they enter or exit defined geofences.

• geofences stores geofence polygons together with a name and category. The area is stored as geometry(Polygon, 4326) so each fence can represent an arbitrary shape.
• truck_latest_positions stores the latest known position of each truck in a geometry(Point, 4326) column.
• truck_geofence_events stores enter and exit events, including the truck ID, geofence ID, event type, and the location where the event occurred.

Indexes are created on the spatial columns to optimize geospatial queries, such as checking if a truck's location is within a geofence. GiST indexes are a special kind of index designed to work with spatial data and can significantly improve the performance of spatial queries like ST_Contains and ST_DWithin.

CREATE TABLE IF NOT EXISTS geofences (
  geofence_id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  area geometry(Polygon, 4326) NOT NULL
);

CREATE INDEX IF NOT EXISTS geofences_area_idx ON geofences USING GIST (area);

CREATE TABLE IF NOT EXISTS truck_latest_positions (
  truck_id TEXT PRIMARY KEY,
  driver_name TEXT NOT NULL,
  latitude DOUBLE PRECISION NOT NULL,
  longitude DOUBLE PRECISION NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL,
  location geometry(Point, 4326) GENERATED ALWAYS AS (
    ST_Point(longitude, latitude, 4326)
  ) STORED
);

CREATE INDEX IF NOT EXISTS truck_latest_positions_location_idx ON truck_latest_positions USING GIST (location);

CREATE TABLE IF NOT EXISTS truck_geofence_events (
  id BIGSERIAL PRIMARY KEY,
  truck_id TEXT NOT NULL,
  driver_name TEXT NOT NULL,
  geofence_id TEXT NOT NULL REFERENCES geofences (geofence_id),
  geofence_name TEXT NOT NULL,
  event_type TEXT NOT NULL CHECK (event_type IN ('entered', 'exited')),
  latitude DOUBLE PRECISION NOT NULL,
  longitude DOUBLE PRECISION NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL,
  location geometry(Point, 4326) GENERATED ALWAYS AS (
    ST_Point(longitude, latitude, 4326)
  ) STORED
);

CREATE INDEX IF NOT EXISTS truck_geofence_events_geofence_time_idx ON truck_geofence_events (geofence_id, occurred_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS truck_geofence_events_truck_time_idx ON truck_geofence_events (truck_id, occurred_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS truck_geofence_events_location_idx ON truck_geofence_events USING GIST (location);

postgis.go

To fill the truck_geofence_events table, we can create a trigger function that runs after every insert or update on truck_latest_positions. The function checks the truck's new location against all defined geofences and decides whether an event should be logged. It uses ST_Contains to test whether the truck is inside each geofence, compares that result with the last known state for the same truck and geofence, and inserts an entered or exited event when the state changes.

CREATE OR REPLACE FUNCTION sync_truck_geofence_events() RETURNS trigger AS $$
DECLARE
  geofence_row RECORD;
  last_event_type TEXT;
  currently_inside BOOLEAN;
BEGIN
  FOR geofence_row IN
    SELECT geofence_id, name, ST_Contains(area, NEW.location) AS is_inside
    FROM geofences
  LOOP
    currently_inside := geofence_row.is_inside;

    SELECT event_type
    INTO last_event_type
    FROM truck_geofence_events
    WHERE truck_id = NEW.truck_id
      AND geofence_id = geofence_row.geofence_id
    ORDER BY occurred_at DESC, id DESC
    LIMIT 1;

    IF (last_event_type IS NULL AND currently_inside)
      OR (last_event_type = 'exited' AND currently_inside)
      OR (last_event_type = 'entered' AND NOT currently_inside)
    THEN
      INSERT INTO truck_geofence_events (
        truck_id,
        driver_name,
        geofence_id,
        geofence_name,
        event_type,
        latitude,
        longitude,
        occurred_at
      )
      VALUES (
        NEW.truck_id,
        NEW.driver_name,
        geofence_row.geofence_id,
        geofence_row.name,
        CASE WHEN currently_inside THEN 'entered' ELSE 'exited' END,
        NEW.latitude,
        NEW.longitude,
        NEW.updated_at
      );
    END IF;
  END LOOP;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS truck_latest_positions_sync_geofences ON truck_latest_positions;
CREATE TRIGGER truck_latest_positions_sync_geofences
AFTER INSERT OR UPDATE ON truck_latest_positions
FOR EACH ROW
EXECUTE FUNCTION sync_truck_geofence_events();

postgis.go

A program can now update the truck_latest_positions table with the current location of each truck, and the trigger will automatically log geofence entry and exit events in the truck_geofence_events table whenever a truck crosses a geofence boundary.

Final Takeaway ¶

PostGIS adds powerful spatial capabilities to PostgreSQL, making it a great choice for applications that need to manage and query geographic data. Whether you are building a store locator, a geofencing system, or a spatial analytics platform, PostGIS provides the tools and performance to handle those use cases effectively.

This blog post only scratches the surface of what PostGIS can do. It includes a rich set of spatial functions for everything from simple distance calculations to more advanced geospatial analysis. I recommend reading the official documentation. In particular, Chapter 4, Data Management, and Chapter 5, Spatial Queries, cover much of the core functionality in more detail.

If your data already lives in PostgreSQL and you have spatial queries in your application, PostGIS is definitely worth considering as a solution for your spatial data needs.