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:
Or
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.
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

CROSS APPLY and OUTER APPLY clause in SQL-SERVER 2005

CROSS APPLY and OUTER APPLY clause in SQL-SERVER 2005
I will be introducing you with magical feature of Microsoft SQL-Server 2005, called CROSS APPLY and OUTER APPLY. If you want to get top 2 or 3 maximum inventory quantity of each product or all product of AdventureWorks database then it is bit difficult in SQL-Server 2000 as it doesn’t support CROSS APPLY or OUTER APPLY. You can use UDF or sub query for CROSS APPY or OUTER APPLY.
BTW, you can consider CROSS APPLY clause as INNER APPLY also as it will use outer (main) query as an input of subquery or function and will return the result set. In CROSS APPLY we will be getting full set of left side query (main query) and its corresponding value from right side query or function, if it is not available in right side query or function, it will return NULL.
First let us see use of CROSS APPLY:
We are going to create one function which will return top row from production.productinventory table of AdventureWorks database based on supplied productID and row number.
CREATE FUNCTION dbo.fn_GetMax_ProductItem(@Productid AS int, @rownum AS INT)
RETURNS TABLE
AS
RETURN
SELECT TOP(@rownum) *
FROM Production.ProductInventory
WHERE ProductID = @Productid
ORDER BY Quantity DESC
GO
You can run above UDF and test it with following query.
select * from dbo.fn_GetMax_ProductItem(1,2)
It will return top two row of productID 1.
Note: I strongly recommend not using “*” in query in live production environment due to performance issue, this is ok in this example.
As function is ready, we will start using that function in our CROSS APPLY example.
SELECT
p.ProductID,
p.Name,
p.ProductNumber,
pmi.Quantity,
pmi.locationID
FROM
Production.Product AS p
CROSS APPLY
dbo.fn_GetMax_ProductItem(p.productid, 2) AS pmi
WHERE
p.ProductID in (1,2,3,531,706)
ORDER BY
p.productid ASC
As soon as you run above query, you will get 8 rows. Two rows for each productID (1,2,3,531). You will not get any row for ProductID 706 as it is not available in Production.Product table. This proves that CROSS APPLY clause works like INNER APPLY.
Now let us tweak above query a bit with OUTER APPLY instead of CROSS APPLY.
SELECT
p.ProductID,
p.Name,
p.ProductNumber,
pmi.Quantity,
pmi.locationID
FROM
Production.Product AS p
OUTER APPLY
dbo.fn_GetMax_ProductItem(p.productid, 2) AS pmi
WHERE
p.ProductID in (1,2,3,531,706)
ORDER BY
p.productid ASC
This time you will get records for 706 productID as well but it will come with NULL in Quantity and LocationID.
Reference: Ritesh Shah