PostGIS: Spatial Types, Indexes and Queries

PostGIS

PostGIS adds spatial data and operations to PostgreSQL, letting you ask location questions alongside ordinary relational queries. Start by choosing the coordinate model and measurement units. Then select predicates, indexes, and validation that preserve the meaning of the question.

A query can execute successfully and still answer the wrong spatial question. Mixing degrees with meters, labeling coordinates with the wrong reference system, or treating overlapping bounding boxes as exact intersections can produce plausible-looking errors.

TL;DR

Use geometry with a deliberate spatial reference system; its measurement units depend on that system.
Use geography when round-earth distance behavior fits the problem; ST_DWithin geography distances use meters.
ST_SetSRID labels coordinates. ST_Transform converts them between coordinate systems.
Pair a compatible spatial index with an index-aware predicate, then inspect the actual query plan on representative data.
Test expected matches, exclusions, coordinate order, and boundary behavior before interpreting a fast query as a correct one.

Quick Example

Use a disposable PostgreSQL database with the PostGIS extension already installed by an authorized administrator. This script creates a temporary teaching table inside a transaction and rolls it back. The coordinates are synthetic; they do not represent customers or operational locations.

The intended matches are origin and near; far is outside the 500-meter radius. In this longitude/latitude example, ST_MakePoint receives longitude first. The spatial filter uses geography on both sides, matching the indexed column type. The ST_DWithin reference defines its units and index-aware bounding-box comparison.

Three rows do not justify a performance conclusion. A sequential scan can be reasonable for this fixture despite the index. Use a representative dataset and inspect the plan again before drawing conclusions about scale. EXPLAIN ANALYZE executes its statement, so apply it thoughtfully to expensive queries and avoid treating it as a harmless dry run for writes.

Core Concepts

Geometry and geography

Geometry uses coordinates interpreted through its spatial reference system. A projected system may use meters or feet; longitude/latitude coordinates use angular units. A geometry labeled with SRID 4326 does not make a numeric planar distance a meter measurement.

Geography supports round-earth measurements. The PostGIS geography introduction explains why longitude and latitude cannot simply be treated as ordinary flat distance coordinates across the globe. Choose the representation for the operations and geographic extent you need, rather than converting indiscriminately at query time.

Reference system and transformation

An SRID identifies the coordinate reference system; it does not prove the incoming data was correctly described. Verify the source system, axis convention, and units before assigning it.

ST_Transform changes coordinates between systems, while ST_SetSRID changes the identifier. If imported coordinates are already in a known source system, label them accurately before transforming. Relabeling projected coordinates as longitude/latitude does not convert their values and can make subsequent results meaningless.

Spatial indexes filter candidates

The spatial indexing workshop describes bounding-box filtering and the need for spatial predicates. A box comparison can identify candidates whose exact shapes still need evaluation. Keep the exact predicate when the question concerns actual intersections or containment.

Index use also depends on query expression, selectivity, and planner decisions. An index on a geometry column is not automatically the right index for every transformed or cast expression. Choose a consistent storage and query strategy, then verify it with plans.

Choose Types from the Spatial Question

These are conditional starting points. Required functions, extent, precision, and performance can change the choice. Do not choose geography solely because the data has latitude and longitude, or geometry solely because a particular query appears faster on a tiny fixture.

Validate Meaning Before Optimizing

Build a small acceptance dataset containing known matches, nonmatches, empty or missing input cases, and locations near the decision boundary. Include negative coordinates and a case that would reveal swapped longitude and latitude. If your service crosses the antimeridian or operates near poles, include those regions deliberately.

For polygon operations, establish how invalid geometries are handled at ingestion. Rejecting, repairing, or quarantining data are different product decisions; repair can change the shape and therefore its meaning. Retain original provenance when transformations or corrections matter to later investigation.

Define what “within the area” means for a point on a boundary. Select a predicate whose documented semantics match that requirement and verify known edge cases. A map display is useful for inspection but should not be the only oracle for automated acceptance tests.

Once correctness is established, increase data volume and inspect plans with realistic geographic distribution. A clustered urban dataset and evenly spread synthetic points can have different selectivity. Include nonspatial filters, joins, and limits from the real query so the experiment represents the application.

Best Practices

State units at the interface

Use names such as radius_meters and validate bounds before issuing a query. This makes both accidental unit mismatches and unexpectedly broad searches easier to catch. A database returning a result does not establish that an unbounded user request is appropriate.

Keep the indexed expression deliberate

Avoid casually wrapping every stored location in a transformation or cast. Prefer a consistent type and coordinate system for the intended operation, or design the corresponding expression index when justified. Inspect the selected plan rather than assuming the index name proves use.

Combine location with relational policy

Apply required ownership and tenant restrictions alongside the spatial predicate. Being nearby does not grant access to a record. Keep sensitive coordinates out of debug logs and public examples, and test that unauthorized records remain excluded even when their locations match.

Common Mistakes

Bad: Use a geometry distance of 500 in longitude/latitude coordinates and describe it as 500 meters.

Correct: Choose a suitable meter-based projected system or the appropriate geography operation, and keep units explicit.

Bad: Call ST_SetSRID to convert imported coordinates.

Correct: Identify the actual source system, assign that identity if necessary, then use ST_Transform for conversion.

Bad: Replace an exact spatial relationship with bounding-box overlap because it is fast.

Correct: Use bounding boxes to narrow candidates while preserving the exact predicate required by the product.

FAQ

Is PostGIS a separate database?

It is an extension used with PostgreSQL. Installation availability, extension versions, permissions, and upgrade procedures depend on the hosting environment. Confirm those before selecting functions for an application.

Should I use geometry or geography?

Choose from geographic extent, required operations, coordinate systems, and measurement semantics. Geography is useful for round-earth distance questions; geometry with a suitable projection can be appropriate for local analysis. Test both correctness and the actual workload.

Why does my query ignore the spatial index?

The table may be small, the predicate may match much of it, or the expression may not match the indexed representation. Inspect statistics and the plan before changing settings. An index scan is not an objective independent of query cost.

Does this radius query find travel time?

No. It evaluates geographic proximity, not road connectivity, traffic, or travel time. A routing question requires a model and data that represent the available routes and relevant costs.