0

I have some tables in Postgis of differents geometries (Points, Lines, Polygons).

I create a buffer of 1 kilometer of one point with this:

ST_Buffer(ST_GeomFromText('POINT(3056563.45195278 804692.195990143)',48402), 1000) AS buffer

What i want to do is populate a new table with the results of my layers(tables in postgis) that are inside of the buffer.

I test with this query and it works, but only if found matches in all the tables.

    SELECT * FROM
         table1,
         table2,
  ST_Buffer(ST_GeomFromText('POINT(3056563.45195278804692.195990143)',48402),1000) AS buffer 
    WHERE
      ST_Within (buffer, table1.geom) AND
      ST_Within (buffer, table2.geom)

The idea is give some point or polygon with a buffer in the map. And return a table with all the matches from table1, table2, table3, table4 that are inside that buffer.

How can i solve that problem?

5

One way is to use a UNION, not a join, which is what you have. UNION just concatenates the results from each query into a single result.

So you'd do something like,

WITH buffer AS (
    SELECT ST_Buffer(ST_GeomFromText('POINT(3056563.45195278804692.195990143)',48402),1000) AS geom
)
SELECT table1.geom FROM table1, buffer WHERE ST_Within(table1.geom, buffer.geom)
UNION
SELECT table2.geom FROM table2, buffer WHERE ST_Within(table2.geom, buffer.geom)
UNION
[etc...]

This query returns rows with geometries from each table that are entirely within the buffer. I haven't given any consideration to the type of geometries I'm returning, but that wasn't part of your question.

I've used WITH here to create the buffer object so that I can reuse it. I refer to it in my queries as if it were a table with a geometry column.

Note that the order of arguments to ST_Within is contained geometry, containing geometry, not the other way around. Also note that for a UNION, the columns returned from each query have to match the columns for the others -- if the table definitions are different for each table, you'll have to explicitly name the columns.

  • Thank you a lot, it works perfect, is awesome the clause "WITH", very usefull, i just change the FROM part of query, because i get an error. It work adding the buffer in the FROM like this: FROM table1, buffer WHERE... – Hassim Aug 2 '16 at 20:00
  • Glad it worked. (Also thanks for noticing the missing table ref. I updated the answer.) – Rob Skelly Aug 2 '16 at 20:29

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.