[postgis-users] intersect two tables one row at a time

[Stephen Woodbridge]

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
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users