Searching a selected distance from given postcode

What sort of information do you have and what progress have you made? Like do you have the Lat/Long of all the Postcodes or not ?
Are you developing this system for America/Canada or Europe ? Do you want the distnace to be the aerial distance or Road distance ? and What sort of Layers do you have ?( Maps, Roads, PostCodes etc.)

As far as distance between two Lat/Long is concerned it is very easy..... You can use straight line approximation for distances less then 200-300 miles.... Please do reply if you need something like this.....

Imran Arshad

ASKER

Thank you for answering my question.

I have an access database that contains all postcodes within the UK and it has Lat & Long details of each postcode. What I would like to do is have a feature on my site whereby someone wants to look at items within a chosen radius of their postcode. ie if a customer wants to search upto 50miles from there postcode it will bring up items within postcodes in that radius. The distance is governed by a dropdown menu next to the postcode selection.

This does not have to be precise and the easiest solution would be great. I am not an expert in this so an idiots guide would be great!

Thanks again for you help

Ok, so you would basically calculate the distance between postcode X and all postcodes in your database. When the result is less than 50 miles, you want to display it, right?

As imarshad has suggested, for short distances the formula is quite simple.

This is from http://www.ga.gov.au/nmd/geodesy/datums/distance.jsp:

Explanation of terms
L1 = latitude at the first point (degrees)
L2 = latitude at the second point (degrees)
G1 = longitude at the first point (degrees)
G2 = longitude at the second point (degrees)
DG = longitude of the second point minus longitude of the first point (degrees)
DL = latitude of the second point minus latitude of the first point (degrees)
D = computed distance (km)

Definitions
South latitudes are negative
East longitudes are positive

Great Circle Distance (Based on Spherical trigonometry)
This method calculates the great circle distance, is based on spherical trigonometry, and assumes that:

1 minute of arc is 1 nautical mile
1 nautical mile is 1.852 km
D = 1.852 * 60 * ARCOS ( SIN(L1) * SIN(L2) + COS(L1) * COS(L2) * COS(DG))

Note: If your calculator returns the ARCOS result as radians you will have to convert the radians to degrees before multiplying by 60 and 1.852 degrees = (radians/PI)*180, where PI=3.141592654...

Comment: latitude is the part of the coordinates with "N" or "S" in it. Longitude is the part with "E" and "W". For your application, remember that UK coordinates are always "N", but longitude may be "E" or "W". For the calculation, change the sign of all "W" coordinates to "-", for all "E" coordinates to "+" [or the other way around, it doesn't really matter]).

Jan

The best source for such formulae is "Aviation Formulary 1.42" here you can go in depth and learn how these formulae are developed.....
Its link is

http://williams.best.vwh.net/avform.htm

If you want to skip all of this and just read the formula directly then this is the formula taken from above link....

Distance between points
The great circle distance d between two points with coordinates {lat1,lon1} and {lat2,lon2} is given by:

d=acos(sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lon1-lon2))
A mathematically equivalent formula, which is less subject to rounding error for short distances is:

d=2*asin(sqrt((sin((lat1-lat2)/2))^2 + cos(lat1)*cos(lat2)*(sin((lon1-lon2)/2))^2))

Implementation notes:
Notes on mathematical functions
Note: ^ denotes the exponentiation operator, sqrt is the square root function, acos the arc-cosine (or inverse cosine) function and asin is the arc-sine function. If asin or acos are unavailable they can be implemented using the atan2 function:

acos(x)=atan2(sqrt(1-x^2),x)
acos returns a value in the range 0 <= acos <= pi
asin(x)=atan2(x,sqrt(1-x^2))}
asin returns a value in the range -pi/2 <= asin <= pi/2
Note: Here atan2 has the conventional (C) ordering of arguments, namely atan2(y,x). This is not universal, Excel for instance uses atan2(x,y), but it has asin and acos anyway. Be warned. It returns a value in the range -pi < atan2 <= pi.

Further note: if your calculator/programming language is so impoverished that only atan is available then use:

asin(x)=2*atan(x/(1+sqrt(1-x*x)))
acos(x)=2*atan(sqrt((1-x)/(1+x))) x>=0
=pi - 2*atan(sqrt((1+x)/(1-x))) x<0

atan2(y,x)=atan(y/x) x>0
atan2(y,x)=atan(y/x)+pi x<0, y>=0
atan2(y,x)=pi/2 x=0, y>0
atan2(y,x)=atan(y/x)-pi x<0, y<0
atan2(y,x)=-pi/2 x=0, y<0
atan2(0,0) is undefined and should give an error.

Another potential implementation problem is that the arguments of asin and/or acos may, because of rounding error, exceed one in magnitude. With perfect arithmetic this can't happen. You may need to use "safe" versions of asin and acos on the lines of:

asin_safe(x)=asin(max(-1,min(x,1)))
acos_safe(x)=acos(max(-1,min(x,1)))
Note on the mod function. This appears to be implemented differently in different languages. Mod(y,x) is the remainder on dividing y by x and always lies in the range 0 <=mod <x. For instance: mod(2.3,2.)=0.3 and mod(-2.3,2.)=1.7

