4

I am currently producing JSON from a database successfully using the following query:

SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features  
FROM (SELECT 'Feature' As type, ST_AsGeoJSON(lg.geom)::json As geometry,
row_to_json((SELECT l FROM (SELECT extent, activationid) As l)) As properties  FROM hat.projectsgeom  As lg  
WHERE activationid = 'HOT-0001' AND agency = 'HOT' AND type = 'activation') As f )  As fc;

However, I am stuck when I try and use INNER JOIN to another table.

This is my attempt so far ..

ERROR: relation "activations.activationid" does not exist

LINE 5: INNER JOIN ( select * from hat.activations.activationid) zz ...

SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features  
FROM (SELECT 'Feature' As type, ST_AsGeoJSON(lg.geom)::json As geometry,
row_to_json((SELECT l FROM (SELECT extent, activationid) As l)) As properties FROM hat.projectsgeom  As lg  
WHERE activationid = 'HOT-0001' AND agency = 'HOT' AND type = 'activation') As f )  As fc
INNER JOIN ( select * from hat.activations.activationid) zz ON (hat.activations.activationid = hat.projectsgeom.activationid );

I have tried a number of variations, I think this is close, but I cannot get a result ..

What do I need to change in the second query to get the Inner Join to work properly and return all fields form the second table?

UPDATE:

Based on Jakub's response, I have been able to get closer.

SELECT row_to_json(fc) FROM 
    ( 
        SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM 
        (
            SELECT 'Feature' As type, ST_AsGeoJSON(lg.geom)::json As geometry, row_to_json
            (
                (
                    SELECT l FROM 
                    (
                        SELECT * 
                        FROM  hat.projectsgeom  
                        INNER JOIN hat.activations ON hat.projectsgeom.activationid = hat.activations.activationid 
                        WHERE hat.projectsgeom.agency = 'HOT' AND hat. projectsgeom.type = 'activation' 
                    ) As l
                )
            ) As properties  FROM hat.projectsgeom As lg  

        ) As f 
    )  As fc;

Is returning the following error

ERROR:  more than one row returned by a subquery used as an expression

I am baffled on how to get this to work, with the Json.

The table projectsgeom has multiple rows that need to be INNER JOINED to table activationid and all the columns from both tables returned as the properties, using the WHERE Clause on table projectsgeom

What Query will work?

  • You should write what is wrong with the query you tried. Syntax error? Wrong result? – Jakub Kania Mar 4 '15 at 8:25
  • And what's more (because your query is a certain syntax error), where did you try to include those fields? – Jakub Kania Mar 4 '15 at 8:29
  • Good point, Question Edited – Mark Cupitt Mar 4 '15 at 8:42
  • I'm still not sure how you want the resultset to look so can't fully correct it but I posted a few hints. – Jakub Kania Mar 4 '15 at 9:22
  • 1
    I have spent the day trying, added an update, in this case, I need multiple rows returned. The projectsgeom table has multiple rows that I want to join with the columns contained in table activation id as properties – Mark Cupitt Mar 5 '15 at 10:00
3

You're getting an error because:

  • activations.activationid is in a subquery that's been aliased to zz. Since the subquery doesn't really do anything you can flatten it and get

    INNER JOIN hat.activations.activationid zz ON (zz.activationid = ...

  • However projectsgeom is on a diffrent nesting level too so the above will only change the syntax error. Either you'll have to move the join into subquery f or select projectsgeom.activationid out of f and fc.

  • What I am trying to achieve is to get ALL the columns from the hat.activations table in the properties of the second row_ro_json – Mark Cupitt Mar 4 '15 at 11:33
  • I have spent the day trying, added an update, in this case, I need multiple rows returned. The projectsgeom table has multiple rows that I want to join with the columns contained in table activation id as properties – Mark Cupitt Mar 5 '15 at 10:14
3

I was able to create the correct answer, so have posted the correct Query here for future users who want to convert GIS data from two joined PostGres Table's to geoJSON

I used information from the following pages to help me solve this:

http://hashrocket.com/blog/posts/faster-json-generation-with-postgresql http://bender.io/2013/09/22/returning-hierarchical-data-in-a-single-sql-query/ https://stackoverflow.com/questions/21137237/postgres-nested-json-array-using-row-to-json https://stackoverflow.com/questions/13227142/postgresql-9-2-row-to-json-with-nested-joins

SELECT row_to_json(fc) FROM 
( 
    SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features  FROM 
    (

        SELECT 'Feature' As type, ST_AsGeoJSON(lg.geom)::json As geometry, row_to_json
        (
        (
            SELECT l FROM 
            (
            SELECT
              p.*
                FROM hat.activations p
                WHERE p.activationid= lg.activationid
            ) As l
        )
        ) As properties  , lg.agency, lg.activationid, lg.projectid, lg.taskid, lg.extent, lg.updated, lg.created
        FROM hat.projectsgeom  As lg   
        WHERE activationid = 'HOT-0001' AND agency = 'HOT' AND type = 'activation'
    ) As f 
)  As fc;

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.