Robin's Blog

How to convert hex-encoded WKB geometries in PostGIS

A quick post today to talk about a couple of PostGIS functions I learnt recently.

I had a CSV file that contained well-known binary (WKB) representations of geometries, stored as hexadecimal strings. I imported the CSV into a PostGIS database, and wanted to convert these to be proper PostGIS geometries.

I initially went for the ST_GeomFromWKB function, but kept getting an error that the function didn’t exist. Well, actually the error said that it couldn’t find a function that exists with that name and those specific parameter types. That’s because I was calling it with the text column containing the hex strings, and the documentation for ST_GeomFromWKB says that its signature is one of:

geometry ST_GeomFromWKB(bytea geom);
geometry ST_GeomFromWKB(bytea geom, integer srid);

So, we need to convert the hexadecimal string to a bytea type – that is, a proper set of bytes. We can do this using the decode function, which takes two parameters: the text to decode, and a format specifier which must be one of base64, escape or hex. In this case, we’re decoding a hex string, so we want the latter.

Putting this together, we can write some simple SQL that does what we want. First, we create a geom column in our table:

ALTER TABLE test ADD geom Geometry;

and then we set that column to be the result of decoding the hex and converting the WKB to a geometry:

UPDATE test SET geom = ST_GeomFromWKB(decode(wkb_column, 'hex'), 4326);

Note that here I knew that my WKB was encoding a point in the WGS84 latitude/longitude co-ordinate system, so I passed the EPSG code 4326 which refers to this co-ordinate system.

If you found this post useful, please consider buying me a coffee.
This post originally appeared on Robin's Blog.

Tagged with:

Categorised as: Computing, GIS, How To, Programming