2

I have a web site the I have created to sell items locally which if free for everyone to use. But I have now come accross a little problem! Now people form further afield are joining I want them to be able to search and lit all items by distance from them and/or see all items in their area.

I have been collecting everyones postcode and converting them to Easting and Northing coords not Latitude-longitude.

Is the best way to create a new recodset on the fly do the distance calc then sort by distance or is there a better way that will use less resources on the site?

Here is the answer

strSQL = "SELECT *, ( 3959 * acos( cos( radians( " & strMyLat & 
    ") ) * cos( radians(Latitude) ) * cos( radians(Longitude) - radians(" & strMyLng & 
    ") ) + sin( radians( " & strMyLat & 
    ") ) * sin( radians(Latitude) ) ) ) AS distance FROM TABLE WHERE HAVING distance < 3;" '## 3 Miles
Peter O.
  • 32,158
  • 14
  • 82
  • 96
Hutchyweb
  • 21
  • 4
  • I've been wanting to know this for a long time. Good question +1 – Aniket Inge Jan 31 '13 at 17:13
  • Personally, I'd prefer lat/long coordinates, as those are often more 'standard' (especially given common exposure through things like the various web-based maps). Also, depending on grid scale, you risk distortions. What's your database backend? Some RDBMSs have built-in geo types (or extensions), so you'd be able to leverage the full power of the database to do your selection/sorting. – Clockwork-Muse Jan 31 '13 at 17:28
  • Here is a similar post using PHP tho: http://stackoverflow.com/questions/2296087/using-php-and-google-maps-api-to-work-out-distance-between-2-post-codes-uk – Kaf Jan 31 '13 at 17:31
  • Thanks for your reply. The backend is MySQL, the reason I use easting and northing is because Royal Mail do a free export for all the postcodes in the uk against E&N. forgot to add I have all the calculations I need to get the distances just need a way to dynamically list by nearest. – Hutchyweb Jan 31 '13 at 18:16

3 Answers3

0

To answer your question:

I did almost the same thing last year, but with lat/long. And had the distances calculated real time. The distances are calculated using strait math, so it's fast and not a lot of resources are used.
Not familiar with Easting and Northing coords, so it may, or may not, be faster to first permanently convert them to lat/long in your DB, or some other coordinate system.

Now, if you're determined to make the query go as fast as possible, and you're dealing with a very small list of postal codes, you can create a lookup table that has the distance between any 2 postal codes already calculated.
However the larger your list, the more this becomes unwieldy. For example, according to Wikipedia, there are about 1,700,000 postal codes in the UK, so such a table would need the square of that, or 2,890,000,000,000 records.

Good Luck!

P.s. Here's the formula for finding the distance between 2 Easting/Northing coords: 

d = squareroot(square(E1-E2)+square(N1-N2))/1000

The 1000 is assuming your coords are in meters and you want the answer in km. Otherwise, adjust this number accordingly.

Tom Collins
  • 4,069
  • 2
  • 20
  • 36
  • 1,755,212 I'm currently importing them in to my db. Thanks. As I said above I'm going th try MySQL geo stuff to do the biz I'll post the code when I'm done – Hutchyweb Jan 31 '13 at 21:52
  • Looks like it's faster to keep it in Easting and Northing coords. This way you won't have to convert the lat/lon to radials during the math. – Tom Collins Feb 01 '13 at 00:04
0

Bit Late to the party, but UK Eastings/Northings are just a 1km by 1km grid overlaid on the country, so the maths to get the distance between two points is just Pythagoras theorem where you want to get the hypotenuse from the other two sides.

Following pseudocode shows the maths:

DeltaX = X1-X2 --the length of side 1
DeltaY = Y1-Y2 --the length of side 2
Hypotenuse = SQRT(DeltaX^2 + DeltaY^2)
Andrew - Eversight Ltd
  • 347
  • 1
  • 4
-1

Easting and northing is not the way to go.

You must first convert the coordinates (whatever they are like Easting , Northing, etc) to latitude, longitude decimal degrees WGS84.
This is the cooridnate system that the whole world uses, and now you can start calculation:

Then simply calculate the distance between two lat,lon pairs, e.g with the haversine-Distance formula (see Google, or Wiki).

AlexWien
  • 28,470
  • 6
  • 53
  • 83
  • Thanks for your reply, I have to do this on the fly and on of the previous comments have given me food for thought using MySQL to do the calls. – Hutchyweb Jan 31 '13 at 21:47
  • This has nothing to do with MySQl: you need the coordinates in lat lon degrees WGS84, like you enter and receive on google maps or google earth. If you have a DB with Easting Northing you shoul dconvert it, to lat / lon WGS84 – AlexWien Jan 31 '13 at 21:49
  • The Haversine-Distance formula first converts the lat/lons to a unit of distance, and then computes the distance between the 2 points. The Easting and Northing coords are already in a unit of distance, so that's less math that needs to be done. – Tom Collins Feb 01 '13 at 00:16