SQL Server Geography vs. DbGeography

Question

I have two GPS coordinates that I wanted to calculate the distance between them, but the result on the SQL server is completely different from the result in C # I googled and found that both approaches return the distance in meters, but this difference drives me crazy.

SQL SERVER query

select geography::Point(25.3132666, 55.2994054 ,4326) .STDistance(geography::Point(25.25434, 55.32820,4326)) as Distance; 

Web interface

 String format = "POINT(25.25434 55.32820)"; DbGeography myLocation = DbGeography.PointFromText(format, 4326); var users = context.Users.Select(u => new { fullName = u.name, lat = u.location.Latitude, lng = u.location.Longitude, distance = myLocation.Distance(u.location) }).ToList(); 

answer

 ,{"fullName":"jons smith","lat":25.3132666,"lng":55.2994054,"distance":4133581.8647264037} 

Thanks in advance.

Answer 1

Check the latitude and longitude order in the WKT representation of a point in SQL when defining a point; they follow GEOGRAPHY::POINT(Latitude, Longitude, srid) :

 SELECT GEOGRAPHY::Point(25.3132666,55.2994054 ,4326) .STDistance(GEOGRAPHY::Point(25.25434, 55.32820,4326)) as distance; //distance: 7142.94965953253 

But when defining DBGeography in C # code, the order is different: "POINT(Longitude Latitude)"

 String format = "POINT(55.32820 25.25434)"; DbGeography myLocation = DbGeography.PointFromText(format, 4326); var users = context.Users.Select(u => new { fullName = u.name, lat = u.location.Latitude, lng = u.location.Longitude, distance = myLocation.Distance(u.location) }).ToList(); //distance: 7142.949659532529 

You should also check the places that were inserted into the Users table. Make sure they are inserted correctly when pasting. Otherwise, they will be somewhere else in the place of your Users

More

 SELECT GEOGRAPHY::Point(25, 55 ,4326).Lat //25 DbGeography.PointFromText("POINT(25 55)", 4326).Latitude.Value //55 Microsoft.SqlServer.Types.SqlGeography.STPointFromText( new System.Data.SqlTypes.SqlChars("POINT(25 55)"), 4326).Lat.Value; //55 

What is WKT and where did it come from? This is the W ell- K nown T ext representation of the various geometry types that OGC introduces into the Simple Feature Access , and all software developers are encouraged to follow to ensure compatibility. This specification shows us how to define point, linear (linear), polygon and some other types of geometry as text (WKT) and binary (WKB).

SQL Server does not fully comply with this specification, and we see the result of non-compliance with standards, causing such problems in different components of even the same company.

Answer 2

Switch Lat / Lng to the API version. In the API, it should go Lng Lat

 select geography::Point(25.3132666, 55.2994054 ,4326).STDistance(geography::Point(25.25434, 55.32820,4326)) as Distance 

Returns

 7142.94965953253 

That's where I flipped one Lat / Lng usinq my UDF

 Select [dbo].[udf-Geo-Meters](55.2994054,25.3132666 ,25.25434,55.32820) 

Returns

 4135883.9028193 

Answer 3

The problem is very minor but complex

SQL SERVER query

 geography::Point(25.3132666, 55.2994054 ,4326) 

SQL Server defines a point where the first value is latitude and the second value is longitude.

Web interface

 String format = "POINT(25.25434 55.32820)"; DbGeography myLocation = DbGeography.FromText(format); 

C # defines a point from the above format in such a way that the first value is longitude and the second value is latitude