2

Spatialite and GPKG mode are enabled:

SELECT load_extension('mod_spatialite');
SELECT EnableGpkgMode();

Spatialite knows about gpkg_spatial_ref_sys (gpkgInsertEpsgSRID() uses it), and the table is populated:

sqlite> SELECT srs_name, srs_id FROM gpkg_spatial_ref_sys;
Undefined cartesian SRS|-1
Undefined geographic SRS|0
WGS 84 geodetic|4326
Amersfoort / RD New|28992

Yet when I try to use ST_Transform() it tries to look for (and fails to find) spatial_ref_sys:

sqlite> SELECT ST_Transform(gpkgMakePoint(5.140743, 51.630698, 4326), 28992);
unknown SRID: 4326      <no such table: spatial_ref_sys>
unknown SRID: 28992     <no such table: spatial_ref_sys>
Error: ST_Transform exception - unable to find the origin SRID.

What am I doing wrong? Does GPKG mode not support ST_Transform()? What is gpkgInsertEpsgSRID() for then?

Version info:

sqlite> SELECT spatialite_version(), sqlite_version();
5.0.1|3.34.1
1
  • 1
    SpatiaLite works internally on SpatiaLite geometries. For using the functions you must a) cast GPKG geometries into SpatiaLite geometries (happens to some extent automagically) and b) have all SpatiaLite support tables available. You need to execute also InitSpatialMetaData. gpkgInsertEpsgSRID() needs to be there because GPKG is not valid without GPKG metadata tables.
    – user30184
    Sep 30 '21 at 14:12
1

As @user30184 says it is in fact necessary to have a separate spatial_ref_sys table. However, instead of using InitSpatialMetadata() and getting many other tables, the following SQL seems to have worked as well (for ST_Transform() at least).

CREATE TABLE spatial_ref_sys (
  srid       INTEGER NOT NULL PRIMARY KEY,
  auth_name  VARCHAR(256),
  auth_srid  INTEGER,
  srtext     VARCHAR(2048),
  proj4text  VARCHAR(2048)
);
INSERT INTO spatial_ref_sys SELECT
  srs_id AS srid,
  organization AS auth_name,
  organization_coordsys_id AS auth_srid,
  definition AS srtext,
  NULL
FROM gpkg_spatial_ref_sys;
1

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.