2

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
  • Please edit this question to state explicitly what error occurs. – whuber Aug 3 '14 at 15:13
  • Are you sure your drawingGeom returned doesn't have a SELECT in front. That looks to be the issue to me. Anyway you really should parameterize those inputs. Not sure how you do that in node.js – LR1234567 Aug 4 '14 at 1:08
0

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

  • Thanks, I used this method - I thought I'd have to do a select to get the_geom of the current row but it seems this works. I removed ST_asgeojson from your query where you had it around (the_geom) as that's a geometry column in my database. Cheers. – hansolo Aug 17 '14 at 16:21

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.