If you have a floor function (int in Excel), that returns floor(x)= "largest integer less than x" e.g. floor(-2.3)=-3 and floor(2.3) =2

mod(y,x) = y - x*floor(y/x)

The following should work in the absence of a floor function- regardless of whether "int" truncates or rounds downward:

mod=y - x * int(y/x)
if ( mod < 0) mod = mod + x

Sign Convention
As stated in the introduction, North latitudes and West longitudes are treated as positive, and South latitudes and East longitudes negative. It's easier to go with the flow, but if you prefer another convention you can change the signs in the formulae.

If you need VB Implementation of this formula then I have it....... Please do post it if you require it in VB.....
The link suggested by Jan is using the similar formula.

Imran Arshad

an example from the same link......

Worked Examples:

Suppose point 1 is LAX: (33deg 57min N, 118deg 24min W)
Suppose point 2 is JFK: (40deg 38min N, 73deg 47min W)
In radians LAX is

lat1=(33+57/60)*pi/180=0.592539, lon1=(118+24/60)*pi/180=2.066470
and JFK is

(lat2=0.709186,lon2=1.287762)
The distance from LAX to JFK is

d = 2*asin(sqrt((sin((lat1-lat2)/2))^2+
cos(lat1)*cos(lat2)*sin((lon1-lon2)/2)^2))
= 2*asin(sqrt((sin(0.592539-0.709186)/2))^2+
cos(0.592539)*cos(0.709186)*sin((2.066470-1.287762)/2)^2))
= 2*asin(sqrt((-0.05829)^2 +0.829525*0.758893*0.379591^2))
= 2*asin(0.306765)
= 0.623585 radians
= 0.623585*180*60/pi=2144nm
or

d = acos(sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lon1-lon2))
= acos(sin(0.592539)*sin(0.709186)+
cos(0.592539)*cos(0.709186)*cos(0.778708))
= acos(0.811790)
= 0.623585 radians
= 0.623585*180*60/pi=2144nm

ASKER

I never thought I would get such detailed answers. I can't thank you guys enough. Even though I don't really understand what you have written I have managed to knock something together which will give me the distance which I require. I can't tell you how happy I am that I have managed to achieve it. My head hurts now and my hair has been pulled out but its worth it!

I am having problems now where I can only do one result at a time. As I said in my original post I require to search the database to give results of the postcodes within the chosen area. I can't seem to do a repeat region on the distance to give me different distances between each postcode returned. How would I go about doing this? Also how would I query the database once I have got the distances set up correctly? Would I need to query the whole database and use a Show/Hide feature or could I use a Recordset? (I use DreamweaverMX by the way)

Thanks again for all your help and hopefully one day I can repay the favour.

Daniel

p.s I've just increased the point value! :-))

What type of database is it? and how are you accessing it...... If you are using ADO then it is very easy to find the points that are within the range......
You can simply use a sql query like this......

strSql="Select * from PostCode where (LONGITUDE between " & (long1 + 0.5) & " and " & (long1 - 0.5) & ") AND (LATITUDE between " & (lat1 + 0.5) & " and " & (lat1 - 0.5) & " ) "

This is taken from one of my project (Using Vb and ADO) where I needed to return the nearest waypoint from my refernce point......

This query will simply return the values that are within a specific rectangle around your Lat,Long (In this case about 50 km North, South, East or West)..... The most important point is that it returns the values that are inside a rectangle of 50 km and not in a circle of radius of 50 km.

This will simplify your calculations......Now you need not to check the distance of each point but only of those records that are returned by this query........

Suppose you have a function that checks the distance between two points then you will need to call it in a loop as follows....

for i= 1 to RecordSet.recordcount

Distance=CalculateDistance(Lat/Long)
if (Distance < 50 ) then
Call AddtoArray()
endif

next

Since I have no experience at all of developing in DreamWeaver so I cant help you in programming Databases in DreamWeaver..... but I assume it will be similar to VB.....

If you need the complete code then Please tell me I will post the code here so that you can better understand......

Hope this helps.......

Imran Arshad

I chose a bit a different approach to imarshad.

The following gives you a query where you must enter your current location (in QueryLatitude and QueryLongitude). It will show you all Areacodes sorted by distance with additional fields that tell you whether the distance is less than 5, 10, 20 or 50 miles.

This is tested on MS Access; I don't know which database you use and unfortunately I do not know how to integrate it with Dreamweaver.

It's a series of 4 queries.

Query qDistance1: Converts all distances from degrees to radians. Input is table tPostcode.

SELECT tPostcodes.Areacode, [Latitude]*3.1415926/180 AS LatRad, [Longitude]*3.1415926/180 AS LonRad, [QueryLatitude]*3.1415926/180 AS QLatRad, [QueryLongitude]*3.1415926/180 AS QLonRad, tPostcodes.Latitude, tPostcodes.Longitude
FROM tPostcodes;

Query qDistance2: Calculates the ArcCos of the distance between each postcode office and the coordinates (QueryLatitude, QueryLongitude) according to the formulae you were given.

