[postgis-users] Finding object that cover multiple polygons

[Nicolas Ribot]

Hello,

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;

Nicolas

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>