基于posthgis 按照距离排序

安装环境 postsql 需要安装拓展 postgis 使用dcoker

version: '3'
services:
  postgis:
    image: mdillon/postgis:latest
    container_name: postgis
    ports:
      - 5432:5432
    environment:
      POSTGRES_PASSWORD: postgis
    volumes:
      - ~/work/data_postgis_dir:/var/lib/postgresql/data

安装后数据 用户名：postgres 密码：postgis

创建数据 使用投影坐标 4326单位

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)', 4326),'zhangsan',0.0001,0
);
INSERT INTO mylocation (geom,name,x,y) VALUES (
  ST_GeomFromText('POINT(0.001 0)', 4326),'zhangsan',0.001,0
);
INSERT INTO mylocation (geom,name,x,y) VALUES (
  ST_GeomFromText('POINT(0.001 0)', 4326),'zhangsan',0.001,0
);
INSERT INTO mylocation (geom,name,x,y) VALUES (
  ST_GeomFromText('POINT(0.1 0)', 4326),'zhangsan',0.1,0
);

查询附近 1000m的距离

SELECT id, name,geom,x,y,   ST_DistanceSphere(
                      geom,
                      ST_GeometryFromText('POINT(0 0)')) distance
FROM mylocation
WHERE ST_DWithin(
  geom::geography, 
  ST_GeomFromText('POINT(0 0)', 4326)::geography,
  1000
) ORDER BY distance asc;