Great Circle Distance Calculation

Great Circle distance calculation
Is there any stored procedure or application that implements Great Circle distance calculation

View Replies


Great Circle Distance Calculation

Great Circle distance calculation
Is there any stored procedure or application that implements Great Circle distance calculation 

View Replies View Related

Great Circle Distance Function - Haversine Formula

This function computes the great circle distance in Kilometers using the Haversine formula distance calculation.

If you want it in miles, change the average radius of Earth to miles in the function.

create function dbo.F_GREAT_CIRCLE_DISTANCE
@Latitude1 float,
@Longitude1 float,
@Latitude2 float,
@Longitude2 float
returns float

Computes the Great Circle distance in kilometers
between two points on the Earth using the
Haversine formula distance calculation.

Input Parameters:
@Longitude1 - Longitude in degrees of point 1
@Latitude1 - Latitude in degrees of point 1
@Longitude2 - Longitude in degrees of point 2
@Latitude2 - Latitude in degrees of point 2

declare @radius float

declare @lon1 float
declare @lon2 float
declare @lat1 float
declare @lat2 float

declare @a float
declare @distance float

-- Sets average radius of Earth in Kilometers
set @radius = 6371.0E

-- Convert degrees to radians
set @lon1 = radians( @Longitude1 )
set @lon2 = radians( @Longitude2 )
set @lat1 = radians( @Latitude1 )
set @lat2 = radians( @Latitude2 )

set @a = sqrt(square(sin((@lat2-@lat1)/2.0E)) +
(cos(@lat1) * cos(@lat2) * square(sin((@lon2-@lon1)/2.0E))) )

set @distance =
@radius * ( 2.0E *asin(case when 1.0E < @a then 1.0E else @a end ))

return @distance


Edit: corrected spelling


View Replies View Related

Help W/ Distance Calculation Query

I'm trying to run a dyncamic query that returns all records within a specific distance of a certain point. The longitude and latitude of each record is stored in the database. The query is constructed from two dynamic variables $StartLatitude and $StartLongitude with represent the starting point.

SELECT UserID, ACOS(SIN($StartLatitude * PI() / 180) * SIN(Latitude * PI() / 180) + COS($StartLatitude * PI() / 180) * COS(Latitude * PI() / 180) * COS(($StartLongitude - Longitude) * PI() / 180)) * 180 / PI() * 60 * 1.1515 AS Distance
FROM HPN_Painters
HAVING (Distance <= 150)

It runs fine until I add the 'HAVING (Distance <= 150)' clause, in which I recieve the error: Invalid column name 'Distance' It seems that Distance cannot be referenced in the HAVING clause.

View Replies View Related

Job Fails, How Do I Reset Circle With Red X

When a job fails, the right pane of EM for Jobs show a circle with a red X to the left
of the job. Thus making it easy to find problems.

Is there a way to reset the job to a status that eliminates the circle with the red X,
without successfully reruning the job?


View Replies View Related

Converting Oracle Calculation To Sql Server 2005 Calculation

Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the



TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')


this is a sfar as I have got  with the sql version


= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )


visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.



VS 2005 BI Tools

SQLServer 2005



View Replies View Related

SQL Server - Green Circle With White Arrow Question

I know that this may be a stupid question but I have yet to find the exact answer. Within the SSMS the servers are displayed in the registered servers. I register the server and then connect successfully. What I don't understand is although the server is running or the sql server agent is running the green dot with the white arrow is completely white. I can click on the server and look at the databases so I know that the server is running. This happens on remote servers and servers here at our location. Does anyone know why the white dot is not green with a white arrow? I'm just curious to know why this is like that. Thanks for any help you can provide.

View Replies View Related

Nearest Distance

How do I get a nearest distance of a point? For example, I have two tables A and B and I want to find the nearest distance between the records of the two tables. In addition, one of the tables should also give me the distance. The data I have geo spatial data. Can this be done in SQL
Help will be appreciated

View Replies View Related

Clustering Distance

Hi !

My question may sound silly but I am new to data mining so I do not know much. Can I define a custom distance measure for a clustering algorithm ie. k-means ? I have created some clr utds and I want to try a clustering algorithm on them. Can I do this? How is the distance calculated ?

Sorry for my poor english.

View Replies View Related

Edit Distance

please, it is possible to know the edit distance used in the fuzzy lookup/grouping.
On this forum I read fuzzy lookup use 4-gram with fix size.
Does exist any document explaining how fuzzy lookup calculate the similarity? In other word, what kind of edit distance, algorithm is used by fuzzy lookup/grouping?
I hope I was enough clear with my poor english.
Thanks All

View Replies View Related

Mirroring Over Distance????

Is there a recommended practice for mirroring in regards to distance?  Is it best practice to mirror with both nodes at the same physical location and use another method for failing over to a remote location or can one just put the other node in the mirror a few thousand miles away?  I'm suspecting not. 

