0

Firstly I apologize for the Title! I couldn't think how to word it!

I have a database with all the area codes in the UK with their average latitudes and longitudes. Now I want to be able to calculate which area codes are within 'n' miles from another.

I tried running a massive calculation on my local host to work out all the distances and store them in a massive database, so all i have to do is look up the distances later, but it ran out of memory, and I'm wondering if that's the best way to go about it? Am I overlooking an easy way to calculate it on the fly?

So to make it clear, I want people to be able to enter an area code and a range in miles, and then get all the area codes within that range. The information i have available is a database with the fields:

| Postcode_ID | Pcode | GRID_N | GRID_E | Latitude | Longitude |

Adi Bradfield
  • 2,063
  • 1
  • 17
  • 27
  • 1
    Have a look at: http://stackoverflow.com/q/407989/2493918 – Markus Hofmann Jul 30 '13 at 16:20
  • @MarkusHofmann I can work out the distances, but working out which area codes are in range takes a very long time to calculate that way! – Adi Bradfield Jul 30 '13 at 16:22
  • Please see this answer http://stackoverflow.com/questions/8599200/calculate-distance-given-2-points-latitude-and-longitude – Vladimir Hraban Jul 30 '13 at 16:23
  • Have you read about the [Haversine Formula](http://www.go4expert.com/articles/haversine-formula-t339/) yet? – Markus Hofmann Jul 30 '13 at 16:23
  • @MarkusHofmann Yes that is the formula that I am using, ooh I've just realized that it can be rearranged to make it a lot nicer for the database I have! Okay I'll get my pad and pen and crunch some maths, see where it leads. I have a feeling I should be able to reduce it something like `if( ((lat1 - lat2) - (long1-long2)) < val ){ include in results }` ? – Adi Bradfield Jul 30 '13 at 16:30

2 Answers2

1

There are several methods to achieve what you want.

  1. Check out the "Haversine Formula" for distance calculation

  2. Have a look at Google Maps API examples on google as these may provide insight on how to solve the problem

  3. To store the I recommend using a PostgreSQL Database togehter with PostGIS

About PostGIS (quote from the site):

PostGIS is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL.

If you'll be able to use the above setup, working with the db data will be way easier.

 
This might also be useful:

PHP Postal (Zip) Code Range and Distance Calculation

Have your eyes roll over this and let me know what you think.

Good luck.

Markus Hofmann
  • 3,427
  • 4
  • 21
  • 31
  • I've also found this: https://www.dougv.com/2009/03/27/getting-all-zip-codes-in-a-given-radius-from-a-known-point-zip-code-via-php-and-mysql/ – Markus Hofmann Jul 30 '13 at 16:41
  • 1
    Thanks very much, I will explore all these routes, I'm particularly interested by the PostgreSQL Database, I've not heard of anything like that before! I'm currently reading the link you've commented, and I see how they have managed to optimize it is clever! – Adi Bradfield Jul 30 '13 at 17:38
  • I have managed to fix the memory issue on the script I'm running to create a whole database with pre-calculated values. It's going to take over a day to complete if it stays at its current rate! Do you think having this database will be a good idea if it's properly indexed? It should be faster than calculating the distances every time, or am I wrong? (There are going to be just short of 4,000,000 records in the table when it's complete) – Adi Bradfield Jul 30 '13 at 18:59
  • 1
    If you're able to save the processed data it'll definitely increase overall performance. Try to pre-process as much as you can. Use cronjobs/tabs to do regular background processing and do as little "on-demand" (per user request) processing as possible. So try to save the processed search results (range in miles, etc.) after a user created a new search. The Database should be able to handle this amount of data, but it depends on the hardware setup in regards to speed and space. – Markus Hofmann Jul 30 '13 at 19:32
0

A similar question has been answered here How to calculate distance from lat/long in php?

You need to calculate the radius given the distance and determine which additional zip codes are inside of the circle.

Community
  • 1
  • 1
Chris Campbell
  • 182
  • 1
  • 1
  • 6