[SCM] PostGIS branch master updated. 3.4.0rc1-805-g28853d7b7
git at osgeo.org
git at osgeo.org
Mon Nov 27 16:28:19 PST 2023
This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "PostGIS".
The branch, master has been updated
via 28853d7b75231f73cf6bfc8d49f9c3f0c2304abd (commit)
from aced4c24a2560606c168fc13cac9499ffe1e3341 (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit 28853d7b75231f73cf6bfc8d49f9c3f0c2304abd
Author: Jan Tojnar <jtojnar at gmail.com>
Date: Sat Oct 28 13:45:11 2023 +0200
Allow promoting column as an id in ST_AsGeoJsonRow
As per GeoJSON RFC, the id should go directly to the feature object
rather than to properties:
> If a Feature has a commonly used identifier, that identifier
> SHOULD be included as a member of the Feature object with the name "id"
Let’s add an argument that will allow designating a column as an identifier
so that people do not have to tediously build the JSON object manually.
Closes #5596 for PostGIS 3.5.0
Closes GH-749
Closes https://git.osgeo.org/gitea/postgis/postgis/pulls/165
diff --git a/NEWS b/NEWS
index 6eca201a7..ea4a9a9e3 100644
--- a/NEWS
+++ b/NEWS
@@ -22,6 +22,11 @@ To take advantage of all SFCGAL featurs, SFCGAL 1.5.0+ is needed.
- #5602, Drop support for GEOS 3.6 and 3.7 (Regina Obe)
- #5571, Improve ST_GeneratePoints performance, but old
seeded pseudo random points will need to be regenerated.
+ - #5596, GH-749, Allow promoting column as an id
+ in ST_AsGeoJson(record,..).
+ Views and materialized views that use the ST_AsGeoJSON(record ..)
+ will need rebuilding to upgrade to new signature
+ (Jan Tojnar)
* New Features *
diff --git a/doc/reference_output.xml b/doc/reference_output.xml
index 428a85742..f04988142 100644
--- a/doc/reference_output.xml
+++ b/doc/reference_output.xml
@@ -623,6 +623,7 @@ F000000000000000000000000000000000000000000000000');
<paramdef choice="opt"><type>text </type> <parameter>geom_column=""</parameter></paramdef>
<paramdef choice="opt"><type>integer </type> <parameter>maxdecimaldigits=9</parameter></paramdef>
<paramdef choice="opt"><type>boolean </type> <parameter>pretty_bool=false</parameter></paramdef>
+ <paramdef choice="opt"><type>text </type> <parameter>id_column=''</parameter></paramdef>
</funcprototype>
<funcprototype>
<funcdef>text <function>ST_AsGeoJSON</function></funcdef>
@@ -703,6 +704,8 @@ Conversely, passing the parameter will save column type lookups.
</itemizedlist>
</para>
+ <para>The <varname>id_column</varname> parameter is used to set the "id" member of the returned GeoJSON features. As per GeoJSON RFC, this SHOULD be used whenever a feature has a commonly used identifier, such as a primary key. When not specified, the produced features will not get an "id" member and any columns other than the geometry, including any potential keys, will just end up inside the feature’s "properties" member.</para>
+
<para>The GeoJSON specification states that polygons are oriented using the Right-Hand Rule,
and some clients require this orientation.
This can be ensured by using <xref linkend="ST_ForcePolygonCCW"/>.
@@ -727,6 +730,7 @@ Conversely, passing the parameter will save column type lookups.
<para role="changed" conformance="2.0.0">Changed: 2.0.0 support default args and named args.</para>
<para role="changed" conformance="3.0.0">Changed: 3.0.0 support records as input</para>
<para role="changed" conformance="3.0.0">Changed: 3.0.0 output SRID if not EPSG:4326.</para>
+ <para role="changed" conformance="3.5.0">Changed: 3.5.0 allow specifying the column containing the feature id</para>
<para>&Z_support;</para>
</refsection>
@@ -736,34 +740,20 @@ Conversely, passing the parameter will save column type lookups.
<para>Generate a FeatureCollection:</para>
<programlisting>SELECT json_build_object(
'type', 'FeatureCollection',
- 'features', json_agg(ST_AsGeoJSON(t.*)::json)
+ 'features', json_agg(ST_AsGeoJSON(t.*, id_column => 'id')::json)
)
FROM ( VALUES (1, 'one', 'POINT(1 1)'::geometry),
(2, 'two', 'POINT(2 2)'),
(3, 'three', 'POINT(3 3)')
) as t(id, name, geom);</programlisting>
-<screen>{"type" : "FeatureCollection", "features" : [{"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]}, "properties": {"id": 1, "name": "one"}}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[2,2]}, "properties": {"id": 2, "name": "two"}}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[3,3]}, "properties": {"id": 3, "name": "three"}}]}</screen>
+<screen>{"type" : "FeatureCollection", "features" : [{"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]}, "id": 1, "properties": {"name": "one"}}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[2,2]}, "id": 2, "properties": {"name": "two"}}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[3,3]}, "id": 3, "properties": {"name": "three"}}]}</screen>
<para>Generate a Feature:</para>
- <programlisting>SELECT ST_AsGeoJSON(t.*)
+ <programlisting>SELECT ST_AsGeoJSON(t.*, id_column => 'id')
FROM (VALUES (1, 'one', 'POINT(1 1)'::geometry)) AS t(id, name, geom);</programlisting>
<screen> st_asgeojson
-----------------------------------------------------------------------------------------------------------------
- {"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]}, "properties": {"id": 1, "name": "one"}}
-</screen>
-
-<para>An alternate way to generate Features with an <varname>id</varname> property
-is to use JSONB functions and operators:</para>
- <programlisting>SELECT jsonb_build_object(
- 'type', 'Feature',
- 'id', id,
- 'geometry', ST_AsGeoJSON(geom)::jsonb,
- 'properties', to_jsonb( t.* ) - 'id' - 'geom'
- ) AS json
-FROM (VALUES (1, 'one', 'POINT(1 1)'::geometry)) AS t(id, name, geom);</programlisting>
-<screen> json
------------------------------------------------------------------------------------------------------------------
- {"id": 1, "type": "Feature", "geometry": {"type": "Point", "coordinates": [1, 1]}, "properties": {"name": "one"}}
+ {"type": "Feature", "geometry": {"type":"Point","coordinates":[1,1]}, "id": 1, "properties": {"name": "one"}}
</screen>
<para>Don't forget to transform your data to WGS84 longitude, latitude to conform with the GeoJSON specification:</para>
diff --git a/postgis/lwgeom_out_geojson.c b/postgis/lwgeom_out_geojson.c
index 31b097fd7..3d6beaf3f 100644
--- a/postgis/lwgeom_out_geojson.c
+++ b/postgis/lwgeom_out_geojson.c
@@ -58,6 +58,7 @@ static void array_to_json_internal(Datum array, StringInfo result,
static void composite_to_geojson(FunctionCallInfo fcinfo,
Datum composite,
char *geom_column_name,
+ char *id_column_name,
int32 maxdecimaldigits,
StringInfo result,
bool use_line_feeds,
@@ -91,8 +92,10 @@ ST_AsGeoJsonRow(PG_FUNCTION_ARGS)
text *geom_column_text = PG_GETARG_TEXT_P(1);
int32 maxdecimaldigits = PG_GETARG_INT32(2);
bool do_pretty = PG_GETARG_BOOL(3);
+ text *id_column_text = PG_GETARG_TEXT_P(4);
StringInfo result;
char *geom_column = text_to_cstring(geom_column_text);
+ char *id_column = text_to_cstring(id_column_text);
Oid geom_oid = InvalidOid;
Oid geog_oid = InvalidOid;
@@ -103,10 +106,12 @@ ST_AsGeoJsonRow(PG_FUNCTION_ARGS)
if (strlen(geom_column) == 0)
geom_column = NULL;
+ if (strlen(id_column) == 0)
+ id_column = NULL;
result = makeStringInfo();
- composite_to_geojson(fcinfo, array, geom_column, maxdecimaldigits, result, do_pretty, geom_oid, geog_oid);
+ composite_to_geojson(fcinfo, array, geom_column, id_column, maxdecimaldigits, result, do_pretty, geom_oid, geog_oid);
PG_RETURN_TEXT_P(cstring_to_text_with_len(result->data, result->len));
}
@@ -118,6 +123,7 @@ static void
composite_to_geojson(FunctionCallInfo fcinfo,
Datum composite,
char *geom_column_name,
+ char *id_column_name,
int32 maxdecimaldigits,
StringInfo result,
bool use_line_feeds,
@@ -134,7 +140,9 @@ composite_to_geojson(FunctionCallInfo fcinfo,
bool needsep = false;
const char *sep;
StringInfo props = makeStringInfo();
+ StringInfo id = makeStringInfo();
bool geom_column_found = false;
+ bool id_column_found = false;
sep = use_line_feeds ? ",\n " : ", ";
@@ -161,6 +169,7 @@ composite_to_geojson(FunctionCallInfo fcinfo,
Oid outfuncoid;
Form_pg_attribute att = TupleDescAttr(tupdesc, i);
bool is_geom_column = false;
+ bool is_id_column = false;
if (att->attisdropped)
continue;
@@ -172,6 +181,9 @@ composite_to_geojson(FunctionCallInfo fcinfo,
else
is_geom_column = (att->atttypid == geom_oid || att->atttypid == geog_oid);
+ if (id_column_name)
+ is_id_column = (strcmp(attname, id_column_name) == 0);
+
if ((!geom_column_found) && is_geom_column)
{
/* this is our geom column */
@@ -194,6 +206,22 @@ composite_to_geojson(FunctionCallInfo fcinfo,
appendStringInfoString(result, "{\"type\": null}");
}
}
+ else if (is_id_column)
+ {
+ id_column_found = true;
+
+ val = heap_getattr(tuple, i + 1, tupdesc, &isnull);
+
+ if (isnull)
+ {
+ tcategory = JSONTYPE_NULL;
+ outfuncoid = InvalidOid;
+ }
+ else
+ json_categorize_type(att->atttypid, &tcategory, &outfuncoid);
+
+ datum_to_json(val, isnull, id, tcategory, outfuncoid, false);
+ }
else
{
if (needsep)
@@ -222,6 +250,17 @@ composite_to_geojson(FunctionCallInfo fcinfo,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("geometry column is missing")));
+ if (id_column_name)
+ {
+ if (!id_column_found)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("Specified id column \"%s\" is missing", id_column_name)));
+
+ appendStringInfoString(result, ", \"id\": ");
+ appendStringInfo(result, "%s", id->data);
+ }
+
appendStringInfoString(result, ", \"properties\": {");
appendStringInfo(result, "%s", props->data);
diff --git a/postgis/postgis.sql.in b/postgis/postgis.sql.in
index 2840dfb55..80eba72c5 100644
--- a/postgis/postgis.sql.in
+++ b/postgis/postgis.sql.in
@@ -5053,7 +5053,8 @@ CREATE OR REPLACE FUNCTION ST_AsGeoJson(geom geometry, maxdecimaldigits integer
_COST_MEDIUM;
-- Availability: 3.0.0
-CREATE OR REPLACE FUNCTION ST_AsGeoJson(r record, geom_column text DEFAULT '', maxdecimaldigits integer DEFAULT 9, pretty_bool bool DEFAULT false)
+-- Changed: 3.5.0 add id_column='' parameter
+CREATE OR REPLACE FUNCTION ST_AsGeoJson(r record, geom_column text DEFAULT '', maxdecimaldigits integer DEFAULT 9, pretty_bool boolean DEFAULT false, id_column text DEFAULT '')
RETURNS text
AS 'MODULE_PATHNAME','ST_AsGeoJsonRow'
LANGUAGE 'c' STABLE STRICT PARALLEL SAFE
diff --git a/postgis/postgis_before_upgrade.sql b/postgis/postgis_before_upgrade.sql
index ddf23a688..56484b1af 100644
--- a/postgis/postgis_before_upgrade.sql
+++ b/postgis/postgis_before_upgrade.sql
@@ -173,3 +173,9 @@ BEGIN
END;
$$;
+-- FUNCTION ST_AsGeoJson added `id_column` optional argument in 3.5.0.
+SELECT _postgis_drop_function_by_identity
+ (
+ 'ST_AsGeoJson',
+ 'r record, geom_column text, maxdecimaldigits integer, pretty_bool boolean'
+ );
diff --git a/regress/core/out_geojson.sql b/regress/core/out_geojson.sql
index 5c235e5e1..827425ae0 100644
--- a/regress/core/out_geojson.sql
+++ b/regress/core/out_geojson.sql
@@ -35,6 +35,9 @@ SELECT 'gj03', i, to_json(g.*) AS rj3
SELECT 'gj04', i, to_jsonb(g.*) AS rj4
FROM g ORDER BY i;
+SELECT 'gj05', i, ST_AsGeoJSON(g.*, id_column => 'i') AS gj5
+ FROM g ORDER BY i;
+
SELECT '4695', ST_ASGeoJSON(a.*) FROM
(
SELECT 1 as v, ST_SetSRID(ST_Point(0,1),2227) as g
diff --git a/regress/core/out_geojson_expected b/regress/core/out_geojson_expected
index 25a2259c2..deccfbca7 100644
--- a/regress/core/out_geojson_expected
+++ b/regress/core/out_geojson_expected
@@ -27,4 +27,11 @@ gj04|4|{"d": "2004-04-04", "f": 4.4, "g": {"type": "GeometryCollection", "geomet
gj04|5|{"d": "2005-05-05", "f": 5.5, "g": {"type": "Point", "coordinates": []}, "i": 5, "t": "five"}
gj04|6|{"d": "2006-06-06", "f": 6.6, "g": null, "i": 6, "t": "six"}
gj04|7|{"d": "2007-07-07", "f": 7.7, "g": {"type": "GeometryCollection", "geometries": [{"type": "Point", "coordinates": []}, {"type": "Point", "coordinates": [1, 2]}]}, "i": 7, "t": "seven"}
+gj05|1|{"type": "Feature", "geometry": {"type":"Point","coordinates":[42,42]}, "id": 1, "properties": {"f": 1.1, "t": "one", "d": "2001-01-01"}}
+gj05|2|{"type": "Feature", "geometry": {"type":"LineString","coordinates":[[42,42],[45,45]]}, "id": 2, "properties": {"f": 2.2, "t": "two", "d": "2002-02-02"}}
+gj05|3|{"type": "Feature", "geometry": {"type":"Polygon","coordinates":[[[42,42],[45,45],[45,42],[42,42]]]}, "id": 3, "properties": {"f": 3.3, "t": "three", "d": "2003-03-03"}}
+gj05|4|{"type": "Feature", "geometry": {"type":"GeometryCollection","geometries":[{"type":"Point","coordinates":[42,42]}]}, "id": 4, "properties": {"f": 4.4, "t": "four", "d": "2004-04-04"}}
+gj05|5|{"type": "Feature", "geometry": {"type":"Point","coordinates":[]}, "id": 5, "properties": {"f": 5.5, "t": "five", "d": "2005-05-05"}}
+gj05|6|{"type": "Feature", "geometry": {"type": null}, "id": 6, "properties": {"f": 6.6, "t": "six", "d": "2006-06-06"}}
+gj05|7|{"type": "Feature", "geometry": {"type":"GeometryCollection","geometries":[{"type":"Point","coordinates":[]},{"type":"Point","coordinates":[1,2]}]}, "id": 7, "properties": {"f": 7.7, "t": "seven", "d": "2007-07-07"}}
4695|{"type": "Feature", "geometry": {"type":"Point","crs":{"type":"name","properties":{"name":"EPSG:2227"}},"coordinates":[0,1]}, "properties": {"v": 1}}
-----------------------------------------------------------------------
Summary of changes:
NEWS | 5 +++++
doc/reference_output.xml | 26 ++++++++----------------
postgis/lwgeom_out_geojson.c | 41 +++++++++++++++++++++++++++++++++++++-
postgis/postgis.sql.in | 3 ++-
postgis/postgis_before_upgrade.sql | 6 ++++++
regress/core/out_geojson.sql | 3 +++
regress/core/out_geojson_expected | 7 +++++++
7 files changed, 71 insertions(+), 20 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list