3

I have a table looking this this

id     geom
1      POLYON....
2      POLYON....
3      POLYON....
4      POLYON....
5      POLYON....
6      POLYON....
....
300    POLYON....

Some (not all) of them are intersecting, some don't. What I need is a new column, with the id's of the polygons that are intersecting: Polygon with id 4 intersects polygons with id 4, 76, 298, 55.

id     geom           intersects
1      POLYON....     4, 55, 8, 1
2      POLYON....     none
3      POLYON....     99, 1, 298, 65, 88, 253, 76
4      POLYON....     4, 76, 298, 55
5      POLYON....     189
6      POLYON....     none
....   ...            ...
300    POLYON....     99

The column intersects can be of type array or of type string, with a separator like comma, semicolon, ... I think a good start is this (Making sure to avoid comparing the a polygon to itself. Also avoid comparing a pair of polygon twice)

SELECT a.id
FROM mytable a, mytable b
WHERE a.ID < b.ID
AND ST_INTERSECTS(a.geom, b.geom);

But I am not sure how to go on from here.

2 Answers 2

2

You can use array_agg():

select
a.id,
a.name,
array_to_string(array_agg(b.id),';') as intersecting_ids -- use array_agg() to get a list of the ids and array_to_string() to make it a string if you want
from myschema.mytable as a

left join -- use a left join to also get the elements from a that do not intersect with b
myschema.mytable as b
on ST_Intersects(a.geom,b.geom)

where 
a.id != b.id -- dont do a self-join

group by -- array_agg() is an aggregate function so you need to use group by
a.id,
a.name

You can do an update for example like this:

alter table myschema.mytable 
add column intersecting_ids_array integer ARRAY, 
add column intersecting_ids_string varchar
with subquery as (
select
a.id,
a.name,
array_agg(b.id) as intersecting_ids_array,
array_to_string(array_agg(b.id),';') as intersecting_ids_string
from myschema.mytable as a

left join
myschema.mytable as b
on ST_Intersects(a.geom,b.geom)

where 
a.id != b.id

group by
a.id,
a.name
)
update myschema.mytable
set intersecting_ids_array = subquery.intersecting_ids_array,
intersecting_ids_string = subquery.intersecting_ids_string
from subquery
where mytable.id = subquery.id
2
  • Oh wow, that is more complicated than I thought. Thanks a lot. Is there an easy way to combine that with an UPDATE? Let's say I have a column intersection_geoms and I want to write the results into this column... I mean, one could perform a JOIN, but is there a way to combine this with an UPDATE
    – Stophface
    Jul 8 at 15:45
  • Thansk a lot..!
    – Stophface
    Jul 8 at 16:05
2

Simpler:

UPDATE
  <table> AS a
  SET (
    intersecting_ids_array,
    intersecting_ids_count
  ) = (
    SELECT
      ARRAY_AGG(b.id),
      COUNT(b.id)
    FROM
      <table>
    WHERE
      ST_Intersects(a.geom, b.geom)
      AND
      a.id <> b.id
  )
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.