{"id":182,"date":"2024-02-25T09:52:56","date_gmt":"2024-02-25T09:52:56","guid":{"rendered":"https:\/\/www.acugis.com\/gis-tutorials\/?page_id=182"},"modified":"2024-02-25T10:06:53","modified_gmt":"2024-02-25T10:06:53","slug":"generating-geojson-from-postgis","status":"publish","type":"page","link":"https:\/\/www.acugis.com\/gis-tutorials\/generating-geojson-from-postgis\/","title":{"rendered":"Generating GeoJSON from PostGIS"},"content":{"rendered":"\n

To export data residing in PostGIS to GeoJSON file, you have the following options:<\/p>\n\n\n\n

Option 1: Export your data using ogr2ogr<\/strong><\/p>\n\n\n\n

Connect to your PostgreSQL server as root and issue below, substituting your own database values.<\/p>\n\n\n\n

Below, we are saving the outputted geojson file to \/root\/mytable.json<\/p>\n\n\n\n

ogr2ogr -f GeoJSON \/root\/mytable.json PG:\"host=localhost dbname=mydb user=mydbuser password=mydbpassword port=5432\" \"mytabl(geom)\"<\/pre>\n\n\n\n
Option 2: Export using PgAdmin<\/strong><\/p>\n\n\n\n
Start PgAdmin and connect to target database.<\/p>\n\n\n\n
Select the database and go to Query Tool<\/p>\n\n\n\n
Paste below into the Query pane:<\/p>\n\n\n\n
SELECT jsonb_build_object(\n    'type', 'FeatureCollection',\n    'features', jsonb_agg(features.feature)\n)\nFROM (\n    SELECT jsonb_build_object(\n        'type', 'Feature',\n        'geometry', ST_AsGeoJSON(ST_Transform(geom, 4326))::jsonb,\n        'properties', to_jsonb(properties) - 'geom'\n    ) AS feature\n    FROM (\n        SELECT *\n        FROM yourtable\n    ) AS properties\n) AS features;<\/pre>\n\n\n\n
Double click on the result row:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
This will bring up the window below.  Be sure the view is set to “Code”<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Past the content into a file.  For example, mydata.json<\/p>\n\n\n\n
Option 3: Use PSQL and Copy<\/strong><\/p>\n\n\n\n
Connect to the target database as user postgres (or other user with superuser role).<\/p>\n\n\n\n
Issue below, substituting your table name for ‘myable’:<\/p>\n\n\n\n
 COPY (\n  SELECT jsonb_build_object(\n    'type', 'FeatureCollection',\n    'features', jsonb_agg(features.feature)\n)\nFROM (\n    SELECT jsonb_build_object(\n        'type', 'Feature',\n        'geometry', ST_AsGeoJSON(ST_Transform(geom, 4326))::jsonb,\n        'properties', to_jsonb(properties) - 'geom'\n    ) AS feature\n    FROM (\n        SELECT *\n        FROM mytable\n    ) AS properties\n) AS features\n) to '\/var\/lib\/postgresql\/mytable.json';<\/pre>\n\n\n\n
Option 4: Use \\o swith with PSQL<\/strong><\/p>\n\n\n\n
A fourth option, is to use the \\o switch, which exports the query ouput to a file.<\/p>\n\n\n\n
Connect to psql.<\/p>\n\n\n\n
Issue \\o with filename for output<\/p>\n\n\n\n
\\o myjsonfile.json;<\/pre>\n\n\n\n
Now, run the following SQL:<\/p>\n\n\n\n
SELECT jsonb_build_object(\n    'type', 'FeatureCollection',\n    'features', jsonb_agg(features.feature)\n)\nFROM (\n    SELECT jsonb_build_object(\n        'type', 'Feature',\n        'geometry', ST_AsGeoJSON(ST_Transform(geom, 4326))::jsonb,\n        'properties', to_jsonb(properties) - 'geom'\n    ) AS feature\n    FROM (\n        SELECT *\n        FROM mytable\n    ) AS properties\n) AS features;<\/pre>\n\n\n\n
IMPORTANT<\/strong>: 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.<\/p>\n\n\n\n
Delete all formatting prior to:<\/p>\n\n\n\n
{\"type\": \"FeatureCollection\", \"features\": <\/pre>\n\n\n\n
And following the final closing brakcet:<\/p>\n\n\n\n
}}]}<\/pre>\n","protected":false},"excerpt":{"rendered":"
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 Option 2: Export using PgAdmin Start PgAdmin and connect … <\/p>\n
Continue reading “Generating GeoJSON from PostGIS”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-182","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.acugis.com\/gis-tutorials\/wp-json\/wp\/v2\/pages\/182","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.acugis.com\/gis-tutorials\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.acugis.com\/gis-tutorials\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.acugis.com\/gis-tutorials\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.acugis.com\/gis-tutorials\/wp-json\/wp\/v2\/comments?post=182"}],"version-history":[{"count":8,"href":"https:\/\/www.acugis.com\/gis-tutorials\/wp-json\/wp\/v2\/pages\/182\/revisions"}],"predecessor-version":[{"id":194,"href":"https:\/\/www.acugis.com\/gis-tutorials\/wp-json\/wp\/v2\/pages\/182\/revisions\/194"}],"wp:attachment":[{"href":"https:\/\/www.acugis.com\/gis-tutorials\/wp-json\/wp\/v2\/media?parent=182"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}