[postgis-users] Finding object that cover multiple polygons

[Clifford Snow]

Nicolas,
Thanks - I think this should solve my problem.

Clifford

On Sat, Oct 29, 2016 at 12:41 AM, Nicolas Ribot <nicolas.ribot at gmail.com>
wrote:

> 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
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>



-- 
@osm_seattle
osm_seattle.snowandsnow.us
OpenStreetMap: Maps with a human touch
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20161029/8dc85476/attachment.html>