Using Spatialite.
If the string is for example '0x0101000000081236FFF5D106C01C25B4DEE5AE4A40'
SELECT CastToBlob(replace('0x0101000000081236FFF5D106C01C25B4DEE5AE4A40','0x',''),1) AS x FROM table
SELECT CastToBlob(replace([WKBhexAStextDUMPfromSOMEdbColumn],'0x',''),1) AS x FROM table
Use ST_GeomFromGeoJSON
.
SELECT ST_GeomFromGeoJSON('{"type":"MultiPolygon","coordinates":[[[[-125045.48351212002,4577567.588141698],[-124816.19981552364,4577552.93014355],[-124765.99472517562,4577419.175847012],[-124842.47121534991,4577392.905406596],[-125045.48351212002,4577567.588141698]]]]}');
Returns:
010600000001000000010300000001000000050000006B3477BC5787FEC0141DA4E54776514134C371320379FEC0D078873B447651411DF264EADF75FEC0D31341CB22765141541B198AA77AFEC0822EF2391C7651416B3477BC5787FEC0141DA4E547765141
To set the SRID (3857: Google Mercator) and transform to EPSG 4326:
ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON('{"type":"MultiPolygon","coordinates":[[ ... ]]]}'), 3857), 4326)
Note that without setting the SRID, PostGIS will "assign" the SRID of 0. Your question did not indicate a projection, but it looks like Google Mercator (EPSG 3857). Your comments indicate you want 4326, so once the SRID has been correctly set, you can transform to that.
This returns:
0106000020E6100000010000000103000000010000000500000074A6F23D0CF9F1BF443A04EE50FE424051E7FD7F9CF0F1BFA74F72874DFE424040E59997C3EEF1BFE64B6F7F2EFE4240A669C2F693F1F1BF8356276728FE424074A6F23D0CF9F1BF443A04EE50FE4240
Which in WKT is:
MULTIPOLYGON(((-1.12330269049053 37.9868447800295,-1.12124300000001 37.986741,-1.12079200000001 37.985794,-1.121479 37.985608,-1.12330269049053 37.9868447800295)))
Best Answer
ST_GeomFromWKB()
takes the a WKB as input data typebytea
.Not sure why your example uses the old escape string syntax for the string literal. Maybe because the examples in the manual still do so, too.
(The manual has eventually been updated. Postgres 9.3 or later show modern notation.)
It's not wrong, just needlessly noisy. Unless you are running a very old Postgres instance with the setting
standard_conforming_strings = off
, you can replace it with the modern, standard form:The above is the escape format for a
bytea
literal. The manual advises:So, double "old-school" syntax. Alternatively, you can use the shorter hex format:
Related: