UK Postcode Manipulation

[thames1971]

Hello everyone,

I used to be a member of some FM forums a few years ago and I'm stuck / looking for some help.

I have just started a small recruitment agency specialising in Architecture and I'd like to be able to use the postcode information to the best of Filemaker's ability.

Has anyone created a postcode radius search calculation that can return records say within 10 / 20 / 50 miles of a certain postcode ?

I've downloaded several thousand postcodes with their longtitude/latitudes coordinates, but at this point I'm stuck.

What I'd ideally like to achieve would be to perform a search, say 20 miles from "HU17 8WD" and for filemaker to return all the client records I have in that catchment.

Any help would be greatly appreciates and I don't mind paying someone to create this as long as it doesn't cost the earth.

Many thanks

Karl

[BobWeaver]

Depending on how strict you want to be about the radius, the solution can vary from slow and complicated to relatively quick and easy. Here's why:

If you use a strict radius, then you need to calculate the distance between your target location, and all of the location records in the database in order to get the radius value to search. This results in a search on an unstored field which will be quite slow. This recalculation of all records must happen every time you pick a new target location.

On the other hand, if you relax your requirement and allow a box shaped area rather than a circular area, you can do a regular search, or even set up a relationship to bring up the results as quickly as you input the target location. The idea is to take the target latitude and longitude and add positive and negative increments to them to define the box boundary, these fields being: NorthLatitudeBounds, SouthLatitudeBounds, EastLongitudeBounds and WestLongitudeBounds. The increments should correspond approximately to the radius you want. Then, you either set up a relationship or a search with the following criteria:

Latitude <= NorthLatitudeBounds

Latitude >= SouthLatitudeBounds

Longitude <= EastLongitudeBounds

Longitude >= WestLongitudeBounds

If you want to get fancy, then you can take the found set of records and perform the radius calculation mentioned earlier, to trim the corners off the circle, so to speak. Doing that on the much smaller found set would likely be acceptably fast.

Once you decide which way you want to go, we can look at the details.

[Søren Dyhr]

Why isn't:

http://www.filemaker.com/help/FunctionsRef-316.html

...mentioned here?

--sd

[thames1971]

Hi Soren,

That sounds like it could be useful, at least until I can afford to get someone to build me a proper database

Would it be difficult to do ?

Karl

[thames1971]

This might actually help.

I have just imported 3624 UK postcodes into a new database.

4 fields:

1) Postcode e.g. AB10 7LE

2) Latitude e.g. 57.000000

3) Longitude e.g. 0.000000

4) Postcode ID

Is there a way to work with this and create searches from my other customers database ?

I don't need anything too accurate, just an approximation of which clients are 'close' to another postcode.

Karl

[BobWeaver]

Have a look at the example file I posted in the Samples forum:

http://fmforums.com/forum/showtopic.php?tid/194335/

This is a subject that has come up a few times in the past, and it seemed worthwhile putting something in the samples forum.

I tested it out using a partial list of Canadian postal codes so that I would have a test area that I'm familiar with. But, you should be able to load your own set of postal codes into the file and use it with minimal changes.

Edited March 29, 2008 by Guest

[thames1971]

Hello Bob,

Many thanks for your reply. I think this could potentially be very useful.

I have one or two brief questions about this. WOuld it be possible to email you off list ?

Regards,

Karl

[BobWeaver]

Assuming the questions are Filemaker related, I would really prefer that they be posted to the forum. You get the advantage of more people being able to answer (there's a lot of Filemaker stuff I can't answer), and the discussion benefits more people.

[BobWeaver]

I just uploaded a new version of the file with some improvements.

[thames1971]

Bob,

I have two files containing postcode information:

1)

Just leading in postcode but general full UK district coverage I think

AB10,391978,804275

AB11,394481,805116

AB12,389980,799273

Ca. 3000 records although not sure what the two figures are as they're not Lat and Long.

AND

2)

Full postcodes, but not entire database

57.000000,0.000000,AB10 7LE

57.145931,-2.165580,AB15 6YJ

57.153180,-2.221180,AB15 8UN

57.204940,-2.211980,AB21 0GU

Ca. 3000 records

How difficult would it be to modify your solution to use this data.

I've looked at a trial piece of software (screen at http://www.architecturalselect.com/assets/docs/screen.jpg

It would be pretty cool for it to return something like this as I can then search the remaining (not found records) online to find clients I've not got in my database.

Hope this makes sense.

Karl

[BobWeaver]

Hi Karl,

I don't think your first file will be much use unless you can figure out what the data means. Maybe some of the UK forum members can shed some light on it.

However, the second file should work just fine as is. You just import it into the PostCode table. You won't have the city names; so just leave those fields blank. Also, you need to modify or delete the validation calculation on the targetPostCode field (in the areasearch table).

After looking at the architecturalselect link that you posted, we need to clarify what you want to do. Do you want to find your customers that are within a target area, or do you want to find all postcodes that are within the target area? Or, do you want to do both? I have my example file set up to find customers, but an early test version that I made, simply found all postcodes within the area. The result portal was virtually identical to the sample screen in your link. Both functions can be easily implemented. To get it to find all postcodes, you simply use the same 4 parameter relationship that is currently set up between the areasearch table and the customer table. Implement that same relationship between the areasearch table and the postcode table, and that's it. In fact, there is no significant difference between the customer table and the postcode table. They are both lists of postcodes and lat/long coordinates. The only difference is that the customer table has a customer name field added.

Assuming that you already have a database set up, and depending on the complexity, it may make more sense to modify your existing file rather than adapt my example file. To do this, I would simply add the postcode table and areasearch table to your existing file; add any missing fields, set up the relationships, and then import the post code data. Your customer table no doubt already has a postcode field, but you will also need the lat/long fields as well. You can populate these by doing a lookup from the postcode table. For new customer records, or when editing them, you can set up an autoenter formula that automatically adds or updates the coordinates whenever the postcode field is modified. My example file does this; you can refer to the field definitions and relationships to see how I did this.

If you have more questions, let me know.