Is it possible to query a geopackage (.gpkg) for features within a given bounding box?

Question

I have GIS data with a very large layer stored within a geopackage from where I want to select features that fall within a bounding box calculated in my R session. I begin by creating a query string:

query<-"SELECT * FROM buildings where ST_Intersects(geom, 'SRID=3006; POLYGON((753028 , 7084328, 764500,7093410))');"

Then I use it in an st_read call:

buildings<-st_read(dsn="database.gpkg",layer="buildings", query=query,driver="GPKG")

The result is the entire layer, and not the subset that I had intended. Is there anything else I have to do in order to be able to run these spatial queries?

EDIT: I have also tried a different version of the query:

query<-"SELECT * FROM buildings where ST_Intersects(geom, ST_GeomFromText( POLYGON((753028 , 7084328, 764500,7093410))',3006)));"

EPSG::3006 has axes of Northing/Easting, is it possible you have the axes in the wrong order in your bounding box? — nmtoken
– nmtoken, Commented Nov 15, 2019 at 17:34
It may be. I am just passing the results of sf st_bbox to a paste() call that builds the query. — Kenji
– Kenji, Commented Nov 15, 2019 at 17:35
So st_bbox( ) gives me coordinates in the order: xmin, ymin, xmax, ymax. Do you reckon that I'd need them to be xmin, xmax, ymin, ymax instead? — Kenji
– Kenji, Commented Nov 15, 2019 at 17:39
The problem is what are X and Y? Generally X should be taken as the first listed axis (North) and Y the second listed axis (East) — nmtoken
– nmtoken, Commented Nov 15, 2019 at 17:42

karlruf · Accepted Answer · 2022-11-09 20:38:20Z

I realise that this question is old, but I wanted to leave a reply for future reference. I was also trying to include a spatial filter in the query argument of st_read(). Instead of using the query argument there is a dedicated WKT filter implemented as argument of the function. You can use this to include a bounding box as filter.

library(sf) # Convert bounding box geometry to WKT bboxWKT <- POLYGON((1 2,1 4,3 4,3 2,1 2)) # Alternative if you want to use an sf as direct input bboxWKT <- st_as_text(st_geometry(bbox)) # Apply bbox as spatial filter buildings <- st_read(dsn="database.gpkg", layer="buildings", wkt_filter=bboxWKT)

Mustafa Uçar · Accepted Answer · 2022-02-08 10:27:20Z

Try that;

SELECT * FROM buildings WHERE (buildings.geom && ST_MakeEnvelope(753028,7084328,764500,7093410, 3006))

Stack Exchange Network

Is it possible to query a geopackage (.gpkg) for features within a given bounding box?

2 Answers 2

Hot Network Questions

Is it possible to query a geopackage (.gpkg) for features within a given bounding box?

2 Answers 2

Related

Hot Network Questions