6 Replies Latest reply on May 18, 2006 4:22 PM by Newsgroup_User

    user Search by Postcode

    Level 7
      Id like to display a list to customers of our closest distributors depending
      on a postcode that the end user enters.

      Id like to display the 20 closest matches to the post code they enter and
      then display the query results in order appearing with the closest matches
      first.

      Since that postcodes run in a somewhat numeric - geograpgical order, I guess
      the calculation would have to do some sought of calculation to get the
      difference on the query results.

      how would I go about this? Post_code is a 4 digit int.

      select * from
      postcodes
      where
      post_code = ???????????


        • 1. Re: user Search by Postcode
          Dan Bracuk Level 5
          Do you have the data to support what you are attempting?
          • 2. Re: user Search by Postcode
            Level 7
            Yep, I have a list of locations in australia with their postcode (a 4 digit
            number)

            "Dan Bracuk" <webforumsuser@macromedia.com> wrote in message
            news:e4getb$lui$1@forums.macromedia.com...
            > Do you have the data to support what you are attempting?


            • 3. Re: user Search by Postcode
              drforbin1970 Level 1
              Show us a few lines of sample data.
              • 4. Re: user Search by Postcode
                Dan Bracuk Level 5
                quote:

                Originally posted by: Newsgroup User
                Yep, I have a list of locations in australia with their postcode (a 4 digit
                number)


                Do you have any data regarding what postcodes are next to each other?
                • 5. Re: user Search by Postcode
                  dempster Level 1
                  In the US, there are databases available that provide the latitude and longitude of the geographic center of zip codes (our postal codes). With that data, you could calcuate distances to get the closest locations (there are a few different complex formulas to do this).

                  If you have that kind of data available in Australia, that's the way to go.

                  • 6. Re: user Search by Postcode
                    Level 7
                    I've worked out a quick and dirty solution that is not perfect but works
                    quite well.
                    Heres a sample of my data.

                    Postcode, Suburb, State, ID

                    1445 ROSEBERY NSW 146
                    1450 CAMPERDOWN NSW 147
                    1455 BOTANY NSW 148
                    1460 MASCOT NSW 149
                    1465 KENSINGTON NSW 150
                    1466 UNSW SYDNEY NSW 151
                    1470 DRUMMOYNE NSW 152
                    1475 MARRICKVILLE NSW 153
                    1480 KINGSGROVE NSW 154
                    1481 HURSTVILLE BC NSW 155
                    1485 KOGARAH NSW 156
                    1490 MIRANDA NSW 157
                    1495 CARINGBAH NSW 158
                    1499 SUTHERLAND NSW 159
                    1515 WEST CHATSWOOD NSW 160
                    1560 NORTHBRIDGE NSW 161
                    1565 MILSONS POINT NSW 162
                    1570 ARTARMON NSW 163
                    1585 CROWS NEST NSW 164

                    Basically i get the users postcode and take 20 from it to give me a
                    minimum_postcode
                    I then get the users postcode and add 20 to the value to give me a
                    maximum_postcode

                    I then return all results between min and max postcodes.

                    If recordsset =0

                    I add and take 50 to the postcode and search again.

                    if recordset still = 0 I increase to 100.

                    Im lucky enough in Australia that the postcodes run in quite a ordered
                    geographical manner.

                    From my testing it works well.



                    "dempster" <webforumsuser@macromedia.com> wrote in message
                    news:e4iao5$79v$1@forums.macromedia.com...
                    > In the US, there are databases available that provide the latitude and
                    > longitude of the geographic center of zip codes (our postal codes). With
                    > that
                    > data, you could calcuate distances to get the closest locations (there are
                    > a
                    > few different complex formulas to do this).
                    >
                    > If you have that kind of data available in Australia, that's the way to
                    > go.
                    >
                    >
                    >