SELECT
ST_Distance (
ST_SetSRID ( ST_MakePoint ( #{lon}, #{lat} ), 4326 ) :: geography,
ST_SetSRID ( ST_MakePoint ( lon::float8, lat::float8 ), 4326 ) :: geography
) as dis, *
FROM
表名
WHERE
ST_Distance_Sphere (
st_setsrid ( st_makepoint ( lon :: FLOAT8, lat :: FLOAT8 ), 4326 ),
st_setsrid ( st_makepoint ( #{lon}, #{lat} ), 4326 )
) <= #{distance}
order by dis asc;
lon、lat是数据库中的字段;
#{lon}、#{lat}、#{distance}都是自定义参数。
题外:
刚开始我使用的是pg的 st_distance_sphere 函数,在执行查询时发现很慢,navicat控制台给了提示信息:
ST_Distance_Sphere signature was deprecated in 2.2.0. Please use ST_DistanceSphere
意思就是 ST_Distance_Sphere这个函数已经被deprecated了,推荐使用ST_DistanceSphere函数。