[Imports] Ottawa, Canada Tree Import

[Rory McCann]

Hi,

On 06/07/17 17:59, Denis Carriere wrote:
> All the priority concerns mentioned above have been resolved in the most 
> recent wiki updates.
> 
> https://wiki.openstreetmap.org/wiki/Canada:Ontario:Ottawa/Import/Trees

Just a quite one, you say:

 > The import data is of high quality accuracy which makes is near
 > impossible to calculate if a tree is exactly on top of a road or in
 > proximity by 3-5 feet away from the centerline which might be the case
 > in some residential neighborhoods.

It's not that hard with PostgreSQL. This SQL query will create a table 
`road_dist` with each tree and the top 20 closest roads, along with the 
distance

     create table road_dist as select trees.wkb_geometry, h.*, 
st_distance(trees.wkb_geometry::geography, h.way::geography) as dist 
from trees cross join lateral (select * from highways order by 
trees.wkb_geometry <-> highways.way limit 20) as h;

You can then limit it. e.g. this will filter out all pairs with a 
distance more than 2 metres:

     delete from road_dist where dist > 2;

Theses results are probably OK, so delete them:

     delete from road_dist where highway IN ('footway', 'path', 'cycleway');

And that gives you ~800 results. For a dataset of ~150k that's not that bad.

I uploaded the 2m distance here:

    https://gist.github.com/anonymous/c570dd0b94e1935da451cf87284eeea6

Hope that helps