So, hier die Top 20 aus DE:
osm_id | way | highway | nr | name
-----------+----------------------------------+---------------+------+-------------------------------------------------------
40130914 | http://www.osm.org/way/40130914 | unclassified | 9000 | Straße 9000
553778258 | http://www.osm.org/way/553778258 | tertiary | 6941 | K6941a
488894283 | http://www.osm.org/way/488894283 | tertiary | 6941 | K6941a
34420179 | http://www.osm.org/way/34420179 | unclassified | 6574 | Lienheimer Straße/ K 6574
440073220 | http://www.osm.org/way/440073220 | secondary | 2132 | OU Hundsdorf St 2132
396936104 | http://www.osm.org/way/396936104 | secondary | 2132 | OU Hundsdorf St 2132
396936100 | http://www.osm.org/way/396936100 | secondary | 2132 | OU Hundsdorf St 2132
377844180 | http://www.osm.org/way/377844180 | secondary | 2062 | Bad Kohlgrub St2062
342813408 | http://www.osm.org/way/342813408 | tertiary | 2040 | K2040 Selbitzer Dorfstraße
161568570 | http://www.osm.org/way/161568570 | unclassified | 2040 | Straße 2040
319187547 | http://www.osm.org/way/319187547 | tertiary | 2040 | K2040 Selbitzer Dorfstraße
4549310 | http://www.osm.org/way/4549310 | tertiary | 2040 | K2040 Selbitzer Dorfstraße
342813409 | http://www.osm.org/way/342813409 | tertiary | 2040 | K2040 Selbitzer Dorfstraße
227110843 | http://www.osm.org/way/227110843 | unclassified | 2040 | Straße 2040
581551501 | http://www.osm.org/way/581551501 | secondary | 2023 | Silheimer Straße (St 2023)
157947027 | http://www.osm.org/way/157947027 | secondary | 2023 | Silheimer Straße (St 2023)
332829425 | http://www.osm.org/way/332829425 | secondary | 2022 | L 2022 Holzweg
150262478 | http://www.osm.org/way/150262478 | secondary | 2022 | L 2022 Holzweg
227110847 | http://www.osm.org/way/227110847 | service | 2020 | 2020
227110846 | http://www.osm.org/way/227110846 | service | 2020 | 2020
38220980 | http://www.osm.org/way/38220980 | unclassified | 2000 | Straße 2000
Noch genauer zu filtern hab ich, ehrlich gesagt, keine Lust
Ist eh eine brotlose Kunst - macht aber richtig Spass und ich lern mal wieder etwas bei Pattern-Abfragen dazu.
Gruss
walter
select osm_id,wno_asOsmLink('W',osm_id) way,highway,substring(name,'[0-9]+') nr, name
from planet_osm_line
where tags->'highway' in('primary','secondary','tertiary','residential','service','road','unclassified',
'living_street','motorway','trunk','motorway_link','trunk_link')
and name ~ '[0-9]+'
and upper(substring(name,1,1)||substring(name,'[0-9]+')) != upper(name)
and upper(substring(name,1,1)||' '||substring(name,'[0-9]+')) != upper(name)
and upper(substring(name,1,2)||substring(name,'[0-9]+')) != upper(name)
and upper(substring(name,1,2)||' '||substring(name,'[0-9]+')) != upper(name)
and way && (select bbox from boundaries where id=51477)
and st_contains((select way from boundaries where id=51477),way)
order by substring(name,'[0-9]+')::int desc
limit 20;