5 Replies Latest reply on Nov 28, 2013 2:15 AM by haighd2

    Relationship based on postcode data

    haighd2

      Hi I wonder if anyone can help,

       

      We have a filemaker solution used to create reports with a subject property address field. I have created a calculated field that picks off the post code of the subject property address so that we have a postcode field.

      Screen shot 2013-11-27 at 13.58.35.png

      What I am looking for is a way to know which local authority this address is in, based on the post code.

       

      I have downloaded a list of all postcodes in the country (List 1 ) which is in roughly 50 separate excel files (csv) I also have a list (list 2) of all local authority areas in the country. List 2 has unique ID numbers for each local authority. List 1 has a collumn with a correspnding code to let you know which authority that aprtiular post code is in.

       

      list 1

      Screen shot 2013-11-27 at 14.05.10.png

      list 2

      Screen shot 2013-11-27 at 14.11.05.png

       

      I have created a filemaker sheet (file I guess you call it!) which when viewed in table view has two columns (fields) one with postcodes in and one with the relevant code. This creates about 1.7 million records one for each postcode and as a result when you attempt to search the list the system takes about 3 mins per search to return a result. I was intending to have a relationship between this and our filemaker solution. Is there a way of setting up a relational database without having a record for every individual postcode?

       

      Generally I need a better system to generate a field in our solution that states the name of the relevant local authority. Open to any suggestions?

       

      Any help would be appreciated.

       

      Cheers

       

      Dom

        • 1. Re: Relationship based on postcode data
          danielfarnan

          Hi Dom.

           

          It's not clear to me what your FileMaker table contains - I am assuming that it has the contents of list 1 (all 50 Excel files), is this correct?

           

          The time taken for the search seems excessively slow to me; I would check to see if indexing has been turned on for the field(s) you are checking with the search.

           

           

          My suggestion is to add another table to your database file, tblAuthorities, which you can create by importing the file shown in your list 2 image. In your relationship graph, create a relationship between your two tables by dragging the authority ID field from your postcodes table to the authority ID field in tblAuthorities.

           

          Note that I am assuming your postcodes table has data in the following format:

           

          postcode     ??     ??????     ??????     AuthorityID

          -------------     -----     -----------     ---------     ------------------

          RH1 1AD     10     528367     150863     E920000001

          RH1 1AD     10     528367     150863     E190000002

          RH1 1AD     10     528367     150863     E180000008

          RH1 1AD     10     528367     150863     E100000030

          RH1 1AD     10     528367     150863     E070000211

          RH1 1AD     10     528367     150863     E050007340

           

           

          Also note that I am Australian, don't understand the UK postcode system, and may have things confused - a description of the fields shown in the list 1 file would probably add some clarity.

           

          Regards,

           

          Daniel

          • 2. Re: Relationship based on postcode data
            usbc

            Daniel, My first question has to be: Is that the only source of data available for your mission ?

            I ask because, as I imagine you know, there are only 121 Postal Area which get sub divided into Post Towns and further broken down into Post Districts.

            You don't really say what the "Authority" controls but I'm guessing the governing council. The post code system in its totality is for sorting mail onto the delivery trucks. The "Authority" surely doesn't break down to the door step. I suspect that if you parse the Postal Code (the one you would write on an envelope) down to the first level (121) and second level (about 21 per) you would have a manageable method to identify the "Authority".

             

            You obviously also have the name of the town so you could parse that out as you did with the postal code. Those two fields concatenated with an underscore might very well serve as a key for a lookup for the Authority.

             

            Either way or any way I think there is a better data source out there for you.

            Lastly, you could get/post from a website like:

            http://local.direct.gov.uk/LDGRedirect/Start.do?searchtype=1&text=RH1%201AD%20&LGSL=0&LGIL=0&Style=&mode=1.1

            That one will cough up the answer on the fly as you enter a property into your system. Notice that I used one of your sample postal codes in the url (street and town is stronger).

            Hope this helps (a Yank who works in the UK on occasion)

            1 of 1 people found this helpful
            • 3. Re: Relationship based on postcode data
              LyndsayHowarth

              Hi Daniel,

               

              Whilst the scale is bigger than in Australia, the problem is the same. I don't see a problem with 1.7 million records merged so long as you are only seeing them via a relationship.

               

              Each region in Australia which shares the same postcode can have many towns and one is the central 'authority'. WHilst this might be tolerable for mail sorting it is a nightmare for mapping or things like Facebook where they define where you live by the main town not the town you actually live in.

               

              I wish there were more postcodes as potentially our mail would be quicker in Regional Australia. When I ordered my passport it was returned to my 'local postoffice' but really the main regional one. They sat on it for 2 weeks then returned it to the Immigration department because the notification I got was sent to a non-existant address... a non-existant street in the main town not the real street in the town where I live.

              This all works fine in the city where sometimes a suburb has more than one postcode but when you are talking about several hundred square km for one postcode... it makes it pretty rediculous.

               

              - Lyndsay

              • 4. Re: Relationship based on postcode data
                haighd2

                Thank you all for the responses,

                 

                I had thought about ‘parsing’ (you learn something everyday) the first 3 or 4 digits in the post code however the problem with that is local authorities (district or borough councils) have areas that do not match with post code areas. For example postcodes that start with RH* * can be in either Molevalley district or Reigate and Banstead district.

                 

                I will look for a different source of data.

                 

                I have thought  about setting up a script that pastes a post code into a web site like the one you suggest or http://www.planningportal.gov.uk/wps/portal/genpub_LocalInformation?docRef=LocalInformation&scope=202&langid=0 however this seems like a  clunky way out and ideally we would like the process to happen in the background rather than in the foremost browser window.

                • 5. Re: Relationship based on postcode data
                  haighd2

                  Daniel,

                   

                  Thanks for the response.

                   

                  Correct the filemaker table contains all 50 excel files worth.

                   

                  The below is what the table looks like:

                   

                  Screen shot 2013-11-28 at 10.05.13.png

                   

                  The speed of a find etc has completely changed since o originally imported that data, Perhaps it was a hardware problem when I was experimenting with it before?

                   

                  What you suggest is what I was going to try befire I was confronted with the slow down problems I will work on it today