postgresql 搜索指定距离内的记录 按近到远排序 并返回距离

概述脚本 CREATE TABLE mylocation ( id SERIAL PRIMARY KEY, geom GEOMETRY(Point, 4326), name VARCHAR(128), x double precision, y double precision); INSERT INTO mylocation (geom,name,x,y) VALUES

脚本

CREATE table mylocation (   ID SERIAL PRIMARY KEY,geom GEOMETRY(Point,4326),name VARCHAR(128),x double precision,y double precision);  INSERT INTO mylocation (geom,name,x,y) VALUES (  ST_GeomFromText('POINT(0.0001 0)','zhangsan',0.0001,0);INSERT INTO mylocation (geom,y) VALUES (  ST_GeomFromText('POINT(0.001 0)',0.001,y) VALUES (  ST_GeomFromText('POINT(0.1 0)',0.1,0);SELECT ID,geom,y,ST_distanceSphere(                      geom,ST_GeometryFromText('POINT(0 0)')) distanceFROM mylocationWHERE ST_DWithin(  geom,ST_GeomFromText('POINT(0 0)',0.001)ORDER BY distance asc;;

查询语句 下面距离单位为m

SELECT ID,ST_GeometryFromText('POINT(0 0)')) distanceFROM mylocationWHERE ST_DWithin(  geom::geography,4326)::geography,1000) ORDER BY distance asc;

搜索结果

总结

以上是内存溢出为你收集整理的postgresql 搜索指定距离内的记录 按近到远排序 并返回距离全部内容，希望文章能够帮你解决postgresql 搜索指定距离内的记录 按近到远排序 并返回距离所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。