# Great Circle Distance Calculation

Great Circle distance calculation

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

Great Circle distance calculation

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

ADVERTISEMENT

Great Circle distance calculation

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

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

as

/*

fUNCTION: F_GREAT_CIRCLE_DISTANCE

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

*/

begin

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

end

Edit: corrected spelling

CODO ERGO SUM

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.

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?

Thanks.

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

calculations.

Oracle

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

SQLSERVER2005

= 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.

using:

VS 2005 BI Tools

SQLServer 2005

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.

Hi

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

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.

Thank

ST.

Hi,

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

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??

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 RelatedI 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.

thanks.

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?

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

as

begin

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

begin

return ( @Miles )

end

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 )

end

DECLARE @RC float

EXEC Distance '39.943762', '-78.122265', '32.334709', '-96.633546'

PRINT @RC /* in miles */

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 RelatedSee here www.merriampark.com/ld.htm for information about the algorithm. This page has a link (http://www.merriampark.com/ldtsql.htm) 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 (http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=502&lngWId=5), 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))

RETURNS int

AS

BEGIN

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

BEGIN

SELECT @s1_char = SUBSTRING(@s1, @i, 1), @c = @i, @cv0 = CAST(@i AS binary(2)), @j = 1

WHILE @j <= @s2_len

BEGIN

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

END

SELECT @cv1 = @cv0, @i = @i + 1

END

RETURN @c

END

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?

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 )

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

Possible?

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

Thanks

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

Ade

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

Ali

Ali

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.

help!!!

There is a great job board located at the employment section ofhttp://www.4charlesson.com . So pass it on to anyone looking for a job.

View Replies View Relatedhttp://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 RelatedDoes anyone know of any links to some great MS SQL sites I can check out to learn from?

Thanks for your thoughts.

Sincerely,

Tim

hello

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.

thanks

bala

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.

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 RelatedHas 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.

Thanks

Eric

Copyrights 2005-15 www.BigResource.com, All rights reserved