Advanced post code matching

[comcraft1966]

I am planning an Access database for a Hotel chain with several hotels
all over the UK. The required functionality is as follows:

1. A client wants to know the nearest hotel/s to a specific location.

2. The db user keys in the client's postcode and the system returns
the three hotels nearest to them in a format similar to the following:

Distance form Postcode Hotel Name Location Tel
5.5 miles The Royal Bristol
01555 5551
123 miles The Regent London 0207
557 5555
125 miles The Plaza London
0207 667 4444

Does any one know of a third party tool that will do this? All I need
is the bit that can take all of the Hotel postcodes (about 30) and
calculate the distance of each of them to the user entered postcode. I
can do the rest.

Thanks,

Paul

[Salad]

co**********@googlemail.com wrote:

I am planning an Access database for a Hotel chain with several hotels
all over the UK. The required functionality is as follows:

1. A client wants to know the nearest hotel/s to a specific location.

2. The db user keys in the client's postcode and the system returns
the three hotels nearest to them in a format similar to the following:

Distance form Postcode Hotel Name Location Tel
5.5 miles The Royal Bristol
01555 5551
123 miles The Regent London 0207
557 5555
125 miles The Plaza London
0207 667 4444

Does any one know of a third party tool that will do this? All I need
is the bit that can take all of the Hotel postcodes (about 30) and
calculate the distance of each of them to the user entered postcode. I
can do the rest.

Thanks,

Paul

Wouldn't it be easier to pass the selection to GoogleMaps for hotels in
that area?

[comcraft1966]

On 12 Nov, 16:35, Salad <[e-mail]:

[e-mail] wrote:

I am planning an Access database for a Hotel chain with several hotels
all over the UK. The required functionality is as follows:

1. A client wants to know the nearest hotel/s to a specific location.

2. The db user keys in the client's postcode and the system returns
the three hotels nearest to them in a format similar to the following:

Distance form Postcode * *Hotel Name * * *Location * * *Tel
5.5 miles * * * * * * * * * * * * The Royal ** * * Bristol
01555 5551
123 miles * * * * * * * * * * * * The Regent * * *London * * * * *0207
557 5555
125 miles * * * * * * * * * * * * The Plaza ** * *London
0207 667 4444

Does any one know of a third party tool that will do this? All I need
is the bit that can take all of the Hotel postcodes (about 30) and
calculate the distance of each of them to the user entered postcode. I
can do the rest.

Thanks,

Paul

Wouldn't it be easier to pass the selection to GoogleMaps for hotels in
that area?

Thanks for your reply.

No that would not work; the Access database stores a list of the 30
hotels owned by this hotel chain.
I am trying to find the best three matches amongst those hotels to a
user entered postcode.

Paul

[The Frog]

Hi there,

I am not aware of a third party tool except for google maps and the
associated api. The issue you face is one of knowing the distance
between postcodes, and you dont have a reference with that data to
work with.

As I see it there are really only two ways to achieve this, either do
what Salad has suggested and let google maps do the calculation for
you, or, find a database / create a table that has the various suburbs
and postcodes along with their geographic (GPS type data) location and
do the distance calculations yourself in the app.

Either way, you are going to have to find a geographic starting point
(the customers point of origin) and a 'nearest' destination point (the
hotel) by calculating the distance between the starting point and each
destination and choosing the shortest, maybe rank them or something
like that. The point is YOU NEED THE GEOGRAPHIC DATA.

Hope this helps

Cheers

The Frog

[Phil Stanton]

Have a look ar http://www.easypeasy.com/guides/article.php?article=64

That appears to be post codes to Lat & Longitude. Some maths should give you
the distance (AS THE CROW FLIES) to your hotels. Obviously you would need to
store both the Post code to Lat & Long data and the Lat & Long of each hotel

HTH

Phil
"The Frog" <Mr************@googlemail.comwrote in message
news:9b**********************************@w39g2000 prb.googlegroups.com...

Hi there,

