HTTP Endpoint with parameter function to calculates Longitude and Latitude distance in SQL-Server 2005

HTTP Endpoint with parameter function to calculates Longitude and Latitude distance in SQL-Server 2005:

I wrote an article for introduction of HTTP Endpoint (web service version of SQL-Server 2005) and have used simple stored procedure in it at:

http://ritesh-a-shah.blogspot.com/2009/03/http-endpoint-in-microsoft-sql-server.html

Or

http://riteshshah.wordpress.com/2009/03/06/http-endpoint-in-microsoft-sql-server-2005-implemented-in-visaul-studio-2008/

If you are new to HTTP Endpoint and yet to create your first HTP Endpoint than please do stop reading this article and go for any of the above link to know more about HTTP Endpoint.

You are reading this line so, now I am assuming that you are aware with basic concept of HTTP Endpoint. Let us move further:

We are going to create one function that will calculate Latitude and Longitude distance in miles. Note: I have searched mathematical formula for calculating distance from internet. I have just used that formula in my function just to demonstrate the use of parameter function in HTTP Endpoint.

CREATE FUNCTION DistanceOfLatLon(

@Latitude1 Float,

@Longitude1 Float,

@Latitude2 Float,

@Longitude2 Float

)

Returns Float

As

Begin

Return 3963.0*acos(

sin(@Latitude1/57.295779513082323)

* sin(@Latitude2/57.295779513082323)

+ cos(@Latitude1/57.295779513082323)

* cos(@Latitude2/57.295779513082323)

* cos((@Longitude2-@Longitude1)/57.295779513082323)

)

End

You can run above function directly in sql server and test its usage by:

SELECT [AdventureWorks].[dbo].[DistanceOfLatLon](33.0,-84.0,33.3,-84.0)

You will get result 20.750219476997 miles

Now we will implement this function in HTTP Endpoint.

CREATE ENDPOINT FindLatLong --This will create HTTP Endpoint

AUTHORIZATION sa --this is something absolutely optional authoraization for db owner

STATE = STARTED -- State could be STARTED, STOPPED and DISABLE

AS HTTP -- You can create HTTP or TCP endpoint

(

PATH = '/SQLLatLong', --virtual path, will be used in adding reference in web or windows app.

AUTHENTICATION = (INTEGRATED), --authentication type for endpoint

PORTS = (CLEAR), -- PORT coulbe be all (CLEAR) or may be SSL

SITE = 'localhost' --site name, in this case "localhost" as I am running it locally

)

FOR SOAP --protocol type

(

WEBMETHOD 'getLetLong' -- you can define more than one webmethod also to expose

(

NAME = '[AdventureWorks].[dbo].[DistanceOfLatLon]', SCHEMA = STANDARD,

FORMAT = ALL_RESULTS

),

WSDL = DEFAULT, --this will generate WSDL as per request

BATCHES = DISABLED --you could enable BATCHES but it becomes security thread

)

I am assuming that you have created Endpoint in Adventureworks database and you are using “localhost” in your endpoint as a site name. You can test Endpoint by running following URL in any of the internet browser.

http://localhost/SQLLatLong?WSDL

Now, you can implement your HTTP Endpoint in your Windows or Web application. Steps to use HTTP Endpoint in your application, has been given in my previous article. You can find that link at the top of this article.

After adding the reference in your windows application, you can write following code in your button click to print the value of calculation in your label on the same form.

private void button1_Click(object sender, EventArgs e)

{

localhost1.FindLatLong finding = new ForHTTPendPoint.localhost1.FindLatLong();

finding.Credentials = System.Net.CredentialCache.DefaultCredentials;

label1.Text = finding.getLetLong(33.0, -84.0, 33.3, -84.0).ToString();

}

Reference: Ritesh Shah