Error in SQL geography POINT Lat, Long

Question

Update: reported by Microsoft.

---

In a simple (SQL Server 2012) table with a geography column (name geopoint ) populated with a few dots of simple rows like this. POINT (-0.120875610750927 54.1165118880234) , etc. performance

 select [geopoint].[STAsText](), [geopoint].Lat lat, [geopoint].Long long from mytable 

produces it

 Untitled1 lat long POINT (-0.120875610750927 54.1165118880234) 54.1165118880234 -0.120875610750927 

which looks like a bug, but it's too simple and needs to be caught before release. So am I doing something wrong?

Information added

IT professionals should look for implementation details of Microsoft SQL Server on MSDN. Since there may be differences in implementation. In this case. As evidence of this, I just checked the Postgist ST_AsText implementation for a geographic column. It works great! and the result, as one would expect. Therefore, the error lies in the implementation of SQL. The correct result for the above example should be

 POINT (54.1165118880234 -0.120875610750927 ) 54.1165118880234 -0.120875610750927 

I can’t say that there is a high probability that there are other errors related to functions that work with geographic columns. Since the basic functions in this area have not been fully tested.

Answer 1

It works as intended.

According to your question, you saved the data in this template:

POINT (-0.120875610750927 54.1165118880234)

then you claimed that lat / long was canceled according to the MSDN documentation

Point(Lat, Long, SRID) .

You can understand that the syntax you are using is different from the one you are claiming:

POINT(aValue anotherValue) vs Point(Lat, Long, SRID)

Now the question is, what does MS SQL do for the data?

It turns out that MS SQL interprets the data as an open geospatial consortium (OGC), a well-known text (WKT), and thus uses the STPointFromText function, since the format is most suitable for a two-dimensional point:

POINT(xy)

Now, the next question is, does this mean POINT(Lat Long) ?

From the sample code

 SET @g = geography::STPointFromText('POINT(-122.34900 47.65100)', 4326); 

it should be clear that the first parameter is not latitude, but longitude (the range of the latitude range is from -90 to 90), so now we assume that the format is POINT(Long Lat) then. But why?

As explained in this article ,

As you can see [...], longitude is indicated before latitude. The reason is that the Open Geospatial Consortium (OGC) view has the (well known text) (WKT) format (x, y). The geographic coordinates are usually Lat / Long, but between the two, X is longitude and Y is latitude .

You might be wondering why the X coordinate is longitude, while the Y coordinate is latitude. Think of the Earth’s equator as the x axis, and the main meridian the Y axis. Longitude is defined as the distance from the prime meridian along the x axis (or equator). Similarly, latitude is defined as the distance from the equator along the Y axis.

Answer 2

This is mistake. The return value for STAsText for the geography column swaps the Lat and Long values. Definitely a mistake that people should be aware of.