duckdb / duckdb/duckdb-spatial

`segmentation fault` in `st_contains` spatial join

Open
#438 3 comments 0 reactions 0 assignees View on GitHub
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:
![image](https://github.com/user-attachments/assets/50d81f5a-8337-4e4f-a863-99a2a41b1a8f)

## 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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.