GAMBAS BOOK 3.19.1

On the website https://creativecommons.tankerkoenig.de/ you can read the following: “Tankerkönig.de has decided to release its API in order to make the experience gained available to others in the spirit of open data.” Great thing, because after registering with a current email address, a personalized API key is sent to this email address.

For an initial test, you can also use the test key published on the above-mentioned website in a console, of which only the syntax is specified here in parameter 6:

URI:	https://creativecommons.tankerkoenig.de/json/list.php

Request-Parameter 1:	lat=52.799685385083116  '-- Geographical latitude Osterburg
Request-Parameter 2:	lng=11.757474003954462	'-- Geographical longitude Osterburg
Request-Parameter 3:	rad=5.5			'-- Radius
Request-Parameter 4:	sort=price		'-- Sorting by price
Request-Parameter 5:	type=diesel		'-- Type of fuel

Request-Parameter 6:	apikey=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx  '-- API-Key-Syntax

With this curl command:

$ curl 'https://creativecommons.tankerkoenig.de/json/list.php?lat=52.799685385083116&lng=11.757474003954462&rad=5.5&sort=price&type=diesel&apikey=test-api-key'

results in the following output - greatly shortened to a petrol station - for petrol stations near Osterburg (radius = 5.5 km, diesel fuel and sorted by price) with a fictitious price, which is only displayed for the test key:

{
    "ok": true,
    "license": "CC BY 4.0 -  https:\/\/creativecommons.tankerkoenig.de",
    "data": "MTS-K",
    "status": "ok",
    "stations": [
        {
            "id": "00000000-0000-0000-0000-000000000002",
            "name": "Aral Tankstelle",
            "brand": "ARAL",
            "street": "Schilddorf",
            "place": "Osterburg",
            "lat": 52.76813,
            "lng": 11.7548838,
            "dist": 5.5,
            "price": 1.009,
            "isOpen": true,
            "houseNumber": "10",
            "postCode": 39606
        }
        ...
    ]
}

You can see that in order to specify your (location), you need to provide its geographical coordinates in the form of the geographical longitude and latitude in degrees. So what could be more obvious than to use another web service - now from Openstreetmap.org - that provides the longitude and latitude of this address in addition to the city, street and house number.

From the output of the following curl command in another test in a console, only the two lines marked in red are of interest:

$ curl 'https://nominatim.openstreetmap.org/?city=osterburg&street=2 roggenworth&format=geojson'

