World Map Lookup

[puforee]

I maintain data in my DB that includes lat. and long. of various Cities throughout the world. This allows our travelers to see where they may be going. Also included is the selected city time zone.

I would like to be able to do two things for my users:

Show city locations on a map based on the DB Lat and Long fields.

Calculate distance between two sets of Longitute and Lattitude data.

I know this is strange but has anyone ever run into this before?

Thanks,

 

[MajP]

Here are two function for the distance

Function polarDistance(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
polarDistance = ArcCos((Cos([radLatStart]) * Cos([radLongStart]) * Cos([radLatEnd]) * Cos([radLongEnd])) + Cos([radLatStart]) * Sin([radLongStart]) * Cos([radLatEnd]) * Sin([radLongEnd]) + (Sin([radLatStart]) * Sin([radLatEnd]))) * radiusOfEarth
'                     (cos($a1)*            cos($b1)*             cos($a2)*          cos($b2)          + cos($a1)*            sin($b1)*              cos($a2)*          sin($b2) +          sin($a1)*             sin($a2)        ) * $r
'                 acos((cos($a) *           cos($b) *             cos($c) *          cos($d)) +          (cos($a) *           sin($b) *              cos($c) *           sin($d)) +         (sin($a) *            sin($c)) ) * $r
End 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

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

 

[MajP]

I found the easiest way to plot (assuming you have a reasonable amount of cities) is to have in your city table two fields which are the "mapTop" and "mapLeft". Then just plot the object on the form overtop of your image. Unless you have some sophisticated mapping software, getting some algorithm that correlates lat long to your projection will be extremely difficult.

 

[puforee]

MajP,

Great,

Two questions....
What are the differences between the two functions? Do you have a preference on what one I should use.

What information should be placed in the mapTop and mapLeft fields?

Thanks for the quick response.

 

[MajP]

Run a couple of checks, try them both with a couple of difft lat longs. I beleive they are both mathematically correct, and you should come out with the same answers. These algorithms are approximations and certain formulas work better for close distances and others for longer distances. I think one is the Haversine formula and the other the Spherical, I can not remember. Been a while.

http://www.movable-type.co.uk/scripts/latlong.html

I think either will work for what you are doing, may not want to rely on it if you plan to program an internal navigation system for an ICBM.

 

[puforee]

MajP

Great.:-D :-D

Now, about those mapTop and mapLeft fields...? What should they be populated with?

Thanks,

 

[MajP]

I did this one time so hopefully someone else chimes in with a better idea. I do a lot of computational heavy dbs not a lot of graphically heavy ones.

Lets say you have a reasonable amount of cities. Your table would look something like this.

tblCity
cityName
Lattitude
Longitude
mapTop
mapLeft
timeZoneName (EST, PST, Monrovia Estonia, etc.)
timeZoneValue (Use for calculations +- hours from zulu)

Drop your map image on your form. Make an little icon, to represent your city. Select "bring to front" because you are going to lay this over your map image. Maybe this is a label because you want to show the name as well as a dot. Put it where you would want it. Record its "top" and "left" property. Put that in your table. Now in code when you are ready to plot, make your icon visible, use a dlookup to return its Top and Left, then set its postion.

 

[MajP]

Here is a general idea. That plots a text box with a city's name based on a position in the table

Private Sub Image0_Click()
  Call plotCity("Detroit", getTop("Detroit"), getLeft("Detroit"))
End Sub

Public Sub plotCity(strCityName As String, sngTop As Single, sngLeft As Single)
   With Me.txtBxCity1
     .Visible = True
     .Top = inchesToPixels(sngTop)
     .Left = inchesToPixels(sngLeft)
     .Value = strCityName
  End With
End Sub

Public Function inchesToPixels(sngInches As Single) As Single
  inchesToPixels = 1440 * sngInches
End Function

Public Function getTop(strCity As String) As Single
  getTop = DLookup("mapTop", "tblCity", "cityName = '" & strCity & "'")
End Function
Public Function getLeft(strCity As String) As Single
  getLeft = DLookup("mapLeft", "tblCity", "cityName = '" & strCity & "'")
End Function

 

[puforee]

MajP,

Hmmmm. So I would initially have to manually place all Cities and record their Top and Left positions. And I have well over 1000 citis. Very time consuming.

I wonder if there would be a way to set the map up as a grid with the top being +90, the bottom being -90. The left being +180 and the right being -180. This way the lat and long could find the location based on the valuse?

Does this sound doable? Have you ever heard of setting up a Form slave to specific dementions or values?

I wonder if I can find a map with the lat long in straight vertical and horizotal directions.

 

[MajP]

This is a little out of my league, we did one with the 50 US capitals so the manual process worked fine. Basically if it ended up in the correct state that was representative.
The problem is you are first going to have to find a projection and then each projection will have some kind of algorithm. Think about drawing a map on an orange and then unpeeling it. Then try to lay the peel on a grid. You can't. So all projections distort the earth especially around the edges. I am sure if you google this someone has a basic projection and a way to plot based of lat long. I would also try posting in the VB forum since this really is no longer an Access problem. There is probably even a freeware utility to do this.

 

[MichaelRed]

If you work in a reasonably sixed organization (100 cities seems to suggest that) you can purchase "MapPoint" (Microsoft) which includes all these capabilities. It is similar to google maps, so your users can just enter the name places and the zoom into the area. From there they can zoom in / out for more/less detail. It also includes "Push Pins" to permit the identification of multiple points in the map.

Then, again, Google Maps might be an easier approach.




MichaelRed

 

[fneily]

Being a simple minded person, couldn't you just have a 1000 little jpgs/bmps of the city/map area? Then store the pathname of the picture and display it as you would with a normal record? Maybe not exotic enough.

 

[ClydeData]

I use an activeX website control to link to maps from within a form
See

http://www.box.net/shared/ej4z7n2snx

She sample I've uploaded has got UK postcodes with latitude and longitude

If you look at the URL being used in the after update event of the combo box the you will find one of the variables is the map scale so you could make use of this variable and zoom in

hope this helps

Jimmy

 

[Markwell]

What about a hyperlink that opens up Google Map

With Chicago being some where near N 41.89° W 87.62° - Google maps would point to.

http://maps.google.com/maps?f=q&q=41.89,-87.62&ll=41.89,-87.62&z=6

Inside the US, you could user Tiger Census direct if you're always online.

http://tiger.census.gov/cgi-bin/map...=10&iht=300&iwd=300&mark=-87.62,41.89,reddot7