Searching 2D arrays

[leuce]

Hello everyone

I've just recently started experimenting with 2D arrays, and I must confess that I still don't quite understand how it all works.

I need a script to index a tab-delimited file by row and column, and then find out in which row is a certain value which is in a certain column, and then find the value in another column on that same row.  But first, I need to create the array, and I think I don't know how.

The tab-delimited file in this case is a list of postal codes and their lattitudes/longitudes.  The user enters a postal code, and the script calculates the bird flight distance from my home town (Apeldoorn) to that location.  Column 2 of the text file is the postal code, column 3 is the place name, column 10 is the lattitude and column 11 is the longitude.  The text file is located here: http://download.geonames.org/export/zip/NL.zip

AutoIt says "Array variable has incorrect number of subscripts or subscript dimension range exceeded."

My script is here:

expandcollapse popup

#include <File.au3>

#cs

Calculate distance from Apeldoorn, based on postal code

Apeldoorn's postal code is 7311 (train station), i.e.
$lat1raw = 52.21 
$lon1raw = 5.9694

1. User runs script, then selects target postal code within 5 seconds.
2. Script goes ^c, then uses that value as default value for InputBox.
3. Script asks user for postal code in InboxBox.
4. Script reads geopostalcodes.txt into 2D array.
5. Script finds LAT and LON and placename of entered postal code.
6. Script calculates distance from Apeldoorn, and outputs result in MsgBox.

List of postal codes: http://download.geonames.org/export/zip/
Distance calculator: http://www.movable-type.co.uk/scripts/latlong.html
ATan2 function: https://www.autoitscript.com/forum/topic/164765-atan2/

#ce

ClipPut ("asdfasdfasdf")

Sleep (5000)

Send ("^c")
Sleep (100)
$gotclip = ClipGet ()
Sleep (400)

If $gotclip = "asdfasdfasdf" Then ; then didn't copy
$gotclip = ""
EndIf

$target = InputBox ("Enter target postal code", "What is the target postal code?  Numbers only, please.", $gotclip)

$target = StringRegExpReplace ($target, "\D", "") ; to remove any letters from postal code

$fileopen = FileOpen ("geopostcodes.txt", 128)
$fileread = FileRead ($fileopen)

$foo = @ScriptDir & "\" & "geopostcodes.txt"
$linecount = _FileCountLines ($foo)

Local $aArray[$linecount - 1][12 - 1]

; At this point, AutoIt Error: Array variable has incorrect number of subscripts or subscript dimension range exceeded.

; The following bit is adapted from AutoIt user manual entry "_ArraySearch".

$split1 = StringSplit ($fileread, @CRLF, 1) ; splits file by lines
For $i = 1 to $split1[0]
$split2 = StringSplit ($split1[$i], @TAB, 1) ; splits line by tabs
For $j = 1 to 12 ; because there are 12 columns in a line
$aArray[$i][$j] = $split2[$j]
Next
Next

$iIndex = _ArraySearch($aArray, $target, 0, 0, 0, 0, 1, 2)

MsgBox(0, "", $target & " is on row " & $iIndex, 0) ; for testing purposes only

$lat2raw = $aArray[$iIndex][10] ; lat value is in column 10
$lon2raw = $aArray[$iIndex][11] ; lon value is in column 11

$placename = $aArray[$iIndex][3] ; place name is in column 3

; Apeldoorn, 7311 (train station)

$lat1raw = 52.21 
$lon1raw = 5.9694

$lat1radian = $lat1raw * 0.017453278 ; convert to radians
$lon1radian = $lon1raw * 0.017453278

$lat2radian = $lat2raw * 0.017453278
$lon2radian = $lon2raw * 0.017453278

$earthradius = 6371000 ; meters

; now do some fancy math

$diflat = ($lat2radian - $lat1radian)
$diflon = ($lon2radian - $lon1radian)

$a = Sin ($diflat / 2) * Sin ($diflat / 2) + Cos ($lat1radian) * Cos ($lat2radian) * Sin ($diflon / 2) * Sin ($diflon / 2)

$b = _ATan2 (Sqrt ($a), Sqrt (1 - $a))

$c = 2 * $b

$d = $earthradius * $c ; in meters

$e = Round ($d / 1000, 0)

