Geospatial Store

1. The ingestion service receives raw location records—GPS pings, polygon boundaries (GeoJSON/WKT), or address strings—and normalizes them to a consistent SRID (typically WGS 84 / EPSG:4326).
2. The geocoder resolves textual addresses into point geometries and, where needed, reprojects to a planar CRS for accurate metric distance work.
3. The geospatial database stores each feature in a native geometry/geography column and builds a spatial index—R-tree/GiST bounding boxes plus a discrete grid (H3 or S2) cell column for cheap coarse filtering.
4. The query planner answers proximity, ST_DWithin, and ST_Intersects requests by first pruning candidates with the bounding-box index, then running exact geometry math only on the surviving rows.
5. The tile/serving layer aggregates results into H3 hexbins or vector tiles and returns them to mapping clients, routing engines, or analytics dashboards.
6. The application consumes ranked-by-distance results, boundary memberships, or heatmap aggregates without ever scanning the full table.

• Exact spatial correctness vs the CPU cost of geometry math and index maintenance on write.
• Rich operator library (PostGIS) vs operational expertise and tuning of GiST/SP-GiST indexes.
• Grid indexing (H3/S2) speed vs edge-case inaccuracy where cells straddle boundaries.
• Single-database SQL joins with spatial predicates vs harder horizontal scaling of large geometry sets.

A food-delivery marketplace stores every restaurant as a point and every courier zone as a polygon in PostGIS. When a customer opens the app, the service issues an ST_DWithin query against a GiST-indexed geography column to list restaurants within a 4 km radius, then runs ST_Contains to confirm the address falls inside an active delivery polygon. Dispatch precomputes H3 level-8 cells for couriers so the matcher can shortlist nearby drivers with a fast cell lookup before doing exact distance ranking—turning what would be a full-table distance scan into a millisecond index probe even during peak dinner traffic.

• Projection correctness: Metric operators (ST_DWithin, length, area) silently return wrong answers if geometries carry mismatched SRIDs or run on a planar CRS far from its valid zone; enforce one storage SRID and reproject explicitly for distance math.
• Invalid geometries: Self-intersecting or unclosed polygons from upstream feeds cause predicate exceptions or false negatives—validate on ingest with ST_IsValid/ST_MakeValid and reject or repair before indexing.
• Index maintenance: GiST/SP-GiST indexes bloat under heavy update churn (moving assets); schedule reindex/vacuum, and store fast-moving points separately from static polygons to limit rebuild cost.
• Grid resolution tuning: H3/S2 cell level trades pruning selectivity against candidate-set size—too coarse returns huge shortlists, too fine multiplies rows for large polygons; pick per-layer and precompute cell columns.
• Cost drivers: The bill is dominated by exact geometry CPU on surviving candidates, not the bounding-box probe; watch rows-post-filter and simplify complex polygons (ST_Simplify) for serving tiles.
• Observability: Instrument planner choices (index vs seq scan), candidate-vs-exact row counts per query, and geocoder resolution failure rates to catch silent full-table fallbacks.
• Schema evolution: Changing SRID or geometry type forces full re-indexing and cache invalidation; version tile/hexbin outputs so downstream clients tolerate resolution changes.