Create a FeatureCollection object from a PostGIS query

Usually to render spatial data on a web map you need GeoJSON data  of the type “FeatureCollection”, and if you have your data on a  PostGIS database you can do a query to get  the geometries on GeoJSON format with the ST_AsGeoJSON(geom) function. Something like this:

SELECT loc_id, loc_name, ST_AsGeoJSON(geom, 4) as geojson FROM locations;

This will return a different row for every feature that should be on the collection, so after run that query you need a function to iterate over every result to insert loc_id and loc_name as properties of every feature and push them to the array of features.

But there is a more efficient way of doing it with this single trick query:

SELECT row_to_json(fc)
FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
   FROM (SELECT 'Feature' As type
    , ST_AsGeoJSON(lg.geom, 4)::json As geometry
    , row_to_json((SELECT l FROM (SELECT loc_id, loc_name) As l
      )) As properties
   FROM locations As lg ) As f ) As fc;

The resulted JSON is a valid FeatureCollection object like this:

{
    "type":"FeatureCollection",
    "features":[
        {
            "type":"Feature",
            "geometry":{
                "type":"Point",
                "coordinates":[42.4005,-71.2577]
            },
            "properties":{"loc_id":1,"loc_name":"Waltham, MA"}
        },
        {
            "type":"Feature",
            "geometry":{
                "type":"Point",
                "coordinates":[42.9902,-71.4626]
            },
            "properties":{"loc_id":2,"loc_name":"Manchester, NH"}
        },
        {
            "type":"Feature",
            "geometry":{
                "type":"Point",
                "coordinates":[-96.7572,32.9098]
            },
            "properties":{"loc_id":3,"loc_name":"TI Blvd, TX"}
        }
    ]
}

Found on this “fantastic” article:  postgresonline.com