Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.narrative.io/llms.txt

Use this file to discover all available pages before exploring further.

Geospatial joins let you match records based on geographic overlap rather than shared keys. This is useful for foot traffic analysis, proximity targeting, and visitation attribution---any scenario where you need to find which observations fall within a set of points of interest.

​
POI polygon-to-polygon join

Use ST_Intersects to join two datasets that both have POI geometry columns. This finds all records from one dataset whose geographic polygon overlaps with polygons in another dataset. 
SELECT
  ls."_rosetta_stone"."geographic_location"."latitude",
  ls."_rosetta_stone"."geographic_location"."longitude",
  ls."_rosetta_stone"."mobile_id_unique_identifier"."value" AS maid_value,
  ls."_rosetta_stone"."event_timestamp"
FROM
  company_data."observations" ls
  INNER JOIN company_data."target_locations" t
    ON ST_Intersects(
      t."_rosetta_stone"."geographic_poi"."poi",
      ls."_rosetta_stone"."geographic_poi"."poi"
    )
WHERE
  ls."_rosetta_stone"."geographic_location"."latitude" IS NOT NULL
  AND ls."_rosetta_stone"."geographic_location"."longitude" IS NOT NULL
  AND ls."_rosetta_stone"."mobile_id_unique_identifier"."value" IS NOT NULL
  AND ls."_rosetta_stone"."event_timestamp" BETWEEN '2026-02-01' AND '2026-04-01'
  AND ls."_rosetta_stone"."nio_last_modified_at" >= '2026-02-01'
Key points:
  • Both datasets must have a geographic_poi.poi attribute mapped through Rosetta Stone
  • ST_Intersects returns true when the two geometries share any area in common
  • Select non-geometry columns only---latitude, longitude, timestamps, and identifiers

​
Point-to-polygon join

When one dataset has raw coordinates instead of POI polygons, use ST_SafePoint and ST_Circle to create a geometry on the fly, then join with ST_Intersects. 
SELECT
  obs."_rosetta_stone"."mobile_id_unique_identifier"."value" AS maid_value,
  obs."_rosetta_stone"."event_timestamp",
  pois.name AS poi_name
FROM company_data."observations" obs
INNER JOIN company_data."poi_locations" pois
  ON ST_Intersects(
    pois."_rosetta_stone"."geographic_poi"."poi",
    ST_Circle(
      ST_SafePoint(
        obs."_rosetta_stone"."geographic_location"."longitude",
        obs."_rosetta_stone"."geographic_location"."latitude"
      ),
      50
    )
  )
WHERE
  obs."_rosetta_stone"."event_timestamp" >= CURRENT_DATE - INTERVAL '30' DAY
  AND obs."_rosetta_stone"."nio_last_modified_at" >= CURRENT_DATE - INTERVAL '30' DAY
ST_Circle takes a point and a radius in meters. In this example, each observation is expanded into a 50-meter circle and checked for overlap with the POI polygons.

​
Combining geospatial and temporal filters

Most geospatial queries also need time-based filtering. Always include both an event_timestamp range and a nio_last_modified_at filter to scope the data appropriately. 
SELECT
  ls."_rosetta_stone"."geographic_location"."latitude",
  ls."_rosetta_stone"."geographic_location"."longitude",
  ls."_rosetta_stone"."mobile_id_unique_identifier"."value" AS maid_value,
  ls."_rosetta_stone"."event_timestamp"
FROM
  company_data."observations" ls
  INNER JOIN company_data."target_locations" t
    ON ST_Intersects(
      t."_rosetta_stone"."geographic_poi"."poi",
      ls."_rosetta_stone"."geographic_poi"."poi"
    )
WHERE
  ls."_rosetta_stone"."event_timestamp" BETWEEN '2026-02-01' AND '2026-04-01'
  AND ls."_rosetta_stone"."nio_last_modified_at" >= '2026-02-01'
  • event_timestamp filters the actual observation time
  • nio_last_modified_at ensures query efficiency by limiting the data scan window

​
Common pitfalls

​
GEOMETRY type cannot be in SELECT

Geometry columns cannot be returned in query results. Use them only in JOIN conditions or WHERE clauses. This fails: 
SELECT
  ls."_rosetta_stone"."geographic_poi"."poi" AS poi_geometry
FROM company_data."observations" ls
Instead, select non-geometry columns like latitude, longitude, or identifiers. See Unsupported Type Errors for details.

​
Argument order in ST_Intersects

Place the reference/POI dataset geometry as the first argument and the observation geometry as the second argument. Reversing the order can cause internal errors. 
-- Correct: reference geometry first
ON ST_Intersects(
  t."_rosetta_stone"."geographic_poi"."poi",
  ls."_rosetta_stone"."geographic_poi"."poi"
)

-- Incorrect: observation geometry first (may cause errors)
ON ST_Intersects(
  ls."_rosetta_stone"."geographic_poi"."poi",
  t."_rosetta_stone"."geographic_poi"."poi"
)

​
Always use _rosetta_stone

Access columns through the _rosetta_stone path. Queries that omit _rosetta_stone may appear to work in some cases, but this is not a supported pattern and will produce unreliable results. 
-- Correct
ls."_rosetta_stone"."geographic_poi"."poi"

-- Incorrect: missing _rosetta_stone
ls."geographic_poi"."poi"

​
Related content

Location Data

Understanding location polygons and geospatial formats

Geospatial Functions

ST_SafePoint, ST_Circle, ST_Intersects, and more

Complex Join Patterns

Advanced join recipes for multi-dataset scenarios

Unsupported Type Errors

Resolving GEOMETRY type errors in SELECT statements