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:


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')

1 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.

  • 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.
    – CNIDog
    Commented Mar 10, 2023 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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