0

I have two set of polygons

select place_id,geom from table1;
select place_id,geom from table2;

I want to see which all polygons meet between these two tables. How do I go about this problem?

  • I did not downvote the question. But it's not fair to downvote without leaving a comment. I guess these people downvoted because the question lacks own research. This site is not meant to bridge your gap of knowledge that you could/should learn with beginners tutorials but to assist in very specific problems. – pLumo Nov 20 '17 at 10:12
3

PostGIS has several functions to see spatial relationships between entities. If you just want to know if your polygons spatially intersect (i.e. they share any portion of space without any further specifications), you can try :

SELECT t1.place_id, t2.place_id as place_idt2
FROM table1 t1
INNER JOIN table2 t2
ON ST_Intersects(t1.geom, t2.geom)

That will return table 1 polygons with intersecting polygons from table 2 for each feature. But maybe you want a full list with all distinct polygon from the two tables which have at least one intersection with an other feature :

WITH unioned AS (
    SELECT place_id, geom
    FROM table1
    UNION
    SELECT place_id, geom
    FROM table2
    )
SELECT u1.place_id, count(*)
FROM unioned u1
JOIN unioned u2
    ON ST_Intersects(u1.geom, u2.geom) AND u1.id != u2.id
GROUP BY u1.id

This one will return each polygon id from the two tables with the number of time it intersects.

Keep in mind that the ST_Intersects function is not the only one available and you should go visit http://postgis.net/docs/manual-2.3/reference.html to see which spatial relationship meets your needs.

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.