get Random small Streets (highway<=living_street)

General talk
1

Hi,

I’ve tried the last week to get small streets (this means highway<=living_street) from Germany into a DB.
I want the Software to produce a random point, in a random small street.

Nice Doomsday,
Felix

2

Hi Felix,

it seems there are two steps necessary: first, you need to filter OSM data for to get these “small streets”, second, you need to pick a node at random.

For filtering there are two ways very common: to filter OSM data by using the Overpass-API, or to download the germany.osm.pbf file from geofabrik.de and filter it with a program like Osmosis or osmfilter.

What did you try so far?
What do you mean by “producing” a point?

Markus

3

Thank you, for your Answer :slight_smile:

I’ve tried to insert the German.osm into a Mysql-DB by a self-written PHP-Script.
This worked well, but unfortunately subsequently i was’nt able to make any DB-query.

With producing a point i mean, one random coordinate somewhere in a street.
How i can do this?

4

One suggestion:

  • Use GDAL for importing OSM data into Spatialite DB. You will need GDAL development version which will become 1.10. See http://www.gdal.org/ogr/drv_osm.html. A good command line for you is
ogr2ogr -f SQLite -dsco spatialite=yes germany.sqlite germany.osm.pbf -gt 20000 --config SQLITE_SYNCHRONOUS OFF --config OSM_COMPRESS_NODES YES -progress

If you are in a big hurry, add option -lco SPATIAL_INDEX=NO. Import will be faster without spatial index and next queries will not be spatial ones.

Rest you can do with SQL and Spatialite-gui program. I made a simplified test with Finland.osm.pbf and it seems to work fine.

Get osm_id list of the candidate features

select osm_id from lines where highway='service'

Select a random osm_id from the list. I do not know best way for that but SQL in SQLite seems to have a function “random” http://www.sqlite.org/lang_corefunc.html. Perhaps getting the count of candidates and selecting one of those by utilising random function would work.

Once you have selected an osm_id the next step is to find how many vertices that feature has

select NumPoints(geometry)
from lines 
where osm_id=2311693

Result for that feature is 10. Final step is to select a random vertex from 1 to 10 and show it. This SQL selects the 5th vertex

select PointN(geometry,5)
from lines 
where osm_id=2311693

The result is POINT(22.231897 60.435018)