Overpass API

[mole999]

Anyone with experience using APIs or this one specifically. I have 92K locations stored with Easting / Northing. The API suggests that it has Road Speed for a large proportion of the road network (UK) and I'd like to use a formula or script to import this data against known location data. I can convert my E/N to other references, just need pointing in the right direction. I'd rather use the online resource where possible as some roads vary their speed in sections and it is not possible to guess the change points. I'm referencing against a known vehicle which exceeded speed limits, but I need to get the offset


Thoughts welcome

 

[Rijnsent]

Hi mole,
I've used the Overpass API casually to extract bike routes which I would afterwards use in my Maps.Me app to navigate that route. I'm trying to understand what your desired input & output is: you basically have 92k locations (lat&lon) and want max speed of nearby roads?
On the API department: I'm connecting to APIs from excel/VBA in my project to connect Excel to cryptocurrency exchanges: https://github.com/krijnsent/crypto_vba , there are 2 main components that are really practical if you want to build something from VBA: a JSON converter and a connector, check out this project: https://github.com/VBA-tools -> VBA-Web & VBA-JSON.
Cheers,
Koen

 

[mole999]

input is currently easting / northing which I can get converted to Lat/Long, and then I need the road speed associated with the location as mph. ultimately I would want a UDF that would do the work, once its, in then cut / paste to set the value as it won't change. My difficulty has been road change their speed by location so slightly unfair if the limit is up, compared to the vehicle speed data

 

[Rijnsent]

Hi Mole,
I did some fiddling around, starting from the OSM map. I dug into the calls the website makes to the overpass API when you right click somewhere on the map and click "query features". That leads to e.g:

https://www.openstreetmap.org/query?lat=52.07527&lon=5.13998

At the left hand side you see a bar with the various road items. As you can see, the query returns a bunch of items, ways, relations etc. You could e.g. filter this result like this: option 1 is a range of 10 meters around a point, option 2 is a box of coordinates. Both work as GET commands, so you could put them in your browser.

https://overpass-api.de/api/interpreter?data=[out:json];way(around:10,52.07530,5.13997);out;
https://overpass-api.de/api/interpreter?data=[out:json];way(52.07526,5.13997,52.07528,5.13999);out;

The result is e.g.:

[
	{
		"type": "way",
		"id": 34692836,
		"nodes": [
			45124056,
			2264748017,
			2264748031,
			700828150,
			269575336,
			45124157,
			45124554,
			4036670960,
			2754666864,
			45127412,
			45129090,
			45129509,
			2754666888,
			45131300,
			45131728,
			2754666896,
			45132655,
			45132725,
			45133116,
			387593873,
			5302257292,
			1763573603,
			5302257293,
			6788534098
		],
		"tags": {
			"bicycle": "use_sidepath",
			"foot": "no",
			"highway": "tertiary",
			"lit": "yes",
			"maxspeed": "50",
			"name": "Koningsweg"
		}
	},
	{
		"type": "way",
		"id": 34885398,
		"nodes": [
			269575326,
			440904727,
			592049731
		],
		"tags": {
			"highway": "cycleway",
			"lit": "yes",
			"lit:indirect": "yes",
			"name": "Koningsweg",
			"oneway": "no",
			"surface": "asphalt",
			"width": "2.00"
		}
	},
	{
		"type": "way",
		"id": 123695267,
		"nodes": [
			592049728,
			1246713213,
			45134748
		],
		"tags": {
			"highway": "cycleway",
			"lit": "yes",
			"lit:indirect": "yes",
			"name": "Koningsweg",
			"oneway": "no",
			"surface": "paved",
			"width": "2.00"
		}
	},
	{
		"type": "way",
		"id": 142607016,
		"nodes": [
			45134697,
			1560677482,
			45133155
		],
		"tags": {
			"agricultural": "no",
			"bicycle": "no",
			"bridge": "yes",
			"foot": "no",
			"highway": "primary",
			"horse": "no",
			"lanes": "2",
			"layer": "1",
			"maxspeed": "70",
			"motorroad": "yes",
			"name": "Waterlinieweg",
			"oneway": "yes",
			"surface": "asphalt"
		}
	},
	{
		"type": "way",
		"id": 142607020,
		"nodes": [
			45133178,
			45134656
		],
		"tags": {
			"agricultural": "no",
			"bicycle": "no",
			"bridge": "yes",
			"foot": "no",
			"highway": "primary",
			"horse": "no",
			"lanes": "2",
			"layer": "1",
			"maxspeed": "70",
			"motorroad": "yes",
			"name": "Waterlinieweg",
			"oneway": "yes",
			"surface": "asphalt"
		}
	},
	{
		"type": "way",
		"id": 723808814,
		"nodes": [
			6788534098,
			45134411,
			45134748
		],
		"tags": {
			"bicycle": "use_sidepath",
			"foot": "no",
			"highway": "tertiary",
			"lit": "yes",
			"maxheight": "default",
			"maxspeed": "50",
			"name": "Koningsweg"
		}
	}
]

As I took a bridge as an example you see there are various roads in that area, so you'll need some logic to loop through that JSON. Again, for that bit I'd use VBA-JSON.
Hope that helps,
Koen

 

[mole999]

Thak you very much, I have currently zero knowledge in this area so lots of pitfalls for me to navigate, though knowing iy is possible is very supportive

 

[Rijnsent]

One addition, this narrows the results: it limits the results to ways with a maxspeed tag and only gives back the tags.

https://overpass-api.de/api/interpreter?data=[out:json];way[maxspeed](around:10,52.07530,5.13997);out tags;

Cheers,
Koen

 

[Norie]

You could probably use XMlHttp for this.

Something like this, which is a bit rough and requires a reference even though I tried using late binding.

Sub test()
Dim http As Object
Dim xmlDoc As MSXML2.DOMDocument60
Dim strURL As String
Dim node As Object

' requires reference to Microsoft XML 6.0

    strURL = "https://overpass-api.de/api/interpreter?data=[out:xml];way(52.07526,5.13997,52.07528,5.13999);out;"
        
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    http.Open "GET", strURL, False
    
    http.send
    Set xmlDoc = New MSXML2.DOMDocument60
    
    
    xmlDoc.LoadXML http.responseText
    
    
    For Each node In xmlDoc.SelectNodes("//osm//way//tag[@k='maxspeed']")
         
        Debug.Print node.XML
    
    Next node

End Sub


P.S. This only prints out the XML for the 'maxspeed' node but there should be ways to get the actual value of the attribute - I've tried a bunch of things (e.g. getAttribute, the Attributes collection etc.) without luck.:)