4

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.

enter image description here

  • 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 postgresonline.com/journal/archives/…. – user30184 Jun 10 '16 at 12:47
  • Edited to show the current point where I am – csf Jun 10 '16 at 12:48
  • 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
5

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;
  • Nice, that is a fair bit easier to read. – John Powell Jun 10 '16 at 15:19
  • I added ST_AsGeoJSON(ST_Transform (geom,4326)), and it worked perfectly, tks ! – csf Jun 10 '16 at 17:54
6

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] } }] }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.