sql-server


radius search by latitude / longitude


I have found a bunch of answers for this question using mysql , but I wasn't able to convert anything into a query ms sql 2008 can use. I have a longitude and latitude column for each row in the database. I am going to have a latitude and longitude for where the user is. I want to be able to find all rows that are within x miles from the user's latitude/longitude. Also when trying to use other queries I found on SO I keep getting the error - 'pow' is not a recognized built-in function name. which is weird , because I'm pretty sure that I have used pow before in sql 2008. Any help with this would be greatly appreciated. So far this is the closest could come up with.
select * from tbl_MyTable
WHERE (
POW( ( 69.1 * ( Longitude - #longitude ) * cos( #latitude / 57.3 ) ) , 2 ) + POW( ( 69.1 * ( Latitude - #latitude ) ) , 2 )
) < ( 5 *5 );
Since you're on SQL 2008, consider using the native geospatial capabilities. You can do fancy things like:
Create a persisted computed column of geography type that represents your point.
Create a spatial index on the computed column. This will make things like yourPoint.STDistance(#otherPoint) <= #distance efficient
Like so:
alter table [yourTable] add [p] as geography::Point(Latitude, Longitude, 4326) persisted;
create spatial index [yourSpatialIndex] on [yourTable] ([p])
declare #Longitude float = <somevalue>, #Latitude float = <somevalue>;
declare #point = geography::Point(#Latitude, #Longitude, 4326);
declare #dist = <distance in meters>;
select * from [yourTable] where #point.STDistance([p]) <= #distance;
I think you want POWER not POW
http://msdn.microsoft.com/en-us/library/ms174276.aspx
DECLARE #CurrentLocation geography;
SET #CurrentLocation = geography::Point(12.822222, 80.222222, 4326)
SELECT * , Round (GeoLocation.STDistance(#CurrentLocation ),0) AS Distance FROM [Landmark]
WHERE GeoLocation.STDistance(#CurrentLocation )<= 2000 -- 2 Km
Wonderful Tutorial
http://www.sql-server-helper.com/sql-server-2008/convert-latitude-longitude-to-geography-point.aspx

Related Links

An Error when trying to connect to SQL Server
Informatica powercenter
Temporary table not created from dynamic query execution
How to process the data in the table, which is frequently inserted
Combine two columns and input the result in a different columns using SQL server
SQL Query to accept time difference
How to change my T-SQL query to overwrite a csv file rather than append data to it?
How to join multiple columns with only one foreign key using linq
Deleting millions on records while using log shipping - SQL Server
Restricting decimal places [duplicate]
Powershell T-SQL Update Statement Error in Function
How to close if case in SQL
Set RESTRICTED_USER on Azure SQL database
How to use highlight index parameter on ssrs
Suddenly msdb.dbo.sp_send_dbmail is unresponsive
Can you add to an SSIS object variable, appending?

Categories

HOME
pug
openssl
ncurses
fparsec
extract
codeblocks
openwrt
structuremap
nixos
squarespace
sign
typeahead
cloudflare
arraylist
prestodb
esql
sensu
ghost-inspector
tcpclient
tflearn
ui5
emv
autofill
apache-cloudstack
outsystems
reactivemongo
powershell-remoting
pace
axis-labels
haskell-pipes
agent
matlab-cvst
sapui
unity3d-editor
gmt
rxvt
autoresize
sqldatareader
inject
picasa
wcf-ria-services
ruby-2.0
foxit
dojox.charting
boost-hana
gradle-eclipse
multilingual-app-toolkit
license-key
lemoon
wikitext
matcaffe
uialertview
ffprobe
swift2.1
code-first-migrations
lexicographic
pisa
viewflipper
livechat
valence
jstack
unison
real-time-updates
cdata
ng-pattern
mstsc
dynamics-ax-2009
lync-server-2010
usn
sunspot-rails
plone-funnelweb
ninject-extensions
electronic-signature
port-scanning
dashcode
search-path
symbol-server
pureftpd
oggvorbis
revision
winsnmp

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App