7

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

Improve this question
3
  • 1
    I suspect you have just pasted sample data, but the JSON and the WKB do not represent the same geometry.
    – alphabetasoup
    Commented Apr 27, 2016 at 12:16
  • For reference, the PostGIS docs, Chapter 14, include an easy-browsing, one-page list of the SQL/MM std "ST_*" functions here.
    – MC5
    Commented Apr 27, 2016 at 13:29
  • 2
    You indicate in comments to suggested answers that your data are in SRID 4326, and select an answer based on that. But the sample coordinates are clearly not lat-long. There is a danger that an inexperienced user will see this question and apply the chosen answer not realizing that it is assigning an SRID that is incorrect for their data. Please consider adding information about the known SRID to your question and using sample coordinates that are possible in that SRID.
    – Lee Hachadoorian
    Commented Apr 27, 2016 at 14:58

4 Answers 4

Reset to default
11

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)))
Improve this answer
2
  • Good but RSID is 0, I need RSID 4326
    – josemi
    Commented Apr 27, 2016 at 14:16
  • Your example data is not in 4326.
    – alphabetasoup
    Commented Apr 27, 2016 at 19:04
7

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]]]]}')

Result:

"010600000001000000010300000001000000050000006B3477BC5787FEC0141DA4E54776514134C371320379FEC0D078873B447651411DF264EADF75FEC0D31341CB22765141541B198AA77AFEC0822EF2391C7651416B3477BC5787FEC0141DA4E547765141"
Improve this answer
1
  • Good but RSID is 0, I need RSID 4326
    – josemi
    Commented Apr 27, 2016 at 14:16
7

Your GeoJSON sample lacks information about the coordinate reference system, although you say it is SRID 900913. This is so-called Google Mercator or Web Mercator, which has been superseded by EPSG:3857, which I will use in this example. An important question is whether you really want Well-Known Binary, which does not include an SRID, or Extended Well-Known Binary, which does. As PostGIS uses EWKB internally ST_GeomFromGeoJSON will produce a geometry with the SRID, but since your test data does not include an SRID, the EWKB will be created with SRID of 0.

(In the code below I omit the lengthy GeoJSON for readability.)

So for example, the geometry can be converted with ST_GeomFromGeoJSON('...'), but

SELECT ST_SRID(ST_GeomFromGeoJSON('...'));

returns 0. If your data are in Web Mercator, and you tried to insert the result into a PostGIS table with a geometry column defined as Web Mercator, the insert would fail.

Nonetheless, if this is what you want (WKB, no embedded SRID), use:

SELECT ST_AsBinary(ST_GeomFromGeoJSON('...'));

If you want EWKB (WKB with an SRID), you have to use the ST_SetSRID function to assign the correct ID. You would then return the EWKB using:

SELECT ST_AsEWKB(ST_SetSRID(ST_GeomFromGeoJSON('...'), 3857));

Note that since PostGIS geometries are all EWKB, the ST_AsEWKB function is possibly unnecessary. But since you haven't specified the use I'm not sure if you would run into data type trouble if you skip it.

Finally, if, as you indicate in your comments to various proposed answers, you actually want the final result to be in EPSG:4326 (WGS 84 lat-long coordinates), you have to apply the ST_Transform function after defining the SRID with ST_SetSRID:

SELECT ST_AsEWKB(ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON('...'), 3857), 4326));
Improve this answer
2
  • I need to SRID 4326
    – josemi
    Commented Apr 27, 2016 at 14:15
  • You mean you need to transform it to 4326? Because the coordinates you posted are not 4326 (lat-long, maximum value would be +/-180). Do you know original coordinate reference system?
    – Lee Hachadoorian
    Commented Apr 27, 2016 at 14:22
4 
SELECT ST_GeomFromText(
  ST_AsText(
    ST_GeomFromGeoJSON('YOUR GEOJSON')
  ), 
4326)
Improve this answer
7
  • Thanks, it is that i wanted, the only finally the SRID not is 4326 is 900913, i changed it and work perfectly
    – josemi
    Commented Apr 27, 2016 at 14:38
  • Glad it help you =)
    – Jose Hermosilla Rodrigo
    Commented Apr 27, 2016 at 14:47
  • 5
    I have to disagree with this manner of assigning the SRID. There is an ST_SetSRID function which takes a geometry and an SRID, so rather than nesting ST_GeomFromText(ST_AsText(...), <srid>) you should use ST_SetSRID(ST_GeomFromGeoJSON(...), <srid>).
    – Lee Hachadoorian
    Commented Apr 27, 2016 at 14:54
  • You're right! PostGIS has a specific method to append the SRID as you mention in your post and this is the right way. But I wanted to share this approach that works and it's a simple way.
    – Jose Hermosilla Rodrigo
    Commented Apr 27, 2016 at 15:10
  • 3
    -1 Using ST_SetSRID is simpler, faster and clearer that converting from WKB to text and back again. Moreover, the data isn't in 4326 so this will set an incorrect SRID. You can check this by inspecting the WKT of the result: despite the result being in EPSG 4326, the coordinates are clearly not latitude and longitude (first coordinate is -125045.483512,4577567.5881417).
    – alphabetasoup
    Commented Apr 27, 2016 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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