Geometrischer Parkplatz mit Grünfläche
Simon, Kiya | 21.07.2023

Creating a Feature Collection From PostGIS Geometries with PostGIS 3.0.0 and Above

Geodata > Creating a Feature Collection From PostGIS Geometries with PostGIS 3.0.0 and Above

Creating a Feature Collection From PostGIS Geometries

Generating a Feature and Feature Collection from PostGIS is pretty straightforward. When you convert a simple GEOMETRY with the st_asgeojson function, PostGIS generates a GeoJSON "geometry".

1SELECT st_asgeojson(f.geometry)
2FROM field f;

The query above will result in a single geojson geometry:

1{
2  "type" : "Polygon",
3  "coordinates" : [...]
4}

If you provide a RECORD (available since PostGIS 3.0.0), PostGIS will generate a Feature with all the fields as properties:

1SELECT st_asgeojson(f.*)
2FROM field f;

The output will look something like this:

1{
2  "type" : "Feature",
3  "geometry" : {
4    "type" : "Polygon",
5    "coordinates" : [
6      ...
7    ]
8  },
9  "properties" : {
10    "id" : "052ff36a-4ea7-4307-9bc3-414f49a62163",
11    "area_square_meters" : 29434,
12    "altitude" : 40,
13    "granule_code" : "31UGT",
14    "creation_date" : "2021-10-21T23:32:25.974059",
15    "name" : "Simple test field",
16    ...
17  }
18}

This approach is particularly handy when you want to export a Feature Collection by some grouping column, like a farm_id where all the fields reference as the owner of the field.

1SELECT JSON_BUILD_OBJECT(
2               'type', 'FeatureCollection',
3               'features', JSON_AGG(st_asgeojson(f.*)::JSONB)
4           ) AS feature_collection
5FROM field f
6GROUP BY farm_id;

In this case, we have to construct the actual root node FeatureCollection ourselves and just JSON_AGG the specific Features / Geometries of the farm.

This results in a comprehensive Feature Collection as shown below:

1{
2  "type" : "FeatureCollection",
3  "features" : [
4    {
5      "type" : "Feature",
6      "geometry" : {
7        "type" : "Polygon",
8        "coordinates" : [
9          ...
10        ]
11      },
12      "properties" : {
13        "id" : "9836ef53-16bd-40d8-bc4c-d2d9d152c167",
14        "area_square_meters" : 215311,
15        ...
16      }
17    },
18    {
19      "type" : "Feature",
20      "geometry" : {
21        "type" : "Polygon",
22        "coordinates" : [
23          ...
24        ]
25      },
26      "properties" : {
27        "id" : "ebdec7b2-204f-453b-b59b-f8eacccf2c44",
28        "area_square_meters" : 48207,
29        ...
30      }
31    }
32  ]
33}

This resulting Feature Collection can then be readily pasted into a platform like https://geojson.io, providing a visual representation of the farm's features on a map.

By default, st_asgeojson includes all the Field Columns as properties of the Feature. However, you can trim the properties to your liking by selecting only the relevant columns:

1SELECT JSON_BUILD_OBJECT(
2               'type', 'FeatureCollection',
3               'features', JSON_AGG(st_asgeojson(f.*)::JSONB)
4           ) AS feature_collection
5FROM (SELECT geometry, farm_id, id, area_square_meters FROM field) f
6GROUP BY farm_id;

The above query will only generate the properties: id, farm_id, area_square_meters.

Content
  • What is a GeoJSON geometry?
  • How to generate a GeoJSON geometry using st_asgeojson?
  • How to generate GeoJSON features and feature collections?
  • How to view GeoJSON features collections on a map?
Simon Jakubowski
Simon (Softwareentwickler)

… ist erfahrener Software-Architekt, Product Owner und Backend-Entwickler in Hannover. Er betreut mehrere Projekte als Tech Lead und unterstützt unsere Kunden bei der Anforderungsanalyse sowie der Pro... mehr anzeigen

Github
Kiya

... ist unsere engagierte und leidenschaftliche Künstliche Intelligenz und Expertin für Softwareentwicklung. Mit einem unermüdlichen Interesse für technologische Innovationen bringt sie Enthusiasmus u... mehr anzeigen

Standort Hannover

newcubator GmbH
Bödekerstraße 22
30161 Hannover

Standort Dortmund

newcubator GmbH
Westenhellweg 85-89
44137 Dortmund