postgres 坐标_Postgres空间地理类型POINT POLYGON实现附近的定位和电子围栏功能

需求和背景

在已有大量经纬度坐标点的情况下，给定一组经纬度如何快速定位到附近的POI有哪些？

现在使用经纬度转geohash的算法，将二维的距离运算转换为like前缀匹配。通过比较9位到5位前缀，来获取附近5米到3km之内的坐标，为了寻求更快的定位方法，测试一下postgres的空间类型。

安装插件postgis

先安装了pg-10, 并且是通过yum安装的。导入过repo.

检查插件

yum search postgis

postgis-docs.x86_64 : Extra documentation for PostGIS

postgis-jdbc.x86_64 : The JDBC driver for PostGIS

postgis-utils.x86_64 : The utils for PostGIS

postgis23_10-client.x86_64 : Client tools and their libraries of PostGIS

postgis23_10-devel.x86_64 : Development headers and libraries for PostGIS

postgis23_10-docs.x86_64 : Extra documentation for PostGIS

postgis23_10-utils.x86_64 : The utils for PostGIS

postgis24_10-client.x86_64 : Client tools and their libraries of PostGIS

postgis24_10-debuginfo.x86_64 : Debug information for package postgis24_10

postgis24_10-devel.x86_64 : Development headers and libraries for PostGIS

postgis24_10-docs.x86_64 : Extra documentation for PostGIS

postgis24_10-utils.x86_64 : The utils for PostGIS

postgis.x86_64 : Geographic Information Systems Extensions to PostgreSQL

postgis23_10.x86_64 : Geographic Information Systems Extensions to PostgreSQL

postgis24_10.x86_64 : Geographic Information Systems Extensions to PostgreSQL

安装

yum install postgis.x86_64 postgis24_10.x86_64

系统安装了插件之后，数据库还要继续启用插件才行。

针对数据库启用插件

# 添加空间插件

CREATE EXTENSION postgis;

CREATE EXTENSION postgis_topology;

安装之后，public下会新增一个表spatial_ref_sys。

点POINT类型和距离

点POINT类型的数据结构为POINT(0 0)，正好可以用作存储经纬度。

表添加POINT类型

AddGeometryColumn

\df+ AddGeometryColumn

Synopsis

text AddGeometryColumn(varchar table_name, varchar column_name, integer srid, varchar type, integer dimension, boolean use_typmod=true);

text AddGeometryColumn(varchar schema_name, varchar table_name, varchar column_name, integer srid, varchar type, integer dimension, boolean use_typmod=true);

text AddGeometryColumn(varchar catalog_name, varchar schema_name, varchar table_name, varchar column_name, integer srid, varchar type, integer dimension, boolean use_typmod=true);

添加两个点字段

SELECT AddGeometryColumn ('poi', 'geom_point', 4326, 'POINT', 2);

SELECT AddGeometryColumn ('poi', 'geom_point_26986', 26986, 'POINT', 2);

其中两个重要的坐标体系

4326 \ GCS_WGS_1984 \ World Geodetic System (WGS)

26986 \ 美国马萨诸塞州地方坐标系(区域坐标系)\ 投影坐标, 平面坐标

直接添加