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