Finding object that cover multiple polygons

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Finding object that cover multiple polygons

Clifford Snow
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
OpenStreetMap: Maps with a human touch

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Finding object that cover multiple polygons

Nicolas Ribot-3
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 <[hidden email]> 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
OpenStreetMap: Maps with a human touch

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Finding object that cover multiple polygons

Brian DeRocher
In reply to this post by Clifford Snow
I did something like this in my acps_redistricting project (on github). I needed to split census districts by elementary school districts. See make_poly.SQL line 205 - 227. (It's in 3 parts because I want to include the parts that don't intersect.)

For your case, I'd split the buildings using st_intersection. Then find the area of each part as a percentage of the whole. If the percentage is over, say 80%, then keep that part and ignore the rest. When you split make sure to keep track of which parcel it intersected with.

Brian
http://mappingdc.org

Brian
http://derocher.org/~brian
http://mappingdc.org
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Finding object that cover multiple polygons

Clifford Snow
In reply to this post by Nicolas Ribot-3
Nicolas,
Thanks - I think this should solve my problem.

Clifford

On Sat, Oct 29, 2016 at 12:41 AM, Nicolas Ribot <[hidden email]> 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 <[hidden email]> 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
OpenStreetMap: Maps with a human touch

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users



--
@osm_seattle
OpenStreetMap: Maps with a human touch

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users