duckdb / duckdb/duckdb-spatial
ST_DWITHIN with zero buffer faster than ST_WITHIN ?
- 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.