[GIS] Select records that intersect more than 3 polygons

polygonpostgissql

I am analyzing parcels in land use zones.

Some parcels overlap 3 or more zones = I am interested in selecting only those records (i.e. the purple-colored parcels)

At this point I have a query that joins zones in zoning to parcels in parcels when an intersection occurs

SELECT t.*, m.*
FROM parcels AS t
LEFT JOIN zoning AS m
ON ST_Intersects(t.geom, m.geom)

However, ST_Intersection() only returns true or false . Is there a way to "count" the intersections and specify 3 or more?

Best Answer

You should use aggregate function count and GROUP BY with HAVING. I supposed there is a primary key column (id) in parcels table.

SELECT t.id, count(*) 
  FROM parcels AS t INNER JOIN zoning AS m ON ST_Intersects(t.geom, m.geom)
  GROUP BY t.id HAVING count(*) > 2

Related Solutions

[GIS] Postgis: Is there a way to do an intersection query based on lines while taking into account the dateline

This can be done but it’s not pretty.

The query needs to be broken into 2 parts. One for those lines that do not cross +- 180, another for those that do. Note that you also need to exclude those segments of the coastline that also cross the 180 since they will exhibit the same behavior you see with p1/p2. Also since you are dealing with points and lines you can do everything with geometry objects.

The first part of the query would look similar to what you have now but with the appropriate where clause to exclude all entities (including coastline) that cross 180. Something like this:

SELECT st_intersection(st_makeline(p1.latlong, p2.latlong), the_geom)
FROM worldcoastline, points p1, points p2 
  WHERE p1.id = 1 AND p2.id = 2 and st_length(st_makeline(p1.latlong, p2.latlong) <= 180
AND st_length(the_geom) <= 180

The second part would look like this:

SELECT
CASE 
WHEN st_X(x) > 180 THEN st_translate(x, -360, 0)
            ELSE x
       END
 from
(SELECT st_intersection(st_shift_longitude(st_makeline(p1.latlong, p2.latlong))), st_shift_longitude(the_geom)) as x
FROM worldcoastline, points p1, points p2 
WHERE p1.id = 1 AND p2.id = 2 and st_length(st_makeline(p1.latlong, p2.latlong) > 180
    AND st_length(st_shift_longitude(the_geom)) <= 180)) AS foo`

The second query selects those p1/p2 pairs that cross 180, shifts them by 180 so you know have 0-360 coordinate system. The coastline is also shifted 180. The where clause also excludes those parts of the coastline that cross 0/360 as they now exhibit the wrap around the globe behavior. The CASE clause corrects the longitude of the shifted coordinates back to the standard coordinate system.

UNION the 2 selects and you should have what you want.

Having said that, I generally believe it is asking for trouble doing queries like this in PostGIS. You might be better off re-structuring your data so that simple queries would work. Also doing anything over long distances with geometries usually leads to unpleasant surprises. One thing you could do is turn your p1/p2 pairs into multi lines that do not cross 180. You certainly should do that to your coastline data. Not knowing your data or application it’s hard to have concrete suggestions, but in my experience queries like this are indicative of a data design shortfall.

[GIS] Merging polygons that intersect by more than specified amount using PostGIS

Try running St_Union and then explode the multipart polygons with St_Dump. The polygons that only touched at a corner should not be combined.

Best Answer

Related Solutions

[GIS] Postgis: Is there a way to do an intersection query based on lines while taking into account the dateline

[GIS] Merging polygons that intersect by more than specified amount using PostGIS

Related Question