duckdb / duckdb/duckdb-spatial

ST_DWITHIN with zero buffer faster than ST_WITHIN ?

Open
#826 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
C
Stars
708
Forks
96
Avg merge
1d 21h
Merged PRs (30d)
5

Description

Hi, I'm performing a spatial join with:

```
CREATE TABLE regions_obs AS
SELECT adm2_name, count(*) AS cnt
FROM atlas a
JOIN regions_qc r ON ST_Within(a.geom, r.geometry)
GROUP BY adm2_name;
```
The atlas table has 55 million rows and an RTREE index on the geometry. The regions_qc file has 17 polygons. The query above takes several hours and a lot of memory on a machine with 15 cores and 180GB of RAM.

However, this query

```
CREATE TABLE regions_obs AS
SELECT adm2_name, count(*) AS cnt
FROM atlas a
JOIN regions_qc r ON ST_DWithin(a.geom, r.geometry,0)
GROUP BY adm2_name;
```
Which, to my understanding, should be identical, finishes in about 15 minutes.

Any idea why that is ?

With Duckdb 1.5.3 and most recent spatial extension commit.

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.