[postgis-users] Finding object that cover multiple polygons

Nicolas Ribot nicolas.ribot at gmail.com
Sat Oct 29 00:41:59 PDT 2016


For this kind of query, you can compute the area of the intersection of the
building with each parcel, and order buildings according to the biggest
intersection with row_number():

with tmp as (
  select p.id, b.id,
    row_number() over (partition by b.id order by
            st_area(st_intersection(b.geom, p.geom)) desc) as rn
  from parcel p join building b on st_intersects(b.geom, p.geom)
) select t.*
from tmp
where rn = 1;


On 29 October 2016 at 00:38, Clifford Snow <clifford at snowandsnow.us> wrote:

> I am working with some parcel data and building outlines. I'd like to
> associate an outline to a parcel if it is located mostly in the parcel.
> Buildings completely within the parcel can easily be found with
> st_contains. How do I find buildings if they are just over the parcel
> boundary. For example, in the first screenshot [1] the outline is just over
> the boundary. I'd like to be able to run a query that would find those that
> are just over. In a second example, this building [2] sits in the middle of
> two parcels. I can safely ignore it.
> [1] https://www.dropbox.com/s/5xdx404ip4g7ycm/just_over.png?dl=0
> [2] https://www.dropbox.com/s/wah2bcg6tz6jw4n/middle.png?dl=0
> Thanks,
> Clifford
> --
> @osm_seattle
> osm_seattle.snowandsnow.us
> OpenStreetMap: Maps with a human touch
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20161029/c03aac9c/attachment.html>

More information about the postgis-users mailing list