[GIS] Error at or near “select” PostGIS

node-jspostgissql

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?

"SELECT  gid 
FROM    buildings 
WHERE   st_area(
            st_intersection(ST_GeomFromGeoJSON('"+drawingGeo+"'),
                (the_geom)))/
                     st_area(ST_GeomFromGeoJSON('"+drawingGeo+"')) > 0 
        AND gid = " + randomnum

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

Related Question