AngoraTek FR
← Back to Blog
Data Engineering 7 min read

Your ETL Pipeline Is Not Ready for Coordinates

Somewhere in almost every GPS feed there is a cluster of shipments resting in the Gulf of Guinea, roughly 570 km off the coast of West Africa, at exactly latitude 0, longitude 0. No driver went there. A GPS unit that has not received its first fix can return zero as a power-on default instead of raising an error, and the pipeline takes it at its word.

A coordinate looks like a value and behaves like a claim. A row either carries an order's weight, or it does not; a coordinate carries a statement about the world that can be silently wrong. A standard ETL treats every field as a value, and most of those assumptions break under that reading. The fixes are design decisions, not tool upgrades. Two matter more than the rest: pick one projection per pipeline, and partition before you compute. Both are cheap on day one and ruinous afterward.

A coordinate is a claim, not a value

Spatial relationships are the first thing vanilla SQL cannot express. Proximity, containment, and intersection all depend on a distance function between two points that does not exist in standard SQL. The query "which deliveries landed within 500 meters of the depot" is not a slow query without PostGIS. It is a query you cannot write.

Coordinate systems come next, and they bite quietly. WGS84 geographic coordinates, Web Mercator, and local projections each trade precision for performance, so two teams computing "distance" in different projections produce numbers you cannot compare. Pick one projection per pipeline, document it, and the disagreement disappears from your reports.

Then the hard one. "Where was vehicle X at time Y?" joins space and time simultaneously, and an index on either dimension alone will not save you. Neither dimension prunes the other.

The failures you can predict on day one

Geo data fails in a small number of predictable ways, which is good news. A failure you can name is a failure you can reject at the door:

Failure ModeDetectionMitigation
Coordinate swap (lat/lng)Validate bounds: latitude in [-90, 90]Schema validation on ingestion
Null Island (0, 0)Explicit check for (0, 0) coordinatesReject or flag for manual review
GPS driftFlag speeds exceeding 500 km/h between pointsMaximum-speed filter
Timezone errorsDetect timestamps crossing DST boundariesStore all timestamps in UTC
# PySpark validation
def validate_geo(df):
    return (
        df
        .withColumn("lat_valid", col("lat").between(-90, 90))
        .withColumn("lng_valid", col("lng").between(-180, 180))
        .withColumn("not_null_island", ~(col("lat") == 0) | ~(col("lng") == 0))
        .filter(col("lat_valid") & col("lng_valid") & col("not_null_island"))
    )

The bounds check is not paranoia. The swap between latitude and longitude is the single most common coordinate defect in any real feed, and a lat of 130 tells you instantly that someone transposed a column.

Partition before you compute

Our first partition key was the delivery date alone, and it pruned nothing: every live question was spatial, and pruning by date still scanned every district on that date. The spatial dimension had to be in the partition, not beside it. Traditional star schemas break down with geo data. The fix is geohashing, which converts two dimensional coordinates into string prefixes that preserve spatial locality. Points that are close together share similar prefixes, which makes partitioning and pruning nearly free.

-- Slow: a distance function on every row of the table
SELECT * FROM deliveries
WHERE ST_Distance(location, ST_GeogFromText('POINT(-119.977 39.162)')) < 10000;

-- Fast: prune the partition first, trust PostGIS for precision
-- single cell for the example; a radius query adds the eight neighbor cells (see below)
SELECT * FROM deliveries
WHERE geohash_prefix = '9qfwvc'
  AND delivery_date = '2025-12-01';

A 6 character geohash cell is, per the geohash precision table, about a kilometer east-west and half a kilometer north-south at mid-latitudes, and it shrinks east-west the closer you get to a pole. That is coarse enough to partition on and fine enough for most business questions. Add the prefix at ingestion time, not months later. Retrofitting a prefix onto an existing history is a full table rewrite, and it buys nothing you could not have had for one derived column on day one.

Two engines, and pick deliberately

