duckdb_spatial icon indicating copy to clipboard operation
duckdb_spatial copied to clipboard

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

Open marklit opened this issue 1 year ago • 8 comments

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.

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?

marklit avatar Apr 07 '23 15:04 marklit