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

https://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

Advertisements

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: