日常工作中有时候会遇到通过两点经纬度计算距离的需求,下面讲一下在sqlserver中实现的方法,方法很简单,只要通过一个函数就可以轻松搞定,计算结果的单位是米 。
geography::Point(star_lat, star_lon, 4326).STDistance(geography::Point(end_lat,end_lon, 4326)) as distance
在sqlserver里用实例计算:
在arcmap里验证:
计算的结果是96.74米,验证结果是96.70米,相差了4毫米左右,可以忽略不计,可见这个计算两点距离的函数还是非常准确的。
下面是sqlserver里关于gis常用到的一些函数:
shape转WKT:
[SHAPE].STAsText() as wkt
WKT转shape :
geometry::STGeomFromText(WKT, 4326) as shape
shape转经纬度:
SHAPE.STX as LON
SHAPE.STY as LAT
取面的中心点:
[SHAPE].STCentroid() as shape
取面的中心经纬度:
[SHAPE].STCentroid().STX as LON
[SHAPE].STCentroid().STY as LAT
面积(单位:平方米):
geography::STGeomFromText(Shape.STAsText(),4326).STArea() as st_area
两点距离(单位:米)
geography::Point(s_lat,s_lon, 4326).STDistance(geography::Point(n_lat,n_lon, 4326)) as distance
点经纬度转shape
geometry::Point(lon,lat,4326) shape
判断两个shape相交
a.SHAPE.STIntersects(b.SHAPE)=1
任意多边形最大最小经纬度:
shape.STEnvelope().STPointN(3).STX AS LON_MAX
shape.STEnvelope().STPointN(1).STX AS LON_MIN
shape.STEnvelope().STPointN(3).STY AS LAT_MAX
shape.STEnvelope().STPointN(1).STY AS LAT_MIN