Using Grid References 2

[tamus121]

I have Grid references stored in a table with address of businesses. They are in the format J123456. I have been thinking that it would be useful to list addresses within a short radius of a particular address – say approximately 3km.
I have done some searching for this but haven’t found anything. Has anyone tackled something like this using access before, I would be grateful for any help. My grasp of access is fairly basic but I think this should be possible
tamus

 

[MajP]

I have done it with lat-long in Access using VBA, but not a grid reference. A grid reference would be a little more involved because you have multiple map sheets with repeating grids. Unless all of your values fall within a single map sheet then you could just do some simple trigonometry. But basically my function using lat long:

Public Function polarDistanceTwo(decLatStart As Single, decLongStart As Single, decLatEnd As Single, decLongEnd As Single) As Single
Const decToRad = 3.14159265358979 / 180
Const radiusOfEarth = 3963.1
'radiusOfEarth =3963.1 statute miles, 3443.9 nautical miles, or 6378 km
Dim radLatStart As Single
Dim radLongStart As Single
Dim radLatEnd As Single
Dim radLongEnd As Single
radLatStart = decLatStart * decToRad
radLongStart = decLongStart * decToRad
radLatEnd = decLatEnd * decToRad
radLongEnd = decLongEnd * decToRad
If Sin(radLatStart) * Sin(radLatEnd) + Cos(radLatStart) * Cos(radLatEnd) * Cos(radLongStart - radLongEnd) > 1 Then
   polarDistanceTwo = 3963.1 * ArcCos(1)
   Else
   polarDistanceTwo = 3963.1 * ArcCos(Sin(radLatStart) * Sin(radLatEnd) + Cos(radLatStart) * Cos(radLatEnd) * Cos(radLongStart - radLongEnd))
End If
End Function

vb does not have an arc cosine function so I wrote this to support the above function

Function ArcCos(X As Single) As Single
    If Abs(X) <> 1 Then
        ArcCos = 1.5707963267949 - Atn(X / Sqr(1 - X * X))
    Else
        ArcCos = 3.14159265358979 * Sgn(X)
    End If
    'ArcCos = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End Function

now you could use this in any query to return records where the distance between them is <= 3.
I have all the state capitals so I can find all the distances between state capitals where the distance is less than 100

Start City	End City	Path Distance
Annapolis	Washington, National AP	25
Providence AP	Boston AP	49
Dover AFB	Annapolis	61
Boston AP	Concord AP	62
Hartford, Brainard Field	Providence AP	63
Trenton Co	Dover AFB	84
Washington, National AP	Dover AFB	86
Montpelier	Concord AP	86
Annapolis	Harrisburg AP	88
Hartford, Brainard Field	Albany AP (S)	92
Washington, National AP	Harrisburg AP	94
Boston AP	Hartford, Brainard Field	94
Richmond AP	Washington, National AP	95
Denver AP	Cheyenne	97

 

[oharab]

When you say Grid Reference, do you mean OSGB National Grid or another?
Either way, you should be able to find the appropriate algorithm to convert grid ref to x and y. From there it's a case of pythagoras to work out your distances.

Let me know which grid you're looking at and we may be able to point you to the correct algorithm.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[tamus121]

Thanks for your replies.
Ben I think it is much the same as the OSGB National Grid only I am in Ireland so there is only a single letter eg J123456. This six figure number refers to a 100km square, probably in the north somewhere.
I think the biggest problem would be dealing with an area when lies beside another. Unfortunately this is the case as I have at least 5 maps covered.
tamus

 

[oharab]

To convert from grid reference to easting/northing is actually pretty simple.

The letter is a grid square reference. Ireland is divided up into 100km squares like so. To convert your grid ref into easting northing, first of all find the easting and northing of the square you are in, so J becomes 300000, 300000 (Each square is 100 km, easting and northing are in metres), then split your numbers into 2 parts 123,456 and pad them with extra zeroes till they're 6 digits long 12300, 45600 and add them to your grid squares.
Easting: 312300
Northing: 345600

It doesn't matter what map they are on, the eastings and northings are now all comparable and you can use simple pythagoras to work out how far apart 2 points are.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[tamus121]

Thanks for that Ben
So a point on map or square H would use 2 for the eastings and 3 for the northings and fill out with the remaining digits plus add zeros.
But I would have no idea how to use Access to work out which Grid references where within 3 km of my original point.
tamus

 

[oharab]

Yep, that's the way.

To work out the distance between 2 points, it's straight forward pythagorean "find the distance along the hypotenuse question".

     A
     |
     |
     |
     |_ _ _ _ _ _ _ _ _ _B
     X

The difference from A-B is the root of the sum of the sqaure of the other 2 sides ie AB=(BX^2+AX^2)^(1/2)

if A=312300,345600
B=378900,299900

then BX=Abs(378900-312300)=66600
AX=Abs(345600-299900)=45700
(note I'm using the absolute value so it doesn't matter which way round we put in the numbers, we only want the difference)

So AB=
(66600^2+45700^2)^(1/2)=80771.59m or 80.7km

Hope that makes sense, I've been staring at this screen for 13 hours now!

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[MajP]

Now that I have a new education in the Irish grid reference system. Here is the function you need

Public Function getGridDistance(grid1 As String, grid2 As String) As Single
  Dim east1 As Long
  Dim north1 As Long
  Dim east2 As Long
  Dim north2 As Long
  
  east1 = getEasting(grid1)
  north1 = getNorthing(grid1)
  east2 = getEasting(grid2)
  north2 = getNorthing(grid2)
  getGridDistance = calcDistance(east1, north1, east2, north2)

