# Great Circle Distance Calculation

Oct 15, 2007

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.

I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.

(so I can use measure security on calculations

as explained hereÂ Â )

SCOPE [Measures].[C];

THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);

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.

I am not very familiar with working with spatial data and I am currently trying to work out how to obtain some properties of a circle defined as a geometry data type. Specifically, I need to determine x and y co-ordinates for the centre point and the diameter of the circle.I have had a look at the MSDN reference for spatial data .

View 4 Replies View RelatedHi

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

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

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

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

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

Thanks for your thoughts.

Sincerely,

Tim

Im not getting data when I execute the below 0 Rows:

select mydate from [dbo].mytable

where convert(varchar, mydate,101) between '11/18/2013' and '02/08/2014'

However the below gives results...

select mydate from [dbo].mytable

where convert(varchar, mydate,101) between '01/01/2013' and '02/08/2014'

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 1 Replies View RelatedHope someone can solve my problem

I have a working version of the above installed on a win 2000 machine with SQL ver (MSDE2000A.exe)

I purchase a laptop with vista home premium installed on it

MSDE2000A.exe would not install hence I installed SQL Server Express I managed to install the server and connect but when trying to login through Great Plains it says that I need SQL version 7. I have done a little research and found that SQL 7 is not supported by Vista.

Which way do I go now ?????

Do I install a copy of xp, update Great Plains or wait for an updated version of SQL Server Express SP ???

Please can anyone help !!!

and please dont get too technical Im not an expert

Many Thanks

Mr Magoo

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 4 Replies View RelatedVarious 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 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.

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 */

Greetings,

We currently use GP 8.00 with the SQL Server 8.0.

We are trying to develop a view based on manfacturing orders - when a finished good is placed on hold and then calculating the componet parts that are on hold.

The calculation runs fine, we multiply end quanity x componet quanity found on the BOM. But we are not getting the correct componet item numbers to display.

Here is the syntax we are using:

SELECT DISTINCT

TOP 100 dbo.BM010115.QUANTITY_I, dbo.WO010032.ENDQTY_I, dbo.WO010032.ENDQTY_I * dbo.BM010115.QUANTITY_I AS QTY_REQHOLDMO,

dbo.BM010115.CPN_I, dbo.WO010032.MANUFACTUREORDER_I, dbo.WO010032.ITEMNMBR, dbo.WO010032.MANUFACTUREORDERST_I

FROM dbo.TEC_MOSumm RIGHT OUTER JOIN

dbo.WO010032 ON dbo.TEC_MOSumm.ITEMNMBR = dbo.WO010032.ITEMNMBR RIGHT OUTER JOIN

dbo.BM010115 ON dbo.WO010032.BOMCAT_I = dbo.BM010115.BOMCAT_I

WHERE (dbo.WO010032.MANUFACTUREORDERST_I = 4)

We have tried several different types of joins but still no luck. The upshot is we need to know that when a finished good is placed on hold, - how many of its componet parts are placed on hold.

Any assistance you can provide will be appreciated.

Sam

Here is my problem.

I am setting up a new server with Great Plains v8.0 (I have migrated all the databases from the other server, which has a working version of Great Plains v8.0 at the present moment). We are planning on getting rid of that server as it causing a lot of problems at the moment. I start the install of Great Plains v8.0 on the new server, install the components. After Great Plains v8.0 installs I go into Great Plains Utilities, and get the following message (I have installed SQL Server 2005 on the new server, and SQL Server 2000 is being used on the old server).

After I run Great Plains Utilities, I get the following error message -

"The stored procedure verifyServerVersion() of form duSQLinstall: 111

Pass Through SQL returned the following results: DBMS: 0, Great Plains: 0."

and when I click OK, it gives me another notification message -

"Your current SQL SERVER is not a support version

Req: Microsoft SQL Server 7.0

Act: Microsoft Server 2005

You need to upgrade to SQL Server 7.0 before continuing".

Why am I get that error message when I have SQL Server 2005 installed?

Can someone point me in the right direction.

Thanks

can anybody give me some nice links with a detailed explaination of the various replication errors and solution

thanks in advance

Jacx

We've been working hard with our teams here to get better/more/good information out to our users. We€™ve created a new a customized Windows Live search, that limits results to Books Online. Check this out, and make sure you let your contacts know to visit it and provide feedback:

http://search.live.com/macros/sql_server_user_education/booksonline

We want to generate as much traffic as possible here so that we can see if this is useful to our users. We€™d love to hear back from everyone we can!

DECLARE @Latitude NUMERIC(9, 6), @Longitude NUMERIC(9, 6)

DECLARE @MyLatitude NUMERIC(9, 6), @MyLongitude NUMERIC(9, 6)

Set @Latitude = 42.329596;

Set @Longitude = -83.709286;

Set @MyLatitude = 42.430883;

Set @MyLongitude = -82.923642;

Question: How do we calculate the distance in miles between the 2 points.

I am trying to write a piece of SQL which gives me a list of enquiries within 10 metre distance of a enquiry.

The idea is to identify possible duplicates.

Table: enquiry

Primary key: enquiry_number

Co-ordinates data fields: enquiry.enquiry_easting and enquiry.enquiry_northing.

I will need to self-search on the same table to find possible enquiries within 10m distance.

See 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 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 7 Replies View RelatedI want to store a small cirle in a text field. Can anyone tell me how I can enter it in ascii code.

Thanks

Hello, I have just started working with Microsoft SQL Server 7.0 and was wondering if anyone could recommend a great book for learning T-SQL. I was looking for something that would provide several examples on triggers and stored procedures.

View 5 Replies View RelatedDoes any one have information on any good Access Sites that are similar to this one, which could be helpful in completing some task. Thanks.

View 1 Replies View Related
Copyrights 2005-15 www.BigResource.com, All rights reserved