Understanding the format: WKB from WKT and how to convert the first into the latter [closed]

Question

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.

Answer 1

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.