I am not aware of a third party tool except for google maps and the
associated api. The issue you face is one of knowing the distance
between postcodes, and you dont have a reference with that data to
work with.

As I see it there are really only two ways to achieve this, either do
what Salad has suggested and let google maps do the calculation for
you, or, find a database / create a table that has the various suburbs
and postcodes along with their geographic (GPS type data) location and
do the distance calculations yourself in the app.

Either way, you are going to have to find a geographic starting point
(the customers point of origin) and a 'nearest' destination point (the
hotel) by calculating the distance between the starting point and each
destination and choosing the shortest, maybe rank them or something
like that. The point is YOU NEED THE GEOGRAPHIC DATA.

Hope this helps

Cheers

The Frog

[Jens Schilling]

Hi,

Phil Stanton wrote:

That appears to be post codes to Lat & Longitude. Some maths should
give you the distance (AS THE CROW FLIES) to your hotels.

Think the neat code sample database contains some useful functions :

http://support.microsoft.com/kb/177972/en-us
Regards
Jens

[Salad]

The Frog wrote:

Hi there,

I am not aware of a third party tool except for google maps and the
associated api. The issue you face is one of knowing the distance
between postcodes, and you dont have a reference with that data to
work with.

As I see it there are really only two ways to achieve this, either do
what Salad has suggested and let google maps do the calculation for
you, or, find a database / create a table that has the various suburbs
and postcodes along with their geographic (GPS type data) location and
do the distance calculations yourself in the app.

Either way, you are going to have to find a geographic starting point
(the customers point of origin) and a 'nearest' destination point (the
hotel) by calculating the distance between the starting point and each
destination and choosing the shortest, maybe rank them or something
like that. The point is YOU NEED THE GEOGRAPHIC DATA.

Hope this helps

Cheers

The Frog

Hi Frog:

I don't know but perhaps he could use some mapping/GPS type software to
return the results. I was at a company years ago and they had the
longitude/latitudes entered into the app and calculated distances from
that. However, there was a problem that the distance between ptA and
ptB may be short...but a lake or canal might be in the way and getting
around that would be too long.

Using my method of Google, he might get Hotels not in his chain. I'm
wondering if he could pass the hotels in the chain and have them come up
with pincushions of those locations.

I entered "San Francisco Hotels Hilton Sheraton" and got a list of 8
hotels. The OP could pass something like that string to GoogleMaps to
limit the list.

[Larry Linson]

Have you investigated Microsoft's Map Point? I have colleagues who used it
for "things geographic", but don't know if it will do what you want... but I
would suspect that it can.

Larry Linson
Microsoft Office Access MVP
<co**********@googlemail.comwrote in message
news:e6**********************************@w1g2000p rk.googlegroups.com...

>I am planning an Access database for a Hotel chain with several hotels
all over the UK. The required functionality is as follows:

1. A client wants to know the nearest hotel/s to a specific location.

2. The db user keys in the client's postcode and the system returns
the three hotels nearest to them in a format similar to the following:

Distance form Postcode Hotel Name Location Tel
5.5 miles The Royal Bristol
01555 5551
123 miles The Regent London 0207
557 5555
125 miles The Plaza London
0207 667 4444

Does any one know of a third party tool that will do this? All I need
is the bit that can take all of the Hotel postcodes (about 30) and
calculate the distance of each of them to the user entered postcode. I
can do the rest.

Thanks,

Paul

[lyle fairfield]

A Google search of "PostCode Latitude" brings up at least one
reference to a publicly available file of the coordinates of areas
associated with UK Postal Codes. One could use these to calculate in-
line distances with code like this:

Const PI# = 3.14159265358979
' circumference in kilometers
Const Circumference# = 40123.648

Public Function GreatCircleDistance#( _
ByVal Latitude0#, ByVal Longitude0#, _
ByVal Latitude1#, ByVal Longitude1#)

On Error Resume Next

Dim Arc#
Dim CosArc#

