duckdb / duckdb/duckdb-spatial
`segmentation fault` in `st_contains` spatial join
- Dominant language
- C
- Stars
- 708
- Forks
- 96
- Avg merge
- 1d 21h
- Merged PRs (30d)
- 5
Description
## Problem
Good morning, again!
When performing an `ST_ContainsProperly` or `ST_Within` of a centroid in another boundary (the centroids of a 152M record table compared against the large township boundaries of only a 2M record table) -- I'm getting a `segmentation fault`.
As an aside, I want to say that we are _very_ close to getting DuckDB to work for processing all our spatial data. This spatial join is the last blocker. DuckDB is performing faster than PostGIS. The work done here has been a blessing. I am grateful for everyone here at DuckDB!
## Traceback
```
segmentation fault
```
## Steps to re-create
Running this query against a row table stored in `.duckdb` file, locally.
```
select
b.row_id,
t.township
from parcel_batch_1 b
join './section_township_range.parquet' t
on st_containsproperly(st_centroid(b.geom), st_geomfromhexewkb(t.geom));
```
- I've added an R-Tree index to the `geom` column of the large, 152M record table, `parcel_batch_1`.
- I've added the query plan to this issue indicating the non-use of the R-tree index.
- Query gets to ~49% completion before hitting `segmentation fault`.
## Initial Query Plan
```
┌─────────────────────────────┐
│┌───────────────────────────┐│
││ Physical Plan ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐
│ PROJECTION │
│ ──────────────────── │
│ row_id │
│ township │
│ │
│ ~25000000 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ FILTER │
│ ──────────────────── │
│ ST_ContainsProperly │
│ (ST_Centroid(geom), │
│ ST_GeomFromHEXEWKB(geom))│
│ │
│ ~25000000 Rows │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ IE_JOIN │
│ ──────────────────── │
│ Join Type: INNER │
│ │
│ Conditions: │
│ ST_XMin(ST_Extent │
│ (ST_Centroid(geom))) <= │
│ ST_XMax(ST_Extent │
│(ST_GeomFromHEXEWKB(geom)))│
│ ST_XMax(ST_Extent │
│ (ST_Centroid(geom))) >= │
│ ST_XMin(ST_Extent ├──────────────┐
│(ST_GeomFromHEXEWKB(geom)))│ │
│ ST_YMin(ST_Extent │ │
│ (ST_Centroid(geom))) <= │ │
│ ST_YMax(ST_Extent │ │
│(ST_GeomFromHEXEWKB(geom)))│ │
│ ST_YMax(ST_Extent │ │
│ (ST_Centroid(geom))) >= │ │
│ ST_YMin(ST_Extent │ │
│(ST_GeomFromHEXEWKB(geom)))│ │
│ │ │
│ ~25000000 Rows │ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ SEQ_SCAN ││ PARQUET_SCAN │
│ ──────────────────── ││ ──────────────────── │
│ parcel_batch_1 ││ Function: │
│ ││ PARQUET_SCAN │
│ Projections: ││ │
│ geom ││ Projections: │
│ row_id ││ geom │
│ ││ township │
│ ││ │
│ ~25000000 Rows ││ ~2379868 Rows │
└───────────────────────────┘└───────────────────────────┘
```
## Alternate Attempt (no `segmentation fault`, but query hangs at 50%)
- I then created a `centroid` column on `parcel_batch_1` so the `st_centroid` needn't be calculated in the `join` condition, and materialized the Parquet file as a table. I added an R-Tree index on both `parcel_batch_1.centroid` and the `geom` column of the township (formerly the Parquet file) table. Still, no usage in query plan.
- I changed `ST_ContainsProperly` to `ST_Contains`, as well.
- Memory is under control, threads being used:

## Environment:
```
% uname -a
Linux john-XPS-15-9520 6.8.0-40-generic #40~22.04.3-Ubuntu SMP PREEMPT_DYNAMIC Tue Jul 30 17:30:19 UTC 2 x86_64 x86_64 x86_64 GNU/Linux
% duckdb --version
v1.1.2 f680b7d08f
```
**My Machine:**
- 64GiB Memory
- 20 CPU
- Dell XPS
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.