CODEWITHSUNDEEP
sqlentity-frameworkspatialgeography
AAsk
AAsk

Reputation: 1491

Getting different answers using DbGeography and SqlGeography: Why?

I am using Entity Framework v6.1.3 ( DbGeography) and 14.0.314.76 (SqlGeography). Both of these are the latest versions.

DbGeography code

        public static double GetDistance(double p1Latitude, double p1Longitude, double p2Latitude, double p2Longitude, int SRID = 4326)
    {
        System.Data.Entity.Spatial.DbGeography p1 = System.Data.Entity.Spatial.DbGeography.FromText(String.Format("POINT({0} {1} {2})", p1Latitude, p1Longitude, SRID));
        System.Data.Entity.Spatial.DbGeography p2 = System.Data.Entity.Spatial.DbGeography.FromText(String.Format("POINT({0} {1} {2})", p2Latitude, p2Longitude, SRID));
        return (double)p1.Distance(p2);
    }

SqlGeography Code

public static double GetDistance(double p1Latitude, double p1Longitude, double p2Latitude, double p2Longitude, int SRID = 4326)
    {
        SqlGeography p1 = SqlGeography.Point(p1Latitude, p1Longitude, SRID);
        SqlGeography p2 = SqlGeography.Point(p2Latitude, p2Longitude, SRID);
        return (double)p1.STDistance(p2);
    }

DbGeography gives 179403.75129861536 and SqlGeography gives 217842.34845013986.

I have checked the calculation in SQL Server 

declare @p1 geography = geography::Point(-11.98260953020022, 54.51564130011218,4326)
declare @p2 geography = geography::Point(-10.55307433448692, 53.14334572793153,4326)
select @p1.STDistance(@p2)

The answer is 217842.34845014. I have also verified the distance in Google Earth Pro creating a line string

            <coordinates>
        54.51564130011218,-11.98260953020022,0  53.14334572793153,-10.55307433448692,0 
        </coordinates>

The length is 217832.

The Dbgeography call is:

double x = EF.GetDistance(-11.98260953020022, 54.51564130011218, -10.55307433448692, 53.14334572793153);

The SqlGeography call is:

            double y = Geography.SQLServerTypes.GetDistance(-11.98260953020022, 54.51564130011218, -10.55307433448692, 53.14334572793153);

I am at a loss to understand why the DbGeography result is so far out. Any insight? Thanks.

Upvotes: 0

Views: 439

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

When using Well Known Text representation, the parameters to POINT are the x coordinate followed by the y coordinate. When mapping this to the geography, this breaks the "expected" convention since x corresponds to longitude and y corresponds to latitude.

So you need to reverse the order that you're passing the parameters:

public static double GetDistance(double p1Latitude, double p1Longitude, 
                     double p2Latitude, double p2Longitude, int SRID = 4326)
{
    System.Data.Entity.Spatial.DbGeography p1 = 
     System.Data.Entity.Spatial.DbGeography.FromText(String.Format("POINT({0} {1} {2})",
          p1Longitude, p1Latitude, SRID));
    System.Data.Entity.Spatial.DbGeography p2 =
     System.Data.Entity.Spatial.DbGeography.FromText(String.Format("POINT({0} {1} {2})",
          p2Longitude, p2Latitude, SRID));
    return (double)p1.Distance(p2);
}

This then produces your expected result of 217842.34845014423.

Upvotes: 1

Related Questions