Solved

Posted on 2004-10-15
1,785 Views
I have all postcodes in my access DB, I want to measure distances by giving the radius of some value and find all other postcodes in side that radius.in my access DB, i have keyCode,PostCode,xCoord,yCoord  only these fields. i dont know the Latitude Longitude values, i know only the x,y Coordinates.i am using vb to calculte this.

any one have solution for this. its very urgent.
0
Question by:vinspire
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 10
• 10
• 6
• +1

LVL 5

Expert Comment

ID: 12329193
The circle equitation is:
x² + y² = r² (r – radius)

First you need to create a query/view, based on a square around the circle.
Xmin = X – r
Xmax = X + r
Ymin = Y – r
Ymax = Y + r
This reduces number of rows we need to calculate.

SELECT *
FROM myTable
WHERE (
Xmin <= xCord and xCord <= Xmax
and
Ymin <= yCord and yCord <= Ymax
)

We have a point P(x, y) and radius (r) as criteria

For each query/view row we need to calculate:
(xCord - Px)² + (yCord – Py) ² <= r²
Rows that satisfy the formula above are inside the circle.

0

LVL 12

Expert Comment

ID: 12330275
The above calculation would be exact only if earth was flat. Unfortunately, it's not. The calculation gives you an approximation which is fine for short distances; for longer distances (>= 50 or 100 miles), you'll need something a bit more involved. You'll find all the maths in http://www.experts-exchange.com/Q_21053014.html.

Here's an extract from the above mentioned Q:

<quote>
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.

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.

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]))));

</quote>

The last step would be to find all post offices which are within a specific distance around a specific point:

SELECT * FROM qDistance3 WHERE DistMiles < [Distance in miles];

HTH, J

0

Author Comment

ID: 12333500
hi fulscher

i tried u r method. i am using MDB file.  i have POSTCODE,xCoord,yCoord fields.

but i tried with your calculations. but i didnt get proper answer.
please note that i have only the x & y Coordinates.

like                    xCoord                               yCoord
1021290.93080301              6229567.94784883

if you want i can send the mdb file to your mail address.
0

LVL 5

Expert Comment

ID: 12333789
> The above calculation would be exact only if earth was flat. Unfortunately, it's not.
> The calculation gives you an approximation which is fine for short distances;

The answer based on 2D calculation has been provided as per authors question, portion:
> i dont know the Latitude Longitude values, i know only the x,y Coordinates

Based on his info, I assume he is dealing with a small area where the approxiamtion could be used.

0

LVL 12

Expert Comment

ID: 12334335
Vilia - I see your point. I just understood this differently.

Vinspire - we would need to know what format the coordinates are in - i.e., how they are encoded. Any idea? Where does the data come from? Otherwise, it would help if you would post a sample here (but be sure to tell us the country - are you in the US?)

I wan't be around the next two days, so I can't look at the DB. Sorry.

J
0

Author Comment

ID: 12336253
hi guys

i am not in US. in France.  Its mainly for the Europe map Search. (not include UK).

search between 20 and 50miles.
and i need to search between 2 postcodes and get the result of postcodes in between those  postcodes (not in radius, for route) ?
0

LVL 13

Expert Comment

ID: 12345218
This is link to a question where I have helped a guy who had a similar problem.....It explains everything in detail......I hope it will be also helpful to you........
http://www.experts-exchange.com/Databases/GIS_GPS/Q_21053014.html

Imran
0

LVL 5

Expert Comment

ID: 12380376
vinspire,

are you satisfied with any of supplied answers, or you are lookong for the solution still?
0

Author Comment

ID: 12516206
hi sorry for the delay.

still i didnt find the proper solution for this.

i didnt get the proper result for this. i am using VB6 for this calculations.
i need some detailed help.
0

LVL 12

Expert Comment

ID: 12518149
vinspire,

you can send me the MDB file with the coordinates; I'll try to decode them and post the results here.

J
0

LVL 13

Expert Comment

ID: 12520656
xCoord                               yCoord
1021290.93080301              6229567.94784883

It seems like you have the Coordinates in UTM coordinate system?

Imran
0

LVL 5

Expert Comment

ID: 12524989
Hi Vinspire:

> Question Title: Radius Search..  with x,y
>
> I have all postcodes in my access DB, I want to measure
> distances by giving the radius of some value and find all
> other postcodes in side that radius.in my access DB

> and i need to search between 2 postcodes and get
> the result of postcodes in between those  postcodes
> (not in radius, for route)

It looks as you have CHANGED the question. So instead of radius you are looking for postal codes along the line between points A and B.

I do not know what is the regular procedure on Experts Exchange for the case. Probably you should close this Question and open another one, as the solution for your second question is going to be misleading for the original one.

I think you should contact one of moderators to help you sort the issue.

The solution for the second question is more easy that for the first one.

Vilia
0

Author Comment

ID: 12541284
hi fulscher  if you send me the email address i will send the MDB file.

