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

Creating a Feature Collection From PostGIS Geometries

PostGIS is an extension for the PostgreSQL database system that allows it to store and manage geospatial data effectively. 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.

This data of course is only an example of what is possible with PostGIS. If you want to learn more about how we successfully implemented features like polygon editing in a project we developed for our customer, click on the success story.