Yes that works thanks ! Would anyone have an explanation why the initial query below seems to intersect the entire two layers and not each records of table A with geometries of table B ?
Cheers Karsten -----Original Message----- From: postgis-users [mailto:[email protected]] On Behalf Of Stephen Woodbridge Sent: Saturday, September 19, 2020 14:39 To: [email protected] Subject: Re: [postgis-users] intersect two tables one row at a time On 9/19/2020 5:03 PM, karsten wrote: > Hi all, > I have two polygon geometry tables say A and B and trying to find the > ids from Table B where their geometries intersect for each individual > record in table A. I have tried many things that did not work and this > was my starting point query: > Select array_agg(B.id) from A ,B where ST_Intersects(A.geom,B.geom) ; Try: Select A.id, Array_agg(B.id( from A, B where ST_Intersects(A.geom,B.geom) group by A.id order by A.id; > I was expecting to get only the ids of table B that intersect the > geometry of one row, but what this query returns seems to be a string > of all ids the there entire two layers intersect. I don't understand > why that is. > How can I restrict the query above further so that I get back only the > ids for each row (one geometry of table A at a time with the ids of > intersecting polygons from table B) Cheers Karsten > > _______________________________________________ > postgis-users mailing list > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