If Latitude0 = Latitude1 And Longitude0 = Longitude1 Then
GreatCircleDistance = 0
Else
Latitude0 = Radians(Latitude0)
Longitude0 = Radians(Longitude0)
Latitude1 = Radians(Latitude1)
Longitude1 = Radians(Longitude1)
CosArc = (Sin(Latitude0) * Sin(Latitude1)) + _
(Cos(Latitude0) * Cos(Latitude1) * Cos(Longitude0 -
Longitude1))
Arc = Degrees(Atn(-CosArc / Sqr(-CosArc * CosArc + 1)) + 2 *
Atn(1))
GreatCircleDistance = Arc / 360 * Circumference
End If
End Function

Private Function Radians#(ByVal Degrees#)
Radians = PI * Degrees / 180
End Function

Private Function Degrees#(ByVal Radians#)
Degrees = Radians / PI * 180
End Function

On Nov 12, 11:26*am, [e-mail] wrote:

I am planning an Access database for a Hotel chain with several hotels
all over the UK. The required functionality is as follows:

1. A client wants to know the nearest hotel/s to a specific location.

2. The db user keys in the client's postcode and the system returns
the three hotels nearest to them in a format similar to the following:

Distance form Postcode * *Hotel Name * * *Location * * *Tel
5.5 miles * * * * * * * * * * * * The Royal * * * * Bristol
01555 5551
123 miles * * * * * * * * * * * * The Regent * * *London * * * * *0207
557 5555
125 miles * * * * * * * * * * * * The Plaza * * * *London
0207 667 4444

Does any one know of a third party tool that will do this? All I need
is the bit that can take all of the Hotel postcodes (about 30) and
calculate the distance of each of them to the user entered postcode. I
can do the rest.

Thanks,

Paul

[CDMAPoster]

On Nov 15, 6:14 am, lyle fairfield <[e-mail]:

A Google search of "PostCode Latitude" brings up at least one
reference to a publicly available file of the coordinates of areas
associated with UK Postal Codes. One could use these to calculate in-
line distances with code like this:

Const PI# = 3.14159265358979
' circumference in kilometers
Const Circumference# = 40123.648

Public Function GreatCircleDistance#( _
ByVal Latitude0#, ByVal Longitude0#, _
ByVal Latitude1#, ByVal Longitude1#)

On Error Resume Next

Dim Arc#
Dim CosArc#

If Latitude0 = Latitude1 And Longitude0 = Longitude1 Then
GreatCircleDistance = 0
Else
Latitude0 = Radians(Latitude0)
Longitude0 = Radians(Longitude0)
Latitude1 = Radians(Latitude1)
Longitude1 = Radians(Longitude1)
CosArc = (Sin(Latitude0) * Sin(Latitude1)) + _
(Cos(Latitude0) * Cos(Latitude1) * Cos(Longitude0 -
Longitude1))
Arc = Degrees(Atn(-CosArc / Sqr(-CosArc * CosArc + 1)) + 2 *
Atn(1))
GreatCircleDistance = Arc / 360 * Circumference
End If
End Function

Private Function Radians#(ByVal Degrees#)
Radians = PI * Degrees / 180
End Function

Private Function Degrees#(ByVal Radians#)
Degrees = Radians / PI * 180
End Function

In:

http://groups.google.com/group/micro...baa12e5809aa90

I derived a formula for the great circle distance between two points
given their latitude and longitude coordinates. I also proved that it
is correct, although some mathematical simplification should be done
for comparison with other formulae. Of course, if you're not aware
that the dot product of two unit vectors gives the cosine of the angle
between them, then the simple proof is not as obvious :-). I have a
lot more confidence in the formulae that I can derive or show to be
equivalent to what I derive. Then when there's a difference I can
determine what assumptions or simplifications were made.

James A. Fortune
CD********@FortuneJames.com

[Chuck]

On Sat, 15 Nov 2008 16:01:53 -0800 (PST), CD********@FortuneJames.com wrote:

