Converting WKB HexString (Text) to Geometry

[Dierickx Dominique]

We use sqlite's '.import' to load CSV files into SpatiaLite tables.

.import source.csv target_table

The source.csv file contains columns which contain WKB geometry as a hex string (eg. 0000000001400CF4BF246D9D92404984C272D6B731). The corresponding column type is "text".

After the import has been finished, we add a SpatiaLite geometry column like this:

SELECT AddGeometryColumn('target_table','GEOM',4326, 'POINT', 'XY');

What I then want to do is something like this:

UPDATE target_table SET GEOM=GeomFromWKB(GEOM_WKB, 4326);

This works fine when I use WKT and SpatiaLite's WKT functions. But for some reason I can't get it to work using WKB.

I have seen examples like this and they work fine:

SELECT AsText(GeomFromWKB(X'010100000000000000000024400000000000003440'))

But, how can I replace the manually input string value here with a value bound to a column, like so:

UPDATE target_table SET GEOM=GeomFromWKB(X(target_table.GEOM_WKB), 4326)

Hereby doing a conversion of the hexstring to binary. Is there a function call I can use instead of the X literal notation?

Thanks all for your help!

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

Really an interesting question.
Apperently there is no way to perform such a conversion simply using
SQL statements and functions.

x'0102cdef' simply is a notation supporting *text literals*
there is no way to use this notation for *text variable values*; e.g.
any
attempt to directly store a text string starting with the 'x' prefix
will
simply produce an ordinary string value (not at all implying
hexadecimal),
as in: 'x''0102cdef'''

accordingly to all this, the next splite 4.1.0 will support a mew
CastToBlob()
SQL function specifically intended to support hexadecimal notations:
e.g.

SELECT
AsText(GeomFromWKB(CastToBlob('010100000000000000000024400000000000003440',
1)));
------
POINT(10 20)

Code already committed into the Fossil repository.
for any further detail please see:
https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.1.0+Changes

bye Sandro

--
Il messaggio e' stato analizzato alla ricerca di virus o
contenuti pericolosi da MailScanner, ed e'
risultato non infetto.