Error at or near "select" PostGIS

Question

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
– whuber, Commented Aug 3, 2014 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 — Regina Obe
– Regina Obe, Commented Aug 4, 2014 at 1:08

Community · Accepted Answer · 2017-05-23 12:39:39Z

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

edited May 23, 2017 at 12:39

CommunityBot

1

answered Aug 4, 2014 at 15:39

ike

9037 silver badges17 bronze badges

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
– hansolo

2014-08-17 16:21:11 +00:00
Commented Aug 17, 2014 at 16:21

Add a comment |

Stack Exchange Network

Error at or near "select" PostGIS

1 Answer 1

Hot Network Questions

Error at or near "select" PostGIS

1 Answer 1

Related

Hot Network Questions