How to perform query on two unrelated tables

Question

I have a table A with Id and Geometry

And Temporary table B

Each Geometry of Temporary table B has intersecting geometry in Table A. I want to find the intersecting geometry and make a union of the geometry. The final table should be id of Table A which is having intersection and the union of geometry. If there are more than 1 intersecting geometry then pick any one.

I can not perform join here as there is no common column between these tables so don't know how to get the final result.

Answer 1

In the demo, I took types intrange for simulating the types geometry. Ranges are close enough to get the same query:

demo:db<>fiddle

SELECT DISTINCT ON (b.id)       -- 3
    a.id,
    a.geom + b.geom             -- 2
FROM a
JOIN b ON a.geom && b.geom      -- 1

1. Join condition should be the check for intersection. For ranges it is && operator, in your case it should be st_intersects(a.geom, b.geom)
2. Union both geometries. Here it is done using the + operator, in your case it should be st_union(a.geom, b.geom)
3. DISTINCT ON(b.id) ensures that there's only one record per b.id

So, your final query should look like this:

SELECT DISTINCT ON (b.id)                    -- 3
    a.id,
    st_union(a.geom, b.geom)                 -- 2
FROM a
JOIN b ON st_intersects(a.geom, b.geom)      -- 1