Selecting (Geo)JSON from PostGIS

PostGIS is a powerful extension for PostgreSQL that adds support for geographic objects, allowing you to store, query, and manipulate spatial data. However, when it comes to selecting feature collections in (geo)JSON format, PostGIS does not provide a built-in function. In this blog post, we'll explore how you can build your own query to select (geo)JSON data from PostGIS.

To select (geo)JSON data from a PostGIS table, we can leverage the json_build_object() and json_agg() functions in PostgreSQL. These functions allow us to construct a JSON object that represents a feature collection with properties and features.

Here's an example of how to build a query to select a feature collection with (geo)JSON data from a table called "field" in PostGIS:

1SELECT json_build_object(
2    'type', 'FeatureCollection',
3    'properties', json_build_object(),
4    'features', json_agg(st_asgeojson(field.geometry)::json)
5) AS feature_collection
6FROM field
7WHERE farm_id = 'XYZ';

In this query, we use the json_build_object() function to create a new JSON object with three properties: 'type', 'properties', and 'features'. The 'type' property is set to 'FeatureCollection', representing the type of the feature collection. The 'properties' property is set to an empty JSON object since we don't have any properties to include in this example. The 'features' property is set to the result of the json_agg() function, which aggregates the (geo)JSON representations of the geometries in the "field" table.

The st_asgeojson() function is used to convert the geometry column to (geo)JSON format. By appending ::json to the result, we ensure that the (geo)JSON is returned as a JSON object rather than a string.

To filter the feature collection based on a specific condition, such as the farm ID, you can use the WHERE clause in the query. In the example query above, we filter the feature collection based on the farm ID 'XYZ'.

It's worth noting that the json_agg() and json_build_object() functions are not specific to PostGIS. They are standard PostgreSQL functions that allow you to work with JSON data in general. If you're interested in learning more about the available JSON functions in PostgreSQL, you can refer to the official documentation.

In conclusion, while PostGIS does not provide a built-in function for selecting feature collections, you can use the json_build_object() and json_agg() functions in PostgreSQL to construct a custom query that returns (geo)JSON data. By leveraging these functions, you have the flexibility to manipulate the feature collection and filter it based on your specific requirements.

If you're working with spatial data in a web development project, this knowledge can be particularly valuable. It allows you to retrieve (geo)JSON data from PostGIS and integrate it into your web application, enabling you to visualize and analyze spatial information in a user-friendly manner.

So, next time you need to select (geo)JSON data from PostGIS, remember this custom query and unlock the power of (geo)JSON in your applications.