需求和背景
在已有大量经纬度坐标点的情况下，给定一组经纬度如何快速定位到附近的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使用函数AddGeometryColumn, 命令行查看函数
\df+ AddGeometryColumnSynopsis
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 美国马萨诸塞州地方坐标系（区域坐标系） 投影坐标, 平面坐标
直接添加 ALTER TABLE poi ADD COLUMN geom_p_alter geometry(POINT,4326); 添加空间索引 CREATE INDEX idx_point ON poi USING gist(geom_point); 插入点使用函数将文本转换为几何类型: ST_GeomFromText
sdx=# SELECT ST_GeomFromText('POINT(120.377041 36.066019)', 4326); st_geomfromtext ---------------------------------------------------- 0101000020E61000001310937021185E4012F5824F73084240 (1 row)使用坐标转换函数转换坐标体系：ST_Transform
sdx=# SELECT ST_Transform(ST_GeomFromText('POINT(120.377041 36.066019)', 4326),26986) st_transform ---------------------------------------------------- 01010000206A690000B6A9B046D9615AC162C3613707DD6441使用函数将几何类型转换为文本描述：ST_AsText
SELECT ST_AsText(ST_GeomFromText('POINT(120.377041 36.066019)', 4326)); st_astext ----------------------------- POINT(120.377041 36.066019) (1 row)插入三个点
update poi set geom_point=ST_GeomFromText('POINT(121.248642 31.380415)', 4326), geom_point_26986=ST_Transform(ST_GeomFromText('POINT(121.248642 31.380415)', 4326),26986), geom_p_alter=ST_GeomFromText('POINT(121.248642 31.380415)', 4326) WHERE uuid='462745f185a349bbb8454f70d085baae'; SELECT geom_point,geom_point_26986,geom_p_alter from poi WHERE uuid='462745f185a349bbb8454f70d085baae'; geom_point | geom_point_26986 | geom_p_alter ----------------------------------------------------+----------------------------------------------------+-- 0101000020E610000085766FC1E94F5E400D1AFA27B858D83F | 01010000206A69000087930146005A5CC1ECE89370F91A6541 | 0101000020E610000085766FC1E94F5E400D1AFA27B858D83F (1 row) 验证： SELECT ST_AsText(geom_point),ST_AsText(geom_point_26986),geom_p_alter from s_poi_gaode WHERE uuid='462745f185a349bbb8454f70d085baae'; st_astext | st_astext | geom_p_alter -----------------------------+-------------------------------------------+---------------------------------- POINT(121.248642 31.380415) | POINT(-7432193.09384621 11065291.5180554) | 0101000020E610000085766FC1E94F5E400D1AFA27B858D83F (1 row)批量更新现有的经纬度字段为POINT
update s_poi_gaode set geom_point=ST_GeomFromText('POINT('||longitude||' ' ||latitude||')', 4326), geom_point_26986=ST_Transform(ST_GeomFromText('POINT('||longitude||' ' ||latitude||')', 4326),26986); 验证： SELECT longitude,latitude,ST_AsText(geom_point),ST_AsText(geom_point_26986) from s_poi_gaode WHERE uuid='e3ebbcf15cc545408ac8b22d4df64ca6'; longitude | latitude | st_astext | st_astext ------------+-----------+-----------------------------+------------------------------------------ 121.417666 | 31.281433 | POINT(121.417666 31.281433) | POINT(-7448729.03389232 11054385.435284) (1 row)其中，需要注意的是，使用pg的字符串拼接符号||，POINT经纬度之间要留空格。
两个点之间的距离距离计算函数
ST_Distance
文本转换地理几何类型函数
ST_GeogFromText 。
文本转换为地理几何类型函数
ST_GeographyFromText