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