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

[Stephen Woodbridge]

On 9/19/2020 5:49 PM, karsten wrote:
> 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 ?
Because A, B says intersect ALL records of A against ALL records of B.
Which is not what you want, You want ALL records of B intersected 
against EACH record in A.
So you got the correct result, but it was not what you wanted. The GROUP 
BY allow you to separate the ALL results into EACH results.

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