I'm using node.js to connect to a PostGIS database to check if an input feature significantly intersects with an existing feature.
The error seems to be with the nested SELECT statement – can anyone correct this for me?
I've checked that the randomnum and drawingGeo variables are valid inputs (examples below). the_geom is also definitely the geometry column.
drawingGeo =
{"type":"Polygon","coordinates":[[[-337154.39223673707,6715717.913400569],[-337111.09782618494,6715721.794968411],[-337102.4389440745,6715706.865861325],[-337135.2829796658,6715702.088547057],[-337154.39223673707,6715717.913400569]]]}
randomnum = 11702
var drawquery = client.query('SELECT gid from buildings
where(st_area(st_intersection(ST_GeomFromGeoJSON("'+drawingGeo+'"),ST_GeomFromGeoJSON(
SELECT ST_AsGeoJSON(the_geom) from buildings where gid = '+randomnum+'))))/
st_area(ST_GeomFromGeoJSON("'+drawingGeo+'")) > .8');
EDIT: Error given in node:
events.js:72
throw er; // Unhandled 'error' event
^
error: syntax error at or near "type"
Error when I copy the query into PgAdmin:
ERROR: syntax error at or near "SELECT"
LINE 2: ...141033,6706842.085406107]]]}'),ST_GeomFromGeoJSON(SELECT ST_...
^
********** Error **********
ERROR: syntax error at or near "SELECT"
SQL state: 42601
Character: 335
Best Answer
The problem looks like it is the select statement interior to the function. Maybe you could try the following version?
If you want to use the original statement, I believe adding a parenthesis to the interior select might help:
https://stackoverflow.com/questions/12452533/nested-query-as-postgis-function-parameter