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

Answer 2

I believe a simple loop with jsonb_build_obejct over a result set from ST_DumpPoints would suffice. If you also want to apply this function in multipart geometries, you have to build another loop to extract all geometries beforehand using ST_Dump:

CREATE OR REPLACE FUNCTION generate_custom_geojson(g GEOMETRY)
RETURNS json AS $$
DECLARE
  j geometry;
  i geometry;
  coords jsonb[] := '{}';
  coords_multi jsonb[] := '{}';
BEGIN     
  FOR j IN SELECT (ST_Dump(g)).geom LOOP
    FOR i IN SELECT (ST_DumpPoints(j)).geom LOOP
      coords := coords || jsonb_build_object('x',ST_X(i),'y',ST_Y(i)); 
    END LOOP;
    IF ST_NumGeometries(g)=1 THEN
      coords_multi := coords; 
    ELSE 
      coords_multi := coords_multi || jsonb_agg(coords); 
    END IF; 
  END LOOP;
  RETURN json_build_object('type',replace(ST_GeometryType(g),'ST_',''),
                           'coordinates',coords_multi);
END;
$$ LANGUAGE plpgsql;

This function simply extracts all points of a given geometry and puts them into an array - appended using ||. This array is later on used to create the coordinates set of x,y pairs. The geometry type is extracted using ST_GeometryType.

Test:

WITH j (g) AS (
  VALUES ('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'),
         ('POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))'),
         ('MULTILINESTRING ((10 10, 20 20, 10 40),(40 40, 30 30, 40 20, 30 10))'),
         ('MULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)),((15 5, 40 10, 10 20, 5 10, 15 5)))'),
         ('MULTIPOINT (10 40, 40 30, 20 20, 30 10)')
)
SELECT generate_custom_geojson(g) FROM j;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"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}]}
 {"type" : "Polygon", "coordinates" : [{"x": 30, "y": 10},{"x": 40, "y": 40},{"x": 20, "y": 40},{"x": 10, "y": 20},{"x": 30, "y": 10}]}
 {"type" : "MultiLineString", "coordinates" : [[[{"x": 10, "y": 10}, {"x": 20, "y": 20}, {"x": 10, "y": 40}]],[[{"x": 10, "y": 10}, {"x": 20, "y": 20}, {"x": 10, "y": 40}, {"x": 40, "y": 40}, {"x": 30, "y": 30}, {"x": 40, "y": 20}, {"x": 30, "y": 10}]]]}
 {"type" : "MultiPolygon", "coordinates" : [[[{"x": 30, "y": 20}, {"x": 45, "y": 40}, {"x": 10, "y": 40}, {"x": 30, "y": 20}]],[[{"x": 30, "y": 20}, {"x": 45, "y": 40}, {"x": 10, "y": 40}, {"x": 30, "y": 20}, {"x": 15, "y": 5}, {"x": 40, "y": 10}, {"x": 10, "y": 20}, {"x": 5, "y": 10}, {"x": 15, "y": 5}]]]}
 {"type" : "MultiPoint", "coordinates" : [[[{"x": 10, "y": 40}]],[[{"x": 10, "y": 40}, {"x": 40, "y": 30}]],[[{"x": 10, "y": 40}, {"x": 40, "y": 30}, {"x": 20, "y": 20}]],[[{"x": 10, "y": 40}, {"x": 40, "y": 30}, {"x": 20, "y": 20}, {"x": 30, "y": 10}]]]}
(5 Zeilen)