MsgBox (0, "", "Distance is " & $e & "km", 0)

Func _ATan2($fY, $fX)
; https://www.autoitscript.com/forum/topic/164765-atan2/
Local $aResult = DllCall("msvcrt.dll", "double:cdecl", "atan2", "double", $fY, "double", $fX)
If @error Then Return SetError(@error, @extended, False)
Return $aResult[0]
EndFunc

What am I doing wrong? :-)

Thanks

Samuel

 

[AutoBert]

Seems not every line has 12 columns, use _FileReadToArray which returns a 2D-Array from file:

#include <Array.au3>
#include <File.au3>

Global $aArray[1]

_FileReadToArray(@ScriptDir&'\NL.txt',$aArray,$FRTA_COUNT,@TAB)
_ArrayDisplay($aArray)

 

But after using frta and correct to

$iIndex = _ArraySearch($aArray, $target, 0, 0, 0, 0, 1, 1)

i get distance 5079 km distance to Assen and i can't believe that NL is so a wide country. Or did you make sightseeing? Apelddoorn results distance 5145 km which shoud result 0. I don't know is distance to Assen 5145-5079 km = 66 km?

 

Edited April 28, 2016 by AutoBert

[Malkey]

For the sake of curiosity -
the array method took 159ms, and,
the regular expression took 29ms.

The Array method.

;ConsoleWrite(FileRead("NL.txt") & @LF)
#include <Array.au3>
#include <File.au3>

Local $hTimer = TimerInit()
Local $Struct[12] = ["country code", "postal code", "place name", "admin name1", "admin code1", "admin name2", "admin code2", "admin name3", "admin code3", "latitude     ", "longitude  ", "accuracy    "]
Global $aArray, $sRet
Local $sSearch = 3033

_FileReadToArray(@ScriptDir & '\NL.txt', $aArray, $FRTA_COUNT, @TAB)
$i = _ArraySearch($aArray, $sSearch, 0, 0, 0, 0, 1, 1)

For $j = 0 To UBound($aArray, 2) - 1
    $sRet &= $Struct[$j] & ":" & @TAB & $aArray[$i][$j] & @CRLF
Next

ConsoleWrite(TimerDiff($hTimer) & @LF)
MsgBox(0, "Postcode: " & $sSearch, $sRet)

;_ArrayDisplay($aArray)

 

The Regular Expression method.

;ConsoleWrite(FileRead("NL.txt") & @LF)
#include <Array.au3>

Local $hTimer = TimerInit()
Local $iPostCode = 3033 ; 8263 ; 1336 ; 9401 ;

; Find row with specific postcode
$a = StringRegExp(FileRead("NL.txt"), _
        '(?i)([A-Z]+)' & @TAB & _           ; country code      : iso country code, 2 characters ("NL" - Netherlands)
        '+(' & $iPostCode & ')' & @TAB & _  ; Postal code       : varchar(20)ostcode
        '+([A-Z (),\-]*)' & @TAB & _        ; place name        : varchar(180)
        '+([A-Z \-]*)' & @TAB & _           ; admin name1       : 1. order subdivision (state) varchar(100)
        '+(\d*)' & @TAB & _                 ; admin code1       : 1. order subdivision (state) varchar(20)
        '+([A-Z \-]*)' & @TAB & _           ; admin name2       : 2. order subdivision (state) varchar(100)
        '+(\d*)' & @TAB & _                 ; admin code2       : 2. order subdivision (state) varchar(20)
        '+([A-Z \-]*)' & @TAB & _           ; admin name3       : 3. order subdivision (state) varchar(100)
        '+(\d*)' & @TAB & _                 ; admin code3       : 3. order subdivision (state) varchar(20)
        '+([\d\.]+)' & @TAB & _             ; latitude          : estimated latitude (wgs84)
        '+([\d\.]+)' & @TAB & _             ; longitude         : estimated longitude (wgs84)
        '+(\d+)' _                          ; accuracy          : accuracy of lat/lng from 1=estimated to 6=centroid
        , 3)

ConsoleWrite(TimerDiff($hTimer) & @LF)
_ArrayDisplay($a)

 

[leuce]

