From 64795afb6f24de4785d7ac330344ea3cade1e341 Mon Sep 17 00:00:00 2001 From: Paul Ramsey Date: Mon, 29 Apr 2019 16:04:10 +0000 Subject: [PATCH] ST_AsGeoJSON(record) implementation from Joe Conway Closes #1833 git-svn-id: http://svn.osgeo.org/postgis/trunk@17415 b70326c6-7e19-0410-871a-916f4a2858ee --- .gitignore | 5 ++++ NEWS | 1 + doc/reference_output.xml | 53 ++++++++++++++-------------------------- libpgcommon/lwgeom_pg.c | 29 ++++++++++++++++++++++ libpgcommon/lwgeom_pg.h | 6 +++++ postgis/Makefile.in | 1 + postgis/postgis.sql.in | 11 +++++++-- 7 files changed, 70 insertions(+), 36 deletions(-) diff --git a/.gitignore b/.gitignore index d9393ef7e..eb947236c 100644 --- a/.gitignore +++ b/.gitignore @@ -109,7 +109,10 @@ postgis/legacy_minimal.sql 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.sql 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 diff --git a/NEWS b/NEWS index 665e47552..a36d20b7a 100644 --- a/NEWS +++ b/NEWS @@ -31,6 +31,7 @@ PostGIS 3.0.0 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 diff --git a/doc/reference_output.xml b/doc/reference_output.xml index eaeee04e1..e585afba8 100644 --- a/doc/reference_output.xml +++ b/doc/reference_output.xml @@ -327,6 +327,13 @@ CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3) + + text ST_AsGeoJSON + record feature + text geomcolumnname + integer maxdecimaldigits=15 + boolean prettyprint=false + text ST_AsGeoJSON geometry geom @@ -345,15 +352,13 @@ CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3) Description - Return the geometry as a GeoJSON element. (Cf GeoJSON specifications 1.0). 2D and 3D Geometries are both supported. GeoJSON only support SFS 1.1 geometry type (no curve + Return the geometry as a GeoJSON "geometry" object, or the row as a GeoJSON "feature" object. (Cf GeoJSON specifications RFC 7946). 2D and 3D Geometries are both supported. GeoJSON only support SFS 1.1 geometry types (no curve support for example). - The gj_version parameter is the major version of the GeoJSON spec. If specified, must be 1. This represents the spec version of GeoJSON. - - 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, maxdecimaldigits=6 can be a good choice for many maps. + 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, maxdecimaldigits=6 can be a good choice for many maps. - The last options argument could be used to add BBOX or CRS in GeoJSON output: + The options argument could be used to add BBOX or CRS in GeoJSON output: 0: means no option (default value) @@ -400,31 +405,9 @@ CIRCULARSTRING(220268 150415 1,220227 150505 2,220227 150406 3) ST_AsGeoJSON only builds geometry. You need to build the rest of Feature from your Postgres table yourself: -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":"Feature","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 @@ from ( SELECT ST_AsGeoJSON(geom) from fe_edges limit 1; st_asgeojson ----------------------------------------------------------------------------------------------------------- - {"type":"MultiLineString","coordinates":[[[-89.734634999999997,31.492072000000000], [-89.734955999999997,31.492237999999997]]]} (1 row) -You can also use it with 3D geometries: + +You can also use the function with 3D geometries: SELECT ST_AsGeoJSON('LINESTRING(1 2 3, 4 5 6)'); st_asgeojson ----------------------------------------------------------------------------------------- {"type":"LineString","coordinates":[[1,2,3],[4,5,6]]} - + + + See Also diff --git a/libpgcommon/lwgeom_pg.c b/libpgcommon/lwgeom_pg.c index 3f69b3b48..ee0cd5981 100644 --- a/libpgcommon/lwgeom_pg.c +++ b/libpgcommon/lwgeom_pg.c @@ -20,6 +20,7 @@ #include #include #include +#include #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 * diff --git a/libpgcommon/lwgeom_pg.h b/libpgcommon/lwgeom_pg.h index e12a8a28c..d5f032de9 100644 --- a/libpgcommon/lwgeom_pg.h +++ b/libpgcommon/lwgeom_pg.h @@ -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); diff --git a/postgis/Makefile.in b/postgis/Makefile.in index bb7b77479..fe86ba273 100644 --- a/postgis/Makefile.in +++ b/postgis/Makefile.in @@ -125,6 +125,7 @@ PG_OBJS= \ lwgeom_out_mvt.o \ geobuf.o \ lwgeom_out_geobuf.o \ + lwgeom_out_geojson.o \ postgis_legacy.o # Objects to build using PGXS diff --git a/postgis/postgis.sql.in b/postgis/postgis.sql.in index 2fe9ce60c..c90158f96 100644 --- a/postgis/postgis.sql.in +++ b/postgis/postgis.sql.in @@ -4587,9 +4587,16 @@ CREATE OR REPLACE FUNCTION ST_AsKML(geom geometry, maxdecimaldigits int4 DEFAULT -- 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; ----------------------------------------------------------------------- -- 2.40.0