Developers coming from SQL Server often approach PostgreSQL with the assumption that spatial functionality will look and behave more or less the same. After all, both systems expose geometry and geography types, both support distance calculations, and both offer spatial indexes. That assumption is only partially correct.

While PostgreSQL with PostGIS covers everything SQL Server Spatial can do, it also introduces new compositional concepts that SQL Server simply does not have. Understanding those concepts is the key to using PostGIS effectively instead of trying to replicate SQL Server patterns verbatim.

This blog posting walks through a small but complete PostGIS example and explains how each piece maps to what SQL Server developers already know—while highlighting the PostgreSQL-specific ideas along the way.

Spatial Support as an Extension

In SQL Server, spatial support is part of the engine. You never “enable” it; geometry and geography are always available. PostgreSQL takes a different approach. Spatial functionality is delivered via an extension that must exist on the system and be enabled per database:

-- Check, if the PostGIS extension is installed on the system
SELECT * FROM pg_available_extensions WHERE name = 'postgis';

-- Create the extension in the current database
CREATE EXTENSION IF NOT EXISTS postgis;

-- Check the PostGIS version
SELECT postgis_version();

Conceptually, this is not a limitation but a design choice. PostgreSQL keeps the core engine small and pushes domain-specific logic into native extensions. PostGIS is one of the most mature examples of this philosophy: it is not a wrapper or add-on, but deeply integrated native code.

For SQL Server developers, the important mental shift is that features are opt-in per database, not global.

Geometry and Geography: Same Names, More Explicit Design

At first glance, PostGIS looks reassuringly familiar. You still get geometry for planar calculations and geography for spheroidal, meter-based calculations. The difference becomes apparent when you look at how the two are related:

-- Create a new table with the GEOMETRY and GEOGRAPHY data types
CREATE TABLE PointOfInterests
(
    PoiID      	BIGSERIAL PRIMARY KEY,
    Name        TEXT NOT NULL,
    Category    TEXT NOT NULL,
    Geom        GEOMETRY(POINT, 4326) NOT NULL,
    Geog        GEOGRAPHY(POINT, 4326) GENERATED ALWAYS AS (GEOM::GEOGRAPHY) STORED,
    CreatedAt   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

In SQL Server, GEOMETRY and GEOGRAPHY are separate columns with no inherent relationship. If you store both, it is your responsibility to keep them consistent. PostgreSQL allows you to declare that relationship explicitly using a generated column. Here, Geog is always derived from Geom. It cannot drift, it cannot be forgotten during inserts, and it is always correct by definition. This is a small feature, but it fundamentally changes how safely you can model spatial data.

Also worth noting for SQL Server developers:

• BIGSERIAL is PostgreSQL’s shorthand for an auto-incrementing bigint
• TIMESTAMPTZ always stores timestamps in UTC internally, unlike SQL Server’s datetimeoffset semantics

Inserting Spatial Data

Inserting data looks very similar to SQL Server, with one important difference: PostGIS requires you to be explicit about SRIDs.

-- Insert some Points of Interests
INSERT INTO PointOfInterests (Name, Category, Geom) VALUES
('Stephansdom', 'sight',   ST_SetSRID(ST_MakePoint(16.373819, 48.208174), 4326)),
('Prater',      'park',    ST_SetSRID(ST_MakePoint(16.404954, 48.216778), 4326)),
('HBF Wien',    'station', ST_SetSRID(ST_MakePoint(16.375000, 48.185000), 4326)),
('Schönbrunn',  'sight',   ST_SetSRID(ST_MakePoint(16.312222, 48.184516), 4326)),
('Donauinsel',  'park',    ST_SetSRID(ST_MakePoint(16.410000, 48.240000), 4326));

SQL Server will happily let you insert spatial data without enforcing SRID consistency. PostGIS treats SRIDs as first-class metadata and uses them aggressively in validation, transformations, and indexing. This strictness pays off later when queries become more complex.

Spatial Indexing

Indexing is where architectural differences start to matter.

-- Create a spatial index
CREATE INDEX poi_geom_gix   ON PointOfInterests USING GIST (geom);

-- Update the statistics
ANALYZE PointOfInterests;

SQL Server spatial indexes are grid-based and require careful configuration to avoid pathological plans. PostGIS relies on GiST indexes, which behave like generalized R-trees and integrate naturally with the PostgreSQL query planner. The ANALYZE step is explicit in PostgreSQL. Statistics are not always refreshed automatically in development environments, and PostGIS queries are particularly sensitive to selectivity estimates.

Distance Calculations

Distance queries using geography will feel immediately familiar:

-- Distance calculation between 2 Point of Interests
SELECT
  	a.Name AS from_name,
  	b.Name AS to_name,
  	ST_Distance(a.geog, b.geog) AS distance_m
FROM PointOfInterests a
JOIN PointOfInterests b ON a.Name = 'Stephansdom' AND b.Name = 'Schönbrunn';

This maps almost one-to-one to STDistance() in SQL Server. The key takeaway is that PostGIS makes no attempt to hide whether you are working in planar or spheroidal space—you choose explicitly via the data type.

CROSS JOIN LATERAL

The next query introduces something that does not exist in SQL Server:

-- All Point of Interests within a radius
SELECT
	p.PoiID,
  	p.Name,
  	p.Category,
  	ST_Distance(p.Geog, c.Geog) AS distance_m
FROM PointOfInterests p
CROSS JOIN LATERAL
(
  SELECT Geog FROM PointOfInterests WHERE Name = 'Stephansdom'
) c
WHERE ST_DWithin(p.Geog, c.Geog, 4000)
ORDER BY distance_m;

CROSS JOIN LATERAL allows a subquery to reference columns from the current row of the outer query. In SQL Server, you would typically simulate this using variables, CTEs, or APPLY. PostgreSQL makes it a first-class relational operator.

Think of LATERAL as a per-row function invocation. The subquery is executed logically once per outer row, but the planner is free to optimize it aggressively. This feature enables expressive, composable spatial queries without procedural workarounds.

Nearest Neighbor Search and the <-> Operator

The final query demonstrates one of PostGIS’s most powerful features:

-- Nearest Neighbor search
SELECT
  	p2.PoiID,
  	p2.Name,
  	p2.Category,
  	ST_Distance(p2.geog, ref.geog) AS distance_m
FROM PointOfInterests p2
CROSS JOIN (SELECT Geog, Geom FROM PointOfInterests WHERE Name = 'Stephansdom') ref
WHERE p2.name <> 'Stephansdom'
ORDER BY p2.Geom <-> ref.Geom
LIMIT 3;

The <-> operator performs K-nearest-neighbor (KNN) ordering using the GiST index. This is not a function call – it is an operator that the planner understands deeply. SQL Server has no equivalent operator. Nearest-neighbor queries there usually involve manual distance calculations combined with TOP and ORDER BY, often resulting in expensive plans.

In PostGIS, <-> allows the database to:

• Use the spatial index for ordering
• Avoid computing exact distances for all rows
• Return nearest neighbors efficiently and predictably

This is a fundamental difference in how spatial querying is expressed.

Summary

For SQL Server developers, PostGIS is not just “SQL Server Spatial with more functions.” It is a system that encourages relational composition over procedural workarounds. Features like generated columns, LATERAL joins, and index-aware operators (<->) enable spatial queries that remain declarative, readable, and performant as complexity grows.

Features like generated columns, LATERAL joins, and index-aware operators (<->) enable spatial queries that remain declarative, readable, and performant as complexity grows.

Thanks for reading,

-Klaus