GPKG Mode with PROJ6 & Topology

[Jan Vaillant]

Hello List & Sandro,

it seems that with the new PROJ6 (proj.db is present) and topology
functions if I open a GeoPackage and enable GPKG mode the corresponding
GPKG tables "gpkg_spatial_ref_sys" and "gpkg_geometry_columns" are ignored.

I got errors when I tried to "Transform" and tried to create a topology
from a GeoPackage I looked into the source and although the functions
handle GPKG geometries (GPKG and amphibious mode) they do ignore the
"gpkg_" prefix when performing queries on "gpkg_spatial_ref_sys" and
"gpkg_geometry_columns".

In order to "Transform" I have to issue a "select initspatialmetadata()"
although a "gpkg_spatial_ref_sys" is present. Is that intended?

Thank you,

Jan

[a.fu...@lqt.it]

Hi Jan,

it's not specifically intended, but it's just a direct consequence of
the architecture.

when SpatiaLite is called by the SQLite's own SQL engine to process
ST_Transform() it simply receives two arguments:
1. the BLOB geometry to be transformed
2. the intended destination SRID

as you can easily see, there is absolutely nothing specifying if
the BLOB-Geometry initially was a GPKG geometry or whatever else;
when ST_Tranforms() receives the BLOB it has just become a SpatiaLite
native geometry as any other, and there is absolutely nothing
that can suggest it's real ancestry.

all this considered, it's now obvious why SpatiaLite can't
look for "gpkg_spatial_ref_sys" and friends; it's because
we haven't the slighest clue that it initially was a GPKG
geometry.

a more general consideration: SpatiaLite is not a tool
intended to fully support GPKS.
it simply is a library mainly intended for implementing
SpatiaLite itself; whenever is possible it will make any
honest effort to support GPKG as well, but it's more
a byproduct than the main design goal.

bye Sandro

[Andrea Peri]

Hi Sandro, I never will understand what rational reason explain why the need for the define of a  geopackage format by the opengis when there was just available the spatialite format.

 

Perhaps the need for more chaos and misunderstanding ?

:)

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/b7e1bbfbae29fb9bdbe82c8ae4520d62%40lqt.it.

[Jan Vaillant]

Hi Sandro,

On 9/11/21 9:20 AM, a.fu...@lqt.it wrote:

> a more general consideration: SpatiaLite is not a tool
> intended to fully support GPKS.
> it simply is a library mainly intended for implementing
> SpatiaLite itself; whenever is possible it will make any
> honest effort to support GPKG as well, but it's more
> a byproduct than the main design goal.

Fair enough - understood. I guess fully supporting both is indeed a lot
of work.

> all this considered, it's now obvious why SpatiaLite can't
> look for "gpkg_spatial_ref_sys" and friends; it's because
> we haven't the slighest clue that it initially was a GPKG
> geometry.

Since I saw many functions where you check first if gpkg or gpkg amphi
mode is enabled to apply the conversion of the geometry into
SpatiaLite's native format I though that likewise it would be possible
to prefix the lookup table (geometry_columns & ref_sys) accordingly. But
I'd guess they even have different table schemas .. :\

Bye,

Jan

[Jan Vaillant]

P.S.:

Sorry, forgot to mention something important:

It actually did work with PROJ < 6

sqlite3 london_boroughs.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .tables
gpkg_contents gpkg_tile_matrix_set
gpkg_extensions london_boroughs
gpkg_geometry_columns rtree_london_boroughs_geom
gpkg_ogr_contents rtree_london_boroughs_geom_node
gpkg_spatial_ref_sys rtree_london_boroughs_geom_parent
gpkg_tile_matrix rtree_london_boroughs_geom_rowid
sqlite> select load_extension('/usr/local/lib/mod_spatialite');
sqlite> select proj_version();
Rel. 5.2.0, September 15th, 2018
sqlite> select enablegpkgmode();
sqlite> select getgpkgmode();
1
sqlite> select transform(geom, 4326) from london_boroughs limit 1;
unknown SRID: 27700    <no such table: spatial_ref_sys>
unknown SRID: 4326    <no such table: spatial_ref_sys>
GP

I get the warnings but then the GPKG geom. With PROJ > 6 it fails.

Jan

[Even Rouault]

Le 11/09/2021 à 10:08, Andrea Peri a écrit :
> Hi Sandro, I never will understand what rational reason explain why
> the need for the define of a  geopackage format by the opengis when
> there was just available the spatialite format.
> Perhaps the need for more chaos and misunderstanding ?

I don't believe there was a will to harm Spatialite when GeoPackage was
born, but it is more than:

- A standardization committee gives a venue, relatively vendor neutral,
to different actors to give their voice and have a documented decision
process on how evolutions are made to a specification (which doesn't
mean that specifications resulting from standardization bodies are
superior to the ones with more grass root origins). One could also
discuss if OGC with a pay-for-play policy meets the open source ethics
(but there a few free seats available to members of the OSGeo
community), but that's another story.

- A number of actors need for various reasons (regulations, buying
processes) a formal specification from an official standardization body
of a format to adopt it, not just a working open source implementation.

Even


--
http://www.spatialys.com
My software is free, but my time generally not.

[a.fu...@lqt.it]

On Sat, 11 Sep 2021 10:31:00 +0200, Jan Vaillant wrote:
> Since I saw many functions where you check first if gpkg or gpkg
> amphi
> mode is enabled to apply the conversion of the geometry into
> SpatiaLite's native format I though that likewise it would be
> possible
> to prefix the lookup table (geometry_columns & ref_sys) accordingly.
> But
> I'd guess they even have different table schemas .. :\
>

Jan,

such an approach will never work.

all right, we can easily imagine to keep some trace in the BLOB
geometry stating "I come from GPKG".

but what will then happen to SQL functions returning a single
geometry from two previous geometries ?
(think of ST_Union(), ST_Difference(), ST_Intersection and alike)

what should be the result of e.g. Union(gpkg_geom, splite_geom) ?
we have obviously reached a dead end.

---------

what it could be possible (at least in pure theory) is to
introduce a further specialized SQL function:

ST_TransformGPGG()

a clone of ST_Transform() except in that it will try to
access gpkg_spatial_ref_sys instead of spatial_ref_sys.

bye Sandro