I have a query which I would like to serialize to geoJSON:

select article_title, pub_date, city, geom 
from article, location
where article.id = location.id;

I can serialize the geom field easily enough with:

select st_asgeojson(geom) from location;

and the fields from article with:

select row_to_json(t) from(select article_title, pub_date from article) t;

The problem is I can't find a way to serialize properly to geojson from both the article and location fields. Can anyone help me with this? Let me know if you need any more details.


I've got some of the way with this:

select row_to_json(featcoll) 
(select 'FeatureCollection' As type, array_to_json(array_agg(feat)) as features 
from (select 'Feature' as type,
st_asgeojson(l.geom)::json as geometry, 
row_to_json((select l from (select city) as l)) 
as properties 
from location as l) 
as feat) 
as featcoll;

which is returning this:


I now need to add time property from the article table so it looks something like:

    "type": "FeatureCollection",
    "features": [
            "type": "Feature",
            "properties": {
                "pub_date": "2013-01-22 08:42:26+01"
            "geometry": {
                "type": "Point",
                "coordinates": [
  • 1
    Yes see gis.stackexchange.com/questions/124413/… Jun 14, 2015 at 19:43
  • thanks for that. still pretty confused but i'll take a bit of time to work my way through it and see how i go. Jun 14, 2015 at 21:01
  • It does take a bit of time to grok writing GeoJSON from a DB. Jun 14, 2015 at 21:07
  • If it would be OK to be dependent on GDAL then using GDAL with GeoJSON driver could be a faster way.
    – user30184
    Jun 14, 2015 at 22:13
  • 1
    You can use gdal from your own code but there is also a utility program ogr2ogr gdal.org/ogr2ogr.html. Read also this gdal.org/drv_pg.html and this gdal.org/drv_geojson.html. Then you will know that what you need to do is ogr2ogr -f geojson output.json PG:"dbname='databasename' host='addr' port='5432' user='x' password='y'" -sql "select article_title, pub_date, city, geom from article, location where article.id = location.id;
    – user30184
    Jun 15, 2015 at 10:27


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.