1

I want to use ogrinfo to get areas from a lat-long (EPSG:4326) layer right on the terminal shell, I'm using the query:

ogrinfo -dialect sqlite -sql "select st_area(st_transform(geom, 32615)) from restauraciones" restauraciones.gpkg

but st_transform returns the following:

Warning 1: unable to read srs_id '32615' from gpkg_spatial_ref_sys
ERROR 1: Target SRID (32615) is invalid

However, if I transform to kml (and change geom to geometry) I get the desired areas:

ogr2ogr -f kml restauraciones.kml restauraciones.gpkg
ogrinfo -dialect sqlite -sql "select st_area(st_transform(geometry, 32615)) from restauraciones" restauraciones.kml

A line from the output:

OGRFeature(SELECT):44
st_area(st_transform(geometry, 32615)) (Real) = 552225.44785029

How can I solve the gpkg_spatial_ref_sys error? I like to use ogr in the shell and I like gpkg.

1

GDAL supports SQLite dialect through a virtual table mechanism of SQLite https://gdal.org/user/sql_sqlite_dialect.html. It means that GDAL starts it's own SQLite database engine that is usually built with SQLite and supports thousands of projections, imports data from the native format like KML and runs the SQL query.

However, GeoPackage is a SQLite database itself and then the SQLite dialect does not go through the virtual table system but it is run directly with features which are supported by the GeoPackage db.

The error message Warning 1: unable to read srs_id '32615' from gpkg_spatial_ref_sys explains what happens. The SQL is executed in the GeoPackage database and by default the gpkg_spatial_ref_sys is populated only by minimum number of projection definitions. The exact command in the question gives most probably another error: no such function: st_area which is due to the same reason: query is executed in the GeoPackage db and it does not have native support for area function.

You can force the SQL query to use external SQLite engine even with GeoPackage by using another version of SQLite dialect -dialect indirect_sqlite. Now GDAL is forced to build virtual tables with the internal version of SQLite that has large number of projections available, as well as the SpatiaLite SQL functions.

Read again the documentation of the SQLite dialect, consider the note "This should be used only when necessary, since going through the virtual table mechanism might affect performance", and run your command this way:

ogrinfo -dialect indirect_sqlite -sql "select st_area(st_transform(geom, 32615)) from restauraciones" restauraciones.gpkg

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.