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

[karsten]

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:postgis-users-bounces at lists.osgeo.org] On Behalf
Of Stephen Woodbridge
Sent: Saturday, September 19, 2020 14:39
To: postgis-users at lists.osgeo.org
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
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users