Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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.

any one have solution for this. its very urgent.

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.

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.

SELECT tPostcodes.Areacode, [Latitude]*3.1415926/180 AS LatRad, [Longitude]*3.1415926/180 AS LonRad, [QueryLatitude]*3.1415926/

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

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*

FROM qDistance2

ORDER BY Abs(1.852*60*180/3.141526*

</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

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.

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

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

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) ?

http://www.experts-exchange.com/Databases/GIS_GPS/Q_21053014.html

Imran

vinspire,

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

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

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.

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

J

1021290.93080301 6229567.94784883

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

Imran

Back to your original question:

> 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 your comment (10/17/2004):

> 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

and

hi Vilia i didnt changed the question

10/15/2004 10:02PM

> i have keyCode,PostCode,xCoord,yC

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

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

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(

FROM qDistance2

ORDER BY Abs(60*180/3.141526*2*Atn(

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

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

i didnt get the answer using your query. i dont know why?

hi fulscher

in this query

SELECT tPostcodes.Areacode, [Latitude]*3.1415926/180 AS LatRad, [Longitude]*3.1415926/180 AS LonRad, [QueryLatitude]*3.1415926/

FROM tPostcodes;

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

i am using SQLServer 2000.

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

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

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.

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

Imran

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

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.

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?

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.

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)*C

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]*[

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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>

SELECT tPostcodes.Areacode, tPostcodes.Latitude*3.1415

SELECT qDistance1.Areacode, Sin([LatRad])*Sin([QLatRad

FROM qDistance1;

SELECT qDistance2.Areacode, Abs(60*180/3.141526*2*Atn(

FROM qDistance2

ORDER BY Abs(60*180/3.141526*2*Atn(

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.