Generating GeoJSON from PostGIS

To export data residing in PostGIS to GeoJSON file, you have the following options:

Option 1: Export your data using ogr2ogr

Connect to your PostgreSQL server as root and issue below, substituting your own database values.

Below, we are saving the outputted geojson file to /root/mytable.json

ogr2ogr -f GeoJSON /root/mytable.json PG:"host=localhost dbname=mydb user=mydbuser password=mydbpassword port=5432" "mytabl(geom)"

Option 2: Export using PgAdmin

Start PgAdmin and connect to target database.

Select the database and go to Query Tool

Paste below into the Query pane:

SELECT jsonb_build_object(
    'type', 'FeatureCollection',
    'features', jsonb_agg(features.feature)
)
FROM (
    SELECT jsonb_build_object(
        'type', 'Feature',
        'geometry', ST_AsGeoJSON(ST_Transform(geom, 4326))::jsonb,
        'properties', to_jsonb(properties) - 'geom'
    ) AS feature
    FROM (
        SELECT *
        FROM yourtable
    ) AS properties
) AS features;

Double click on the result row:

This will bring up the window below. Be sure the view is set to “Code”

Past the content into a file. For example, mydata.json

Option 3: Use PSQL and Copy

Connect to the target database as user postgres (or other user with superuser role).

Issue below, substituting your table name for ‘myable’:

 COPY (
  SELECT jsonb_build_object(
    'type', 'FeatureCollection',
    'features', jsonb_agg(features.feature)
)
FROM (
    SELECT jsonb_build_object(
        'type', 'Feature',
        'geometry', ST_AsGeoJSON(ST_Transform(geom, 4326))::jsonb,
        'properties', to_jsonb(properties) - 'geom'
    ) AS feature
    FROM (
        SELECT *
        FROM mytable
    ) AS properties
) AS features
) to '/var/lib/postgresql/mytable.json';

Option 4: Use \o swith with PSQL

A fourth option, is to use the \o switch, which exports the query ouput to a file.

Connect to psql.

Issue \o with filename for output

\o myjsonfile.json;

Now, run the following SQL:

SELECT jsonb_build_object(
    'type', 'FeatureCollection',
    'features', jsonb_agg(features.feature)
)
FROM (
    SELECT jsonb_build_object(
        'type', 'Feature',
        'geometry', ST_AsGeoJSON(ST_Transform(geom, 4326))::jsonb,
        'properties', to_jsonb(properties) - 'geom'
    ) AS feature
    FROM (
        SELECT *
        FROM mytable
    ) AS properties
) AS features;

IMPORTANT: When using this method, additional formatting rows can be inserted into both the top and the bottom of the file. Open the file in a file editor and remove these.

Delete all formatting prior to:

{"type": "FeatureCollection", "features": 

And following the final closing brakcet:

}}]}