pg数据库-距离计算实践和结论 - 《postgresql踩坑记》

前言（由arcgis文档和其他资料得出，准确）：
geometry支持平面对象也支持空间对象，而geography则仅支持空间对象。参考：https://yq.aliyun.com/articles/228281/

srid 空间参考标识符 (SRID) 是与特定坐标系、容差和分辨率关联的唯一标识符。参考：https://www.cnblogs.com/gitwow/p/10916414.html ，
https://www.cnblogs.com/Joetao/articles/2086846.html
4326属于球坐标系，2163属于直角坐标系。

在数据库中，place字段存储的是srid=4326转换的几何对象（球坐标系），position字段存储的是srid=2163转换的几何对象（直角坐标系）。即，place = ST_GeomFromText(‘POINT(120.19 30.26)’,4326)，空间对象；position = ST_Transform(ST_GeomFromText(‘POINT(120.19 30.26)’,4326),2163)，平面对象。

<-> 表示两个几何类型的距离排序。参考：
https://yq.aliyun.com/articles/78842?spm=a2c4e.11163080.searchblog.67.17612ec1uaCcwS

&& 表示两个几何类型相交。参考：同上

ST_Buffer 获取几何对象和距离，然后返回表示围绕源对象的缓冲区的几何对象，参考：https://desktop.arcgis.com/zh-cn/arcmap/10.5/manage-data/using-sql-with-gdbs/st-buffer.htm。例：ST_Buffer(ST_GeomFromText(‘POINT(120.19 30.26)’,4326),25000)，翻译为在点(120.19 30.26)的25000m范围内的区域。有固定语法。如下。

place&&ST_Buffer表示在place字段列中与上述范围相交的点。

ST_GeomFromText 表示以熟知文本表示和空间参考 ID 作为输入参数，并返回几何对象，参考：https://desktop.arcgis.com/zh-cn/arcmap/10.5/manage-data/using-sql-with-gdbs/st-geomfromtext.htm。

ST_Transform 将二维 ST_Geometry 数据作为输入，并返回已转换为空间参考的值，该空间参考由您所提供的空间参考 ID (SRID) 指定。如果地理坐标系不同，则 ST_Transform 将执行地理变换。地理变换是指在两个地理坐标系间进行转换。例ST_Transform(ST_GeomFromText(‘POINT(120.19 30.26)’,4326),2163) 将球坐标系转换为直角坐标系。
参考：https://desktop.arcgis.com/zh-cn/arcmap/10.5/manage-data/using-sql-with-gdbs/st-transform.htm。

坐标计算距离公式

-- 适用平面直角坐标，适用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);

参考：https://yq.aliyun.com/articles/228281/

结论（由以下测试语句和arcgis文档得出）：
geometry&&ST_Buffer只适用于直角坐标系，即当place字段存储的是直角坐标系类型时，该语法可用。
从两个空间数据实例派生的任何空间方法的结果仅在这两个实例具有相同的 SRID（该 SRID 基于相同的用于确定实例坐标的度量单位、数据和投影）时才有效。
算球面距离，不要算直线距离。直线距离较实际距离相差较大。

实践
1.
select id,ST_Distance(place, ST_GeomFromText(‘POINT(120.19 30.26)’, 4326),true) as dist
from tb_account
where 1 = 1
and st_distance(place, ST_GeomFromText(‘POINT(120.19 30.26)’, 4326),true) >= 0
and place && ST_Buffer(ST_GeomFromText(‘POINT(120.19 30.26)’, 4326), 25000,10)
order by place <-> ST_GeomFromText(‘POINT(120.19 30.26)’, 4326)
result:

该语句使用了球坐标系，计算出的距离是球面距离。距离准确，距离范围和个数筛选失败。这种情况下place&&ST_Buffer语法失效。

2.
select id,ST_Distance(position, ST_Transform(ST_GeomFromText(‘POINT(120.19 30.26)’, 4326),2163))
from tb_account
where 1 = 1
and ST_Distance(position, ST_Transform(ST_GeomFromText(‘POINT(120.19 30.26)’, 4326),2163)) >= 0
and position && ST_Buffer(ST_Transform(ST_GeomFromText(‘POINT(120.19 30.26)’, 4326),2163), 25000, 10)
order by position <-> ST_Transform(ST_GeomFromText(‘POINT(120.19 30.26)’, 4326),2163)
result:

该语句使用直角坐标系，计算出的距离是平面距离，距离与示例1的结果相差较大，距离范围和个数筛选有效。该情况下place&&ST_Buffer语法有效。

经过多次计算验证：
目前得出结论，place&&ST_Buffer语法只对直角坐标系计算距离有效。

在球坐标系下，ST_Buffer失效的情况下，如何做距离范围和个数筛选？
在之前的阿里云云栖社区的参考文档中，给出了更适合业务的答案。

该作者依旧使用的是直角坐标系，我们对其进行改造，改成适合我们自己业务的语句。
select * from (
select id,ST_Distance(place, ST_GeomFromText(‘POINT(120.19 30.26)’, 4326),true) as dist
from tb_account
where 1 = 1
and st_distance(place, ST_GeomFromText(‘POINT(120.19 30.26)’, 4326),true) >= 0
order by place <-> ST_GeomFromText(‘POINT(120.19 30.26)’, 4326)
limit 10
) t where dist < 25000;
result:

这样的结果，符合我们的要求。最终选取该方案。

总结
到目前为止，遇到的一些问题：
1.
select id,ST_Distance(place, ST_Transform(ST_GeomFromText(‘POINT(120.19 30.26)’, 4326),2163))
from tb_account
where 1 = 1
and ST_Distance(place, ST_Transform(ST_GeomFromText(‘POINT(120.19 30.26)’, 4326),2163)) >= 0
and position && ST_Buffer(ST_Transform(ST_GeomFromText(‘POINT(120.19 30.26)’, 4326),2163), 25000, 10)
order by position <-> ST_Transform(ST_GeomFromText(‘POINT(120.19 30.26)’, 4326),2163)

使用了不同空间坐标id，place字段存储的几何对象是由4326转换，这里与2163转换的几何对象做运算会报错。

2.
place && ST_Buffer(ST_Transform(ST_GeomFromText(‘POINT(120.19 30.26)’, 4326),2163), 25000, 10)
该语句会导致最后运算结果为空。原因在于两种不同的srid转换的几何类型做相交，结果一定为空。

解决方案：
1.百度搜索相关错误，检索结果提示使用了不同的srid，思考做运算的两种几何类型的差别在哪。理解st_geometry函数的定义，找到问题。
2.在1的基础上思考，理解geometry&&ST_Buffer的作用，想到不同srid转换的几何类型相交一定为空。即从两个空间数据实例派生的任何空间方法的结果仅在这两个实例具有相同的 SRID（该 SRID 基于相同的用于确定实例坐标的度量单位、数据和投影）时才有效。