and

hi Vilia i didnt changed the question

10/15/2004 10:02PM
> i have keyCode,PostCode,xCoord,yCoord  only these fields. i dont know the Latitude Longitude >values, i know only the x,y Coordinates.i am using vb to calculte this

sometimes i mentioned only some fields, while continue the question history.
0

LVL 12

Expert Comment

ID: 12541624
vinspire - my e-Mail address is in my profile (http://www.experts-exchange.com/M_95086.html).
0

Author Comment

ID: 12591713
hi
now i am out of station. after i came home i will send the file.
0

Author Comment

ID: 13417595
Hi  Jan Fülscher

i send the Excel file to your mail.

if possible check that one.
0

LVL 12

Expert Comment

ID: 13417815
vinspire,

The Latitude and Longitude values are in the data (columns D and E, respectively). They are in degrees, so can directly use the formulae mentioned.

For example. in your data, Nurnberg has the coordinates 49.4478 and 11.0683. If you check a map - for example, in MS Encarta, you'll find that Nurnberg is at 49°27' N and 11°03' E.

27 minutes are 27 / 60 = 0.45 centigrades and 03 minutes are 03/60 = 0.05 centigrades, i.e. the decimal coordinates of Nurnberg according to Encarta are 49.45° N and 11.05° E, which are about the same values as you have.

To use the data in your Excel table in the Access query posted earlier, you substitute the values in column D with QueryLatitude and the values in column E with QueryLongitude for the place in the center of your circle.

You'll probably want the distance in km, you would have to change the last of the 4 queries to

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

A set of utilities and tools in German to verify your calculations can be found at http://www.koordinaten.de/online/.

0

LVL 13

Assisted Solution

ID: 13418025
Since you have the LAtitudes and Longitudes in degree format then you can use this query directly.....

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...... Replace the PostCode by the name of your table and LONGITUDE by the name of Field having longitudes and LATITUDE by the field containing your latitude values.....

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 will return all the values that are within a rectangle of 50 km from your point of interest.....(Lat1,Long1).....
If you need to change the radius then play with the value 0.5.....

This factor 0.5 is just a rough estimate of how many kilometers in one degree.......
For you it will be best to extract your factor ( km / degree ) for your own placement on earth........Note that the this factor only depends upon your Latitude value.........
See this table for a rough estimate
http://www.zodiacal.com/tools/lat_table.htm

Now lets's assume that you are somewhere between 20-30 degree latitude (Note that Longitude is of no consequence)

Now you can take either 20 or 30 degree chart or take both of them and average them....

The table shows....

AT LATITUDE 20 DEGREES (NORTH OR SOUTH)

One degree of latitude =  110.70 km or  68.79 mi
One minute of latitude =    1.85 km or   1.15 mi
One second of latitude =   30.75 m  or 100.89 ft

One degree of longitude = 104.65 km or  65.02 mi
One minute of longitude =   1.74 km or   1.08 mi
One second of longitude =  29.07 m  or  95.37 ft

So the table says

110.70 km ------> 1 degree in latitude
1 km ------------> 1/110.70 degrees
50 km -----------> 1/110.7 * 50  = 0.452 degrees in Latitude
40 km -----------> 0.36

and so on........

and similarly for Longitude

104.65 km -------> 1 degree in longitude
1 km -------------> 1/104.65
50 km ------------> 1/104.65 * 50 =0.477
40 km ------------> 0.38

So now for 50 km your query should be

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

Similarly you can calculate all values in miles and for your specific region as well and for 50, 40, 30, 20 and 10 miles as well
For me 0.5 was a quite good estimate.......

If something is not clear please do tell me..... I will try to clarify it.......

Imran
0

Author Comment

ID: 13442217
hi Imran
i didnt get the answer using your query. i dont know why?

hi fulscher

in this query

FROM tPostcodes;

what should i pass the value in Latitude,Longitude  & in QueryLatitude,QueryLongitude ?

i am using SQLServer 2000.

0

LVL 13

Expert Comment

ID: 13442306
>> i didnt get the answer using your query. i dont know why?

What have you put in the lat1 and long1 variables?
They are supposed to be the location from where you are trying to find postcodes......
This is taken from a working project and it does work(In VB6 and Access)......

Imran
0

LVL 13

Expert Comment

ID: 13442321
and also have you changed the Table and field names according to your database??

Imran
0

LVL 12

Expert Comment

ID: 13444632
Actually - the problem is not so easy... My queries do not work because they find all post offices around a center place, but not along a line. Sorry - I misread the question.

Back to the drawing board: You're looking for all places within a certain distance from a line between two places, is that right? Like this?

d-          A         d+
|          ||       . |
|   .      ||         |
|          ||         |
|          ||         |
|       .  ||         |
|          ||         |
|          ||   .     |
|          ||         |
|          ||         |
|          ||         |
B
where A and B are the locations? So, all the points between d- and d+ should be found?

Jan
0

Author Comment

ID: 13446528
sorry i think i didnt explain the question properly.

hi Jan
its same like http://www.koordinaten.de/online/ , i want to give a postcode and radius in KM and i need the all postcodes within that radius. the same like the above url  http://www.koordinaten.de/online/

i have the Latitude(column D) and Longitude(Column  E) values in Excel sheet.
in my program i want to add a postcode (Column B) and add Radius value (ex: 10KM) after that serach, i need the all Postcodes(Column B) will display with in that radius circle.

hi imaran
i used to pass the values for the long1 and lat1. even that its gives only 0 records in the search result.
0

LVL 13

Expert Comment

ID: 13446601
>>i used to pass the values for the long1 and lat1. even that its gives only 0 records in the search result.

Quite strange..... Can you give me the database??. I will like to check it.... and test i with my query....

Imran
0

LVL 12

Accepted Solution

fulscher earned 400 total points
ID: 13447124
Ok - so we're back at the original question.

For the solution I suggested, you would put all the values as you have them in your spreadsheet. You would put the Latitude values in a column "Latitude" and the Longitude values in a column "Longitude".

If this was MS Access (my solution was in Access), you would create the following queries (reposted and cleaned up):

<sql>

FROM qDistance1;

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

SELECT * FROM qDistance3 WHERE DistKm < [Distance in km];
</sql>

where the location of the post office in the center of the radius has the coordinates [QueryLatitude] and [QueryLongitude] and the maximum distance is [Distance in km].

The result of the query will be all post offices in a circle around [QueryLatitude] and [QueryLongitude] which are less than [Distance in km] away. ordered by distance, i.e. nearest post offices come first.

What I'd suggest then is to enter the data and the queries into MS Access first and then use the migration wizard to transfer them to the SQL DB. If you can't do this, I can give it a try.
0

LVL 12

Expert Comment

ID: 13447130
Ups - sorry, something got lost. Read the above as:

"For the solution I suggested, you would put all the values as you have them in your spreadsheet into the SQL DB. You would put the Latitude values in a column "Latitude" and the Longitude values in a column "Longitude"."
0

Author Comment

ID: 13449812
hi jan

now its working fine. but i think its display the distance in less compare to driving distance. anyway thanks.

hi imran thanks for u r help too.
0

Author Comment

ID: 13449888
hi jan..
if i give the value distkm*2 then i will get the near by distance. why this happens? i have one more quetion in this (thats why i increased the points).   why every time we need to generate the query 3 queries. is that possible we can make the one time table and use select query?
0

LVL 12

Expert Comment

ID: 13450492
Which of the queries I've posted have you used?

There are two reasons for making this 3 queries:
- First. we need the results of one query in the second and third query.
- Second, it's easier to understand what's going on.
It's probably possible to combine this into one query, but you wan't get a big performance increase - it might even be slower.

0

LVL 12

Expert Comment

ID: 13453741
Ok, back to the drawing board.

Here's the formula from http://mathworld.wolfram.com/GreatCircle.html:

d = a*acos ( cos lat1 * cos lat2 * cos (long1 - long2) + sin(lat1) * sin(lat2) )

Since sql does not support acos, we need to use the following formula to replace it:

acos(x) = atn(sqrt(1-x^2) / x)

In SQL:

qPostcode1:
SELECT Cos([Latitude]/57.29578)*Cos([CenterLatitude]/57.29578)*Cos([Longitude]/57.29578-[CenterLongitude]/57.29578)+Sin([Latitude]/57.29578)*Sin([CenterLatitude]/57.29578) AS subexpr, Geocodes.Postcode, Geocodes.Town
FROM Geocodes;

CenterLatitude and CenterLongitude are the coordinates of the point in the center of your search radius.

In the second query, we calculate the rest:

qPostcode2:
SELECT qPostcode1.Postcode, qPostcode1.Town, 6378*Atn(Sqr(1-[subexpr]*[subexpr])/[subexpr]) AS DistKm
FROM qPostcode1;

And finally, we use a third query to evaluate the results:

qPostcode3:
SELECT qPostcode2.Postcode, qPostcode2.Town, qPostcode2.DistKm
FROM qPostcode2
WHERE qPostcode2.DistKm < 50
ORDER BY qPostcode2.DistKm

This selects all postcodes within a distance of 50 km or less around the chosen coordinates, the nearest postcodes being output first.

The results are within .5% of those of http://www.koordinaten.de/cgi-koord/2plz.cgi.

0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

I feel like more and more people want to know how to programmatically convert addresses into geospatial locations. So in this article, I will show you how you can do it with Bing Maps. I'm going to use PowerShell, which is a nice scripting language,…
Introduction This article is designed to assist GIS (Geographic Information System) and GPS (Global Positioning System) developers using ESRI ArcGIS and other spatial information management systems.   For the uninitiated the concept of projectio…
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…