>On Nov 15, 6:14 am, lyle fairfield <[e-mail]:

>A Google search of "PostCode Latitude" brings up at least one
reference to a publicly available file of the coordinates of areas
associated with UK Postal Codes. One could use these to calculate in-
line distances with code like this:

Const PI# = 3.14159265358979
' circumference in kilometers
Const Circumference# = 40123.648

Public Function GreatCircleDistance#( _
ByVal Latitude0#, ByVal Longitude0#, _
ByVal Latitude1#, ByVal Longitude1#)

On Error Resume Next

Dim Arc#
Dim CosArc#

If Latitude0 = Latitude1 And Longitude0 = Longitude1 Then
GreatCircleDistance = 0
Else
Latitude0 = Radians(Latitude0)
Longitude0 = Radians(Longitude0)
Latitude1 = Radians(Latitude1)
Longitude1 = Radians(Longitude1)
CosArc = (Sin(Latitude0) * Sin(Latitude1)) + _
(Cos(Latitude0) * Cos(Latitude1) * Cos(Longitude0 -
Longitude1))
Arc = Degrees(Atn(-CosArc / Sqr(-CosArc * CosArc + 1)) + 2 *
Atn(1))
GreatCircleDistance = Arc / 360 * Circumference
End If
End Function

Private Function Radians#(ByVal Degrees#)
Radians = PI * Degrees / 180
End Function

Private Function Degrees#(ByVal Radians#)
Degrees = Radians / PI * 180
End Function

In:

http://groups.google.com/group/micro...baa12e5809aa90

I derived a formula for the great circle distance between two points
given their latitude and longitude coordinates. I also proved that it
is correct, although some mathematical simplification should be done
for comparison with other formulae. Of course, if you're not aware
that the dot product of two unit vectors gives the cosine of the angle
between them, then the simple proof is not as obvious :-). I have a
lot more confidence in the formulae that I can derive or show to be
equivalent to what I derive. Then when there's a difference I can
determine what assumptions or simplifications were made.

James A. Fortune
CD********@FortuneJames.com

The only assumptions in lyle's functions is that the earth is spherical and
that its diameter is 12771.753 KM - both of which, for all practical purposes
are true.

Chuck
--

[The Frog]

Hi Everyone,

I was doing a little research on this and it appears that google maps
has a route calculator with shortest route capabilities. So, knowing
this function exists, and knowing that (from previous posts) there is
a list of postcodes with associated long / lat positions, AND knowing
the long / lat of the hotels themselves it SHOULD be possible to have
a user provide their postcode, then query google maps for the shortest
route to each hotel, then compare the results and provide user with a
ranked list of nearest to farthest.

The google maps API will be your friend here. As I understand it there
is an 'object' that can be defined for these purposes that is purely
HTML (ie/ text). You could potentially provide your users with a nice
little map with the route on it maybe!

Everybody wins :-)

@Lyle: Nice - I was looking for some great circle stuff for some time
but never got round to hunting for it. Thanks for putting it up.

@Salad: Have you had any success with software integration along these
lines? I have tried to 'play' with this stuff but not successfully. I
was looking at the google andriod OS, it seems to have the maps /
route planning stuff directly integrated - if you are looking for
software to work with along those lines maybe an Andriod VM?

Cheers

The Frog

[Salad]

The Frog wrote:

Hi Everyone,

I was doing a little research on this and it appears that google maps
has a route calculator with shortest route capabilities. So, knowing
this function exists, and knowing that (from previous posts) there is
a list of postcodes with associated long / lat positions, AND knowing
the long / lat of the hotels themselves it SHOULD be possible to have
a user provide their postcode, then query google maps for the shortest
route to each hotel, then compare the results and provide user with a
ranked list of nearest to farthest.

The google maps API will be your friend here. As I understand it there
is an 'object' that can be defined for these purposes that is purely
HTML (ie/ text). You could potentially provide your users with a nice
little map with the route on it maybe!

Everybody wins :-)

