Here is a handy SQL query I used at work to export geographic point data as GeoJSON from a plain, "spatially-dumb" PostgreSQL database. By that, I mean without the awesome PostGIS extension which offers native GeoJSON support.
It makes use of high-level JSON functions that appeared in PostgreSQL 9.4, so this assumes you use at least this version.
SELECT json_build_object( 'type', 'FeatureCollection', 'features', json_agg(json_build_object( 'type', 'Feature', 'id', p.id, 'geometry', json_build_object( 'type', 'Point', 'coordinates', json_build_array(p.longitude, p.latitude), -- assuming your coordinates are stored like that. ), 'properties', json_build_object( 'name', p.name, 'address', p.address -- etc. Those are just examples of fields in the table. ) ) )) FROM my_places p;
With this indenting and thanks to the simple function signatures (lists of alternating keys and values), you can see the final JSON shape directly in the query. The
json_agg function is an SQL aggregate (like
min, etc.) which reduces multiple values to a single JSON array.
Saved in a UTF-8 text file, the result is a valid GeoJSON
Point features. It can be opened in QGIS, geojson.io, and a multitude of other tools.