Any comments??

View Replies View Related

Distance Between Postal Codes

I'm looking to find out how I'd go about setting up a database where avisitor to my site could punch in their postal code, and find out how farthey are from another postal code. For example, AutoTrader has this featureI believe to tell you how far the vehicle is from you. Dating sites havethem so you can do proximity searches.Anyone have any ideas where I could start? I'm thinking the post office,but if anyone else has suggestions, I'm open to hear them.Thanks!

View Replies View Related

Cluster Euclidean Distance

I am new to data mining so please excuse my ignorance. Lets assume

- i have created a cluser model

- identified 3 clusters ( a, b, c)

- each record consists of 15 columns

- collecting new records( 15 variables) real time

what i would like to do is  plot these new records programmatically as i collect them realtime. I assume this new record will belong to one of these three clusters. I believe we can find the cluster this new record belongs to by ' SELECT Cluster()....' and distance from the center of the cluster by ClusterDistance(). To plot this on a 2-dimentional space i need (x, y).

ClusterDistance() could be Y but what will be X.



View Replies View Related

Database Mirrors Over Distance

Various posts have noted that mirroring over distance is not advisable or that either async connections should be used.

Are there any limits/recommendations i.e. if two datacenters are a couple of files part with 10GBs fibre links and <50ms response times would this be acceptable for high-availability mirroring?


View Replies View Related

Distance Between Two Points Lat/long

I have a user defined function, I want to determine the distance between the 2 points. I have it working but i'm having a problem getting to print.


Code Snippetcreate function dbo.Distance( @lat1 float , @long1 float , @lat2 float , @long2 float)
returns float



declare @DegToRad as float
declare @Ans as float
declare @Miles as float

set @DegToRad = 57.29577951
set @Ans = 0
set @Miles = 0

if @lat1 is null or @lat1 = 0 or @long1 is null or @long1 = 0 or @lat2 is
null or @lat2 = 0 or @long2 is null or @long2 = 0


return ( @Miles )


set @Ans = SIN(@lat1 / @DegToRad) * SIN(@lat2 / @DegToRad) + COS(@lat1 / @DegToRad ) * COS( @lat2 / @DegToRad ) * COS(ABS(@long2 - @long1 )/@DegToRad)

set @Miles = 3959 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans)

set @Miles = CEILING(@Miles)

return ( @Miles )


EXEC Distance '39.943762', '-78.122265', '32.334709', '-96.633546'
PRINT @RC /* in miles */

View Replies View Related

Haversine SQL Trouble - Distance Between Zip Codes

I am trying to use the haversine function to find the distance betweentwo points on a sphere, specifically two zip codes in my database. I'mneither horribly familiar with SQL syntax nor math equations :), so Iwas hoping I could get some help. Below is what I'm using and it is,as best as I can figure, the correct formula. It is not however,giving me correct results. Some are close, others don't seem right atall. Any ideas?SET @lat1 = RADIANS(@lat1)SET @log1 = RADIANS(@log1)SET @lat2 = RADIANS(@lat2)SET @log2 = RADIANS(@log2)SET @Dlat = ABS(@lat2 - @lat1)SET @Dlog = ABS(@log2 - @log1)SET @R = 3956 /*Approximate radius of earth in miles*/SET @A = SQUARE(SIN(@Dlat/2)) + COS(@lat1) * COS(@lat2) *SQUARE(SIN(@Dlog/2))SET @C = 2 * ATN2(SQRT(@A), SQRT(1 - @A))/*SET @C = 2 * ASIN(min(SQRT(@A))) Alternative calculation*/SET @distance = @R * @Cthnx,cjrsumner

View Replies View Related

Levenshtein Edit Distance Algorithm

See here for information about the algorithm. This page has a link ( to a T-SQL implementation by Joseph Gama: unfortunately, that function doesn't work. There is a debugged version in the also-referenced package of TSQL functions (, but this still has the fundamental problem that it only works on pairs of strings up to 49 characters.

CREATE FUNCTION edit_distance(@s1 nvarchar(3999), @s2 nvarchar(3999))
DECLARE @s1_len int, @s2_len int, @i int, @j int, @s1_char nchar, @c int, @c_temp int,
@cv0 varbinary(8000), @cv1 varbinary(8000)
SELECT @s1_len = LEN(@s1), @s2_len = LEN(@s2), @cv1 = 0x0000, @j = 1, @i = 1, @c = 0
WHILE @j <= @s2_len
SELECT @cv1 = @cv1 + CAST(@j AS binary(2)), @j = @j + 1
WHILE @i <= @s1_len
SELECT @s1_char = SUBSTRING(@s1, @i, 1), @c = @i, @cv0 = CAST(@i AS binary(2)), @j = 1
WHILE @j <= @s2_len
SET @c = @c + 1
SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j-1, 2) AS int) +
CASE WHEN @s1_char = SUBSTRING(@s2, @j, 1) THEN 0 ELSE 1 END
IF @c > @c_temp SET @c = @c_temp
SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j+1, 2) AS int)+1
IF @c > @c_temp SET @c = @c_temp
SELECT @cv0 = @cv0 + CAST(@c AS binary(2)), @j = @j + 1
SELECT @cv1 = @cv0, @i = @i + 1

