contains vs intersects in docs sql queries

[GregoryCMiller]

Im posting here because the docs repo does not have discussions.

In the docs, I expected from the text context that the data query examples return features intersecting the region of interest. However, most of the example bbox queries are for features fully contained within the region of interest.

340 features (7%) are missing from the paris roads query because they extend beyond the region of interest.

SELECT 
  count(1) 
FROM read_parquet('s3://overturemaps-us-west-2/release/2025-05-21.0/theme=transportation/type=segment/*', filename=true, hive_partitioning=1) 
WHERE ( 
  bbox.xmin < 2.314 
  AND bbox.ymin < 48.882 
  AND bbox.xmax > 2.276 
  AND bbox.ymax > 48.865 
  ) 
AND NOT ( 
  bbox.xmin > 2.276 
  AND bbox.ymin > 48.865 
  AND bbox.xmax < 2.314 
  AND bbox.ymax < 48.882 
  )

┌──────────┐
│ count(1) │
│  int64   │
├──────────┤
│      340 │
└──────────┘

Its not obvious to everyone. im an expert and i had to get out some paper.

[stepps00]

Thanks for the issue @GregoryCMiller. As you point out, the existing transportation query example results in features fully contained within the bounding box being passed. That method works well for point geometries, but excludes some expected, intersecting features when handling line and polygon geometries (as in the case with transportation segments).

LOAD spatial; -- noqa

SET s3_region='us-west-2';

COPY(
    SELECT
       id,
       names.primary as name,
       class,
       geometry   -- DuckDB v.1.1.0 will autoload this as a `geometry` type
    FROM read_parquet('s3://overturemaps-us-west-2/release/2025-05-21.0/theme=transportation/type=segment/*', filename=true, hive_partitioning=1)
    WHERE 
      bbox.xmin < 2.314 
      AND bbox.ymin < 48.882 
      AND bbox.xmax > 2.276 
      AND bbox.ymax > 48.865 
) TO 'paris_roads.geojson' WITH (FORMAT GDAL, DRIVER 'GeoJSON');

This should result in the expected 4713 features. I'm going to suggest we update the documentation to include this query and clarify intersecting vs. fully contained.

[GregoryCMiller]

thanks @stepps00 !

[jwass]

We should probably just document both. The Python library does the bbox intersection vs. contains: https://github.com/OvertureMaps/overturemaps-py/blob/main/overturemaps/core.py#L23-L30