沧州瑞智网站建设,软文广告文案案例,wordpress手机侧边导航栏,威海高区建设局官方网站标签
PostgreSQL , PostGIS , 球坐标 , 平面坐标 , 球面距离 , 平面距离 背景
PostGIS中有两种常用的空间类型geometry和geography#xff0c;这两种数据类型有什么差异#xff0c;应该如何选择#xff1f;
对于GIS来说#xff0c;首先是坐标系#xff0c;有两种#…标签
PostgreSQL , PostGIS , 球坐标 , 平面坐标 , 球面距离 , 平面距离 背景
PostGIS中有两种常用的空间类型geometry和geography这两种数据类型有什么差异应该如何选择
对于GIS来说首先是坐标系有两种一种是球坐标地理坐标另一种是平面坐标投影坐标。
球坐标通常用于计算平面坐标通常用于展示也可以计算。
投影坐标是从球坐标投影后展开得来(用一个圆柱将地球包起来把地球当成会发光的光源投影后将圆柱展开得到)投影的范围越大精度就越低。范围越小精度就越高。除了投影扇形的大小有区别在不同的行业也有不同的坐标系例如用于测绘的坐标系。
目前用得最多的有SRID4326球坐标SRID为EPSG:3785的墨卡托投影坐标。
再来说geometry和geography两种类型geometry支持平面对象也支持空间对象而geography则仅支持空间对象。
geometry支持更多的函数一些几何计算的代价更低。
geography支持的函数略少计算代价更高。那为什么还要geography呢因
4.2.2. When to use Geography Data type over Geometry data type The geography type allows you to store data in longitude/latitude coordinates,
but at a cost: there are fewer functions defined on GEOGRAPHY than there are on GEOMETRY;
those functions that are defined take more CPU time to execute. The type you choose should be conditioned on the expected working area of the application you are building.
Will your data span the globe or a large continental area, or is it local to a state, county or municipality? If your data is contained in a small area, you might find that choosing an appropriate
projection and using GEOMETRY is the best solution, in terms of performance and functionality available. If your data is global or covers a continental region, you may find that GEOGRAPHY
allows you to build a system without having to worry about projection details.
You store your data in longitude/latitude, and use the functions that have been defined on GEOGRAPHY. If you dont understand projections, and you dont want to learn about them,
and youre prepared to accept the limitations in functionality available in GEOGRAPHY,
then it might be easier for you to use GEOGRAPHY than GEOMETRY.
Simply load your data up as longitude/latitude and go from there. Refer to Section 14.11, “PostGIS Function Support Matrix” for compare between
what is supported for Geography vs. Geometry.
For a brief listing and description of Geography functions,
refer to Section 14.4, “PostGIS Geography Support Functions” 既然提到距离计算和投影坐标系有关引入了本文的问题
在不知道要计算的geometry点在什么投影坐标系下时往往计算距离得到的结果并不准确。
例如下面的点换算成2163坐标系计算距离得到的结果并不准确。
db1# SELECT st_distance(ST_Transform(ST_GeomFromText(POINT(120.08 30.96), 4326), 2163 ), ST_Transform(ST_GeomFromText(POINT(120.08 30.92), 4326), 2163 )); st_distance
------------------ 4030.46766234184
(1 row) 2163坐标系内容如下
postgres# select * from spatial_ref_sys where srid2163;
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
srid | 2163
auth_name | EPSG
auth_srid | 2163
srtext | PROJCS[US National Atlas Equal Area,GEOGCS[Unspecified datum based upon the Clarke 1866 Authalic Sphere,DATUM[Not_specified_based_on_Clarke_1866_Authalic_Sphere,SPHEROID[Clarke 1866 Authalic Sphere,6370997,0,AUTHORITY[EPSG,7052]],AUTHORITY[EPSG,6052]],PRIMEM[Greenwich,0,AUTHORITY[EPSG,8901]],UNIT[degree,0.0174532925199433,AUTHORITY[EPSG,9122]],AUTHORITY[EPSG,4052]],PROJECTION[Lambert_Azimuthal_Equal_Area],PARAMETER[latitude_of_center,45],PARAMETER[longitude_of_center,-100],PARAMETER[false_easting,0],PARAMETER[false_northing,0],UNIT[metre,1,AUTHORITY[EPSG,9001]],AXIS[X,EAST],AXIS[Y,NORTH],AUTHORITY[EPSG,2163]]
proj4text | projlaea lat_045 lon_0-100 x_00 y_00 a6370997 b6370997 unitsm no_defs AUTHORITY[EPSG, 9122]指的是EPSG数据集中UNIT为degree的ID是9122 AUTHORITY[EPSG, 4326]指的是地理坐标系WGS 84的ID是4326 AUTHORITY[EPSG, 9001]指的是EPSG中UNIT为meter的ID是9001 AUTHORITY[EPSG, 32650]指的是该投影坐标系WGS 84 / UTM zone 50N的ID是32650 这样会造成一个假象如下
用st_distance函数计算出来的经纬度之间的距离跟用java程序算出来的距离相差很大。 这个例子st_distance算出来的距离是4030我们程序算出来的是4445换另外两个相距很远的点这个差距值会更大。 正确姿势
算球面距离不要算直线距离。
1、使用球坐标通过st_distancespheroid计算两个geometry类型的球面距离。
对于geometry类型可以使用st_distancespheroid直接用球坐标计算在计算时会自动设置这个椭球特性SPHEROID[Krasovsky_1940,6378245.000000,298.299997264589] 。
postgres# SELECT st_distancespheroid(ST_GeomFromText(POINT(120.08 30.96), 4326),ST_GeomFromText(POINT(120.08 30.92), 4326), SPHEROID[WGS84,6378137,298.257223563]); st_distancespheroid
--------------------- 4434.73734584354
(1 row) 2、使用平面坐标通过st_distance计算两个geometry类型的平面投影后的距离。
采用精准的投影坐标小面积投影坐标系但是必须要覆盖到要计算的两个点
ST_Transform(ST_GeomFromText(POINT(120.08 30.96), 4326), 2163 ) 如果geometry值的SRID不是高精度目标坐标系可以使用ST_Transform函数进行转换转换为目标投影坐标系再计算距离。
postgres# SELECT st_distance(ST_Transform(ST_GeomFromText(POINT(120.08 30.96), 4326), 2390 ), ST_Transform(ST_GeomFromText(POINT(120.08 30.92), 4326), 2390 )); st_distance
------------------ 4547.55477647394
(1 row) 3、使用球坐标通过ST_Distance计算两个geography类型的球面距离。
float ST_Distance(geography gg1, geography gg2, boolean use_spheroid); -- 适用椭球体WGS84 use_spheroid设置为ture表示使用: -- WGS84 椭球体参数定义 vspheroid : SPHEROID[WGS84,6378137,298.257223563] ; 这里的XXXX就是你要选择的球坐标系SRID。在spatial_ref_sys表里可以查看各种坐标系。
postgres# SELECT st_distance(ST_GeogFromText(SRIDxxxx;POINT(120.08 30.96)), ST_GeogFromText(SRIDxxxx;POINT(120.08 30.92)), true); st_distance
---------------- xxxxxxxxxxxxxxxxxxxx
(1 row) 例如 postgres# SELECT st_distance(ST_GeogFromText(SRID4610;POINT(120.08 30.96)), ST_GeogFromText(SRID4610;POINT(120.08 30.92)), true); st_distance
---------------- 4434.739418211
(1 row) 如果允许一定的偏差可以使用全球球坐标系4326。
postgres# SELECT st_distance(ST_GeogFromText(SRID4326;POINT(120.08 30.96)), ST_GeogFromText(SRID4326;POINT(120.08 30.92)), true); st_distance
---------------- 4434.737345844
(1 row) geography只支持球坐标系使用投影坐标会报错。
postgres# SELECT st_distance(ST_GeogFromText(SRID2369;POINT(120.08 30.96)), ST_GeogFromText(SRID2369;POINT(120.08 30.92)), true);
错误: 22023: Only lon/lat coordinate systems are supported in geography.
LOCATION: srid_is_latlong, lwgeom_transform.c:774 4、指定SPHEROID内容通过st_distancesphereoid计算geometry的球面距离。
这种方法最为精确但是要求了解计算距离当地的地形属性即输入参数的spheroid的内容。
db1# SELECT st_distancespheroid(ST_GeomFromText(POINT(120.08 30.96), 4326),ST_GeomFromText(POINT(120.08 30.92), 4326), SPHEROID[WGS84,6378137,298.257223563]); st_distancesphere
------------------- 4447.803189385
(1 row) 小结
计算距离应该考虑到被计算的两点所在处的地球特性spheroid。这样计算得到的距离才是最精确的。
geometry和geography类型的选择建议使用geometry既能支持球坐标系又能支持平面坐标系。主要考虑到用户是否了解位置所在处的地理特性选择合适的坐标系。
-- 适用平面直角坐标适用geometry类型计算直线距离
float ST_Distance(geometry g1, geometry g2); -- 适用椭球体坐标适用geography类型计算球面距离
float ST_Distance(geography gg1, geography gg2); -- 适用椭球体坐标WGS84适用geography类型计算球面距离
float ST_Distance(geography gg1, geography gg2, boolean use_spheroid); use_spheroid设置为ture表示使用: vspheroid : SPHEROID[WGS84,6378137,298.257223563] ; -- WGS84 椭球体参数定义 -- 适用椭球体坐标以及投影坐标适用geometry类型求球面距离需要输入spheroid
float ST_DistanceSpheroid(geometry geomlonlatA, geometry geomlonlatB, spheroid measurement_spheroid); 参考
1、计算球面距离
ST_DistanceSphere
2、计算球面以及平面距离(取决于输入的类型geometry还是geography)
ST_Distance
3、坐标系转换
ST_Transform
4、投影坐标和球坐标
《地理坐标系球面坐标系和投影坐标系平面坐标系》
5、PostGIS学习建议
《PostGIS 空间数据学习建议》
6、 http://blog.163.com/jey_df/blog/static/18255016120149145755781/
墨卡托 (Mercator) 投影—帮助 | ArcGIS for Desktop
