[Talk-us] Using TIGER to find missing road segments in OSM after license change
Martijn van Exel
m at rtijn.org
Thu Mar 29 19:32:14 BST 2012
On Thu, Mar 29, 2012 at 7:45 AM, Ian Dees <ian.dees at gmail.com> wrote:
> On Thu, Mar 29, 2012 at 8:11 AM, Josh Doe <josh at joshdoe.com> wrote:
>> On Thu, Mar 29, 2012 at 8:40 AM, Richard Weait <richard at weait.com> wrote:
>> > On Wed, Mar 28, 2012 at 8:48 AM, Ian Dees <ian.dees at gmail.com> wrote:
>> >> On Wed, Mar 28, 2012 at 5:47 AM, Josh Doe <josh at joshdoe.com> wrote:
>> > [ ... ]
>> >>> I was thinking more about using TIGER 2011
>> >>> to find roads that seem to be missing in the OSM database. My PostGIS
>> >>> skills are nil, but it seems like it should be a fairly trivial query
>> >>> to buffer the
>> >>> OSM ways and find TIGER segments which don't intersect the buffered
>> > [ ... ]
>> >> I started playing with this last night and ended up with the Chicago
>> >> metro extract from Mike and the Cook County TIGER roads data as layers
>> >> QGIS. Next up is to play with various queries to find missing roads in
>> >> I like the idea of buffer and joining as a start and will probably
>> move over
>> >> to PostGIS to do that.
>> > We used OpenJUMP and the RoadMatcher plugin in the early days of
>> > Canadian imports to generate lists of matching or missing roads.
>> I've actually just converted the conflation JOSM-plugin to use the
>> Java Conflation Suite (JCS), which RoadMatcher is based on. I don't
>> expect to have RoadMatcher-like capabilities in there for quite a
>> while, but I should soon at least be able to find which segments don't
>> have a match in OSM based on string similarity (e.g. Levenshtein) and
>> curve similarity (e.g. Hausdorff, Frechet).
> After loading Cook County TIGER road features and OSM linear features into
> PostGIS, I ran a simple query to find how well the roads matched:
> SELECT a.name, b.fullname, ST_HausdorffDistance(a.geom, b.geom) as dist
> FROM cook_tiger a, cook_osm b
> WHERE (a.geom && b.geom) AND ST_HausdorffDistance(a.geom, b.geom) <
> LIMIT 50
> This returned results that made sense (the names matched in all 50
> I removed the LIMIT clause and let it run before going to work to see how
> many of the TIGER records match existing OSM features.
> Next up is building a table of TIGER -> OSM matches and using that to find
> TIGER rows that don't have a corresponding OSM feature.
> If anyone has any ideas for speeding this up I'd love to hear it. It took
> well over a couple hours to run one county. There are a lot of counties in
> the US.
I ran a similar query on Salt Lake County and found ~7000 matches in under
7 seconds. That would scale to 3400-something counties OK.
The query I used was a little more elaborate and included a levenshtein
string distance comparison where I took name, name_1, name_2 and loc_name
a.fullname::text as tiger_name,
b.tags->'name' as osm_name,
b.tags->'name_1' as osm_name1,
b.tags->'name_2' as osm_name2,
b.tags->'loc_name' as osm_locname,
ST_HausdorffDistance(a.the_geom, b.linestring) as dist,
NULLIF(difference(a.fullname::text, b.tags->'name'), 0),
NULLIF(difference(a.fullname::text, b.tags->'name_1'), 0),
NULLIF(difference(a.fullname::text, b.tags->'name_2'), 0),
ST_HausdorffDistance(a.the_geom, b.linestring) < 0.005
(a.the_geom && b.linestring)
-- THE TIGER NAME FIELD SHOULD BE FILLED
char_length(a.fullname::text) > 0
-- AT LEAST ONE OF THE NAME FIELDS SHOULD BE FILLED
char_length(b.tags->'loc_name')) IS NOT NULL
a.roadflg = 'Y'
I calculated the Soundex difference as well as the Levenshtein difference
for informational purposes. A Levenshtein distance of 7 represented the
sweet spot between false positives and false negatives in terms of string
The main problem I found with the original query is the Haussdorff distance
parameter, which chosen so small would not catch TIGER ways and OSM ways
that are of very different length. I increased the distance threshold by a
factor 10 but still get about 38000 candidates for missing roads based on
this query (doing a select * from tigerlines where gid not in ....). But
maybe I'm overlooking something else altogether in this. More complex
queries tend to be overwhelming to me. Maybe I'm not using the right TIGER
source file? I picked the all lines one.
Have others made any progress with this?
> Talk-us mailing list
> Talk-us at openstreetmap.org
martijn van exel
1109 1st ave #2
salt lake city, ut 84103
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the Talk-us