PostGIS to GeoJSON

SELECT
    row_to_json(feature_collection)
FROM (
    SELECT
        'FeatureCollection'::text as type,
        case
            when
                array_agg(feature_collection_data) is null
            then
                array_to_json(ARRAY[]::varchar[]) 
            else
                array_to_json(array_agg(feature_collection_data))
        end as features
    FROM (
        SELECT
            'Feature'::text as type,
            ST_AsGeoJSON({geomCol})::json as geometry,
            row_to_json((SELECT row FROM (SELECT {properties}) as row)) as properties
        FROM
            {table} as row_data
        WHERE
            {where}
    )  as feature_collection_data
)  as feature_collection;";

Notes:

{geomCol} - geom column to select from or geometry returning call such as ST_GeomFromText('POINT(' || lon || ' ' || lat || ')' ,4326)

{properties} - a comma separated list of columns to select from; can be aliased

{table} - a table to select from

{where} - a where clause if needed

blog comments powered by Disqus