[GIS] Transform GeoJSON geometry to WKB

geojsongeometrypostgiswell-known-binary

I need transform geometry from JSON like:

{"type":"MultiPolygon","coordinates":[[[[-125045.48351212002,4577567.588141698],[-124816.19981552364,4577552.93014355],[-124765.99472517562,4577419.175847012],[-124842.47121534991,4577392.905406596],[-125045.48351212002,4577567.588141698]]]]}

to WKB:

010600002031BF0D0001000000010300000001000000050000008FEF9C07089AFEC0B90A9856E87251410F355DB1B395FEC0DCCEADDCED72514194BE3130A693FEC0DFD23127D072514114797186FA97FEC094E0C313CB7251418FEF9C07089AFEC0B90A9856E8725141

I use SRID 900913

Best Answer

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)))

Related Solutions

[GIS] Spatialite – get geometry from WKB

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

PostGIS – Transforming Geometry to SRID 900913

If you run, using your original geometry:

SELECT  
    ST_AsText(
        ST_GeomFromEWKT('010600002031BF0D000100000001030000000100000004000000CE001744EA0212414BB26942EFE95641DD9AF00E49FF1441706072C152EC56413F53AF6BF9191441242DF04F7EC85641CE001744EA0212414BB26942EFE95641'
    ));

you will see:

MULTIPOLYGON(((295098.566494 6006717.0377012,344018.26458971 6009163.022606,329342.355161 5972473.2490342,295098.566494 6006717.0377012)))

or with you second EWKT string, you get:

MULTIPOLYGON(((-125344.50185865 4574113.3530299,-125275.23080178 4574135.4481084,-125242.38676619 4574016.612416,-125311.65782306 4573996.3088304,-125344.50185865 4574113.3530299)))

Both of these are already in 900913. If you look at the definition for EPSG:900913, which, note, is now called 3857, you will see your bounds are within those limits -- very large numbers, as meters covering the whole planet.

You second query seems wrong, as you are forcing this string to 4326, which it is not, and then converting it to 900913. In fact, if you wrap your query in ST_Astext, you will see that what you are getting back is not in 900913 at all.

SELECT 
    ST_AsText(
         ST_SetSRID(
              ST_AsText(
                  ST_Transform('010600002031BF0D0001000000010300000001000000050000008FEF9C07089AFEC0B90A9856E87251410F355DB1B395FEC0DCCEADDCED72514194BE3130A693FEC0DFD23127D072514114797186FA97FEC094E0C313CB7251418FEF9C07089AFEC0B90A9856E8725141'::geometry,
         4326)),
    900913));

returns:

MULTIPOLYGON(((-1.12598881799973 37.9623844064892,-1.1253665455084 37.9625408936206,-1.12507150251677 37.9616992426904,-1.1256937750081 37.9615554420694,-1.12598881799973 37.9623844064892)))

which is almost certainly 4326, lat/lon.

As it is already in 3857 (900913), all you need to do is wrap it in ST_SetSRID, ie,

SELECT ST_SetSRID(ST_GeomFromEWKT(<your ewkt geom>, 900913));

which will make the geometry you are loading have the SRID 900913 and get rid of the error message:

Geometry SRID (0) does not match column SRID (900913)

Best Answer

Related Solutions

[GIS] Spatialite – get geometry from WKB

PostGIS – Transforming Geometry to SRID 900913

Related Question