OpenStreetMaps - playing around

[stanl]

I like this URL... The code below should return Json with specifics for the PNC sports arena in Raleigh, home of the Hurricanes hockey team.

Code (WINBATCH) Select Expand



;Winbatch 2020A - json return from openstreetmap
address= "PNC Arena, Raleigh NC"
cURL = "https://nominatim.openstreetmap.org/search?q=%address%&format=geojson&addressdetails=1&extratags=1"
request = Createobject("WinHttp.WinHttpRequest.5.1")
request.Open("GET", cUrl, @False )
request.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded")
request.SetRequestHeader("Accept", "application/json")
request.Send()
jdata = request.ResponseText
ObjectClose(request)


Message("Return",jdata)



If you parse the Json data you can concatenate a further url:


https://nominatim.openstreetmap.org/ui/details.html?osmtype=W&osmid=47732090&class=leisure


which will provide a table + a map [map is .png]. I'm thinking maybe add code for an Excel web request, but figured I'd ask if anyone has a better mousetrap.

[JTaylor]

What is the end goal.  To show that web page with the map and data?

Jim

[stanl]

What is the end goal.  To show that web page with the map and data?
Jim

I thought Excel could do this easily with a web query... but that failed [for me] miserably. But the end goal would be an Excel table with map..

[JTaylor]

Not sure I have any inspired suggestions.  Just the usual suspects.    Maybe WinHttp for the initial query.  Parse that, probably with my extender.   Form the new query and submit that and parse those results.  Add it to your spreadsheet.   Not sure on the picture part though.   If it is a png guessing that shouldn't be difficult either.

This obviously doesn't tell you anything new.   Just didn't want to ghost you after replying.

Jim

[stanl]

I was surprised. I have Office 2029 but Excel get data from Web failed with parsed url.


But, you can see from the json, there is an icon element that references a .png....

Code Select Expand



{"type":"FeatureCollection","licence":"Data © OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright","features":
[{"type":"Feature","properties":
{"place_id":102626499,
"osm_type":"way",
"osm_id":47732090,
"display_name":"PNC Arena, 1400, Edwards Mill Road, Westover, Raleigh, Wake County, North Carolina, 27607, United States",
"place_rank":30,
"category":"leisure",
"type":"sports_centre",
"importance":0.856567106841251,
"icon":"https://nominatim.openstreetmap.org/ui/mapicons//sport_leisure_centre.p.20.png",
"address":{"leisure":"PNC Arena","house_number":"1400","road":"Edwards Mill Road","suburb":"Westover","city":"Raleigh","county":"Wake County","state":"North Carolina","postcode":"27607","country":"United States","country_code":"us"},"extratags":{"phone":"+1-919-861-2300","sport":"basketball;hockey","website":"http://www.rbccenter.com/","wikidata":"Q39177","wikipedia":"en:PNC Arena","building:use":"commercial"}},"bbox":[-78.722984,35.8025265,-78.7209863,35.8042806],"geometry":{"type":"Point","coordinates":[-78.7219165859909,35.80339805]}}]}

[JTaylor]

Yeah...but it doesn't appear to be the map.

jim

[stanl]

Yeah...but it doesn't appear to be the map.

jim

Of course, why I'm a little ticked that Excel can't handle the parsed URL.

[stanl]

Just got worse...


Jim;


added you DOM Extender and just wrapped the url Json around exisiting code I had posted which used Extender to place data in Excel. Line 62 in code is a message box which iterates the steps to create the output. I know you can't get Office 365 to work with these scripts but it won't get that far. Error is attached.

Code (WINBATCH) Select Expand



;Winbatch 2020A - json return from openstreetmap
IntControl(73,1,0,0,0)
Gosub udfs
AddExtender("wbdomdata.dll")
address= "PNC Arena, Raleigh NC"
cURL = "https://nominatim.openstreetmap.org/search?q=%address%&format=geojson&addressdetails=1&extratags=1"
BoxOpen("Parsing:":cUrl,"Please Wait...")
request = Createobject("WinHttp.WinHttpRequest.5.1")
request.Open("GET", cUrl, @False )
request.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded")
request.SetRequestHeader("Accept", "application/json")
request.Send()
jdata = request.ResponseText
ObjectClose(request)
dmParse(jdata,@dmJSON)
decimals(18)
jdata = "Element":@TAB:"Fld":@TAB:"Data":@CRLF
path = dmjGetTreePath()
cnt = ItemCount(path,@LF)
For i = 1 To cnt
   element = ItemExtract(i,path,@LF)
   BoxText(element)
   tree  = MapCreate(dmjGetElementMap(element),@TAB,@CR)
   subcnt = ArrInfo(tree,1)
   If subcnt==0
      fld = ItemExtract(ItemCount(element,"/"),element,"/")
      dodata()       
   Endif
Next


BoxText("Moving Data To Excel")
toXLSX()




Exit


:WBERRORHANDLER
geterror()
Terminate(@TRUE,"Error Encountered",errmsg)
Exit


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine


#DefineSubRoutine dodata()
IntControl(73,1,0,0,0)
If dmjIsData(element)
   data = dmjGetValue(element)
   If Strlen(StrTrim(data))>0
     data = StrReplace(data,@TAB," ")
     data = StrReplace(data,@CRLF," ")         
     data = StrReplace(data,@LF," ")
     jdata = jdata:element:@TAB:fld:@TAB:data:@CRLF
     Message("",jdata) ;checks how output is built
   Endif
Endif
Return(1)
:WBERRORHANDLER
IntControl(73,1,0,0,0)
Return(1)
#EndSubRoutine