When you need live dashboards today and historical analysis of last quarter, from the same system, a Lambda Architecture splits the work cleanly. Flink or Spark Streaming processes live data and writes to Redis or Druid with spatial indexes for the low-latency reads dashboards need. Spark processes the historical layer and writes to PostgreSQL with PostGIS, serving aggregations and ML feature computation.

Reach for this pattern when your operators need live positions right now and route efficiency reports for last month. Both groups deserve good answers.

CriteriaSparkFlink
Batch processingExcellentPossible but not ideal
StreamingMicro-batch latencyTrue streaming, low latency
Geo functionsVia Sedona (formerly GeoSpark)Limited, requires UDFs
State managementCheckpointing overheadNative state backends
Pick Spark for batch geo ETL and historical transformations. Flink for real-time pipelines and geofence alerting. Teams that pick one tool for both jobs usually end up happy with neither.

PostGIS is an analyst, not a warehouse worker

PostGIS is the standard for spatial SQL, and it deserves that reputation. Use it for complex spatial queries, joins, and analytics. Do not use it as the primary ETL engine for high-volume ingestion or real-time updates. It is a brilliant analyst, not a warehouse worker.

One rule inside PostGIS matters more than the rest: prefer the GEOGRAPHY type over GEOMETRY. GEOGRAPHY measures in meters and accounts for Earth's curvature. GEOMETRY is the planar alternative, usable only with the right projected CRS for your region. Get this wrong and a "5,000 meter" radius quietly drifts with latitude, larger in some regions of your map than others.

The pattern that works: Flink or Spark processes streaming data, then writes results to PostgreSQL with PostGIS for downstream querying. The exact distance work happens where the volume is already thin.

A 500-meter question, pre-filtered

Say the question arrives on a Tuesday: which deliveries in the past 30 days happened within 500 meters of a competitor location? With more than a million delivery records per day, the naive query reads every row and computes a distance function on all of it. The shape that makes the question answerable during a meeting instead of overnight:

  1. Ingest delivery records through Kafka into Flink.
  2. Enrich each record with a 6 character geohash prefix.
  3. Store in PostgreSQL, partitioned by geohash and date.
  4. Query with PostGIS for exact distance on the pre-filtered set.
WITH candidate_deliveries AS (
  SELECT * FROM deliveries
  WHERE geohash_prefix IN (
    -- the cell containing the point, plus the eight neighbors
    -- a 500 m radius can touch
    '9qfwvc', '9qfwvf', '9qfwvb', '9qfwv9', '9qfwu1',
    '9qfwv8', '9qfwvd', '9qfwu0', '9qfwu4'
  )
  AND delivery_date >= now() - interval '30 days'
)
SELECT * FROM candidate_deliveries
WHERE ST_Distance(
  location,
  ST_GeogFromText('POINT(-119.977 39.162)')  -- North Lake Tahoe
) < 500;

Nine cells bound the candidate set to a few square kilometers, next to the million-plus rows the naive query would read for one day alone. PostGIS then computes exact distances on only those candidates, which is why the 500 meters stays trustworthy. The pruning never touched the measurement. It only decided who got measured.

What geohashes get wrong

The honest limits, because every one of them shows up in production eventually. Points a few meters apart can fall on opposite sides of a cell edge and land in different prefixes, so equality on one prefix silently drops real candidates. Compute the neighbor prefixes at query time and include every cell your radius can touch, as the query above does. The cells are not equal-area, so a prefix is a partition key, never a distance. And the International Date Line, plus the antimeridian, will break naive spatial implementations. So will polar regions, where every projection drifts. Test those coordinates from the very first sprint, because they do not read as exotic until they are your data.

If your spatial queries recompute a distance function on every row, you are paying a database to read the same million rows again and again. Compute the prefix at ingestion, prune the partition, and let PostGIS see only the candidates worth measuring.

The prefix does the pruning. PostGIS does the truth.

AngoraTek builds data pipelines that fuse geo and business data. Let's talk if you're combining location data with the rest of your records.

Thanks for reading this far. If this sounds like the problem you're wrestling with, tell us about it. The team reads every message.

Start a conversation