PostgreSQL GeoJSON <- php -> JavaScript

Question

I'm in the throes of re-building something that built almost a year ago (don't ask where the old version went - it's embarrassing).

The core functionality uses an $.getJSON (ajax-ish) call in javascript that runs a PHP script that runs a PostgreSQL query that builds a JSON object and returns it. (Pause for breath).

The issue is what PostgreSQL spits out when it's its turn to shine.

I'm aware of the build_json_object() and build_json_array() functionality in PostgreSQL 9.4+, but one of the DBs on which this has to run hasn't been upgraded from 9.2 and I don't have time to do so in the next month or so.

For now I am using row_to_json() (and ST_AsGeoJSON() on the geometry) to build my GeoJSON collection, which gets flung back at the client via a callback.

Taking my cue from this very nice post (and staying within very small epsilon of that post's query structure), I run 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,
      row_to_json((select l from (select $vars) as l)) as properties,
      ST_AsGeoJSON(ST_Transform(lg.g1,4326)) as geometry    
        from $source_table as lg   
 where g1 && ST_Transform(ST_SetSRID(ST_MakeEnvelope($bounds),4326),4283)
 ) as f ) as fc;

($vars, $source_table and $bounds are supplied by PHP from POST variables).

When I fetchAll(PDO::FETCH_ASSOC) that query to $result, and json_encode($result[0]["row_to_json"]), the object returned to javascript is an object which can be JSON.parse()'d to give the expected (an Object with a FeatureCollection which in turn contains a bunch of Features, one of which is geometry).

So far, so good. And quick - gets the data and is back in a second or so.

The problem is that at the query stage, the array of stuff that relates to the geometry is double-quoted: the relevant segment of the JSON for an individual Feature looks like

{"type":"Feature","geometry":"{\\"type\\":\\"Polygon\\",
                              \\"coordinates\\":"[[[146.885447408,-36.143199088],
                                               [146.884964384,-36.143136232],
                                                ... etc
                                             ]]"
                              }",
                              "properties":{"address_pfi":"126546461",
                                            "address":"blah blah",
                                             ...etc }
}

This is what I get if I COPY the PostgreSQL query result to file: it's before any mishandling of the output.

Note the (double-escaped) double-quotes that only affect attributes (in the non-JSON sense) of the geometry {type, coordinates}: the "geometry" bit looks like

"geometry":"{stuff}"

instead of

"geometry":{stuff}

If the JSON produced by PostgreSQL is put through the parser/checker at GeoJSONLint, it dies in a screaming heap (which it should - it's absolutely not 'spec') - and of course it's never going to render: it spits out 'invalid type' as you might expect.

For the moment I've sorted it out by a kludge (my normal M.O.) - when $.getJSON returns the object, I

1. turn it into a string, then
2. .replace(/"{/g, '{') and .replace(/}"/g, '}') and .replace(/\\/g, ''), and then
3. turn it back into an object and proceed with shenanigans.

This is not good practice (to say the least): it would be far better if the query itself could be encouraged to return valid GeoJSON.

It seems clear that the problem is the row_to_json() stage: it sees the attribute-set for "geometry" and treats it differently from the attribute-set for "properties" - it (incorrectly) quote-escapes the "geometry" (after slash-escaping all double-quotes) one but (correctly) leaves the "properties" one as-is.

So after this book-length prelude... the question.

Is there some nuance about the query that I'm missing or ignoring? I've RTFD for the relevant PostgreSQL commands, and apart from prettification switches there is nothing that I'm aware of.

And of course, if there is a parsimonious way of doing the whole round-trip I would embrace it: the only caveat is as it must retain its 'live-fetch' nature - the $.getJSON runs under a listener that triggers on "idle" in a Google Map, and the source table, variables of interest and zoom (which determines $bounds) are user-determined.

(Think of it as being a way to have a map layer that updates with pan and zoom by only fetching ~200-300 simple-ish (cadastre) features at a time -0 far better that, than to generate a tile pyramid for an entire state for zooms 10-19. I bet someone has already done such a thing on bl.ocks, but I haven't found it).

Answer 1

It seems that you are missing the cast to json. It should be

ST_AsGeoJSON(ST_Transform(lg.g1,4326))::json

Without the cast, st_asgeojson returns a string, that is double-encoded.

However, you could also get attributes and geoJson, than json_decode the json with PHP, create a geoJson featurecollection array with php, and finally json_encode the whole result.