[postgis-tickets] r17416 - Casts from geometry to json and jsonb

[Paul Ramsey]

Author: pramsey
Date: 2019-04-29 09:05:21 -0700 (Mon, 29 Apr 2019)
New Revision: 17416

Modified:
   trunk/NEWS
   trunk/postgis/lwgeom_export.c
   trunk/postgis/postgis.sql.in
Log:
Casts from geometry to json and jsonb
Allows to_json and to_jsonb to serialize geometry columns into json
for more intuitive json handling.
Closes #3687


Modified: trunk/NEWS
===================================================================
--- trunk/NEWS	2019-04-29 16:04:10 UTC (rev 17415)
+++ trunk/NEWS	2019-04-29 16:05:21 UTC (rev 17416)
@@ -32,6 +32,8 @@
   - #4171, ST_3DLineInterpolatePoint (Julien Cabieces, Vincent Mora)
   - #4311, Introduce `--with-wagyu` as an option for MVT polygons (Raúl Marín)
   - #1833, ST_AsGeoJSON(row) generates full GeoJSON Features (Joe Conway)
+  - #3687, Casts json(geometry) and jsonb(geometry) for implicit GeoJSON
+           generation (Paul Ramsey)
 
 * Enhancements and fixes *
   - #4342, Move deprecated functions into legacy.sql file

Modified: trunk/postgis/lwgeom_export.c
===================================================================
--- trunk/postgis/lwgeom_export.c	2019-04-29 16:04:10 UTC (rev 17415)
+++ trunk/postgis/lwgeom_export.c	2019-04-29 16:05:21 UTC (rev 17416)
@@ -33,6 +33,12 @@
 #include "executor/spi.h"
 #include "utils/builtins.h"
 
+#if POSTGIS_PGSQL_VERSION > 95
+#include "utils/fmgrprotos.h"
+#else
+#include "utils/jsonb.h"
+#endif
+
 #include "../postgis_config.h"
 #include "lwgeom_pg.h"
 #include "liblwgeom.h"
@@ -44,6 +50,8 @@
 Datum LWGEOM_asSVG(PG_FUNCTION_ARGS);
 Datum LWGEOM_asX3D(PG_FUNCTION_ARGS);
 Datum LWGEOM_asEncodedPolyline(PG_FUNCTION_ARGS);
+Datum geometry_to_json(PG_FUNCTION_ARGS);
+Datum geometry_to_jsonb(PG_FUNCTION_ARGS);
 
 /*
  * Retrieve an SRS from a given SRID
@@ -416,6 +424,33 @@
 
 
 /**
+ * Cast feature to JSON
+ */
+PG_FUNCTION_INFO_V1(geometry_to_json);
+Datum geometry_to_json(PG_FUNCTION_ARGS)
+{
+	GSERIALIZED *geom = PG_GETARG_GSERIALIZED_P(0);
+	LWGEOM *lwgeom = lwgeom_from_gserialized(geom);
+	char *geojson = lwgeom_to_geojson(lwgeom, NULL, 9, 0);
+	text *result = cstring_to_text(geojson);
+	lwgeom_free(lwgeom);
+	pfree(geojson);
+	PG_FREE_IF_COPY(geom, 0);
+	PG_RETURN_TEXT_P(result);
+}
+
+PG_FUNCTION_INFO_V1(geometry_to_jsonb);
+Datum geometry_to_jsonb(PG_FUNCTION_ARGS)
+{
+	GSERIALIZED *geom = PG_GETARG_GSERIALIZED_P(0);
+	LWGEOM *lwgeom = lwgeom_from_gserialized(geom);
+	char *geojson = lwgeom_to_geojson(lwgeom, NULL, 9, 0);
+	lwgeom_free(lwgeom);
+	PG_RETURN_DATUM(DirectFunctionCall1(jsonb_in, PointerGetDatum(geojson)));
+}
+
+
+/**
  * SVG features
  */
 PG_FUNCTION_INFO_V1(LWGEOM_asSVG);

Modified: trunk/postgis/postgis.sql.in
===================================================================
--- trunk/postgis/postgis.sql.in	2019-04-29 16:04:10 UTC (rev 17415)
+++ trunk/postgis/postgis.sql.in	2019-04-29 16:05:21 UTC (rev 17416)
@@ -4599,6 +4599,23 @@
 	LANGUAGE 'c' STABLE STRICT _PARALLEL
 	_COST_LOW;
 
+-- Availability: 3.0.0
+CREATE OR REPLACE FUNCTION json(geometry)
+	RETURNS json
+	AS 'MODULE_PATHNAME','geometry_to_json'
+	LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL;
+
+-- Availability: 3.0.0
+CREATE OR REPLACE FUNCTION jsonb(geometry)
+	RETURNS jsonb
+	AS 'MODULE_PATHNAME','geometry_to_jsonb'
+	LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL;
+
+-- Availability: 3.0.0
+CREATE CAST (geometry AS json) WITH FUNCTION json(geometry);
+-- Availability: 3.0.0
+CREATE CAST (geometry AS jsonb) WITH FUNCTION jsonb(geometry);
+
 -----------------------------------------------------------------------
 -- Mapbox Vector Tile OUTPUT
 -- Availability: 2.4.0