What is the difference between ST_SetSRID and ST_Transform in PostGIS?

ST_SetSRID only labels a geometry with an SRID; it does not move any coordinates. ST_Transform actually reprojects coordinates from the geometry's current SRID to a new one. Using ST_SetSRID when you needed ST_Transform leaves coordinates in the wrong place under a correct-looking label.

How do I make spatial queries fast in PostGIS?

Build a GiST index on the geometry column, ensure all geometries share one SRID, and write queries that use indexable operators (ST_Intersects, the && bounding-box operator) so the planner uses the index. Run ANALYZE after bulk loads.

When should I use geometry vs geography in PostGIS?

Use geometry with a projected SRID for analysis and fast planar math over a region. Use geography for accurate distance and area on the WGS84 spheroid across large or global extents, accepting slower computation and a smaller function set.

How do I find and fix invalid geometries in PostGIS?

Flag them with ST_IsValid and ST_IsValidReason, then repair with ST_MakeValid. Always validate after import, because self-intersections and bad ring order pass through file conversions silently and break spatial predicates.

PostGIS for Spatial Data Management: A Field Guide

Short answer

PostGIS turns PostgreSQL into a spatial database with geometry/geography types, spatial indexing, and hundreds of ST_ functions, and it becomes the right home for spatial data the moment several people, apps, or products need to query the same authoritative layers. The error that quietly ruins more PostGIS work than any other is confusing ST_SetSRID (which relabels) with ST_Transform (which reprojects). Get SRID discipline right, index with GiST, and keep geometries valid, and PostGIS will serve millions of features with sub-second spatial queries.

SRID: the label is not the projection

Every PostGIS geometry carries an SRID — an integer key into the spatial_ref_sys table that says which CRS the coordinates are in (4326 for WGS84, 2154 for Lambert-93, 32631 for UTM 31N, and so on). Two functions touch it, and they do completely different things:

-- WRONG when the data is actually in another CRS:
-- this stamps SRID 2154 onto coordinates that are still in 4326.
UPDATE boreholes SET geom = ST_SetSRID(geom, 2154);

-- RIGHT: actually moves the coordinates from 4326 to 2154.
UPDATE boreholes SET geom = ST_Transform(ST_SetSRID(geom, 4326), 2154);

ST_SetSRID only writes metadata; the numbers in the geometry do not change. ST_Transform runs the coordinate operation through PROJ and produces new coordinates. The reason ST_SetSRID exists is for geometries that have the right coordinates but a missing or zero SRID — for example after a raw ST_GeomFromText. Using it to "fix" misplaced data produces a layer that looks correctly tagged and is silently in the wrong place. A geometry with SRID 0 means "unknown," and most spatial functions refuse to mix it with a defined SRID — which is a feature, because it forces you to declare intent.

Choosing one project SRID

Pick a single analysis SRID for a project and transform everything to it on import. For regional work that means a projected CRS in metres (UTM or a national grid) so that ST_Distance, ST_Area, and ST_Buffer return real planar measurements. Storing analysis layers in 4326 (degrees) and then buffering by "0.001" is a common trap — the unit is degrees, not metres, and a degree of longitude shrinks toward the poles. Enforce the SRID at the schema level:

ALTER TABLE faults
  ADD CONSTRAINT enforce_srid_geom CHECK (ST_SRID(geom) = 2154);

Now a bad import fails at write time instead of corrupting an analysis weeks later.

Geometry vs geography

PostGIS offers two spatial types:

geometry treats coordinates as Cartesian. With a projected SRID, math is fast and accurate over the region the projection is designed for. This is the default for analysis.
geography treats coordinates as longitude/latitude on the WGS84 spheroid. ST_Distance and ST_Area are then true geodesic measurements, correct over large or global extents — but computation is slower and the function set is smaller.

Use geography when you need correct great-circle distances across continents or a worldwide dataset; use projected geometry for everything regional and analysis-heavy. You can always cast (geom::geography) for an occasional geodesic measurement without changing the storage type.

