My current side project is cataloging Holocaust locations and plotting them on a map (http://www.holocaustmap.com). As there ultimately might be thousand of locations, I want to run checks to ensure that we aren't getting duplicate locations, i.e. pass in latitude and longitude values and see how many locations are within 10000 meters. It took me a LONG time to find the answer, but now that I've got it, I want to share it. This is for Microsoft SQL Server 2008, and latitude and longitude are stored as floats. lat1 and long1 are CF variables for the location being "checked" - in this case, Auschwitz I.
<cfset lat1 = 50.026199> <cfset lon1 = 19.2041> <cfquery name="geography" datasource="zzzz"> SELECT name, latitude, longitude, geography::Point(#lat1#, #lon1#, 4326).STDistance(geography::Point(latitude, longitude, 4326)) AS distancefrom FROM tblZZZZZZZ WHERE geography::Point(#lat1#, #lon1#, 4326).STDistance(geography::Point(latitude, longitude, 4326)) < 10000 ORDER BY distancefrom </cfquery> Resultset is: query DISTANCEFROM LATITUDELONGITUDE NAME 10 50.02619919.2041 Auschwitz I2 2294.13058246 50.03559919.1756 Auschwitz II - Birkenau 33254.3685978450.055321 19.199724 Auschwitz (Babice subcamp) 44803.19765558 50.03089119.270737Farbenindustrie LB5 5134.35369884 50.03633119.274012 Monowitz Concentration Camp 65589.1182748150.062317 19.258347 Auschwitz (Bobrek subcamp) 76288.17066753 50.03220419.291363Auschwitz III8 8824.96722369 50.10023919.24839 Auschwitz (CheÅmek subcamp) (Auschwitz I was already in the database, which is what I wanted as I was checking for dupes) Distance shown is in meters. Want to change the radius of locations within X meters of a specific point? Just change the value at the end of the WHERE clause. Anyway, I hope this helps someone. Thanks, Pete ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339414 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
