Create GeoJSON with CRS from Postgis

Question

I have a table of Multiline Strings and need to generate a GeoJSON with the properties turning to EPSG 4326. I made the SQL below but is not in the correct format GeoJSON.

How do I create the GeoJSON using Postgis, so I have a FeatureCollection with the CRS 4326 set.

please add an example of the rows in the table and the output that you get, preferably with an explanation of why it is not correctly formatted — Ian Turton, Jun 10 '16 at 12:41
it is not so simple, have a look at http://www.postgresonline.com/journal/archives/267-Creating-GeoJSON-Feature-Collections-with-JSON-and-PostGIS-functions.html. — user30184, Jun 10 '16 at 12:47
Tks @user30184! I'm exercising the example provided on the site and actually seems pretty simple! But the error occurs type "json" does not exist I use PostGIS 2.0.6, preicso enable something ?: — csf, Jun 10 '16 at 12:59
I apologize, I have never done that myself. However, the json part with test query works for me select row_to_json(foo) from (select name from test_table limit 1) as foo; — user30184, Jun 10 '16 at 13:14
I have done it. It gets complicted, as going from rows to JSON collections isn't that obvious, but see this answer, http://gis.stackexchange.com/questions/124413/how-to-export-thousands-of-features-from-postgis-to-a-single-geojson-file/124415#124415, in which I cite the original postgresonline article. — John Powell, Jun 10 '16 at 13:16
Thanks for the link! As for the error I need to update my Postgres SQL that is at version 9.1 — csf, Jun 10 '16 at 13:17
But you might need to add the crs into the GeoJSON, as in this example: http://help.cadcorp.com/en/7.0/sis/help/Savers_GeoJSONFeaturesDataset.html — John Powell, Jun 10 '16 at 13:20

dbaston · Accepted Answer · 2016-12-13T18:29:52.187

In PostgreSQL 9.4+, you can use the json_build_object and jsonb_build_object functions to construct JSON without subqueries, which feels more similar to how this is done outside of PostgreSQL. For example:

WITH test_geoms(id, point_type, geom) AS 
(VALUES(1, 'Park', ST_MakePoint(0,0)), (2, 'School', ST_MakePoint(1,1)))

SELECT json_build_object(
    'type', 'FeatureCollection',
    'crs',  json_build_object(
        'type',      'name', 
        'properties', json_build_object(
            'name', 'EPSG:4326')),
    'features', json_agg(
        json_build_object(
            'type',       'Feature',
            'id',         id,
            'geometry',   ST_AsGeoJSON(geom)::json,
            'properties', json_build_object(
                'point_type', point_type
            )
        )
    )
)
FROM test_geoms;

I added ST_AsGeoJSON(ST_Transform (geom,4326)), and it worked perfectly, tks ! — csf, Jun 10 '16 at 17:54

John Powell · Answer 2 · 2016-06-10T15:07:08.880

You can create a feature collection by using row_to_json, selecting 'FeatureCollection' as type, and rolling up all you geometries into an array using array_to_json(array_agg(geom)) in a subquery, which creates the appropriate GeoJSON using ST_AsGeoJSON.

You can also add the EPSG:4326 as a crs attribute, at the top level of the GeoJSON. When you are creating keys and values, the trick is to realize that, for example, Select name as type gets converted into type:name and (SELECT properties FROM (SELECT 'EPSG:4326' AS name) AS properties) becomes properties:{'name':EPSG:4326} once the whole lot is wrapped up by the row_to_json function.

Replace your_table with your table name in the below.

SELECT row_to_json(featcoll)
  FROM 
    (SELECT 'FeatureCollection' AS type, 
    (SELECT crs FROM 
        (SELECT 'name' AS type, 
             (SELECT properties 
                FROM (SELECT 'EPSG:4326' AS name) AS properties)
    ) AS crs),
    array_to_json(array_agg(feats)) AS features 
         FROM (SELECT 
                'Feature' AS type, 
                ST_AsGeoJSON(geom)::JSON AS geometry 
               FROM your_table) 
      AS feats
) AS featcoll;

or with some test data,

WITH test_geoms(geom) AS 
    (VALUES(ST_MakePoint(0,0)), (ST_MakePoint(1,1)))

SELECT row_to_json(featcoll)  
   FROM 
    (SELECT 'FeatureCollection' AS type, 
    (SELECT crs FROM 
      (SELECT 'name' AS type, 
         (SELECT properties 
            FROM (SELECT 'EPSG:4326' AS name) AS properties)
     ) AS crs),
   array_to_json(array_agg(feats)) AS features 
     FROM (SELECT 
            'Feature' AS type, 
            ST_AsGeoJSON(geom)::JSON AS geometry 
           FROM test_geoms) 
     AS feats
 ) AS featcoll;

which produces:

{ "type": "FeatureCollection", "crs": { "type": "name", "properties": { "name": "EPSG:4326" } }, "features": [{ "type": "Feature", "geometry": { "type": "Point", "coordinates": [0, 0] } }, { "type": "Feature", "geometry": { "type": "Point", "coordinates": [1, 1] } }] }

Create GeoJSON with CRS from Postgis

2 Answers2