[thelist] Sorting Long and Lat in Mysql...

[David Miller]

My default option for geographic database fun is PostGis[1]... which is
awesome :)

Meanwhile, this thread[2] seems to be not a million miles away from what you
want (And actually relates to MySQL :)

tl;dr version:

SELECT latitude, longitude, SQRT( POW( 69.1 * ( latitude - [startlat]
) , 2 ) + POW( 69.1 * ( [startlng] - longitude ) * COS( latitude /
57.3 ) , 2 ) ) AS distance FROM foobar ORDER BY distance ASC

where [starlat] and [startlng] is the position where to start

messuring the distance.



[1] http://postgis.refractions.net/
[2] http://lists.mysql.com/mysql/204766

Love regards etc

David Miller
http://www.deadpansincerity.com
07854 880 883



On 17 November 2010 20:27, 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
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>