postcode search problems

inspireme

Im doing a postcode search script, which is very ambitious for my level of skill. I have it working in a very very messy way, so am looking for advice on how to tidy it up.

Currently somebody types in a postcode/zipcode and a radius. It returns all the postcodes within the radius. Now i want to query the table for partial matches of the returned postcodes to find all records that are within the radius.

Is this the best way? Its the only way i can think of.

The other problem is my database has 6-7 digit postcodes, but the postcode database only does the area section (which is good enough for my needs) this is the reason i put the % wildcard at the end of the returned postcodes.

now, although i would never use the following in a commerical suituation I did it like this to test it would work, im fully aware of the code being shameful!

// the first bit of script is fine, it returns the array "finalResult"

if(is_array($finalResult)){
foreach($finalResult as $location){
$item[] =$location['Pcode']."%";
}

}

$sql_5 = "select * from idx_link where postcode LIKE '$item[0]' or postcode like '$item[1]' or postcode like '$item[2]'
$sitesfound = doQueryResult($sql_5);

I cut Sql_5 down for posting, it carries on with the or postcode like '$item[??]' as i can have up to 50 postcodes returned. This is what im not happy with. Any suggestions ? I would really like it to be automatically generated with the right numner of sql statments... or even do it a completly different way?

thanks 🙂

paulnaj

Hi, inspireme

What's the 'exact' criteria for two postcodes to be similar?

Paul.

inspireme

my data table contains the area selector codes. eg.

SG12
or
BG1

I have full postcodes in my listings table. The radius search will return an array of up to 50 parital postcodes which are lopped through to add the wildcard % at the end.

I really need a way of building up a query from this array of partial postcodes that will select all the listings that meet hte criteria .

For example :
the radius search may return the following codes : SY2%, SY3%, SY34%,

I need to select the postcodes that is like this SY23 5HJ from my listings table.

I hope this is clear, i find it hard to explain 🙂

thanks for replying.

RossC0

Hi,

Do you have any other information regarding the postcode / geographical data?

Firstly I would split the postcodes to the following fields:

Postcode_Area: SY
Postcode_District: 23
Postcode_Sector: 5
Postcode _Sub_Sector: H
Postcode_Unit: J
Postcode: SY23 5HJ

This will make any future queries / searches on your data more accessable. (You may not need the sub sector and the unit fields - but that depends on your situation.)

Then the query is easy i.e.:

SELECT Postcode
FROM idx_link
WHERE Postcode_Area = 'SY' AND Postcode_District = '"23"

Of course if you wanted to return postcodes within a certain distance you would need to store the distances between the center of the postcodes (which would probably be down to sector level).

If you had this in a table then returning all the postcodes within the radius would be one query.

inspireme

The way it works now is that the user enter the first part of a postcode and a radius. Using the postcode data I have i can select all postcode areas that fit withing the radius. So 50 miles aroud SY53 may return 40 postcode areas eg. BH,GH,WH.

Now these are returned in an array and any postcode that fits into the desctiption above should be selected from my listing table.

Even if i do split up the postcodes like you described i would have to make multiple queries to the database still so i could select all listings with postcodes that begin with : BH,GH,WH.

Correct?

I cant see anyway other than to hit the database with lots of queries. my main problem is constructing the last query to select all the listings that comply with the returned list of postcodes withing the 50mile radius.

thanks for all your help so far!

RossC0

How are you calculating whether or not a postcode is within the radius ?

Do you have a matrix table or are you doing a calculation?

If you had it in a matrix table - then you could do a join with the postcode table and return all the postcodes within the area.

inspireme

a bit of pythogoras id the way its owrked out.

All the table contains is :
Pcode
Latitude
Longitude

RossC0

If you get your data structures sorted in a logical way this process will be easy.

First things first what does pcode from the long / lat table represent - the full Postcode ?

Now these are returned in an array and any postcode that fits into the desctiption above should be selected from my listing table.

Do you do a single query to get all the postcodes for the array you get?

If so why not do a join to the postcode table I suggested and vola one query does the trick.

btw where did you get your postcode data from? PM me if you dont want to broadcast it.

inspireme

I tried doing a join but i cant seem to get it to work becuase the postcode data is not full, so i dont get exact matches for my listings table.

Sql dump of the postcode data : http://www.sloomedia.com/postcodes.sql

Heres what i tried :
$query = "SELECT .listings FROM postcodes,
listings WHERE (POW((69.1(lon-\"$lon\")
cos($lat/57.3)),\"2\") +
POW((69.1(lat-\"$lat\")),\"2\")) <
($radius*$radius) AND
Postcode.listings = Pcode.postcodes";

But the problem is :

SYBG does not match SYBG 5GH

can I use LIKE? If so how would i construct the above query ?

inspireme

I should have mentioned that sloomedia is not my site, just where i got the data.

they have a very nice distance script tutorial here : http://www.sloomedia.com/php_postcode_script.php

RossC0

Cool.

Here I set up a dummy table called postcodedata - which is based on the table I described above.

The sql to return all the stuff would be something like (pseudo sql):

SELECT p.postcode
FROM postcodedata pd
LEFT JOIN postcodes p ON CONCAT(pd.postcode_area,pd.postcode_district)=p.pcode
WHERE (p.lon > x AND p.lon< y) AND (p.lat > x AND p.lat< y);

RossC0

Was that any help?

inspireme

i have been working elsewhere for a few days so havent had a chance to reply. your method im sure is the right way to do this, i will be trying it tommorow. i think i will definatly need to split my postcode data up in the listings table for this to work. (into district code)

previously i was using like SY23% as a way to avoid doing this. Im not that advanced with mysql so im going to have to sit down and carefully work through your suggestion.

your help is very much appreciated.