Convert SQLite3 Geometry BLOB to WKB for PostgreSQL

Question

I have used iterdump() to create an output file from a SQLite database that includes SpatiaLite data. I need to convert the hex data to a geometry in PostgreSQL.

I have found here a summary of how the hex data is constructed, but I have not been able to discern how I can manipulate the data to get an ST_ASTEXT() function to work properly (or ST_ASEWKB() or ST_ASWKB()).

For example, the following was written as the data for a location field:

X'0001E610000095A40B52A3A557C042EAC26F5C8C434009F1E36998A557C0DDE8B25E5E8C43407C020000000200000009F1E36998A557C042EAC26F5C8C434095A40B52A3A557C0DDE8B25E5E8C4340FE'

From the link above, I learned that:

1. SpatiaLite internally stores geometry values using ordinary SQLite's BLOB columns in a format that is very closely related to WKB format, but not exactly identical.
2. "a GEOMETRY encoded BLOB value must always start with a 0x00 byte" (first two digits)
3. "a GEOMETRY encoded BLOB value must always end with a 0xFE byte" (last two digits)

Left as-is, PostgreSQL returns SQL Error [XX000]: ERROR: Unknown WKB type (31854592)!

If I remove the first two digits (00) the error is curiously different, in that it reflects the correct SRID: SQL Error [XX000]: ERROR: Unknown WKB type (4326)!

Removing the next two digits (01) results in an invalid Geometry error.

What must I do to get the following SQL to return a valid result?

SELECT ST_ASTEXT('0001E610000095A40B52A3A557C042EAC26F5C8C434009F1E36998A557C0DDE8B25E5E8C43407C020000000200000009F1E36998A557C042EAC26F5C8C434095A40B52A3A557C0DDE8B25E5E8C4340FE')

Answer 1

Bytes 0-38 are all specific to SpatiaLite BLOB and WKB begins after them. You can find Java code from https://github.com/openjump-gis/openjump/blob/main/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteValueConverterFactory.java#L105. After that place comes also code for extracting WKB from GeoPackage BLOBs.

• CNIDog: Thanks. I have marked the answer as useful. Once I get it translated to function properly in python (my familiarity with Java is nominal at best) I will be happy to mark the question as answered.