3

The function ST_asGeoJSON returns something like:

"st_asgeojson":
   {
     "type":"MultiLineString",
     "coordinates":[
        [
          [-3.13512629299993,39.7174353130001],    
          [-19.13510345099996,2.7174767110001]
        ]
      ]
    }

And I wonder if there's a way to simplify or customize the JSON it returns, as I only need the coordinates, to something like:

{
  coordinates: [
    [-9.13512629299993,38.7174353130001],
    [-9.13510345099996,38.7174767110001]
  ]
}
10

Be aware that the GeoJSON specification states that

The GeoJSON object must have a member with the name "type". This member's value is a string that determines the type of the GeoJSON object.

http://geojson.org/geojson-spec.html#geojson-objects

If you decide that you do not want/need to be compliant with the GeoJSON spec, you can use the - (minus) operator as of postgresql 9.5.

ST_AsGeoJSON(the_geom)::jsonb - 'type'

as of postgres 9.3 you can extract value with ->

ST_AsGeoJSON(the_geom)::json->'coordinates'

so you can do some string magic (assuming you still want the coordinates in quotes to stay json compatible)

CONCAT('{"coordinates":', ST_AsGeoJSON(the_geom)::json->'coordinates', '}')

For postgres <= 9.2 I think the answer by Michal Zimmermann is the best approach.


To reduce the dimensionality of the json output you can use the postgis function ST_LineMerge before passing it to ST_AsGeoJSON.

ST_AsGeoJSON(ST_LineMerge(the_geom))

If you have to do this, you should as well consider the impact on MultiLineStrings that actually DO contain multiple linestrings (which is not the case in your example) or if you do not have linestrings with multiple parts (and do not plan to handle such), you should directly save your data as LineString instead of MultiLineString.

| improve this answer | |
  • Wow, that is cool SQL. I had no idea you could do that, but then I am still on 9.2, sadly. – John Powell Oct 20 '15 at 10:23
  • That would be great, I'm using postgreSQL v9.3! – tvieira Oct 20 '15 at 11:12
  • I added a solution which is compatible with 9.3. I think that using the internal json mechanism is saver than parsing json with string methods. E.g. There is no guarantee that the order of the elements in the json objects stays the way it currently is and that there's no other occurence of a , somewhere in the raw json string. – Matthias Kuhn Oct 20 '15 at 11:20
  • hmm I tried your alternative for postgres9.3, but I get an ERROR: type "jsonb" does not exist – tvieira Oct 20 '15 at 11:31
  • 1
    It's a MultiLineString: the outermost array is the Multi part, the middle one a list of coordinates of every linestring and the most inner one an array of the parts of each coordinates (lat/lon). The most inner one will always have as many elements as the coordinates have dimensions. – Matthias Kuhn Oct 20 '15 at 12:11
3
SELECT concat('{', substring(
    ST_AsGeoJSON(wkb_geometry) from 
    position(',' in ST_AsGeoJSON(wkb_geometry)) + 1
    ))
FROM table_name;

It simply finds the first , character, and extracts the substring from its position to the end of the geojson.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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