-9

Update: this has been reported to Microsoft.

In a simple (SQL Server 2012) table with a geography column (name geopoint) populated with a few simple rows points similar to this. POINT (-0.120875610750927 54.1165118880234) etc. executing

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

produces this 

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

which looks like a bug, but it is too basic and should have been caught before release. So am I doing something wrong?

Added info

IT professionals should look for the details of Microsoft's implementation of SQL server on MSDN. As there can be differences in implementation. As per this case. As a proof of this I just checked PostGist's implementation of ST_AsText for a geographic column. This works fine! and result is as one would expect. Therefore the bug is in implementation of SQL. The correct result for the above example should be 

POINT (54.1165118880234 -0.120875610750927 ) 54.1165118880234 -0.120875610750927

Dare I say there is a high likelihood that there are other bugs associated with functions working geographic columns. As basic functionality in this area has not been fully tested.

Improve this question
12
  • 4
    Why do you think there is some error, looks good to me! Commented Dec 4, 2014 at 15:00
  • 1
    "am i doing something wrong?" - at a guess, you've assumed that the arguments to POINT are latitude followed by longitude, rather than longitude followed by latitude. Commented Dec 4, 2014 at 15:16
  • when displaying [geopoint].[STAsText]() Lat and Long should be other way. I have double check returned values for STAsText() for both a geography point and a geometry point. For a geometry point the returned values are correct as per STX and STY property extensions. For geography points [STAsText]() returns the wrong values for POINT. It should be POINT (lat, long) This is a bug which i will report it. Commented Dec 4, 2014 at 15:47
  • 2
    Okay. We'll try saying it another way. Look at the example in STPointFromText: SET @g = geography::STPointFromText('POINT(-122.34900 47.65100)', 4326);. If you can provide me a link to a map showing the location on earth of that point (at a latitude of -122, as you claim) then you might have a point. Commented Dec 5, 2014 at 7:22
  • 2
    @Farjad - I've pointed you at an example where a geography is constructed from a Well-Known Text, using a first parameter of -122.34900. Where on the globe do you think that this point is located? The convention in WKT is long lat, not lat long. This is different from the convention you may have expected but it's not a bug. What it may indicate is that because you assumed it was lat long, you've entered your data incorrectly. Commented Dec 5, 2014 at 17:44

2 Answers 2

Reset to default
17

This is working as intended.

According to your question, you stored the data in this pattern:

POINT (-0.120875610750927 54.1165118880234)

then you claimed that the lat/long is reversed according to the MSDN documentation of

Point(Lat, Long, SRID).

You may realize that the syntax you're using is not the same as the one you claim:

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

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

Turns out, MS SQL interprets the data as Open Geospatial Consortium (OGC) Well-Known Text (WKT), and thus use STPointFromText function since the format is the most suitable for 2-D point:

POINT(x y)

Now, the follow-up question, does it 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 latitude range is from -90 to 90 only), so now we guess that the format is POINT(Long Lat) then. But why?

As explained in this article,

As you can see [...], the longitude is specified first before the latitude. The reason is because in the Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation, the format is (x, y). Geographic coordinates are usually specified by Lat/Long but between these two, the X is the Longitude while the Y is the Latitude.

You may be wondering why the X-coordinate is the Longitude while the Y-coordinate is the Latitude. Think of the equator of the earth as the x-axis while the prime meridian is the Y-axis. Longitude is defined as the distance from the prime meridian along the x-axis (or the equator). Similarly, latitude is defined as the distance from the equator along the Y-axis.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Note: This is just a result of researching and collecting related information. I'm not an MS SQL or GIS expert at all.
Andrew thanks for taking the time to reply to this thread. Andrew you mentioned the syntax i am using is not the same as i claim. The reported list is nothing other than what is produced by sql engine. The syntax is merely the return values of STAsText(). So it is NOT me that is changing anything. It is the returned values of the function by sql engine. As per MSDN definition the returned value for point is incorrect. One should be able to pick this text up and consume it to convert the point (with lat,long) to geometry version.
I see... then please clarify how you input the data. Did you use Point(Lat, Long, SRID) or POINT (-0.120875610750927 54.1165118880234)? Currently I assume you used the latter, and from your output, it printed the latter. But still, I don't find it as a bug. you may input with the former, but if it's printed as the latter, then there is nothing wrong. Similar to how I input 2014-12-6 then it output 12/6/2014. The point is, as long as the format is correct, there is no problem.
-4

This is a bug. Returned value for STAsText for a geography column swaps the Lat and Long values. Definitely a bug which people should be aware of.

Improve this answer

3 Comments

No, you have it backwards, the y coordinate is longitude. People talk about lat/lon, but it is implemeneted as lon/lat. It is not a bug.
-John Barca and others. MSDN clearly defines point as Point ( Lat, Long, SRID ). As i mentioned before. The geometry version of STAsText works correctly but NOT the geography version. So when testing this issue be careful not to confuse the two versions.
I think there might be a problem. Take a look at my unit test: latitude = 40.584474F; longitude = -111.633491F; var location = SqlGeography.Point( latitude, longitude, 4326 ); var point = location .ToString(); At this point, the variable point has a value of: POINT (-111.63349151611328 40.58447265625) That looks like a bug to me.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.