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
For example, the following was written as the data for a
From the link above, I learned that:
- 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.
- "a GEOMETRY encoded BLOB value must always start with a 0x00 byte" (first two digits)
- "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?