Creating GeoJSON output from Well Known Text with PostgreSQL

Question

I have wkt data and I am trying to create a JSON output in PostgreSQL.

I know that there is a function ST_AsGeoJSON (https://postgis.net/docs/ST_AsGeoJSON.html) which creates for example:

SELECT ST_AsGeoJSON('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)');

Output:
{"type":"LineString","coordinates":[[77.29,29.07],[77.42,29.26],[77.27,29.31],[77.29,29.07]]}

But, I am looking to create an ouput as shown below:

{"type":"LineString","coordinates":[{"x":77.29,"y":29.07},{"x":77.42,"y":29.26},{"x":77.27,"y":29.31},{"x":77.29,"y":29.07}]}

Please note that I am looking for a generic solution for all types of geometry objects. Thank you

Answer 1

You could use regex to replace the [a,b] with {"x":a,"y":b} with something like this:

CREATE OR REPLACE FUNCTION ST_AsCustomGeoJson(geom geometry)
RETURNS TEXT
AS 
$$
    -- Look for each coordinate and replace [number_a,number_b] with {"x":number_a,"y":number_b}
    SELECT REGEXP_REPLACE(
        ST_AsGeoJSON(geom),
        '\[(-?[0-9]+\.?[0-9]*)(e\+[0-9]+)?,(-?[0-9]+\.?[0-9]*)(e\+[0-9]+)?\]',
        '{"x":\1\2,"y":\3\4}',
        'g');
$$
LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE;

Using the new function, you get the expected response:

# Select ST_AsCustomGeoJson('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'::geometry);                                              st_ascustomgeojson                                               
---------------------------------------------------------------------------------------------------------------
 {"type":"LineString","coordinates":[{x:77.29,y:29.07},{x:77.42,y:29.26},{x:77.27,y:29.31},{x:77.29,y:29.07}]}
(1 row)

And it should work with other geometry types too:

# Select ST_AsCustomGeoJson('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0),(1 1, 1 9, 9 9, 9 1, 1 1))'::geometry);
                                                                                   st_ascustomgeojson                                                                                   
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"type":"Polygon","coordinates":[[{"x":0,"y":0},{"x":0,"y":10},{"x":10,"y":10},{"x":10,"y":0},{"x":0,"y":0}],[{"x":1,"y":1},{"x":1,"y":9},{"x":9,"y":9},{"x":9,"y":1},{"x":1,"y":1}]]}
(1 row)

# Select ST_AsCustomGeoJson('LINESTRING(3e20 3e20, 1e100 40)'::geometry);
                               st_ascustomgeojson                                
---------------------------------------------------------------------------------
 {"type":"LineString","coordinates":[{"x":3e+20,"y":3e+20},{"x":1e+100,"y":40}]}
(1 row)

Even geometry collections:

# Select ST_AsCustomGeoJson('GEOMETRYCOLLECTION (POINT(-1 0), LINESTRING(4 4,5 5))');
                                                                      st_ascustomgeojson                                     

-----------------------------------------------------------------------------------------------------------------------------
---------------------------------
 {"type":"GeometryCollection","geometries":[{"type":"Point","coordinates":{"x":-1,"y":0}},{"type":"LineString","coordinates":
[{"x":4,"y":4},{"x":5,"y":5}]}]}