{
  "type": "FeatureCollection",
  "licence": "Data © OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright",
  "features": [
    ...
      "geometry": {
        "type": "Point",
        "coordinates": [
          11,7542305128689,
          52,79821905
        ]
	...
  ]
}

Note the syntax when specifying the value for the 'street' parameter: <house number> <SPACE><street>.

When the program starts, the defined constants are read out first and the geographical coordinates for the specified address (city, house number, street) are retrieved once from web service 1 and saved (A). Then all the data for the relevant petrol stations is retrieved and saved (B). Then only the data relevant to the planned display for the petrol stations is selected and displayed in a grid view (C). Finally, steps B and C are triggered in a timer event every full minute.

The complete source code is specified and then commented:

[1] ' Gambas class file
[2]
[3] Public sJSONData As String
[4] Public hHTTPClientData As HttpClient
[5] Public hJSONCollection As JSONCollection
[6] Public sJSONGeoData As String
[7] Public hHTTPClientGeoData As HttpClient
[8] Public hJSONGeoCollection As JSONCollection
[9] Public sAPIKey As String
[10] Public sLAT As String
[11] Public sLON As String
[12]
[13] Const BASE_CITY As String = "Osterburg"      '-- Your City
[14] Const BASE_STREET As String = "Rosenstraße"  '-- Your Street
[15] Const BASE_HOUSE_NUMBER As String = "7A"     '-- Your House number
[16] Const RAD As String = "15.0"                 '-- Maximum Distance to own address as datatype Float
[17] Const SORT As String = "price"               '-- Sort criteria "price" or "dist"
[18] Const TYPE As String = "diesel"              '-- Fuel type "diesel", "e5" or "e10"
[19]
[20] '-- Key for access to the free 'Tankerkönig' fuel price API
[21] '-- For *your* own key please register here: https://creativecommons.tankerkoenig.de.
[22] '-- Testkey ! Replace by own API - key
[23] Const APIKEY As String = "00000000-0000-0000-0000-000000000002"
[24]
[25] Public Sub Form_Open()
[26]
[27]     Dim sJSONFormatted As String
[28]     Dim avElements As New Variant[]
[29]     Dim cLines As New Collection
[30]
[31]     FMain.Resizable = False
[32]     FMain.Title = ("Current Fuel Prices")
[33]     MAdditional.CheckNetwork()
[34]
[35]     Timer1.Delay = 1000
[36]     Timer1.Enabled = True
[37]
[38] '-- Get geo data
[39]     sJSONGeoData = GetAndStoreGeoData(BASE_CITY, BASE_STREET, BASE_HOUSE_NUMBER, "geojson")
[40]     hJSONGeoCollection = JSON.Decode(sJSONGeoData, True)
[41]
[42] '-- Extract geo location (latitude/longitude) out of received JSON Data Set
[43]     avElements = hJSONGeoCollection["features"]
[44]     cLines = avElements[0]
[45]     sLON = cLines["geometry"]["coordinates"][0]
[46]     sLAT = cLines["geometry"]["coordinates"][1]
[47]
[48] '-- Set grid properties
[49]     With grvGasStation
[50]        .Resizable = True
[51]        .Mode = Select.Single
[52]        .Columns.Count = 6
[53]        .Header = grvGasStation.Horizontal
[54]        .Columns[0].Width = 150
[55]        .Columns[0].Title = ("Company")
[56]        .Columns[1].Width = 150
[57]        .Columns[1].Title = ("City")
[58]        .Columns[2].Width = 180
[59]        .Columns[2].Title = ("Street")
[60]        .Columns[3].Width = 100
[61]        .Columns[3].Title = ("Radius")
[62]        .Columns[4].Width = 80
[63]        .Columns[4].Title = ("Status")
[64]        .Columns[5].Width = 80
[65]        .Columns[5].Title = ("Price")
[66]     End With
[67]
[68]     Update()
[69]
[70] End
[71]
[72] Public Sub Timer1_Timer()
[73]     If Format(Now, "ss") = "00" Then Update()
[74] End
[75]
[76] Public Function GetFuelData(argLat As String, argLon As String, argRadius As String, argSort As String, argType As String, argAPIKey As String) As String
[77]
[78]     Dim sURL, sQuery, sURI, sRawData As String
[79]
[80]     sURL = "https://creativecommons.tankerkoenig.de/json/list.php"
[81]
[82]     sQuery = "?"
[83]     sQuery &= "lat=" & argLat
[84]     sQuery &= "&" & "lng=" & argLon
[85]     sQuery &= "&" & "rad=" & argRadius
[86]     sQuery &= "&" & "sort=" & argSort
[87]     sQuery &= "&" & "type=" & argType
[88]     sQuery &= "&" & "apikey=" & argAPIKey
[89]
[90]     sURI = sURL & sQuery
[91] '-- Print sURI
[92]
[93]     With hHTTPClientData = New HttpClient
[94]       .URL = sURI
[95]       .Async = False
[96]       .Timeout = 10
[97]       .Get()
[98]       Return .Peek()
[99]     End With
[100]
[101] End
[102]
[103] Public Sub DisplayFuelData()
[104]
[105]     Dim avStations, avLines As New Variant[]
[106]     Dim sDate, sDay, sDateDMY, sTime, sType, sSubstituteForBrand As String
[107]     Dim iRow, iColumn, i As Integer
[108]     Dim picBrandLogo As Picture
[109]     Dim sMessage As String
[110]
[111]     hJSONCollection = JSON.Decode(sJSONData, True)
[112]
[113]     If hJSONCollection["ok"] = False Then
[114]        sMessage = "<br><b><font size='+1', color='DarkRed'>"
[115]        sMessage &= Subst("&1 &2&3", ("Error in the program"), Application.Name, "!")
[116]        sMessage &= "</b></font><hr>"
[117]        sMessage &= Subst("&1 &2", "Status:", hJSONCollection["status"]) & gb.NewLine
[118]        sMessage &= Subst("&1 &2&3", ("Message:"), hJSONCollection["message"], ".")
[119]        sMessage &= gb.NewLine & gb.NewLine
[120]        sMessage &= ("<b>" & ("The application is therefore terminated!") & "</b>")
[121]        Message.Info(sMessage)
[122]    '-- The (main) program is terminated.
[123]        Quit
[124]     Else
[125]        For i = 0 To hJSONCollection["stations"].Max
[126]          If hJSONCollection["stations"][i]["brand"] = Null Then
[127]             sSubstituteForBrand = Split(hJSONCollection["stations"][i]["name"], " ")[0]
[128]             avLines.Add("   " & sSubstituteForBrand)
[129]          Else
[130]             avLines.Add("   " & hJSONCollection["stations"][i]["brand"])
[131]          Endif
[132]          avLines.Add(hJSONCollection["stations"][i]["place"])
[133]          avLines.Add(hJSONCollection["stations"][i]["street"] & " " & hJSONCollection["stations"][i]["houseNumber"])
[134]          avLines.Add(Format(CFloat(hJSONCollection["stations"][i]["dist"]), "0.0 km"))
[135]          If hJSONCollection["stations"][i]["isOpen"] = True Then
[136]             avLines.Add(("Open"))
[137]          Else
[138]             avLines.Add(("Closed"))
[139]          Endif
[140]          avLines.Add(Format(CFloat(hJSONCollection["stations"][i]["price"]), "0.000 €"))
[141]          avStations.Add(avLines)
[142]          avLines = New Variant[]
[143]        Next
[144]        grvGasStation.Rows.Count = avStations.Count
[145]
[146]    '-- Format and populate grid
[147]        For iRow = 0 To avStations.Max
[148]          picBrandLogo = Picture.Load("./symbols/default_g.png")
[149]          grvGasStation[iRow, 0].Picture = picBrandLogo
[150]          grvGasStation.Rows.Height = 28
[151]          avLines = avStations[iRow]
[152]          For iColumn = 0 To avLines.Max
[153]            If iRow Mod 2 = 0 Then
[154]               grvGasStation[iRow, iColumn].Background = Color.RGB(223, 239, 255)
[155]            Endif
[156]            grvGasStation[iRow, iColumn].Text = avLines[iColumn]
[157]          Next
[158]        Next
[159]
[160]    '-- Display form caption and title
[161]        sDay = Format$(Now(), "dddd")
[162]        sDateDMY = Format$(Now, "d. mmmm yyyy")
[163]        sTime = Format$(Now(), "hh:nn") & " Uhr"
[164]        sDate = sDay & ", " & sDateDMY & " - " & sTime
[165]        sType = Upper(Left(TYPE)) & Mid(TYPE, 2)
[166]        FMain.Caption = Subst("&1 &2&3&4", ("Current Fuel Prices"), "(", sType, ")")
[167]        txlTitle.Text = BASE_CITY & " - " & sDate
[168]     Endif
[169]
[170] End
[171]
[172] Public Function GetAndStoreGeoData(argCity As String, argStreet As String, argHouseNumber As String, argFormat As String) As String
[173]
[174]     Dim sURL, sQuery, sURI, sRawData As String
[175]
[176]     sURL = "https://nominatim.openstreetmap.org/"
[177]
[178]     If argHouseNumber Then argStreet = argHouseNumber & " " & argStreet
[179]
[180]     sQuery = "?"
[181]     sQuery &= "city=" & Lower(argCity)
[182]     sQuery &= "&" & "street=" & Lower(argStreet)
[183]     sQuery &= "&" & "format=" & Lower(argFormat)
[184]
[185]     sURI = sURL & sQuery
[186]
[187]     With hHTTPClientData = New HttpClient
[188]       .URL = sURI
[189]       .Async = False
[190]       .Timeout = 10
[191]       .Get()
[192]       Return .Peek()
[193]     End With
[194]
[195] End
[196]
[197] Private Sub Update()
[198]     sJSONData = GetFuelData(sLAT, sLON, RAD, SORT, TYPE, APIKEY)
[199]     DisplayFuelData()
[200] End

With a real API key, the following diesel prices were obtained for filling stations in the town of Osterburg within a radius of 15 km on the specified date:

Figure 24.2.4.2.1: Display of selected data of the relevant petrol stations

Comment:

• In lines 13 to 23, the required parameters are defined as constants.
• Line 33 checks whether there is (necessary) access to the Internet - otherwise the program is terminated.
• In lines 39 to 46, the geodata for the specified address is retrieved once (function 'GetAndStoreGeoData(…)' ) and stored in the variable sJSONGeoData.
• In lines 45 and 46, the length and width data are edited and stored in the variables sLON and sLAT.
• Selected properties of the GridView display control are then defined in lines 49 to 65.
• In line 68, the display is updated for the first time when the program is started.
• In lines 35 and 36, the timer Timer1 is switched on and the cycle time is set to 1 second.
• In the timer event Timer1_Timer(), line 73 defines that the display (TextLabel and Gridview) is updated every full minute using the Update() procedure, which is described in lines 197 to 200.
• The Update() procedure uses the GetFuelData(sLAT, sLON, RAD, SORT, TYPE, APIKEY) function and the DisplayFuelData() procedure.
• You should not omit the query in line 113, as errors can already be intercepted and handled at this point:

Figure 24.2.4.2.2: Example: Display API key error