在一个简单的(sql Server 2012)表中,地理列(名称geopoint)填充了一些类似于此的简单行点. POINT(-0.120875610750927 54.1165118880234)等执行
select [geopoint].[STAsText](),[geopoint].Lat lat,[geopoint].Long long from mytable
产生这个
Untitled1 lat long POINT (-0.120875610750927 54.1165118880234) 54.1165118880234 -0.120875610750927
这看起来像一个bug,但它太基本了,应该在发布之前被捕获.我做错了什么？
添加了信息
IT专业人员应该查找Microsoft在MSDN上实现sql Server的详细信息.由于实施方面可能存在差异.按照这种情况.作为证明,我刚刚检查了PostGist为地理列实现的ST_AsText.这很好用！和结果是人们所期望的.因此,错误在于sql的实现.上面例子的正确结果应该是
POINT (54.1165118880234 -0.120875610750927 ) 54.1165118880234 -0.120875610750927
解决方法
根据您的问题,您将数据存储在以下模式中：
要点(-0.120875610750927 54.1165118880234)
然后你声称纬度/经度根据MSDN documentation反转
点(Lat,Long,SRID).
您可能会发现您使用的语法与您声明的语法不同：
POINT(aValue anotherValue)vs Point(Lat,SRID)
现在,问题是,MS sql对数据做了什么？
事实证明,MS sql将数据解释为Open Geospatial Consortium(OGC)着名文本(WKT),因此使用STPointFromText函数,因为格式最适合2-D点：
要点(x y)
现在,后续问题,是指POINT(Lat Long)？
来自示例代码
SET @g = geography::STPointFromText('POINT(-122.34900 47.65100)',4326);
应该清楚的是,第一个参数不是纬度,而是经度(纬度范围的范围仅为-90到90),所以现在我们猜测格式是POINT(长纬).但为什么？
如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.
