duckdb_spatial
duckdb_spatial copied to clipboard
ST_WITHIN doesn't seem to completely filter roads outside of its bounds
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.
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.

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

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