2

I need to add a subquery to a working function. Instead of select * from table, I'd like to select * from (select * from table where height>1500) as sub, something that works in other functions I have.

However, it doesn't work in this function. See below:

This works as expected:

select row_to_json(t) from (
    select * from table
    where st_intersects(
        st_transform(
            st_setsrid( 
                ST_geomfromgeojson(
                    '{"type":"Polygon","coordinates":[[[42.56236553192139,37.96026293121387],[42.56258010864258,37.960165652451245],[42.56299    85332489,37.95957774762163],[42.56308972835541,37.95940856550367],[42.5631058216095,37.95926898996297],[42.56291270256042    5,37.959137873304314],[42.56252110004425,37.9590532817874],[42.561931014060974,37.9590828888294],[42.561678886413574,37.9    59137873304314],[42.56154477596283,37.95921823515514],[42.56145358085632,37.959319744735744],[42.561437487602234,37.95942    548373303],[42.56181299686431,37.959861126796085],[42.562150955200195,37.9601318163297],[42.56202757358551,37.96030522628    786],[42.56236553192139,37.96026293121387]]]}'
                ), 
            4326), 
        3857), 
    table.geom)) 
as t;

This doesn't work (added subquery), and I get the error missing FROM-clause entry for table:

select row_to_json(t) from (
    select * from (
        select * from table where height>1500
    ) sub 
    where st_intersects(
        st_transform(
            st_setsrid( 
                ST_geomfromgeojson(
                    '{"type":"Polygon","coordinates":[[[42.56236553192139,37.96026293121387],[42.56258010864258,37.960165652451245],[42.56299    85332489,37.95957774762163],[42.56308972835541,37.95940856550367],[42.5631058216095,37.95926898996297],[42.56291270256042    5,37.959137873304314],[42.56252110004425,37.9590532817874],[42.561931014060974,37.9590828888294],[42.561678886413574,37.9    59137873304314],[42.56154477596283,37.95921823515514],[42.56145358085632,37.959319744735744],[42.561437487602234,37.95942    548373303],[42.56181299686431,37.959861126796085],[42.562150955200195,37.9601318163297],[42.56202757358551,37.96030522628    786],[42.56236553192139,37.96026293121387]]]}'
                ), 
            4326), 
        3857), 
    table.geom)) // <- error points to here
as t;

How can I do this? I need to add the string (SELECT * FROM table WHERE height>1500) AS sub every time, as this is set somewhere else and needs to be included wholly.

3
  • It might help to paste the text of the error message into your question. Have you tried adding that as a wrapper to your initial query instead of as a subquery? Since you are including all fields anyway, it would essentially accomplish the same thing. From a performance standpoint, it might make sense to limit the number of fields that are being queried each time. Aug 3, 2015 at 15:07
  • How can I do that exactly?
    – knutole
    Aug 3, 2015 at 15:16
  • 1
    Which part are you asking about? Error message is a matter of copying from message window if using PGAdmin III into your question. The rest of it is sort of moot since you solved your issue, unless you want to limit the fields you are returning. Aug 3, 2015 at 15:31

1 Answer 1

1

Thanks to @RhodiumToad on #postgres, trick is to use sub.geom instead of table.

 select row_to_json(t) from (
    select * from (
        select * from table where height>1500
    ) as sub 
    where st_intersects(
        st_transform(
            st_setsrid( 
                ST_geomfromgeojson(
                    '{"type":"Polygon","coordinates":[[[42.56236553192139,37.96026293121387],[42.56258010864258,37.960165652451245],[42.56299         85332489,37.95957774762163],[42.56308972835541,37.95940856550367],[42.5631058216095,37.95926898996297],[42.5629127025     6042    5,37.959137873304314],[42.56252110004425,37.9590532817874],[42.561931014060974,37.9590828888294],[42.561678886413     574,37.9    59137873304314],[42.56154477596283,37.95921823515514],[42.56145358085632,37.959319744735744],[42.561437487602     234,37.95942    548373303],[42.56181299686431,37.959861126796085],[42.562150955200195,37.9601318163297],[42.5620275735855     1,37.96030522628    786],[42.56236553192139,37.96026293121387]]]}'
                ), 
            4326), 
        3857), 
    sub.geom)) // <-- sub instead of table
 as t;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.