1

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.

UPDATE!

I've got some of the way with this:

select row_to_json(featcoll) 
from
(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:

{  
    "type":"FeatureCollection",
    "features":[  
        {  
            "geometry":{  
                "type":"Point",
                "coordinates":[  
                    -1.54907226540932,
                    54.7547462165902
                ]
            },

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": [
                    7.582512743,
                    51.933292258,
                ]
            }
        },
  • 1
    Yes see gis.stackexchange.com/questions/124413/… – John Powell Jun 14 '15 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. – sammy88888888 Jun 14 '15 at 21:01
  • It does take a bit of time to grok writing GeoJSON from a DB. – John Powell Jun 14 '15 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 '15 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 '15 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.