0

Here is a question that has me awake for a number of days now. The only conclusion I came up so far is that Red Bull does not usually help coders.

I have a scenario in my application where I have a couple of jobs (1 to 50). The job has an address and I have the following properties of an address: Postcode, Latitude, and Longitude.

I have a table of workers also and they too have addresses. While the jobs or workers are created through screens, I use Google Map queries to make sure the provided Postcode is valid and is in UK so all the addresses are verified.

I am using a scheduler control to display some workers on y-axis and a timeline on x-axis. Every job has a date and can only move vertically on the scheduler on the job’s date. The user selects a number of jobs and they are displayed in a basket close to the scheduler. The user can then drag and drop job against workers. All this is manual so it works.

My task is to automate this so that the user does not do much except just verifying and allotting the jobs. Therefore, I have to automate the process.

Every worker has a property called WillingMaximumDistanceTravel which is an integer representing miles, the worker is willing to travel for a job.

Now here is the headache: I have over 1500 workers. I have a utility function that uses Newtonsoft’s Json Convert to de-serialize a stream of response from Google Maps. I need to feed it Postcode A and B.

I also plan to introduce a new table to DB to store the distance finds as Postcode A, Postcode B, and Distance. Therefore, if I find myself comparing the same postcodes again, I will just retrieve the result from DB instead and slowly and eventually, I would no longer require bothering Google anymore as this table would be very comprehensive.

I cannot use the simple Haversine formula, as Crow-fly path is not my requirement here. The pain in this is that it takes a lot of time to calculate. Some workers can travel over 10 miles while some vary from 15 to 80. I have to take the first job from the list and run it with every applicable worker o the system! I was wondering that the UK postcode has a pattern to it. If we sort a list of UK postcodes, can we rough-estimate, from the alphanumeric pattern, where will we hit a 100-mile mark, a 200-mile mark and so on?

If anyone is interested in the code, please drop a line and I will paste it.

DoomerDGR8
  • 4,840
  • 6
  • 43
  • 91
  • Okay, I have this SQL Query. In order to search closeby, I'm adding and subtracting a 0.100000 to Lat and Lon: – DoomerDGR8 Apr 12 '11 at 13:48
  • Hi @HassanGulzar - Any luck on this query? Did you find any tool / file that maps a given UK postcode to the closest UK postcode? – Daniela Rodrigues Sep 10 '21 at 18:00
  • Hello, @DanielaRodrigues! I believe I made it so that I was calling the paid version of Google Maps API on demand instead of pre- calculating distance between each address in my system. Once the user was doing the scheduling, the system would solve the distance only for the addresses involved. Has been a very long time. – DoomerDGR8 Sep 10 '21 at 22:17

2 Answers2

1

(I work for Google, but I'm not speaking on behalf of Google. I have nothing to do with the maps API.)

I suspect this isn't a great situation for using the Google Maps API, simply because you're pushing so much data through. You really don't want to make that many requests, even if you could do so under the directions limits.

When I tackled something similar in a previous job, we bought into a locally-hosted maps API - but even that wasn't fast enough for this sort of work. We ended up precomputing the time to travel from the centroid of each postcode "area" (probably the wrong name for it, but the first part of the postcode followed by the first digit of the remainder, e.g. "SW1W 9" for "SW1W 9TQ") to every other area, storing the result in a giant table. I think we only did it for postcodes which were within 100 miles or something similar, to cut down on the amount of preprocessing.

Even then, a simple DB wasn't quite as fast as we wanted - so we stored the results in a giant file, with a single byte per source/destination pair. (We had a fixed sequence of source postcodes and target postcodes, so we didn't need to specify those.) At that point, computing a travel time consisted of:

  • Work out postcode areas (substring work)
  • Find the index of each postcode area within the sequence
  • Check if we'd loaded that part of the file (we lazy loaded for startup speed)
  • Load the row if necessary, and just access it otherwise

The bytes were on a sliding scale of accuracy, so for the first 60 minutes it was on a per-minute basis, then each extra value meant an extra 2 minutes, then 5 etc. (Those aren't the exact values, but it was something like that.)

When you've worked out "good candidates" you can ask an on-site API or the Google Maps API for more accurate directions for your exact postcodes, of course.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • I undertand the concern. That's why I wanted to R&D on this before making any Google Call. I guess I'll be scratching my head a bit more. – DoomerDGR8 Apr 12 '11 at 10:03
1

You want to look for a spatial-index or a space-filling-curve. A spatial index reduce the 2d problem to a 1d problem and recursivley subdivide the surface into smaller tiles but it is basically a reordering of the tiles. You can subdivide the surface either with an index or a string using 4 characters. The latter one can be useful to you because it let you query the string with all string operation hidden in the database engine. You want to look for Nick's spatial index quadtree hilbert-curve blog.

Micromega
  • 12,486
  • 7
  • 35
  • 72