@Lyle: Nice - I was looking for some great circle stuff for some time
but never got round to hunting for it. Thanks for putting it up.

@Salad: Have you had any success with software integration along these
lines? I have tried to 'play' with this stuff but not successfully. I
was looking at the google andriod OS, it seems to have the maps /
route planning stuff directly integrated - if you are looking for
software to work with along those lines maybe an Andriod VM?

No. All I have is a presentation of a map or driving instructions for
an address. I thought that presenting Google maps seemed to be easiest
but your idea of the API would be better for the OP.

Cheers

The Frog

[CDMAPoster]

On Nov 16, 7:12 am, Chuck <[e-mail]:

On Sat, 15 Nov 2008 16:01:53 -0800 (PST), [e-mail] wrote:

On Nov 15, 6:14 am, lyle fairfield <[e-mail]:

A Google search of "PostCode Latitude" brings up at least one
reference to a publicly available file of the coordinates of areas
associated with UK Postal Codes. One could use these to calculate in-
line distances with code like this:

Const PI# = 3.14159265358979
' circumference in kilometers
Const Circumference# = 40123.648

Public Function GreatCircleDistance#( _
ByVal Latitude0#, ByVal Longitude0#, _
ByVal Latitude1#, ByVal Longitude1#)

On Error Resume Next

Dim Arc#
Dim CosArc#

If Latitude0 = Latitude1 And Longitude0 = Longitude1 Then
GreatCircleDistance = 0
Else
Latitude0 = Radians(Latitude0)
Longitude0 = Radians(Longitude0)
Latitude1 = Radians(Latitude1)
Longitude1 = Radians(Longitude1)
CosArc = (Sin(Latitude0) * Sin(Latitude1)) + _
(Cos(Latitude0) * Cos(Latitude1) * Cos(Longitude0 -
Longitude1))
Arc = Degrees(Atn(-CosArc / Sqr(-CosArc * CosArc + 1)) + 2 *
Atn(1))
GreatCircleDistance = Arc / 360 * Circumference
End If
End Function

Private Function Radians#(ByVal Degrees#)
Radians = PI * Degrees / 180
End Function

Private Function Degrees#(ByVal Radians#)
Degrees = Radians / PI * 180
End Function

In:

http://groups.google.com/group/micro...browse_frm/thr...

I derived a formula for the great circle distance between two points
given their latitude and longitude coordinates. I also proved that it
is correct, although some mathematical simplification should be done
for comparison with other formulae. Of course, if you're not aware
that the dot product of two unit vectors gives the cosine of the angle
between them, then the simple proof is not as obvious :-). I have a
lot more confidence in the formulae that I can derive or show to be
equivalent to what I derive. Then when there's a difference I can
determine what assumptions or simplifications were made.

James A. Fortune
[e-mail]

The only assumptions in lyle's functions is that the earth is spherical and
that its diameter is 12771.753 KM - both of which, for all practical purposes
are true.

Chuck
--

I agree since his formula is exactly equivalent to mine once the
cosine of a sum trig identity is applied. My point remains whether
there is a difference in the formulae or not. It is interesting to
note that for a much tougher calculation involving the sun's position
in the sky for any latitude and longitude at a given time, one of the
two angles turned out to be the arccosine of a product of cosine
terms. That derivation used vector dot and cross products to get the
solution and the formula was fairly accurate in spite of the fact that
it assumed a circular orbit of the earth about the sun. Having a
closed-form solution was a nice plus. You could get approximate
sunrise and sunset times by solving for when the vertical angle is
zero, plus you could find the different sunrise and sunset azimuth
angles as they change throughout the year. I believe that in
Australia the sun swings into the northern sky (year round once the
latitude is greater than the tilt of the Earth's axis).

James A. Fortune
CD********@FortuneJames.com

In the absence of daylight savings time, if your time zone is not too
irregular your actual solar noon can be up to about half an hour
before or after nominal noon depending on how far East or West you are
of the center of your time zone.