duckdb / duckdb/duckdb-spatial

ST_WITHIN doesn't seem to completely filter roads outside of its bounds

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

Description

The following should restrict to streets within the boundary they're within. I exported the table to a GPKG file and brought it into QGIS.

```sql
CREATE OR REPLACE TABLE joined1 AS
SELECT a.geom,
b.TR_CODE21,
b.TR_NAME21,
b.STE_CODE21,
b.STE_NAME21,
b.AUS_CODE21,
b.AUS_NAME21,
b.AREASQKM21,
b.LOCI_URI21,
b.SHAPE_Leng,
b.SHAPE_Area
FROM roads2 a
JOIN tourist_spots2 b
ON ST_WITHIN(a.geom, b.geom);

COPY (SELECT ST_AsWKB(geom) geom,
TR_CODE21,
TR_NAME21,
STE_CODE21,
STE_NAME21,
AUS_CODE21,
AUS_NAME21,
AREASQKM21,
LOCI_URI21,
SHAPE_Leng,
SHAPE_Area
FROM joined1
WHERE TR_NAME21='Gold Coast')
TO 'joined.gpkg'
WITH (FORMAT GDAL,
DRIVER 'GPKG',
LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES');
```

The roads are in yellow and the convex hull for the Gold Coast is in purple. There are a lot of roads that are outside of those bounds.

Screenshot 2023-04-07 at 18 21 06

Some are completely disconnected from any other road and outside of the boundary.

Screenshot 2023-04-07 at 18 17 18

Could there be a projection issue or something else that is affecting the filtering?

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.