Filling table matches multilayer from buffer in PostGIS?

Question

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?

Answer 1

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.