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.
Advertisement
Answer
In the demo, I took types intrange
for simulating the types geometry
. Ranges are close enough to get the same query:
SELECT DISTINCT ON (b.id) -- 3 a.id, a.geom + b.geom -- 2 FROM a JOIN b ON a.geom && b.geom -- 1
- Join condition should be the check for intersection. For ranges it is
&&
operator, in your case it should best_intersects(a.geom, b.geom)
- Union both geometries. Here it is done using the
+
operator, in your case it should best_union(a.geom, b.geom)
DISTINCT ON(b.id)
ensures that there’s only one record perb.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