Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BigMeat
Yak Posting Veteran
56 Posts |
 Posted - 2011-04-19 : 09:38:47
|
| Hi I am doing some html5 development and using the geolocation function which can identify where the user is. The browser will give me a Latitude and Longitude eg Lat = 51.833302 Long = -2.25I have a table that has lots of lats and longs of cities, what I want to do is find the nearest city according to the lat and long provided by user. How do I do this in TSQL, I cant use the equals funtion as it is unlikely the lat and long given by user is in my DB. But I just need to find the nearest lat and long in my db according to what user gives meMany thanks in advance |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2011-04-19 : 12:11:31
|
| First thing that comes to mind is adding a preset number to the Geo results and then using some sort of range lookup with "between". I've not done this so I would be curious to see what others come up with. Sounds cool if something solid can be put in place though!Hey, it compiles. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-19 : 12:18:57
|
| SQL Server 2008 introduced the geography data type (and others). You might want to investigate if you're using 2008:http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspxThere are methods available to find distance, intersections, and so on. You'd have to convert your data to geography, but this can probably be done by adding a new column of that type. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-04-19 : 14:27:01
|
| This may give you some ideas about how to go about this.Function F_FIND_SEARCH_LIMITShttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112369The returned values from function F_FIND_SEARCH_LIMITS can be used to setup a bounded search of a table containing min/max Latitude and Longitude values for applications that need to return values within a specified distance from a central point.Great Circle Distance Function - Haversine Formulahttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360This function computes the great circle distance in Kilometers using the Haversine formula distance calculation.CODO ERGO SUM |
|
|
|
BigMeat
Yak Posting Veteran
56 Posts |
Posted - 2011-04-19 : 17:04:43
|
| Thanks for all your posts, very helpfulYep im running SQL 2008 Web Edition so I like the Geography data type option. However I just created a Geography data type column in my db and tried to insert a value into the geography column and I keep getting a conversion error. I have stored my lat and longs as a Float i tried a simple insert fro my float oolumn to geography column and keep getting the following error. even if I try and insert roe egupdate tblAreas set LatGeography = LatFloat where id = 1which gives an error of Operand type clash: float is incompatible with geographyJust seems odd, LatFloat = 51.9888, I tried a convert(gepgraphy, LatFloat) but this also failedAny ideas? |
|
|
|
|
|
|
|
|
Get nearest lat and long, using HTML5 Geolocation