#DefineSubRoutine toXLSX()
IntControl(73,1,0,0,0)
ClipPut(jdata)
oXL = CreateObject("Excel.Application")
oXL.Visible          = @TRUE  ; change this to @FALSE to run hidden
oXL.ScreenUpdating   = @TRUE  ; if running hidden, change this to @FALSE
oXL.UserControl      = @TRUE
oXL.DisplayAlerts    = @FALSE
oXL.WorkBooks.Add()
BoxShut()
oWS = oXL.ActiveWorkBook.Worksheets(1)
oWS.Activate()
oWS.Name = TabName
oWS.Cells(1,1).Select()
oWS.Paste()
oWS.UsedRange.Select()
oXL.Selection.Font.Name = 'Tahoma'
oXL.Selection.Font.Size = 9
oXL.Selection.Font.Bold = @True
oWS.UsedRange.Columns.Autofit()
oWS.ListObjects.Add(:1,oWS.UsedRange, , 1).Name ="Table1"
oWS.Range("Table1[#All]").Select()
oWS.ListObjects("Table1").TableStyle = "TableStyleLight15"
oXL.ActiveWindow.DisplayGridlines = @False


;to create hyperlinks
nRows = oWS.UsedRange.Rows.Count
oWS.Range("C1").Select
oXL.Selection.Interior.ColorIndex = 6
r=2
c=3
While r<nRows+1
   oWS.Cells(r,c).Select()
   v=oWS.Cells(r,c).value
   If StrIndexNC(v,"http",0,@FWDSCAN)
      oWS.Hyperlinks.Add(::Anchor=oXL.Selection,Address="%v%",SubAddress="", TextToDisplay="%v%")
   Endif
   r+=1
Endwhile


oWS.Cells(1,1).Select()
oWS=0
oXL=0
Pause("Data Loaded Into Excel","Save or Close Workbook")
Return(1)
;======================================================================================================
:WBERRORHANDLER
Terminate(@TRUE,"Error Encountered",err())
;======================================================================================================
:CANCEL
Display(2,"Thank you","Goodbye...")
Exit
#EndSubRoutine


Return




[JTaylor]

Thanks for letting me know.  Will get it sorted and let you know something shortly.

Jim

[JTaylor]

You ever have one of those moments when you are working on one thing and you have a stray thought saying you should check on this other thing too but, for some reason, you don't?   This was one of those issues.  Thanks for finding the problem.

Also, you've caught me in the middle of some changes but I think I have all the JSON stuff solid.   The new Map stuff created a bit of a quandary for me.   I didn't want to create a separate set of functions but I didn't want to break stuff for people either so had to decide which it would be.   Not sure what would be the best but what I did was...

If you are using Winbatch version 2021B it will, by default, return a Map for related functions.   You MUST set a parameter to get the Map-compatible string instead. This is the only option if using a version prior to 2021B.  I realize this will be a breaking change for what you are doing.  Hopefully this doesn't create anything more than an inconvenience for folks but it seemed like the most forward-thinking approach.   If upgrade costs were prohibitive I might of gone a different route.   

   http://www.jtdata.com/anonymous/DomData_Test.zip

As far as I know all functions are good in this version.  I just haven't finished changing over all the functions for the Map stuff.   JSON is done though.

Thanks again and sorry for the hassle.

Jim

[stanl]

Thanks for letting me know.  Will get it sorted and let you know something shortly.

Jim

If it helps, I think it is failing parsing the osm_id.


[EDIT]:  you were adding your response while I was typing this. I haven't upgraded yet, but plan to. So by using your latest version with previous code I will get the error, otherwise latest version will work with URL and 2021B

[JTaylor]

The problem is fixed either way but you will need to adjust the code to account for the new Map stuff.   If using pre-2021B you will need to add @FALSE as the last parameter to any of the extender functions that return a Map.  If using 2021B you can remove the MapCreate() function as it will be returning a Map rather than a string.

Jim

[stanl]

Downloaded earlier version of Extender - script worked fine then.

[stanl]

The problem is fixed either way but you will need to adjust the code to account for the new Map stuff.   If using pre-2021B you will need to add @FALSE as the last parameter to any of the extender functions that return a Map.  If using 2021B you can remove the MapCreate() function as it will be returning a Map rather than a string.

Jim

So, to clear things up:


Pre2021B
tree  = MapCreate(dmjGetElementMap(element,@FALSE),@TAB,@CR)


2021B
tree  = dmjGetElementMap(element)

[JTaylor]

The issue related to the data and the changes I made to handle dates rather than a bug you would encounter every time.   You would only encounter it with a field below a certain length.

jim

Downloaded earlier version of Extender - script worked fine then.

[JTaylor]

Correct.   Not to confuse things but you could still get the string using @FALSE with 2021B and later but obviously no point on new stuff.   I did that so as to not break any scripts when someone upgrades later.   By forcing the @FALSE now for pre-2021B it protects things later but keeps people from having to mess with the parameter in the future when using 2021B+.  I only took this liberty since I am still claiming it is a Beta and still early in the release cycle.   Had it been more established I probably would have created different functions.   

Jim

The problem is fixed either way but you will need to adjust the code to account for the new Map stuff.   If using pre-2021B you will need to add @FALSE as the last parameter to any of the extender functions that return a Map.  If using 2021B you can remove the MapCreate() function as it will be returning a Map rather than a string.

Jim

So, to clear things up:


Pre2021B
tree  = MapCreate(dmjGetElementMap(element,@FALSE),@TAB,@CR)


2021B
tree  = dmjGetElementMap(element)