February 9, 2015 at 2:38 pm
Hi,
I have zip code database and i am trying to get the records based on 100/80/60/40 miles radius. Below the sample structure of my table
Create table ZipCodes(ID bigint primary key identity(1,1),ZipCode varchar(10),
Address varchar(4000), city varchar(50),state varchar(50),Latitude float,Longitude float)
If i pass the Zipcode as '10021' i need to get zipcodes and other details of my table which is 20 mile radius circle.
I Google through and got couple of article explains about using Haversine Formula. Also, following link has a function to calculate the distance.
seems, this article/function build before the sqlserver 2008 born. Is there any better way to achieve this? Any sample will be very appreciated.
thanks
February 9, 2015 at 2:54 pm
Being that that formula is basically just a mathematical calculation, there really wouldn't be much update to be done.
In your case you may want to change this to an inline table valued function (instead of a scalar), or even try the same algorithm in a CTE or view.
Also, if your table is a zip codes table, what is address doing there? Are the lats/longs specific to the address or a centre point in the zip code? And if the second, is finding the other zip codes with centre points within a 20 km "crow flies" radius of the centre point of the current zip code really what you want?
February 9, 2015 at 3:07 pm
Hi Nevyn,
thanks for your reply and am sorry that address is not part of my zipcode table. Basically, i need to search the shopping mall/stores based on zipcode. I have another table called "Malls_Stores" below the schema
Create table ZipCodes(ID bigint primary key identity(1,1),ZipCode varchar(10),
Address varchar(4000), city varchar(50),state varchar(50),Latitude float,Longitude float)
Create table Mall_Sores(StoreId bigint primary key identity(1,1),StoreName varchar(50),ZipCode varchar(10),
Address varchar(4000), city varchar(50),state varchar(50))
Mall_Sores table has 1 million records. If i pass '10001' as zip i need to look at 20 mile radius of the zipcode and list out the store names.
any sample please
February 9, 2015 at 5:03 pm
There's an iTVF form of Haversine function here:
Departures from Origins and Arrivals at Destinations[/url]
Note that this method of haversine does not take into account adjusted curvature of the Earth.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 9, 2015 at 5:57 pm
Hi Dwain,
thanks for your reply and i gone through link. Really it's very high level and am struggling how do i correlate that with my requirement. If possible could you please post some sample for my requirement would be much appreciated
February 9, 2015 at 6:07 pm
KGJ-Dev (2/9/2015)
Hi Dwain,thanks for your reply and i gone through link. Really it's very high level and am struggling how do i correlate that with my requirement. If possible could you please post some sample for my requirement would be much appreciated
Note that I didn't say the article addresses your requirement. Only that it contains a Haversine function.
Did you try searching Haversine on that page?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 9, 2015 at 7:02 pm
Yes Dwain, Is the below one you are asking?
CREATE FUNCTION HaversineDistance
(@OriginLat FLOAT
,@OriginLong FLOAT
,@DestLat FLOAT
,@DestLong FLOAT)
RETURNS TABLE WITH SCHEMABINDING
RETURN
SELECT Miles=CASE
-- Fudge the case when Lat/Long for Origin/Destination is the same to avoid
-- possibly getting a floating point error.
WHEN @OriginLat = @DestLat AND @OriginLong = @DestLong THEN CAST(0. AS FLOAT)
ELSE -- Replace 3960 with 6371 for KM instead of miles (radius of the Earth)
( 3960. * ACOS( COS( RADIANS( @OriginLat ) ) * COS( RADIANS( @DestLat ) ) *
COS( RADIANS( @DestLong ) - RADIANS( @OriginLong ) ) + SIN( RADIANS( @OriginLat ) ) *
SIN( RADIANS( @DestLat ) ) ) ) END
on this method they just calculating the distance.
not sure how do i correlate this with mine.
February 9, 2015 at 7:07 pm
KGJ-Dev (2/9/2015)
Yes Dwain, Is the below one you are asking?
CREATE FUNCTION HaversineDistance(@OriginLat FLOAT
,@OriginLong FLOAT
,@DestLat FLOAT
,@DestLong FLOAT)
RETURNS TABLE WITH SCHEMABINDING
RETURN
SELECT Miles=CASE
-- Fudge the case when Lat/Long for Origin/Destination is the same to avoid
-- possibly getting a floating point error.
WHEN @OriginLat = @DestLat AND @OriginLong = @DestLong THEN CAST(0. AS FLOAT)
ELSE -- Replace 3960 with 6371 for KM instead of miles (radius of the Earth)
( 3960. * ACOS( COS( RADIANS( @OriginLat ) ) * COS( RADIANS( @DestLat ) ) *
COS( RADIANS( @DestLong ) - RADIANS( @OriginLong ) ) + SIN( RADIANS( @OriginLat ) ) *
SIN( RADIANS( @DestLat ) ) ) ) END
on this method they just calculating the distance.
not sure how do i correlate this with mine.
That looks like it yes.
The correction I was referring has to do with latitude. Because the Earth is an oblate ellipsoid and not a sphere, there are corrections that can be applied to this method to make it more accurate depending on latitude. This is the formula for calculating the haversine distance on a sphere.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 9, 2015 at 7:13 pm
Yes i understand that. Even i tried the below on and produces distance in miles.
CREATE Function [dbo].[CalculateDistance]
(@Longitude1 Decimal(8,5),
@Latitude1 Decimal(8,5),
@Longitude2 Decimal(8,5),
@Latitude2 Decimal(8,5))
Returns Float
As
Begin
Declare @Temp Float
Set @Temp = sin(@Latitude1/57.2957795130823) * sin(@Latitude2/57.2957795130823) + cos(@Latitude1/57.2957795130823) * cos(@Latitude2/57.2957795130823) * cos(@Longitude2/57.2957795130823 - @Longitude1/57.2957795130823)
if @Temp > 1
Set @Temp = 1
Else If @Temp < -1
Set @Temp = -1
Return (3958.75586574 * acos(@Temp) )
End
but how do i can search my table based on this 20 mile radius and how this function will be useful to me as i already know what radius i can search for. Basically a function should return the min and maximum lang and latitude to write the query against my table.
February 9, 2015 at 7:19 pm
KGJ-Dev (2/9/2015)
but how do i can search my table based on this 20 mile radius and how this function will be useful to me as i already know what radius i can search for. Basically a function should return the min and maximum lang and latitude to write the query against my table.
You should probably be using the SQL spatial data types that were introduced in SQL Server 2008.
https://msdn.microsoft.com/en-us/library/bb964711.aspx
They've also provided a library of functions to do the distance calculations you need, and also to locate other coordinates occurring within a geofence (that's what you are trying to do - your geofence is a circle).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 9, 2015 at 9:13 pm
Hi Dwain,
thanks and i tried to understand the geography data type and i am trying to add new column in my table with geography datatype.
alter table Zipcode add Coordinates geography null
update ZipCode set Coordinates = 'geography::STGeomFromText(''POINT(' + Latitude + ',' + Longitude + ', 4326))'
but am getting error as below
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException: 24114: The label geography::STGeomFro in the input well-known text (WKT) is not valid. Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, or GEOMETRYCOLLECTION.
System.FormatException:
at Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType type)
at Microsoft.SqlServer.Types.OpenGisWktReader.Read(OpenGisType type, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)
.
The statement has been terminated.
any clue? am i doing anything wrong here
February 9, 2015 at 9:37 pm
KGJ-Dev (2/9/2015)
Hi Dwain,thanks and i tried to understand the geography data type and i am trying to add new column in my table with geography datatype.
alter table Zipcode add Coordinates geography nullupdate ZipCode set Coordinates = 'geography::STGeomFromText(''POINT(' + Latitude + ',' + Longitude + ', 4326))'
but am getting error as below
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException: 24114: The label geography::STGeomFro in the input well-known text (WKT) is not valid. Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, or GEOMETRYCOLLECTION.
System.FormatException:
at Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType type)
at Microsoft.SqlServer.Types.OpenGisWktReader.Read(OpenGisType type, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.Parse(SqlString s)
.
The statement has been terminated.
any clue? am i doing anything wrong here
Unfortunately I've never used the geography types, but perhaps someone else can help you with this.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 9, 2015 at 11:04 pm
Quick note, it is better to use the Point method.
😎
UPDATE Z set Coordinates = geography::Point(Latitude,Longitude ,4326)
FROM dbo.ZipCodes Z;
The spatial data types are implemented in SQL Server as CLR, which means that methods more or less replace normal operators in the code.
February 10, 2015 at 7:53 am
Wonderful, thanks eric your solution worked.
but, geography function takes time to respond. i have 50000 zipcode in my test table and used below query to get the result.
egin
DECLARE @geo1 geography
, @RadMiles INT
, @MaxMeters DECIMAL (12,2);
SET @RadMiles = 20; -- radius of area searched
SET @MaxMeters = @RadMiles *1609.344;
-- get coordinates of entered zipcode
SELECT @geo1 = Coordinates
FROM [Zipcode]
WHERE ZipCode = '10001';
SELECT ZipCode, Coordinates.Lat, Coordinates.Long,@geo1.STDistance(Coordinates)
FROM [ZipCode]
WHERE @geo1.STDistance(Coordinates) <= @MaxMeters;
END
it took 5 seconds to produce 142 rows . any suggestion to improve the speed of the functionality?
February 10, 2015 at 8:08 am
Check out this thread
😎
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply