[postgis-tickets] r17415 - ST_AsGeoJSON(record) implementation from Joe Conway
Paul Ramsey
pramsey at cleverelephant.ca
Mon Apr 29 09:04:11 PDT 2019
Author: pramsey
Date: 2019-04-29 09:04:10 -0700 (Mon, 29 Apr 2019)
New Revision: 17415
Modified:
trunk/.gitignore
trunk/NEWS
trunk/doc/reference_output.xml
trunk/libpgcommon/lwgeom_pg.c
trunk/libpgcommon/lwgeom_pg.h
trunk/postgis/Makefile.in
trunk/postgis/postgis.sql.in
Log:
ST_AsGeoJSON(record) implementation from Joe Conway
Closes #1833
Modified: trunk/.gitignore
===================================================================
--- trunk/.gitignore 2019-04-24 11:07:56 UTC (rev 17414)
+++ trunk/.gitignore 2019-04-29 16:04:10 UTC (rev 17415)
@@ -109,7 +109,10 @@
postgis/legacy.sql
postgis/postgis.sql
postgis/postgis_proc_set_search_path.sql
+postgis/postgis_for_extension.sql
postgis/postgis_upgrade.sql*
+postgis/postgis_upgrade_for_extension.sql
+postgis/postgis_upgrade_for_extension.sql.in
postgis/sfcgal_upgrade.sql*
postgis/sqldefines.h
postgis/uninstall_legacy.sql
@@ -126,8 +129,10 @@
raster/rt_pg/rtpostgis_drop.sql
raster/rt_pg/rtpostgis_legacy.sql
raster/rt_pg/rtpostgis_proc_set_search_path.sql
+raster/rt_pg/rtpostgis_for_extension.sql
raster/rt_pg/rtpostgis_upgrade.sql.in
raster/rt_pg/rtpostgis_upgrade*.sql
+raster/rt_pg/rtpostgis_upgrade_for_extension.sql.in
raster/rt_pg/uninstall_rtpostgis.sql
raster/scripts/Makefile
raster/scripts/python/Makefile
Modified: trunk/NEWS
===================================================================
--- trunk/NEWS 2019-04-24 11:07:56 UTC (rev 17414)
+++ trunk/NEWS 2019-04-29 16:04:10 UTC (rev 17415)
@@ -31,6 +31,7 @@
Libre de Bruxelles (ULB), Darafei Praliaskouski)
- #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)
* Enhancements and fixes *
- #4342, Move deprecated functions into legacy.sql file
Modified: trunk/doc/reference_output.xml
===================================================================
--- trunk/doc/reference_output.xml 2019-04-24 11:07:56 UTC (rev 17414)
+++ trunk/doc/reference_output.xml 2019-04-29 16:04:10 UTC (rev 17415)
@@ -329,6 +329,13 @@
<funcsynopsis>
<funcprototype>
<funcdef>text <function>ST_AsGeoJSON</function></funcdef>
+ <paramdef><type>record </type> <parameter>feature</parameter></paramdef>
+ <paramdef choice="opt"><type>text </type> <parameter>geomcolumnname</parameter></paramdef>
+ <paramdef choice="opt"><type>integer </type> <parameter>maxdecimaldigits=15</parameter></paramdef>
+ <paramdef choice="opt"><type>boolean </type> <parameter>prettyprint=false</parameter></paramdef>
+ </funcprototype>
+ <funcprototype>
+ <funcdef>text <function>ST_AsGeoJSON</function></funcdef>
<paramdef><type>geometry </type> <parameter>geom</parameter></paramdef>
<paramdef choice="opt"><type>integer </type> <parameter>maxdecimaldigits=15</parameter></paramdef>
<paramdef choice="opt"><type>integer </type> <parameter>options=0</parameter></paramdef>
@@ -345,15 +352,13 @@
<refsection>
<title>Description</title>
- <para>Return the geometry as a GeoJSON element. (Cf <ulink
- url="http://geojson.org/geojson-spec.html">GeoJSON specifications 1.0</ulink>). 2D and 3D Geometries are both supported. GeoJSON only support SFS 1.1 geometry type (no curve
+ <para>Return the geometry as a GeoJSON "geometry" object, or the row as a GeoJSON "feature" object. (Cf <ulink
+ url="https://tools.ietf.org/html/rfc7946">GeoJSON specifications RFC 7946</ulink>). 2D and 3D Geometries are both supported. GeoJSON only support SFS 1.1 geometry types (no curve
support for example).</para>
- <para>The <varname>gj_version</varname> parameter is the major version of the GeoJSON spec. If specified, must be 1. This represents the spec version of GeoJSON.</para>
+ <para>The maxdecimaldigits argument may be used to reduce the maximum number of decimal places used in output (defaults to 15). If you are using EPSG:4326 and are outputting the geometry only for display, <varname>maxdecimaldigits</varname>=6 can be a good choice for many maps.</para>
- <para>The third argument may be used to reduce the maximum number of decimal places used in output (defaults to 15). If you are using EPSG:4326 and are outputting the geometry only for display, <varname>maxdecimaldigits</varname>=6 can be a good choice for many maps.</para>
-
- <para>The last <varname>options</varname> argument could be used to add BBOX or CRS in GeoJSON output:
+ <para>The <varname>options</varname> argument could be used to add BBOX or CRS in GeoJSON output:
<itemizedlist>
<listitem>
<para>0: means no option (default value)</para>
@@ -400,31 +405,9 @@
<para>ST_AsGeoJSON only builds geometry. You need to build the rest of Feature from your Postgres table yourself:</para>
<programlisting>
-select row_to_json(fc)
-from (
- select
- 'FeatureCollection' as "type",
- array_to_json(array_agg(f)) as "features"
- from (
- select
- 'Feature' as "type",
- ST_AsGeoJSON(ST_Transform(way, 4326), 6) :: json as "geometry",
- (
- select json_strip_nulls(row_to_json(t))
- from (
- select
- osm_id,
- "natural",
- place
- ) t
- ) as "properties"
- from planet_osm_point
- where
- "natural" is not null
- or place is not null
- limit 10
- ) as f
-) as fc;
+SELECT planet_osm_point.*
+FROM planet_osm_point
+LIMIT 10;
st_asgeojson
-----------------------------------------------------------------------------------------------------------
{"type":"FeatureCollection","features":[{"type":"Feature","geometry":{"type":"Point","coordinates":[23.569251,51.541599]},"properties":{"osm_id":3424148658,"place":"locality"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[23.625174,51.511718]},"properties":{"osm_id":4322036818,"place":"locality"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[23.613928,51.5417]},"properties":{"osm_id":242979330,"place":"hamlet"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[23.586361,51.563272]},"properties":{"osm_id":3424148656,"place":"locality"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[23.605488,51.553886]},"properties":{"osm_id":242979323,"place":"village"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[23.6067,51.57609]},"properties":{"osm_id":242979327,"place":"village"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[23.636533,51.575683]},"properties":{"osm_id":5737800420,"place":"locality"}},{"type":"F
eature","geometry":{"type":"Point","coordinates":[23.656733,51.518733]},"properties":{"osm_id":5737802397,"place":"locality"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[23.672542,51.504584]},"properties":{"osm_id":242979320,"place":"hamlet"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[23.574094,51.63389]},"properties":{"osm_id":242979333,"place":"village"}}]}
@@ -433,17 +416,19 @@
<programlisting>SELECT ST_AsGeoJSON(geom) from fe_edges limit 1;
st_asgeojson
-----------------------------------------------------------------------------------------------------------
-
{"type":"MultiLineString","coordinates":[[[-89.734634999999997,31.492072000000000],
[-89.734955999999997,31.492237999999997]]]}
(1 row)</programlisting>
-<para>You can also use it with 3D geometries:</para>
+
+<para>You can also use the function with 3D geometries:</para>
<programlisting>SELECT ST_AsGeoJSON('LINESTRING(1 2 3, 4 5 6)');
st_asgeojson
-----------------------------------------------------------------------------------------
{"type":"LineString","coordinates":[[1,2,3],[4,5,6]]}</programlisting>
- </refsection>
+
+ </refsection>
+
<refsection>
<title>See Also</title>
Modified: trunk/libpgcommon/lwgeom_pg.c
===================================================================
--- trunk/libpgcommon/lwgeom_pg.c 2019-04-24 11:07:56 UTC (rev 17414)
+++ trunk/libpgcommon/lwgeom_pg.c 2019-04-29 16:04:10 UTC (rev 17415)
@@ -20,6 +20,7 @@
#include <executor/spi.h>
#include <utils/guc.h>
#include <utils/guc_tables.h>
+#include <catalog/namespace.h>
#include "../postgis_config.h"
#include "liblwgeom.h"
@@ -31,6 +32,34 @@
#define PGC_ERRMSG_MAXLEN 2048 //256
+/* Global cache to hold GEOMETRYOID */
+Oid GEOMETRYOID = InvalidOid;
+Oid GEOGRAPHYOID = InvalidOid;
+
+Oid postgis_geometry_oid(void)
+{
+ if (GEOMETRYOID == InvalidOid) {
+ Oid typoid = TypenameGetTypid("geometry");
+ if (OidIsValid(typoid) && get_typisdefined(typoid))
+ {
+ GEOMETRYOID = typoid;
+ }
+ }
+ return GEOMETRYOID;
+}
+
+Oid postgis_geography_oid(void)
+{
+ if (GEOGRAPHYOID == InvalidOid) {
+ Oid typoid = TypenameGetTypid("geography");
+ if (OidIsValid(typoid) && get_typisdefined(typoid))
+ {
+ GEOGRAPHYOID = typoid;
+ }
+ }
+ return GEOGRAPHYOID;
+}
+
/*
* Error message parsing functions
*
Modified: trunk/libpgcommon/lwgeom_pg.h
===================================================================
--- trunk/libpgcommon/lwgeom_pg.h 2019-04-24 11:07:56 UTC (rev 17414)
+++ trunk/libpgcommon/lwgeom_pg.h 2019-04-29 16:04:10 UTC (rev 17415)
@@ -23,6 +23,12 @@
#include "liblwgeom.h"
#include "pgsql_compat.h"
+/* Globals to hold GEOMETRYOID, GEOGRAPHYOID */
+extern Oid GEOMETRYOID;
+extern Oid GEOGRAPHYOID;
+Oid postgis_geometry_oid(void);
+Oid postgis_geography_oid(void);
+
/* Install PosgreSQL handlers for liblwgeom use */
void pg_install_lwgeom_handlers(void);
Modified: trunk/postgis/Makefile.in
===================================================================
--- trunk/postgis/Makefile.in 2019-04-24 11:07:56 UTC (rev 17414)
+++ trunk/postgis/Makefile.in 2019-04-29 16:04:10 UTC (rev 17415)
@@ -125,6 +125,7 @@
lwgeom_out_mvt.o \
geobuf.o \
lwgeom_out_geobuf.o \
+ lwgeom_out_geojson.o \
postgis_legacy.o
# Objects to build using PGXS
Modified: trunk/postgis/postgis.sql.in
===================================================================
--- trunk/postgis/postgis.sql.in 2019-04-24 11:07:56 UTC (rev 17414)
+++ trunk/postgis/postgis.sql.in 2019-04-29 16:04:10 UTC (rev 17415)
@@ -4587,11 +4587,18 @@
-- ST_AsGeoJson(geom, precision, options) / version=1
-- Changed 2.0.0 to use default args and named args
CREATE OR REPLACE FUNCTION ST_AsGeoJson(geom geometry, maxdecimaldigits int4 DEFAULT 15, options int4 DEFAULT 0)
- RETURNS TEXT
+ RETURNS text
AS 'MODULE_PATHNAME','LWGEOM_asGeoJson'
- LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
+ LANGUAGE 'c' IMMUTABLE STRICT _PARALLEL
_COST_LOW;
+-- Availability: 3.0.0
+CREATE OR REPLACE FUNCTION ST_AsGeoJson(r record, geom_column text DEFAULT '', maxdecimaldigits int4 DEFAULT 15, pretty_print bool DEFAULT false)
+ RETURNS text
+ AS 'MODULE_PATHNAME','ST_AsGeoJsonRow'
+ LANGUAGE 'c' STABLE STRICT _PARALLEL
+ _COST_LOW;
+
-----------------------------------------------------------------------
-- Mapbox Vector Tile OUTPUT
-- Availability: 2.4.0
More information about the postgis-tickets
mailing list