Can someone help me using a stored procedure or function to passing my stored varchar lat;lng in table to individuals fields as float as Lat and Lng to use in radius search.
lanlng in Table
33.0000;15.222222
Thanks
views:
37answers:
2
A:
SQL Server Zipcode Latitude/Longitude proximity distance search might be of help to you, look at the functions on that page
SQLMenace
2010-10-06 14:38:40
I have similar procedures thanks, but my table is not as Latitude1 and Longitude1 but in one varchar field Latitude1;Longitude1 . How to passing in the stored prodecure split and cast on fly?
2010-10-06 15:04:47
A:
Are you just trying to split the string? If so:
declare @LatLng varchar(100)
set @LatLng = '33.0000;15.222222'
declare @Lat float
declare @Lng float
select @Lat = CAST(LEFT(@LatLng, charindex(';',@LatLng)-1) as float)
select @Lng = CAST(SUBSTRING(@LatLng, charindex(';',@LatLng)+1, LEN(@LatLng)-charindex(';',@LatLng)) as float)
select @Lat as Latitude, @Lng as Longitude
Joe Stefanelli
2010-10-06 15:01:40
Joe, this is correct for inputs values but what about using the column in a Where clause (GeoLatLng is the table column) like WHERE (@R*2*ATN2(SQRT((SIN((RADIANS(CAST(LEFT(GeoLatLng, charindex(';',GeoLatLng)-1) as float) - @lat2Compare)) / 2) no better way?
2010-10-06 15:20:22
You've probably got a couple of options here. Add computed columns to your table based on the string split functions. Write user defined functions to encapsulate the string split and use those functions in your where clause.
Joe Stefanelli
2010-10-06 16:01:31