Indexing: the difference between 5 ms and 5 s

A spatial query without an index scans every row. The GiST index on the geometry column lets the planner use the bounding-box operator && to discard almost everything before the expensive exact test runs:

CREATE INDEX faults_geom_gix ON faults USING GIST (geom);
ANALYZE faults;

ANALYZE matters: after a bulk load the planner's statistics are stale and it may ignore the index. The indexable predicates are the ones built on bounding-box logic — ST_Intersects, ST_DWithin, ST_Contains, and the raw && operator. A classic mistake is filtering with ST_Distance(a.geom, b.geom) < 500, which cannot use the index because it computes a distance for every pair. Rewrite it as the index-friendly form:

-- index-using "within 500 m" query
SELECT a.id, b.id
FROM sites a
JOIN hazards b ON ST_DWithin(a.geom, b.geom, 500);

ST_DWithin uses the GiST index to prune candidates first, then checks the exact distance only on survivors. Confirm with EXPLAIN ANALYZE that you see an Index Scan (or Bitmap Index Scan) rather than a Seq Scan.

Validity: invalid geometry breaks predicates

Self-intersecting polygons and wrong ring orientation pass through Shapefile→GeoPackage→PostGIS conversions without complaint, then cause ST_Intersects and overlay operations to return wrong or error results. Validate on import and repair:

-- find the problems
SELECT id, ST_IsValidReason(geom)
FROM parcels
WHERE NOT ST_IsValid(geom);

-- repair in place
UPDATE parcels
SET geom = ST_MakeValid(geom)
WHERE NOT ST_IsValid(geom);

Make validation a standing step in ingestion, not a reaction to a broken query.

A loading and management workflow

Import with a known SRID. Load with ogr2ogr -f PostgreSQL ... -t_srs EPSG:2154 -lco GEOMETRY_NAME=geom -lco SPATIAL_INDEX=GIST so reprojection, geometry column, and index are set on arrival.
Enforce SRID and geometry type with CHECK constraints or a typed geometry column (geometry(MultiPolygon, 2154)).
Validate with ST_IsValid and repair with ST_MakeValid.
Index and ANALYZE. GiST on geometry, plus B-tree indexes on attribute columns used in filters.
Cluster or partition very large tables by region or time if queries are spatially or temporally local.
Version the schema. Keep table definitions and migration SQL in version control as the provenance record.

Validation and QA

EXPLAIN ANALYZE every hot query and confirm the spatial index is used; an unexpected Seq Scan usually means a non-indexable predicate or stale statistics.
Count and extent checks after import: feature count should match the source, and ST_Extent(geom) should sit inside the expected project bounds (a wild extent signals an SRID mistake).
SRID audit: SELECT DISTINCT ST_SRID(geom) FROM table; should return exactly one value — your project SRID.
Validity sweep after any bulk edit or import.

Common pitfalls and why they happen

Relabel instead of reproject. ST_SetSRID used where ST_Transform was needed — the geometry looks correctly tagged but sits in the wrong place. It happens because both functions take an SRID argument and the names suggest similarity.
Buffering in degrees. A buffer of "0.001" on a 4326 layer is degrees, not metres, and varies with latitude. Work in a projected SRID.
Distance filters that ignore the index. WHERE ST_Distance(...) < d forces a full scan; ST_DWithin is the indexable equivalent.
Forgetting ANALYZE. After a large load the planner has no fresh statistics and may skip the GiST index entirely.
Trusting imported validity. Invalid geometries arrive clean-looking and break overlays later; validate at ingest.

Bathyl perspective

We move data into PostGIS when GIS stops being files on a disk and becomes shared infrastructure that products and people query concurrently. The aim is a schema where the SRID is enforced, geometries are valid, and the index is doing the work — so the database is a single source of spatial truth that a teammate can query without inheriting hidden assumptions.

PostGIS for Spatial Data Management