5

I have got stuck with a dynamic analysis that I would like to include in a project I am working with. I want to save the query in the project file or make it to a view in my database. I have tried a couple of combination without success so far, but I am on a SQL learning curve =).

I would like to combine the queries below so that my ST_buffer is used in the same query as the layer where building.geom need to be completely inside. Is this possible?

SELECT
    event_point.id,
    event_point.geom,
    st_buffer(event_point.geom, 100) AS "Buffer",

FROM public.event_point

SELECT 
    building.id
    building.address
FROM 
    building, "Buffer"
WHERE
    ST_within(building.geom, "Buffer".geom)
Improve this question
  • 4
    The question isn't totally clear. However, in general, it is better to use ST_DWithin than ST_Buffer -- it amounts to the same thing. Saying ST_DWithin(geom1, geom2, 1000) is the same as saying ST_Intersecs(geom1, ST_Buffer(geom2,1000)) except that the first is much more efficient. – John Powell Feb 24 '15 at 15:36
  • You are completely right! I ended up using you suggested ST_DWithin. It worked much faster compared to use the combined query. 40 seconds faster! – geogrow Feb 25 '15 at 10:06
  • Glad it helped. – John Powell Feb 25 '15 at 10:39
3

Put your first query in the from clause of the 2nd: something like that should work:

SELECT building.id building.address FROM building, (SELECT event_point.id, event_point.geom, st_buffer(event_point.geom, 100) AS buffergeom FROM public.event_point) buffer WHERE ST_within(building.geom, buffer.buffergeom)

Improve this answer
  • This only works if there is only one row in the event_point table; otherwise you'll need a WHERE clause to limit to one row, or a JOIN to merge multiple rows correctly. – Vince Feb 24 '15 at 16:05
  • This worked, thanks for explaining it how to build a query like that! However, I ended up using John Barca suggestion because the query was much faster to return – geogrow Feb 25 '15 at 10:07

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.