Export GeoJSON from PostgreSQL using Beekeeper Studio

Query > Download > Copy > Paste!

Beekeeper Studio is an open source SQL editor written in NodeJS and Vue JS. The UI is clean and simple, and it has just the right amount of tools to get the job done. The query interface even has a handy Download button to export the results of a query. Using the Copy to Clipboard option we can extract GeoJSON directly from a PostGIS enabled PostgreSQL database to a computer without having to open QGIS or another GIS desktop or command-line tool.

  1. Open Beekeeper Studio and copy the following query, replacing your_table with your table name, and changing the geom fields and id field if need be.
SELECT jsonb_build_object(
'type', 'FeatureCollection',
'features', jsonb_agg(features.feature)
)
FROM (
SELECT jsonb_build_object(
'type', 'Feature',
'id', id,
'geometry', ST_AsGeoJSON(ST_Transform(geom, 4326), 8)::jsonb,
'properties', to_jsonb(inputs) - 'geom'
) AS feature
FROM (SELECT * FROM your_table) inputs) features;
  1. After the script runs, use the Download button to copy the results to the clipboard.
  2. Open a text editor such as VS Code and paste the data.
  3. Use the Home button to go to the beginning of the text and delete “jsonb_build_object”.
  4. Save the result as a .geojson file.
Newer Post
Peaks 3D Visualization
Older Post
FOSS4G Image Processing

Related Posts