[devtalk] Sorting long/lat within MySql

[Ross Clutterbuck]

I've been struggling with SQL a lot more basic than that recently so I can't
offer much query help.

But as an idea, couldn't you add an extra 2 fields to the table to indicate
North/South and East/West and have them populated through code - i.e. the
lat and long goes through a quick algorith to determine if it's n/w and e/w
and the result is added to the db. Then you wouldn't need to calcuate any
minus or anything for the SQL query - when you SELECT the lot you could add
extra ORDER BY to automatically sort them north-to-south and east-to-west.

Ross


On 17 November 2010 20:39, Tris <beertastic at gmail.com> wrote:

> Hey all...
> I've been tasked with a global search project.
> They want to search a database of pictures, all with long/lat
> references storied per image in a mysql DB.
>
> However, their search criteria is a little specific.
>
> A simplified table structure is:
> `id` INT auto inc primary
> `imagePath` varchar 255
> `long` DECIMAL 12,7
> `lat` DECIMAL 12,7
>
> I need to provide the SQL statement with a start long/lat position.
> They then want to receive 34 images, going south from their initial
> long reference.. once they get to -90 (the bottom of the earth) to pop
> over 1 degree to the right/East and search up to the north to 90 and
> repeat until 34 images are found.
>
> What's confusing me is how to calculate the minus to plus changes..
> ensuring that we loop up and down along the earths 'long' reference.
>
> Has anyone done this before..?
> I'd LOVE to do it in a single query...
>
> Ok, back to google!!
>
> Thoughts...
>
> Tris...
> --
>
>
> ==========================
>
> "The only people who don't make mistakes are those who don't do anything"
>
> Give a man a fish and he'll feed himself for a day.
> Give a man a religion and he'll starve to death praying for a fish.
> Anon
>
> `We are what we pretend to be, so we must be careful what we pretend to
> be.`
> Kurt Vonnegut
>
> `When a person can no longer laugh at himself, it is time for others
> to laugh at him.`
> Thomas Szasz
> _______________________________________________
> devtalk mailing list
> devtalk at lists.preshweb.co.uk
> http://lists.preshweb.co.uk/mailman/listinfo/devtalk
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.preshweb.co.uk/pipermail/devtalk/attachments/20101118/a0a727cf/attachment.html>