Reading geometry column from a GeoPackage database (sqlite3) with Python

[Neel Patel]

I am new to the geo spatial eco system and having trouble with the following:

• Unable to read a geometry column from the GeoPackage db file with python
• Also an explanation regarding the byte string will be really helpful
  

[Vedran Stojnović]

Geopackage has its own binary format with an additional header, it's not pure WKB, that's why you are getting an error.

If you would like to use it with wkb.loads you should remove the GeoPackage header data first. You have all details about the format in the GeoPackage specification.

--
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/ed386e6c-e087-4c2b-8028-101d2541a67bn%40googlegroups.com.

--

Srdačan pozdrav,

Vedran Stojnović.

[Neel Patel]

Thank you so much for your reply, I am still having issues with it, I was able to read a Point geometry but, the same does not work with a string. I had a tuff time understanding the header itself from the original source can you please suggest to me a better solution or read for the same.

You received this message because you are subscribed to a topic in the Google Groups "SpatiaLite Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/spatialite-users/fJSlg6qGUfc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to spatialite-use...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/CAL9iSMEOO-aUVg%2BiDY6MU88obMeS-MREAYYteB%2Bj_11nsJPt%2Bw%40mail.gmail.com.

--

Neel Patel

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

On Thu, 10 Feb 2022 17:40:09 -0500, Neel Patel wrote:
> Thank you so much for your reply, I am still having issues with it, I
> was able to read a Point geometry but, the same does not work with a
> string. I had a tuff time understanding the header itself from the
> original source can you please suggest to me a better solution or
> read
> for the same.
>

Hi Neel,

please note that in your SQL query you are not using SpatiaLite
at all:

SELECT geom FROM NRN_ON_14_0_FERRYSEG;

this is just a pure and simple SQLite query that will return
a generic BLOB (that is an opaque binary string).
in your specific case it will be a GeoPackage's own Geometry
adopting a binary encoding that is notably different from
WKB as you seem to naively expect.
just for the sake of completness: SpatiaLite has its own
internal binary encoding for Geometries that differs from
both GPKS and WKB, exactly as PostGIS has its own encodings
and so on.

general lesson: any Spatial DBMS has its specific binary
Geometries and none of them are pure WKB just because
WKB is a format intended for transparent data exchange
but is not well suited for storage purposes.

however any Spatial DBMS supports lots of SQL functions
allowing for converting back and forth from a binary
encoding to another one, so you just have to invoke
the appropriate SQL function for retriving a WKB from
your SQL query as you are expecting:

SELECT ST_AsBinary(geom) FROM NRN_ON_14_0_FERRYSEG;

ST_AsBinary() is a standard OGC function supported by
SpatiaLite and its role is to transform an internal
BLOB geometry into a standard WKB expression; it
works both with SpatiaLite and GPKG BLOBs.

bye Sandro

[Neel Patel]

Thank you so much for your detailed answer, I have tried the same but not sure why it returns me a None value for each instance in the table. I am working on this GeoPacakge.

>>> Next I tried to decode a point geometry in the following manner

- The only issue is this works for a POINT geometry, but fails for a Line String

- Yes, I am still not totally convinced about how the encoding of the binary headers from the following image correspond to the the hex value, that I was able to fetch from the BLOB value. I get the first few bytes but then it is confusing to me. (source)

Thank you so much in advance.

--
You received this message because you are subscribed to a topic in the Google Groups "SpatiaLite Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/spatialite-users/fJSlg6qGUfc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to spatialite-use...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/0e038cffff4ea17c2ad7772d289148aa%40lqt.it.

--

Neel Patel

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

On Fri, 11 Feb 2022 09:13:06 -0500, Neel Patel wrote:
> Thank you so much for your detailed answer, I have tried the same but
> not sure why it returns me a None value for each instance in the

> table. I am working on this GeoPacakge [4].
>

short review:

- GeometryType(geom): this works by pure hazard just because the first
bytes in GPKG and SpatiaLite BLOB Geometries have a similar pattern.
- ST_AsBinary(geom) and ST_AsText(geom): both fail because they
are expecting to receive a SpatiaLite's own BLOB Geometry and
not a GPKG one.

I've directly tested your GPKG and these are the correct SQL queries:

SELECT ST_AsBinary(GeomFromGPB(geom)) FROM NRN_ON_14_0_TOLLPOINT;

SELECT ST_AsBinary(GeomFromGPB(geom)) FROM NRN_ON_14_0_FERRYSEG;

note that they correctly work for both POINTs and LINESTRINGs

the critical function is GeomFromGPB() that converts the GPKG Blob
into a native one.

>>>> Next I tried to decode a point geometry in the following manner

> - The only issue is this works for a POINT geometry, but fails for a
> Line String
>

carefully read the GPKG documentation; as it's clearly explained
your POINTs have no Envelope, so the WKB expressiont start after
the first 8 header bytes.
but your Linestrings have an Envelope (minx,maxx,mimy,maxy) so
you have to skip 32 more bytes.
it depends on the value declared by the E flag bits.

bye Sandro

[Neel Patel]

Thank you again, this is very helpful. Really grateful for your help and explanation.

- Yes this solves the problem.

- Also, I figured out another way from the following article.

----------

Neel

--
You received this message because you are subscribed to a topic in the Google Groups "SpatiaLite Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/spatialite-users/fJSlg6qGUfc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to spatialite-use...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/54176987aee3c5b85ca211a210fe052f%40lqt.it.

--

Neel Patel