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,
                ]
            }
        },
sammy88888888
  • 201
  • 2
  • 3
  • 1
    Yes see http://gis.stackexchange.com/questions/124413/how-to-export-thousands-of-features-from-postgis-to-a-single-geojson-file/124415#124415 – 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
  • Ah ok, i'll look into that. Actually I'm using a django/geodjango setup and was originally hoping to achieve this with querysets but found it a clumsy system. Could you elaborate a little of the GDAL approach. – sammy88888888 Jun 15 '15 at 07:43
  • 1
    You can use gdal from your own code but there is also a utility program ogr2ogr http://www.gdal.org/ogr2ogr.html. Read also this http://www.gdal.org/drv_pg.html and this http://www.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
  • cool, thanks, I'll have a look at implementing. @JohnBarça i've got most of the way with the raw sql but struggling to find where to put the where clause to join the location and article table. any suggestions. I've updated above to show the stage I'm at now. – sammy88888888 Jun 15 '15 at 14:39

0 Answers0