SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: A puzzle...

  1. #1
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Came across this SQL / Server Side scripting puzzle which I figure is quite interesting to stretch the brain with. Just wondering what anyone else thinks about it (I grinding out an answer slowly at the moment)?

    An SQL database table contains postcodes and grid reference x and y positions in metres. Another table contains records where one field is a valid postcode.

    A postcode is given as a parameter to a script. The required output from the script is the 10 nearest records from the second table which are geographically nearest to the supplied postcode, ordered nearest first and with the distance displayed in miles.

    Writing a query/script to achieve this, how would one approach this?

    Can it be done in a single SQL query using only ANSI standard SQL?

  2. #2
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's where I've got to so far...

    Call the table with the unique column of postcodes alongside two columns of X and Y values "Postcode_Grid"

    The second table, with postcodes along with other details (perhaps a list of customer addresses), call the "Address" table.

    Call the postcode that's been submitted from the script Postcode_Sbmt and the coordinates corresponding to that postcode X_Sbmt and Y_Sbmt.

    Taking Postcode_Sbmt from the script, first want to find it in the Postcode_Grid table and then gather it's coordinates; X_Sbmt and Y_Sbmt.

    Next sort the Postcode grid table into an order where the postcodes that are geographically nearest to our Postcode_Sbmt are listed first (i.e SORT BY Distance). Time for some Pythagoras, like this;

    Distance = SQRT ( ( ABS ( X_Sbmt - X_Sort ) ) **2 + ( ABS ( Y_Sbmt - Y_Sort ) ) **2 )

    where X_Sort and Y_Sort are coordinates of the other records in the Postcode_Grid table.

    Now set a limit of 10 records on this list, starting with the nearest, then take each postcode from this list and find it's matching postcode in the Address table.

    With the query / queries defined to do this, return the 10 records to an array in the script then display them in a table using a WHILE loop.


    How to turn that into a single query is like...

    Any ideas?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •