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

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:

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.

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

### One Response to “HTTP Endpoint with parameter function to calculates Longitude and Latitude distance in SQL-Server 2005”

1. dharani Says:

Hi,
I want to update multiple records at a time using sql server.

I am having two tables one is PROD_Table another one is PROD_SUB_Table.

PROD_Table having the columns like Prod_id,Title,BookId,Pubdate,Upd_Pubdate.

PROD_SUB_Table having the columns like Prod_id,PAR_Prod_id

I want to update the filed of Upd_Pubdate with least date of publication date in each product id.

I wrote this query but it is useful for only one production id.

update

prod_table set upd_pudate =
(

select min(upd_pubdate) from prod_table pm join prod_sub_table pr on pm.id = pr.id group by pr.par_id)

where id in( select pr.id from prod_sub_table pr join prod_table pm on pr.par_id = pm.id)

See the below table to better understand.
Ex:

PROD_Table

Prod_Id
Title
BookID
Pubdate
Upd_Pubdate

1234
RealEstate
Null
Null
Null

1234x
Null
Bid1
03/05/08
03/05/08

1234y
Null
Bid2
05/05/08
03/05/08

2234
Test
Null
Null
Null

2234x
Null
EID1
08/01/08
06/04/08

2234y
Null
EID2
06/04/08
06/04/08

PROD_SUB_Table

Prod_Id
Par_Prod_Id

1234x
1234

1234y
1234

2234x
2234

2234y
2234

Thanks,
Dharani