Hello! I am trying to implement support for doing SQL queries through the ExecuteSQL
functions. I've got basic functionality working: you can pass a SQL query to Dataset::execute_sql
and you get back a handle to a new type ResultSet
, which is a thin wrapper around a handle to a Layer
and the layer's Dataset
. The new type was necessary because SQL result sets must be freed explicitly, whereas regular layers owned by a Dataset
do not.
However, I'm submitting a draft, as I'd like your feedback on the changes, and also because I'm running into a problem, and I was hoping maybe you could help. It seems that there is some kind of problem with choosing the sqlite dialect. Using ogrinfo
works just fine using sqlite with spatialite functions:
✦ ❯ ogrinfo -dialect sqlite -sql "select * from roads where highway = 'pedestrian' and NumPoints(GEOMETRY) = 3" -spat 26.1017 44.4297 26.1025 44.4303 fixtures/roads.geojson
INFO: Open of `fixtures/roads.geojson'
using driver `GeoJSON' successful.
Layer name: SELECT
Geometry: Line String
Feature Count: 1
Extent: (26.102255, 44.429870) - (26.102745, 44.430318)
Layer SRS WKT:
GEOGCRS["WGS 84",
DATUM["World Geodetic System 1984",
ELLIPSOID["WGS 84",6378137,298.257223563,
LENGTHUNIT["metre",1]]],
PRIMEM["Greenwich",0,
ANGLEUNIT["degree",0.0174532925199433]],
CS[ellipsoidal,2],
AXIS["geodetic latitude (Lat)",north,
ORDER[1],
ANGLEUNIT["degree",0.0174532925199433]],
AXIS["geodetic longitude (Lon)",east,
ORDER[2],
ANGLEUNIT["degree",0.0174532925199433]],
ID["EPSG",4326]]
Data axis to CRS axis mapping: 2,1
Geometry Column = GEOMETRY
kind: String (0.0)
sort_key: Real (0.0)
is_link: String (0.0)
is_tunnel: String (0.0)
is_bridge: String (0.0)
railway: String (0.0)
highway: String (0.0)
OGRFeature(SELECT):0
kind (String) = path
sort_key (Real) = -9
is_link (String) = no
is_tunnel (String) = no
is_bridge (String) = no
railway (String) = (null)
highway (String) = pedestrian
LINESTRING (26.1027453 44.4303179,26.1024025 44.4300045,26.102255 44.4298696)
However, doing the same query from inside a unit test, it seems like it is not using the sqlite dialect, even though there are no errors that say as much. The failing unit test is test_sql_with_dialect
. It currently fails with:
thread 'vector::vector_tests::test_sql_with_dialect' panicked at 'called `Result::unwrap()` on an `Err` value: CplError { class: 3, number: 1, msg: "Undefined function \'NumPoints\' used." }', src/vector/vector_tests/mod.rs:133:73
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace
I'm not sure if there's something special that needs to be done at compile time to enable sqlite/spatialite support, and my search-fu has not been very effective. Do you have any ideas about what is needed to get sqlite working?
Work left
- [X] I agree to follow the project's code of conduct.
- [x] Add an entry to
CHANGES.md
- [x] Add documentation for
Dataset::execute_sql
and ResultSet
- [x] Get SQLITE dialect working
- [x] Add more unit tests around the boundaries of the API: empty result sets, erroneous queries, etc.