View Replies View Related

Calculating Distance Based On Latitude And Longitude

I need to be able to take the latitude and logitude of two locations and compare then to determine the number of miles between each point.  It doesn't need to account for elevation, but assumes a flat plane with lat and long.

Does anyone have any algorithms in T-SQL to do this?

View Replies View Related

How To Control The Distance Between The Two Matrix? Or (matirx And Table )

hi  everyone:

the  report  show  two tables   two matrixs

how  can  i  control   the distance between them

I  want  to  set  the  same  distance between the table  and  matrix

or  (table and  table )




View Replies View Related

Any Distance Limited For Failover Clustering Solution?

Could I implement a failover cluster solution on the two DBs which are based in two different cities?

View Replies View Related

How To I Enter A Small Square Or A Small Circle In A NVARCHAR Field?

I want to store a small cirle in a text field. Can anyone tell me how I can enter it in ascii code.


View Replies View Related

Stored Procedure To Retrieve Zipcodes Within A Specified Zipcode And Distance

Hi All,
Does anyone have a Stored Procedure that works perfectly to retrieve all zipcodes within a specified zipcode and distance radius - a zipcode and radius is passed and the Store Procedure result shows all zipcodes that falls within that range.
Thanks in advance

View Replies View Related

Need Great Help

hi, and I appreciate your help.
I have the following table:

id shipmentid cust_id invoice# amount shipmentid vendorid invoice#amount
1 610 891 109012 22594.00 610 1586 17601 392.70
2 610 891 109012 22594.00 610 1586 17600 419.40
3 610 891 109012 22594.00 610 1586 17599 396.45

This table contains invoices for both customers and vendors. Both invoices have $amounts . The customer invoices are duplicates which resuts in duplicate amount. How can I remove the duplicate amount from the customer side and keep a distinc invoice for customers .

thanks for your help



View Replies View Related

SQL Hell....little Help Would Be Great!

hi, im currently on sql hell right now. im having a hard time learning this sql thingie....

...the thing is this: im currently using the book ASP.NET Unleashed and most of the examples there are on SQL. what i was trying to do before was convert everything to OleDb to fit the ms access which i have right now.

unfortunately, some of the codes seem not to work properly. maybe its because of im using OleDb...

so what i did was i downloaded the MSDE sp3 package and installed it on my PC. now that i have an sql server for my WebMatrix, i just dont know what to do next? i mean, where do i put the sql sample databases like northwind and pubs???

im really confused about this sql thing. i really hate it.


View Replies View Related

Great Job Board

There is a great job board located at the employment section of . So pass it on to anyone looking for a job.

View Replies View Related

Great New Application

http://www.iqub.comThis is a wicked program . great price too ... Check it out....IQub Browser Professional Edition is a feature-rich applicationproviding online multidimensional analysis. By connecting directly toMicrosoft's SQL Server MS-OLAP/Analysis Server cubes, or to localcubes, you can explore large volumes of summarized data quickly andefficiently. With Professional Edition you can create local cubecopies from MS-OLAP/Analysis Server cubes, perfect for company widedistribution or taking on the road.CheersChris

View Replies View Related

Great MS SQL Sites?

Does anyone know of any links to some great MS SQL sites I can check out to learn from?

Thanks for your thoughts.



View Replies View Related

Great Oppurtunity

Great oppurtunity for MCDBA candidates with 10+ years of exp.
OLAP and .NET exp. is big+
Its a long term project.pls send ur resume in a doc format.


View Replies View Related

Morning - Any Help Would Be Great

I have two servers both with different collation. Server A being SQL_Latin1_General_CP1_CI_AS and the live server and Server B being Latin1_General_CI_AS and a dev server. Now i have a load of data on the dev which i'm query to see if its on the live server.
select * from ServerA.Table1 where Col1 in
(select Col1 from ServerB.Table1)

I get this Error message.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

View Replies View Related

TARA Is Great

I am SQL server DBA for 4 years. ALways i am getting help from TARA and other SQL gurus. Salute to all

View Replies View Related

Great Plains

Has anyone found or know of a site or place where there is a community of Great Plains SRS Report writers.

At Convergence people spoke of a place to share and help each other.




View Replies View Related

Copyrights 2005-15, All rights reserved