Arthur Wang's Blog
Follow me on
  • My General Blog
  • Software Development
    • Latest Articles on Software Development
    • Complete Resources for Developers >
      • Tutorial Information for Developers
      • .NET Developer Blogs
      • Developer Journals and Magazines
      • Developer's Tools
      • Database Development
      • ​Developer Conference and Events
  • Tech
    • Latest Articles on Technology Development
  • Health
  • Money
  • Services
    • Modern Website Design
    • Web Maintenance of Existing Websites Service
    • Corporate Business Consulting Service
  • About
  • Contact
  • Art
  • 中文部落格

How to Calculate Distance between addresses Latitude/Longitude Geo Coordinates

4/22/2014

0 Comments

 
Picture
The EASY way is to have .NET 4 Framework or above and reference System.Device.dll, and using namespace found in System.Device.Location. (You should see it if you are developing a Windows Phone program) There is a method called GetDistanceTo().

Here is a quick example:

var location1 = new GeoCoordinates(-29.83245, 31.04034);
var location2 = new GeoCoordinates(-51.39792, -0.12084);
double distance = location1 .GetDistanceTo(location2);

Please note that the method returns in km (kilometer).

Under the hood
The formulas used in the method is actually using the Haversine method. It assumes that the earth is a perfect sphere rather than an ellipsoid, so as a result, it has an error of less than 0.1 percent.  
Since it didn't account for altitude in its calculation, the distance is shorter than the driving distance.

Here is the LONG way:

    public class GeoCoordinate
    {
        public double Latitude { get; set; }
        public double Longitude { get; set; }
    }

    public class GeoCoordinateTool
    {
        public double Distance(GeoCoordinate loc1, GeoCoordinate loc2, int type)
        {
            //1- miles, other km
            //Use 3960 if you want miles; use 6371 if you want km
            double R = (type == 1) ? 3960 : 6371;          // R is earth radius.
            double dLat = this.toRadian(loc2.Latitude - loc1.Latitude);
            double dLon = this.toRadian(loc2.Longitude - loc1.Longitude);

            double a = Math.Sin(dLat / 2) * Math.Sin(dLat / 2) + Math.Cos(this.toRadian(loc1.Latitude)) * Math.Cos(this.toRadian(loc2.Latitude)) * Math.Sin(dLon / 2) * Math.Sin(dLon / 2);

            double c = 2 * Math.Asin(Math.Min(1, Math.Sqrt(a)));
            double d = R * c;

            return d;
        }

        private double toRadian(double val)
        {
            return (Math.PI / 180) * val;
        }
    }

How to use this class?

            GeoCoordinate g1 = new GeoCoordinate();
            GeoCoordinate g2 = new GeoCoordinate();

            g1.Latitude =-29.83245;
            g1.Longitude = 31.04034;

            g2.Latitude = -51.39792;
            g2.Longitude = -0.12084;

      var geotool = new GeoCoordinateTool();
      var distance = geotool.Distance(g1, g2, 1);
Here are technical references for the formulas:
  • The Haversine Formula (Assume Earth is spherical)
  • The Vincenty's Formula (More Accurate;Using Theoretical Ellipsoid for Earth)
search texts: easiest way to calculate a distance, example of using GetDistanceTo, without Google Maps API
0 Comments

Using CTE Common table Expression in MS SQL Server

4/18/2014

0 Comments

 
If you have not used CTE in any of your SQL server projects, you've been missing a good tool.  CTE, also known as Common Table Expression, is a new construct introduced early in Microsoft SQL Server 2005, and it becomes popular when SQL server 2008 came out since the performance had been improved in this version.  

Basically, we can use it to return a particular subset of data or aggregate data across many tables, and do more manipulations on the subset.  However, one might ask, it sounds like doing a subquery(See Fig 1).  Well, it is true, but CTE can be encapsulated and have multiple references to it, and unlike subquery, you have choices of using CTE as non-recursive just like subquery or recursive.  Best of all, it is more maintainable than subquery.


Fig 1: An example of subquery

SELECT Ord.SalesOrderID, Ord.OrderDate, 
 (SELECT MAX(OrdDet.UnitPrice) FROM AdventureWorks.Sales.SalesOrderDetail AS OrdDet WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice
FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS Ord


When using CTE, complex SQL statement can be made to easier to understand format that you or other developer can still follow the logic of the what it is intended to do.  From years of experience, you know that your requirement is gradually more complex and in this way, the query would be better maintainable.

Temporary Table vs CTE

In a way, CTE is similar to temporary table that it can be logical separated.  However, CTE is a temporary resultset that does not physically created in the Tempdb database like the temporary table does.  So if exists in memory and cannot be indexed.  In addition, table variable is also like the temporary table which is created in the Tempdb database.

Here is a simple example of CTE, where CTEReports is the name of your temporary resultset

;With CTEReports(EmpID, MgrID) AS
(
    Select EmployeeID, ManagerID
    From Employees
    Where ManagerID is NULL
)
Select * from CTEReports


Please note that you should always place a ";" semicolon in front of "With" statement as a good habit.

Simple Example of Recursive CTE
There are two blocks of code within the With statement called the Anchor block and Recursive block.

;WITH Numbers AS
(

    SELECT n = 1        <------- first block
    UNION ALL
    SELECT n + 1          <------- begins of second block
    FROM Numbers
    WHERE n+1 <= 10

)
select * from Numbers


The anchor block is what defined the temporary resultset, and the recursive block is the one using the resultset.
So first block has 1, and in the second block it increases by +1, and loop until it is less than or equals to 10.
Picture
Using MAXRECURSION value to prevent CTE going into infinite loop since you can set your recursion limit.

So in the last select statement, you can write:
Select * From Numbers OPTION (MAXRECURSION 8) when you want to throw an error when recursion goes beyond 8th recursion.

There are many examples on the web about this topic but I hope I have used the simplest examples to explain this useful tool.
0 Comments

    Arthur Wang

    @ArthurWangLA
    MCSD App Builder
    MCSD Web Applications
    ​Member of Windows Insider Program & HoloLens Developer Community & Dev Center Insider Program

    Over 17+ years of  experience in web-based software development & management.  Specialized in Microsoft technology with c# language and its architecture design.  MCSD, MCSE, Microsoft Specialist, MCP + Internet, and B.S. from UCLA

    Archives

    August 2018
    March 2018
    January 2017
    December 2016
    May 2016
    April 2016
    March 2016
    February 2016
    April 2014

    Categories

    All
    API
    Arduino
    ASP.NET
    Cognitive
    CSS
    Database
    Deep Learning
    DevOps
    Electronics
    Flexbox
    HTML5
    IoT
    Katana
    Machine Learning
    Management
    .NET
    .NET Core
    Neural Network
    OWIN
    Programming
    Programming Tools
    Recognition
    Security
    SQL Server
    UWP
    Visual Studio
    Web API
    Web Developer

    RSS Feed

    Latest Articles

© 2014-2020 ArthurWiz.com All Rights reserved. | Home | About |
Protected by Copyscape