[sorry, I have no idea how the new AutoIt forums' quote function works]

AutoBert said:
> Seems not every line has 12 columns,

That's odd.  Even if I remove the line break at the end of the file, it would seem to me like every line has 12 columns.

AutoBert said:
> I get distance 5079 km distance to Assen and i can't believe
> that NL is so a wide country.

Yes, well, I tested the math only briefly and it did seem to work (maybe it was a fluke).  Google will solve that problem for me, eventually, if I can figure out the correct formulas for doing this calculation.

I'll first test if the ATan2 function actually works correctly...

Malkey wrote:
> For the sake of curiosity -
> the array method took 159ms, and,
> the regular expression took 29ms.

Thanks, I'll look into this.  Do you happen to know what the correct syntax is for the StringSplit method that I tried to use?

 

[Jfish]

The file does appear to have 12 columns - some are not populated with data in some of the rows.  I would propose avoiding the confusion of manually creating the array and having to worry about nested for loops by using _FileReadToArray as in the sample below.  It does it automatically for you.   While I had the hood open I also added a small GUI and made the calculation into a function that can be called every time you press the button.   I did not try to optimize it to save the 130 ms ... 

expandcollapse popup

#include <File.au3>
#include <Array.au3>
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>


; use a GUI inn lieu of clip functions to avoi time based input and allow for subesquent look ups
#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Bird distance v1.0", 535, 224, 192, 124)
$Input1 = GUICtrlCreateInput("", 136, 88, 265, 21)
$Button1 = GUICtrlCreateButton("Calculate", 176, 136, 185, 25)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###


#cs

Calculate distance from Apeldoorn, based on postal code

Apeldoorn's postal code is 7311 (train station), i.e.
$lat1raw = 52.21
$lon1raw = 5.9694

1. User runs script, then selects target postal code within 5 seconds.
2. Script goes ^c, then uses that value as default value for InputBox.
3. Script asks user for postal code in InboxBox.
4. Script reads geopostalcodes.txt into 2D array.
5. Script finds LAT and LON and placename of entered postal code.
6. Script calculates distance from Apeldoorn, and outputs result in MsgBox.

List of postal codes: http://download.geonames.org/export/zip/
Distance calculator: http://www.movable-type.co.uk/scripts/latlong.html
ATan2 function: https://www.autoitscript.com/forum/topic/164765-atan2/

#ce

global $zip, $aArray ; declare global vars
$sourceFile = @ScriptDir & "\NL.txt" ; identify source data file
_FileReadToArray($sourceFile,$aArray,$FRTA_NOCOUNT,@tab) ; use built in function to read into array - avoid for loops
; Loaded file first so arry is created that can be re-used with function call below
;_ArrayDisplay($aArray) ; for debug - is a 12 column file

; made calculuation piece into function call activated by button
func _calculate()
$zip=GUICtrlRead($Input1) ; read zip from GUI - should add some error checking to this ...
$fileopen = FileOpen ("NL.txt", 128)
$fileread = FileRead ($fileopen)

$iIndex = _ArraySearch($aArray, $zip)

MsgBox(0, "", $zip & " is on row " & $iIndex+1, 0) ; for testing purposes only

$lat2raw = $aArray[$iIndex][10] ; lat value is in column 10
$lon2raw = $aArray[$iIndex][11] ; lon value is in column 11

$placename = $aArray[$iIndex][3] ; place name is in column 3

; Apeldoorn, 7311 (train station)

$lat1raw = 52.21
$lon1raw = 5.9694

$lat1radian = $lat1raw * 0.017453278 ; convert to radians
$lon1radian = $lon1raw * 0.017453278

$lat2radian = $lat2raw * 0.017453278
$lon2radian = $lon2raw * 0.017453278

$earthradius = 6371000 ; meters

; now do some fancy math

$diflat = ($lat2radian - $lat1radian)
$diflon = ($lon2radian - $lon1radian)

$a = Sin ($diflat / 2) * Sin ($diflat / 2) + Cos ($lat1radian) * Cos ($lat2radian) * Sin ($diflon / 2) * Sin ($diflon / 2)

$b = _ATan2 (Sqrt ($a), Sqrt (1 - $a))

$c = 2 * $b

$d = $earthradius * $c ; in meters

$e = Round ($d / 1000, 0)

MsgBox (0, "", "Distance is " & $e & "km", 0)

EndFunc

Func _ATan2($fY, $fX)
; https://www.autoitscript.com/forum/topic/164765-atan2/
Local $aResult = DllCall("msvcrt.dll", "double:cdecl", "atan2", "double", $fY, "double", $fX)
If @error Then Return SetError(@error, @extended, False)
Return $aResult[0]
EndFunc


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        case $Button1
            _calculate()
        Case $GUI_EVENT_CLOSE
            Exit

    EndSwitch
WEnd

 

[leuce]

Thanks, jfish, I used your version to see if I could figure out what's wrong with the actual calculation.

I know this is off-topic for my original question, but I must say I'm stumped.  The TXT file's data isn't very accurate but it's accurate enough (e.g. it says that all postal codes in a single city have the same GPS coordinates, which is not true, obviously, but it does show that if I were to select my house's coordinates, I should get a result of "0 km" distance to train station in the centre of town).

I have tried the following things, and none of them give me useful results.

First, I tried my own version of an ATan2 function:

; https://www.easycalculation.com/trigonometry/atan2-calculator.php

$yy = 100
$xx = 200
$answer = _arctan2 ($yy, $xx)

MsgBox (0, "", $answer, 0) ; should be 0.4636

Func _arctan2 (ByRef $y, ByRef $x)
Select
Case $x > 0 
Return (ATan ($y / $x))
Case  $x < 0 AND $y = 0 
Return (ATan ($y / $x) + $PI)
Case  $x < 0 AND $y < 0 
Return (ATan ($y / $x) - $PI)
Case  $x = 0 AND $y > 0  
Return ($PI / 2)
Case $x = 0 AND $y < 0 
Return (- ($PI / 2))
Case $x = 0 AND $y = 0
Return @error
EndSelect
EndFunc

...but got the same results, so that means the problem does not lie with the ATan2 function that I used in the script.

I tried using ASin instead of ATan2, as per the information on Wikipedia for haversines:

$b = ASin (Sqrt((Sin(($diflat)/2)*Sin(($diflat)/2))+Cos($lat1radian)*Cos($lat2radian)*(Sin (($diflon)/2)*Sin(($diflon)/2))))

And I tried ACos, although some web sites say that ACos method is dodgy for distances of less than about 10 km:

; http://bluemm.blogspot.nl/2007/01/excel-formula-to-calculate-distance.html
$e =ACos(Cos(_Radian(90-$lat1raw))*Cos(_Radian(90-$lat2raw))+Sin(_Radian(90-$lat1raw)) *Sin(_Radian(90-$lat2raw))*Cos(_Radian($lon1raw-$lon2raw)))*6371

I even tried a flat-earth formula (the other formulas in this thread try to take the curvature of the earth into account):

; http://www.mathwarehouse.com/algebra/distance_formula/index.php
$e = Sqrt (($diflat)*($diflat)+($diflon)*($diflon))

And I tried doing all of this with degree inputs instead of radian inputs.  And I tried to use Abs () to ensure that the input values are all positive.  No luck.  All of this tells me that I must be missing something really, really, really basic.  Really basic.

Samuel

 

[leuce]

3 hours ago, Jfish said:

While I had the hood open I also added a small GUI and made the calculation into a function that can be called every time you press the button.

; use a GUI inn lieu of clip functions to avoid time based input and allow for subsequent look ups

 

Thanks, that's an interesting twist.  The way I intended to use the script was to run it when I saw a postal code, and then select the postal code with my mouse, so that I don't have to type it in manually.  I think eventually I would have added a HotKeySet into the script, so that I could select any postal code, click the shortcut, and let the script do it's thing.

I've lived in my current city for over 5 years but I still can't tell if e.g. Kralendijk is within cycling distance of Apeldoorn or not, if you were to ask me.  It's not, by the way.

[Jfish]

Where do you see the postal codes that you are copying with your mouse?

Also, on your calculation question above have you managed to get the calc to work in Excel or any other language? (and have you checked all the units of measure).

 

[leuce]

1 hour ago, Jfish said:

Where do you see the postal codes that you are copying with your mouse?

In my case, they'd be on web sites that I visit.

Quote

Also, on your calculation question above have you managed to get the calc to work in Excel or any other language? (and have you checked all the units of measure).

Good point, I'll see if I can test it in e.g. Excel.