8

I don't understand which kind of format I have my data. The column name is wkb_geom, so I supposed that data were in WKB format, but then I was checking around and I couldn't find example of it. Data are like this:

"0106000020E6100000010000000103000000010000007218000007000060B1D42B4010000060A372454007000060B1D42B40030000009D724540030000E0D5D42B40030000009D724540030000E0D5D42B40050000C08A7245400B000040FAD42B40050000C08A7245400B000040FAD42B40130000807B7245400B000040FAD4 (...)"

Is it in WKB or WKT format?? Second question, in case it's in WKB format, how can I convert it into WKT format? I was trying to follow this suggestion

How to convert WKB to WKT?

so the query is

UPDATE "ita_adm1"
SET wkb_geometry  = ST_GeomFromWKB("wkb_geometry",4326)

but it keeps saying that the function ST_GeomFromWKB doesn't exist.

  • 1
    That's a hexadecimal dump of WKB, not WKB itself. – Vince May 6 '15 at 15:12
  • 1
    What software environment are you using? Is it PostGIS as per the example or something else?. Also, if you are using Postgresql, do you have PostGIS installed (it is not a standard part of Postgresql - which could explain why the function is missing - can you confirm? Finally PostGIS stores geometry in a hexadecimal format (note Vince's comment), so have you tried ST_AsText? – MappaGnosis May 6 '15 at 16:17
  • yes I use PostGIS with the postgreSQL environment. I have tried that one too as it was suggested in the thread I already followed...Paul Ramsey's functions I've tried them all.. Btw with that query UPDATE "ita_adm1" SET wkb_geometry = ST_AsText("wkb_geometry") that's the error I get ERRORE: Geometry SRID (0) does not match column SRID (4326) – user3520314 May 7 '15 at 14:01
17

Generally speaking, this is called hex-encoded WKB. This specific example is the extended version, called EWKB, since it has SRID=4326 as found by E6100000.

WKB can be viewed in a few forms. The hex-encoded string representation is the most common, which if it is actually text can be converted using a simple ::geometry cast:

SELECT ST_AsText(wkb_geometry), ST_AsEWKT(wkb_geometry)
FROM (
  SELECT '0106000020620D000001000000010300000001000000040000000000000'
  '00000000000000000000000000000000000000000000000000000F03F000000000'
  '0000040000000000000004000000000000000000000000000000000'::geometry AS wkb_geometry
) AS f;
-[ RECORD 1 ]------------------------------------------
st_astext | MULTIPOLYGON(((0 0,0 1,2 2,0 0)))
st_asewkt | SRID=3426;MULTIPOLYGON(((0 0,0 1,2 2,0 0)))

Only use ST_GeomFromWKB if it is a raw bytea binary stream.

Furthermore, when geometry data is selected from a PostGIS database, the hex-encoded EWKB representation is shown in the query result. To get WKT or EWKT representations, use the ST_AsText or ST_AsEWKT functions, as demonstrated above.

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