Fusing Geo and Non-Geo Data in ETL Pipelines

Location data alone tells you where something happened. Fused with business data — orders, deliveries, customer records — it tells you why it matters. But combining geo and non-geo data in ETL pipelines introduces challenges that traditional tools were not built to handle.

The Challenge

Geo data has three properties that break standard ETL assumptions:

1. Spatial relationships — Proximity, containment, and intersection cannot be expressed in standard SQL without spatial extensions.
2. Coordinate systems — WGS84, Web Mercator, and local projections each carry tradeoffs in precision and performance.
3. Temporal-spatial queries — "Where was vehicle X at time Y?" requires indexing on both dimensions simultaneously.

Lambda Architecture for Geo Data

When you need both real-time dashboards and historical analysis, a Lambda Architecture splits the work:

• Streaming layer — Flink or Spark Streaming processes live data and writes to Redis or Druid with spatial indexes, serving queries under 100 milliseconds.
• Batch layer — Spark processes historical data and writes to PostgreSQL with PostGIS, serving analytical queries such as aggregations and ML feature computation.

Use this pattern when operators need live positions today and route efficiency reports for last month.

Geo-First Schema Design

Traditional star schemas break down with geo data. The fix is geohashing — converting two-dimensional coordinates into string prefixes that preserve spatial locality. Points close together share similar prefixes, enabling efficient partitioning and pruning.

-- Slow: spatial function on every row
SELECT * FROM deliveries
WHERE ST_Distance(location, ST_Point(-120, 39)) < 10000;

-- Fast: partition pruning first, then precision
SELECT * FROM deliveries
WHERE geohash_prefix = '9q8yy'  -- Lake Tahoe area
  AND delivery_date = '2026-04-01';

A 6-character geohash provides approximately 1.2 km precision — coarse enough for partitioning, fine enough for most business queries.

Validation Strategies

Geo data fails in specific, predictable ways. Build validation into ingestion, not after:

Failure Mode	Detection	Mitigation
Coordinate swap (lat/lng)	Validate bounds: latitude in [-90, 90]	Schema validation on ingestion
Null Island (0, 0)	Explicit check for (0, 0) coordinates	Reject or flag for manual review
GPS drift	Flag speeds exceeding 500 km/h between points	Maximum-speed filter
Timezone errors	Detect timestamps crossing DST boundaries	Store 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"))
    )

Spark vs. Flink for Geo ETL

Criteria	Spark	Flink
Batch processing	Excellent	Possible but not ideal
Streaming	Micro-batch latency	True streaming, low latency
Geo functions	Via Sedona (formerly GeoSpark)	Limited, requires UDFs
State management	Checkpointing overhead	Native state backends

Our recommendation: Spark for batch geo ETL and historical transformations. Flink for real-time pipelines and geofence alerting.

PostGIS: When and When Not

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

The common pattern: Flink or Spark processes streaming data, then writes results to PostgreSQL with PostGIS for downstream querying.

Real-World: Delivery Analytics

A logistics client needed to identify deliveries within 500 meters of competitor locations over the past 30 days. Over 1 million delivery records per day.

The solution:

1. Ingest delivery records via Kafka into Flink.
2. Enrich each record with a 6-character geohash prefix.
3. Store in PostgreSQL partitioned by geohash and date.
4. Query using PostGIS for precise distance calculations on the pre-filtered set.

WITH candidate_deliveries AS (
  SELECT * FROM deliveries
  WHERE geohash_prefix IN ('9q8yy', '9q8yz', '9q8yx')
    AND delivery_date >= now() - interval '30 days'
)
SELECT * FROM candidate_deliveries
WHERE ST_Distance(
  location,
  ST_GeogFromText('POINT(-119.977 39.162)')
) < 500;

Query time dropped from 12 seconds to 200 milliseconds.

Lessons from Production

1. Add geohashes during ingestion. Computing prefixes early enables dramatic query optimizations downstream.
2. Prefer GEOGRAPHY over GEOMETRY in PostGIS. The GEOGRAPHY type uses meters and accounts for Earth's curvature. Use GEOMETRY only for small areas where curvature is negligible.
3. Test edge cases from the start. The International Date Line, polar regions, and antimeridian crossings will break naive spatial implementations.

Fusing geo and non-geo data is where the business value lives. Think spatially from day one. AngoraTek builds data pipelines that handle geo and non-geo data at scale. Let's talk if you are fusing location data with business data.