End Function

supporting functions

Public Function getEasting(ByVal strGrid As String) As Long
  Dim strIdentifier As String
  strIdentifier = Left(strGrid, 1)
  strGrid = Mid(strGrid, 2, 3)
  Select Case strIdentifier
  Case "A", "F", "L", "Q", "V"
   '0
  Case "B", "G", "M", "R", "W"
   strGrid = 1 & strGrid
  Case "C", "H", "N", "S", "X"
   strGrid = 2 & strGrid
  Case "D", "J", "O", "T", "Y"
   strGrid = 3 & strGrid
  Case Else
    MsgBox "Invalid indenifier"
  End Select
  getEasting = CLng(strGrid)
End Function

Public Function getNorthing(ByVal strGrid As String)
  Dim strIdentifier As String
  strIdentifier = Left(strGrid, 1)
  strGrid = Mid(strGrid, 5)
  Select Case strIdentifier
  Case "A", "B", "C", "D"
   strGrid = 4 & strGrid
  Case "F", "G", "H", "J"
   strGrid = 3 & strGrid
  Case "L", "M", "N", "O"
   strGrid = 2 & strGrid
  Case "Q", "R", "S", "T"
   strGrid = 1 & strGrid
  Case "V", "W", "X", "Y"
   '0
  Case Else
    MsgBox "Invalid indenifier"
  End Select
  getNorthing = CLng(strGrid)
End Function

Public Function calcDistance(east1 As Long, north1 As Long, east2 As Long, north2 As Long) As Single
  calcDistance = ((east2 - east1) ^ 2 + (north2 - north1) ^ 2) ^ 0.5
  calcDistance = Round(calcDistance, 1)
End Function

Now you can use the function getGridDistance in a query using a cartesian join (i.e. drop two instances in your query of the same table without any join). This will allow you to compare each city to every other city and get the distance from on city to all other cities.

 

[tamus121]

Thanks Ben and Majp
I will study this and see how I get on. Although I can see a little of what is happening in the code I expect that I will still have difficulties with making this work.
tamus

 

[MajP]

Here is an example of using the function

assume I have a table "tblLocations"

ID	locationName	locationGrid
1	Belfast	        J338740
2	Castle	        H231442
3	Causeway	C947447

then I can do a query (this you might have to ask for more detail)

SELECT From.locationName AS FromLocation, From.locationGrid AS FromGrid, To.locationName AS ToLocation, To.locationGrid AS ToGrid, GetGridDistance([from].[locationGrid],[to].[locationGrid]) AS Distance
FROM tblLocations AS [From], tblLocations AS [To]
WHERE (((To.locationName)<>[From].[LocationName]));

Now this gives the answer like

FromLocation	FromGrid	ToLocation	ToGrid	Distance
Belfast	J338740	Castle	H231442	1146.40
Belfast	J338740	Causeway	C947447	807.90
Castle	H231442	Belfast	J338740	1146.40
Castle	H231442	Causeway	C947447	1234.00
Causeway	C947447	Belfast	J338740	807.90
Causeway	C947447	Castle	H231442	1234.00

 

[MajP]

I think these answers are in tens of meters so I think the answers need to be moved one decimal place. So Belfast to the Causeway would be 80.8km. No idea if that is a correct solution, need Ben to take a look to see if my work is correct.

 

[tamus121]

Majp in the public function getNorthing should it be declared As Long?
Should I have two Modules to hold these public functions one for the getGridDistance and one for the rest?
If I only want to select the locations that are 3km or less from an address or Grid Reference selected by the user on a form I presume I don’t need to do a Cartesian join? This would simplify the Select statement I presume.
Thanks for interest in this. tamus

 

[MajP]

1)Yes in vb if you do not explicitly declare the return type it will return a variant. It will not cause any problems, but it is good practice to always declare your variables to the smallest possible scope. A long is 4 bytes a variant is 16. I was just sloppy.
2)Modules are like folders and provide organization. I would put this all in a single module with a name like "mdlGridUtilities" since they all are serving the same purpose. I just seperated to show which function you call from queries or a calculated control
3)There are probably a couple different strategies, but you need to compare your selected city against every other city to find the distance, and then return only those where the distance is less than 3Km. So you will likely have to do a cartesian product. It is actually the simplest join. Just add any table to the query builder twice without any join, and you will return each record with every other record.

I you are always going to want to return just distances less than three km I would make a query that uses a cartesian product and returns each city and every other city that is less than 3km away. Then I would make a table from this query. Now I can quickly pull data from this table. Doing it dynamically could be a little expensive. If you have 1000 cities and do a cartesian product you return 1000000 records, but there may only be a handful less than 3 km. It may save you a lot of time to do this once and then build a table from your results.

This is what I did in the example above. I used the distance equations to make a table of the 50 US state capitals and the distance to every other city. So there was 50 X 49 records.

 

[tamus121]

Majp
By using the data and code you have suggested I get this to work with similar results.
My distance results have different amounts of decimal places and I can't work out how to set the output to have two as you have acheived.
Is this what is called a Select Query? Can I produce a report from the results of this query?

btw I wasn't thinking that you were being sloppy when not declaring the type of variable. I had entered it as Long but couldn't work out why you hadn't made it the same as a previous one.
tamus

 

[MajP]

you can set the format of the output by setting the properties of the form or report field or query.

 

[tamus121]

Guys thanks for your help with this. I have it working and producing reports as required.
tamus