SELECT qDistance1.Areacode, Sin([LatRad])*Sin([QLatRad])+Cos([LatRad])*Cos([QLatRad])*Cos([LonRad]-[QLonRad]) AS cosd
FROM qDistance1;

Query qDistance3: Calculates the distance in miles. This extra step is required because Access doesn't support the ACos function, so we have to calculate ACos through ATan.

SELECT qDistance2.Areacode, Abs(1.852*60*180/3.141526*2*Atn(Sqr((1-[cosd])/(1+[cosd])))) AS DistMiles
FROM qDistance2
ORDER BY Abs(1.852*60*180/3.141526*2*Atn(Sqr((1-[cosd])/(1+[cosd]))));

Query qDistance4: Finds out whether a post office is in the range of 5, 10, 20 or 50 miles within the post office.

SELECT qDistance3.Areacode, qDistance3.DistMiles, [DistMiles]<5 AS Dist05, [DistMiles]<10 AS Dist10, [DistMiles]<20 AS Dist20, [DistMiles]<50 AS Dist50
FROM qDistance3;

How to use this:
Execute qDistance4 by passing QueryLatitude and QueryLongitude as parameters. From the result set, choose those results that satisfy your criteria, i.e. Dist5, Dist10, Dist20 or Dist50 are set.

Integration into MX: You probably have to ask over in the Web Programming area.

Jan

ASKER

Thanks for that it has been most useful. I have got it working on Acess(Which is the database I am using) but the problem is how I intergrate Dreamweaver to query the database and give me the results I require. I will endeavor to find out and I will keep you posted.

Many thanks

Concerning integration: I'm not really familiar with database integration into Web sites.

It certainly depends on how your server environment looks like. If you use ASP, it shouldn't be very difficult; you might need to move the data to a server database (MS SQL Server or MySQL).

If you do NOT have a database on your server, it might be an option to code the entire thing in PHP or Perl, if your server supports that. I'm not sure about the performance, though. Probably needs some clever coding to make the thing fast enough. I can help with PHP coding, but unfortunately not with ASP.

How does your server environment look like?

Jan

Ups _ I just noticed that the Access queries return the distance in km, but not in miles. To fix this, change qDistance3 as follows:

SELECT qDistance2.Areacode, Abs(1.852/1.61*60*180/3.141526*2*Atn(Sqr((1-[cosd])/(1+[cosd])))) AS DistMiles
FROM qDistance2
ORDER BY Abs(1.852/1.61*60*180/3.141526*2*Atn(Sqr((1-[cosd])/(1+[cosd]))));

(A mile is approx. 1.61 km).

Jan

ASKER

Thanks for that I did need it in miles. Well spotted!

I have no worries about the server accepting the database. I have many on there already that work so its not a problem. Its just getting the information to and from the database which is the problem. I have posted a question within the Dreamweaver section of this website so I will have to wait and see.

Thank again for all your help, I really appreciate it.

Dan

ozo

You can avoid the trignometric calculations during the lookup by precomputing
x=cos(lat)*sin(lon),y=cos(lat)*cos(lon),z=sin(lat) for each zip code
then searching for sin(distance)² < (x1-x0)²+(y1-y0)²+(z1-z0)²

ASKER

Hello!

This is a question for Imran Arshad....

The SQL code below that you gave me that returns the postcodes in a rectangle of 50km works a treat but I would like to know if I change the 0.5 to 0.4 will it make the seach results be up to 40km etc or would I have to filter the SQL somehow to show the results up to 40km?

strSql="Select * from PostCode where (LONGITUDE between " & (long1 + 0.5) & " and " & (long1 - 0.5) & ") AND (LATITUDE between " & (lat1 + 0.5) & " and " & (lat1 - 0.5) & " ) "

Thanks again and I look forward to hearing from you

Regards

Dan

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Also note that these estimates are good enough for an accuracy of 100m lets say...... If you want more accuracy then you can use the formula that the article is refering to......

Imran Arshad

Oops I have found an error in my comment
>>This factor 0.5 is just a rough estimate of how many kilometers in one degree.......
should have been
This factor 0.5 is just a rough estimate of how many degrees in 50 km......

Have you got it working? or is there some problem.... Please do let me know....

ASKER

Hi Imaran

Sorry I haven't replied sooner but I have had been away on business. Thank you for your interest and help with my problem. I have looked into your answer regarding my last question and I have worked out how add different values into the SQL statement you provided so I can work out the distances of 50,40 30 etc. All I have to do implement it!

I would like to thank you so much for your help and I really appreciate it.

Regards

Dan

Glad to hear I could help you.......If you still feel any difficulty then please feel free to post here......

Imran Arshad

spikehalliwell

Can i ask where you got the database from?

Atiqi36

Hi,
I have been trying to do the same thing but I am not getting any results. I have SQL SERVER database with post code, x , y, longitutde, latitude and I am using the same query but its giving an empty result. Can anyone help me.
Thank you

wnickson

Just a thought, using strSql="Select * from PostCode where (LONGITUDE between " & (long1 + 0.47) & " and " & (long1 - 0.47) & ") AND (LATITUDE between " & (lat1 + 0.45) & " and " & (lat1 - 0.45) & " ) "

how would you then order by nearest first?