Skip to main content
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