Data engineering · Current

Data pipelines with custom spatial connectors

Pipelines that move data from business applications into Google BigQuery through Hevo, with custom connectors for the spatial data the standard connectors don’t handle.

Organization
FilmLA
Year
Current
15+
sources into one warehouse
~2M
events a month through Hevo
Nightly
warehouse refresh

Results

  • Business and spatial data land in one warehouse.
  • Recurring and gapped permit schedules, stored as versioned location-by-time records.
  • Nightly incremental loads from the GIS database, with a full rebuild when a table needs it.

Stack

  • Hevo
  • Google BigQuery
  • PostgreSQL
  • Custom connectors
  • Spatiotemporal data model
Diagram: business applications and APIs flow through Hevo standard connectors, about 2 million events a month, and spatial data through custom spatial connectors for data the standard connectors don’t handle; both land in Google BigQuery and feed the permit data model.
Business apps and APIs run through Hevo’s standard connectors, spatial data through custom spatial connectors, and both land in BigQuery.

From the GIS database to the warehouse

A separate pipeline loads the GIS database (PostgreSQL) into BigQuery every night. It sends only what changed since the last run and reprojects geometry from Web Mercator (EPSG:3857) to WGS 84 (EPSG:4326), the coordinate system BigQuery’s geography type uses. A separate full-rebuild path reloads a table from scratch when it needs it.

A spatiotemporal data model for permits

A film permit rarely has one start and one end. A production can hold the same location every other Tuesday from noon to 3 PM, skip a holiday, then move to the next block over. The pipelines feed a multi-temporal spatial data model built for that: each reservation keeps its location and its schedule together, recurring and gapped time windows included.

Diagram: a film permit has a location and a schedule that can be one-off, recurring such as every other Tuesday from noon to 3 PM, or have excluded dates. Each change creates a new version with its own geometry and time windows, and the model expands them into location-by-time records that answer what is filming where at a given time. Table rows are illustrative.
Each permit version keeps its own geometry and time windows, expanded into location × time records you can query.
  • Recurring and gapped schedules. One-off dates, repeating patterns such as every other Tuesday, and excluded dates live on the same record.
  • Versioned. A change to a location or a schedule is a new version, so earlier versions stay on record.
  • Granular. Each reservation expands into location-by-time records, so what is filming where at a given hour comes straight from the data.