> ## 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 POI Joins

> Join datasets on point of interest geometries using ST_Intersects

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.

```sql theme={null}
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`.

```sql theme={null}
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.

```sql theme={null}
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:**

```sql theme={null}
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](/guides/nql/troubleshooting/unsupported-type-error) 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.

```sql theme={null}
-- 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.

```sql theme={null}
-- Correct
ls."_rosetta_stone"."geographic_poi"."poi"

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

***

## Related content

<CardGroup cols={2}>
  <Card title="Location Data" icon="location-dot" href="/concepts/data-formats/location-data">
    Understanding location polygons and geospatial formats
  </Card>

  <Card title="Geospatial Functions" icon="map-location-dot" href="/nql/functions/geospatial-functions">
    ST\_SafePoint, ST\_Circle, ST\_Intersects, and more
  </Card>

  <Card title="Complex Join Patterns" icon="diagram-project" href="/cookbooks/nql/complex-joins">
    Advanced join recipes for multi-dataset scenarios
  </Card>

  <Card title="Unsupported Type Errors" icon="ban" href="/guides/nql/troubleshooting/unsupported-type-error">
    Resolving GEOMETRY type errors in SELECT statements
  </Card>
</CardGroup>