Postcode Distance Finder Help

condoug

Hi all

I have built a postcode distance finder for a client. In a nutshell, the user enters their own postcode and chooses a radius to search within (either 5, 10, 50 or 100 miles).

Now everything works, but I am just putting the feelers out to see if the way I have built it could be better done or adapted somewhat to gain best performance so any thoughts are very welcome.

I have obtained a paid for database of UK postcodes. The table contains 1.8 million rows. There is an index on the longitude, latitude and postcode fields.

I have a 2nd table which contains my "shops". Each shop has a 'postcode' and this field has an index on it.

When a user submits my form to search for shops within their postcode search, I do the following:

1) Use a mathematical SQL query (written by the postcode database providers) to obtain all records from the postcode database within the radius set
2) store all of those results into a huge array (a typical array is over 20,000 key/value pairs)
3) Loop through every key/value pair fo the array and query my database to see if a "Shop" has the same postcode
4) if it does, output it into a HTML results list

Now, immediately I know the issue here is that I am running a query for each result in the array but I am sure there would be a better way of doing it - can you help or advise?

Thanks

bradgrafelman

One way would be to let the DBMS keep track of the array of postcode matches. You could do a subquery for this, e.g.:

SELECT col1, col2
FROM shops
WHERE postcode IN
    (
    # SELECT stmnt here to find the postcodes in range
    )

Ashley_Sheridan

Could you not store the longitude and latitude of the shops also in that second table? That way, I'm sure you could mathematically compare the distances with a single query, and as it's based on numbers, can be indexed quite efficiently.

condoug

Thanks for the advice.

bradgrafelman, in your suggestion you said I could use a sub query to get the postcode matches. How could I do this if the postcodes are just stored in an array and not the database?

I tried a method of storing the results into a new table and INNER JOIN that table with the SHOPS table to get my results out but in some cases where the postcodes array has over 100,000 results I get the error 'MySQL has gone away' which after investigating seems like it is to do with the sheer volume of data I am trying to insert each time I do a search.

Therefore storing the array in a variable is the only way I can achieve what I need (at the moment anyway).

bradgrafelman

Sounds like you have an isssue with your DBMS config/tuning or you're doing something very wrong in your code or in your SQL query. I say this only because I work with daily reporting applications that generate a couple of queries that return anywhere between one and two million results.

While it's possible to leave the data stored in a PHP variable - you'd simply replace the subquery in the IN() clause with a comma-delimited list of matching values - I would still investigate why you can't store your data in your database. That is, after all, the whole reason they exist.

johanafm

condoug;11021961 wrote:
I tried a method of storing the results into a new table

If you didn't set indices on the proper field(s), then there's your answer.

sneakyimp

condoug;11021961 wrote:
I tried a method of storing the results into a new table and INNER JOIN that table with the SHOPS table to get my results out but in some cases where the postcodes array has over 100,000 results I get the error 'MySQL has gone away' which after investigating seems like it is to do with the sheer volume of data I am trying to insert each time I do a search.

Sounds to me like you might have neglected to index the appropriate columns in your table.

condoug;11021961 wrote:
1) Use a mathematical SQL query (written by the postcode database providers) to obtain all records from the postcode database within the radius set

If you have a query that fetches the postcodes in a particular radius, it should be a small matter to expand this query such that it does an INNER JOIN onto your shops table. Note that the postcode column in your shops table and the postcode column in the postcode database should ideally be of the same data type AND INDEXED.

Perhaps you could share some table structures and queries with us?