名词解释
PostGIS
- 简单理解：PostGIS是PGSQL的一个扩展，既然是扩展，那就需要进行安装，而不是直接就带着的
- PostGIS类似Oracle上的spatial（没接触过Oracle中的GIS）
- PGSQL如果想用地理位置相关的功能，就要安装PostGIS
SRID
SRID：Spatial Reference System Identifier，理解为空间引用标识符，或者说空间坐标系
空间引用标识系统是由欧洲石油测绘组 (EPSG) 标准定义的，它是为绘图、测绘以及大地测量数据存储而开发的一组标准。该标准归石油天然气生产商 (OGP) 测绘和定位委员会所有。
几何图形实例默认 SRID 为零（就是几何图形！什么点线面体狼神的）
PGSQL中 geometry 实例的默认 SRID 为 0。利用 geometry 空间数据，执行计算是不需要空间实例的指定 SRID 的；因此，实例可驻留在未定义的平面空间。
地域实例必须使用支持的 SRID
PGSQL支持基于 EPSG 标准的 SRID。必须使用 geography 实例的支持 PGSQL 的 SRID 执行计算或将方法用于地域空间数据。SRID 必须与 sys.spatial_reference_systems 目录视图中显示的 SRID 中的一个匹配。如前所述，在使用 geography 数据类型对空间数据执行计算时，结果将取决于在创建数据时使用的是哪个椭圆体，因为为每个椭圆体都分配了一个特定空间引用标识符 (SRID)。
对 geography 实例使用方法时，PGSQL使用等于 4326 的默认 SRID，它将映射到 WGS84 空间引用系统。如果要使用 WGS84 （或 SRID 4326）之外的某个空间引用系统中的数据，您需要确定地域空间数据的特定 SRID。
WGS84、GCJ02
关于这个，相信接触过位置开发的小伙伴都多少了解过，为什么坐标定位会有偏移？那可能你的GCJ02（国测局02年制定的坐标系，又被大家称为火星坐标系）没有转成WGS84导致的。更多的内容请自行了解一下。这个对于位置的计算来说很重要。
总结来说就是：
来自GPS的信号的坐标基本都是WGS84坐标系（Android和iOS都不会转的，需要自己手动转换）
对于微信来说，来自腾讯地图上的坐标（不论是点选的还是怎么着的），都是加密后的GCJ02坐标系
对于微信公众号来说，公众号开启的获取用户位置功能返回的就是WGS84的坐标（恶不恶心？可能对于地图服务商来说不准提供WGS84的坐标，但是手机GPS获取的都是WGS84的坐标，所以就会出现这种情况啦！）
启用PostGIS扩展
--UUID
CREATE EXTENSION pgcrypto;
CREATE EXTENSION "uuid-ossp";
-- Enable PostGIS (as of 3.0 contains just geometry/geography)
-- 启用PostGIS功能（仅包括geometry/geography相关）
CREATE EXTENSION postgis;
-- enable raster support (for 3+)
-- 启用栅格扩展
CREATE EXTENSION postgis_raster;
-- Enable Topology
-- 启用拓扑扩展
CREATE EXTENSION postgis_topology;
-- Enable PostGIS Advanced 3D
-- and other geoprocessing algorithms
-- sfcgal not available with all distributions
CREATE EXTENSION postgis_sfcgal;
-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
-- rule based standardizer
CREATE EXTENSION address_standardizer;
-- example rule data set
CREATE EXTENSION address_standardizer_data_us;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;
关于SRID
很多盆友都会遇到SRID的问题，如何选择SRID让我头疼了许久，也踩了许多坑。上面举例中用的SRID是26910，有的例子中最开始用的SRID是26918，而对于我们GPS返回的坐标来说，使用的是WGS84坐标系，对应的SRID应该是4326的地理坐标系。
所以建议在数据库中保存GPS坐标时使用4326的SRID进行保存，这样在地理位置计算上可以做到更加统一。
坐标为"地理（geographics）"形式或者说是" 纬度（latitude）/经度（longitude）"形式的数据非常常见。
与Mercator（墨卡托）、UTM（通用横轴墨卡托）、Stateplane中的坐标不同，地理坐标不是笛卡尔平面坐>标（Cartesian coordinates）。地理坐标并不表示平面上与原点的线性距离，相反，这些球坐标描述了>地球上的角坐标。在球坐标中，点由该点与参考子午线（经度）的旋转角度和该点与赤道的角度（纬度）指定。image你可以将地理坐标看作近似的笛卡尔平面坐标，并继续进行空间计算，然而，关于距离、长度和面积的测量>将会是毫无意义的。由于球坐标测量角度距离，因此单位以"度"表示。此外，索引和真/假测试（如相交和包>含）可能会变得非常错误，因为越与极点或国际日期线接近的区域，点与点之间的距离变得越大。
建议
-数据库中存储GPS位置（Point）或者范围等内容的列建议设置SRID为4326
-这些列中存储的GPS数据需要在存储前转换为WGS84坐标系再存储，不要存GCJ02的坐标！
-由于4326中规定的度量单位是度（经度、纬度的那个度），而不是我们常用的米，所以在计算点的位置关系的时候，根据需要进行SRID的转换（比如计算GPS点之间在地球上的距离等场景），比如需要将SRID4326的坐标点Point转换为geography格式，这时再通过ST_DWithin函数查询周边范围的时候就是以米为单位了。
那有童鞋会问了，为什么不在存储的时候就存储成地理位置坐标系（geography），而是存储为SRID=4326的geometry呢？因为对于geography来说，相较于geometry会少很多空间计算的函数，在你深入了解号geograhy和geometry之间的曲别和联系后，你就可以根据场景灵活的选择使用哪种空间类型了。对于入门的我们来说，统一使用geometry不是什么坏处，毕竟它和geography之间可以直接转换！
比如：ST_X()这个函数，只支持geometry类型，如果我们存储的是geography的话，需要获取这个点的X坐标，需要先将其转换为geometry。。。
pgsql运算符
- PostgreSQL中独有的操作符，比如文本查找操作符，@@（tsvector类型与tsquery类型字符是否匹配），@>（tsquery类型之间是否包含）,<@（tsquery类型之间是否被包含）操作符。网络地址操作符如<<=（表示包含于或者等于），>>=（包含或者等于）。几何操作符如@-@（表示图形的周长或者长度）,@@（表示圆心），<->（表示圆心距）,&&（表示图形是否重叠）
- 创建表
-- 添加 点
CREATE TABLE mytable (
id SERIAL PRIMARY KEY,
geom_fence_alter geometry(POLYGON,4326)，
name VARCHAR(128),
lon varchar(255),
lat varchar(255)
);
CREATE INDEX mytable_gix ON mytable USING GIST (geom);
--添加面
ALTER TABLE basic_mall_v1 ADD COLUMN geom_fence_alter geometry(POLYGON,4326);
CREATE INDEX idx_area_fence ON basic_mall_v1 USING gist(geom_fence);
--批量更新现有的经纬度字段为POINT（其中，需要注意的是，使用pg的字符串拼接符号||，POINT经纬度之间要留空格。）
update s_poi_gaode set
geom_point=ST_GeomFromText('POINT('||longitude||' ' ||latitude||')', 4326)
- 查询两个点间的距离
select ST_DistanceSphere (ST_GeomFromText ('POINT(113.667096 34.753029)',4326), geom);
- 查询数据库中距离这个坐标最近的5个对象，并输出这5个对象距离此地的距离，此处单位是 米。
select
id,
name,
ST_AsText(geom),
ST_DistanceSphere (ST_GeomFromText ('POINT(113.667096 34.753029)',4326), geom)
from
mytable
order by
geom <-> ST_GeomFromText ( 'POINT(113.667096 34.753029)', 4326 )
limit 3;
- 查看此对象1000 米内的所有对象与距离
SELECT
id,
name,
lon,
lat,
ST_DistanceSphere (ST_GeomFromText ('POINT(113.667096 34.753029)',4326), geom)
FROM mytable
WHERE
ST_DWithin ( geom::geography, ST_GeomFromText ( 'POINT(113.667096 34.753029)', 4326 ) :: geography, 5000 ) IS TRUE
;
- 查询当前点是否在某个多边形内
SELECT ST_Within ( ST_GeomFromText('POINT(113.667096 34.753029)',4326),ST_GeomFromText ( 'POLYGON((113.67227 34.758545,113.652795 34.755639,113.650495 34.742707,113.673564 34.750182,113.67227 34.758545))',4326 ));
- 返回某个多边形内所有的点
SELECT
id,
name,
lon,
lat,
ST_DistanceSphere (ST_GeomFromText ('POINT(113.667096 34.753029)',4326), geom)
FROM mytable
WHERE
ST_Within ( geom, ST_GeomFromText ( 'POLYGON((113.67227 34.758545,113.652795 34.755639,113.650495 34.742707,113.673564 34.750182,113.67227 34.758545))', 4326 )) IS TRUE
;
强烈注意 ST_DWithin 与 ST_Within 的区别##
网格聚合算法
--WIDTH_BUCKET的原理是将对应的经度、纬度范围值划分为多少份，二这个参数就是地图四至。比如高德--地图，它的可见范围，从西至东（经度范围），从南至北（纬度范围），正确的四至是一个数组表示的值--如，[minX,minY,maxX,MaxY]。
SELECT
width_bucket(st_x(geom), 117.057442 ,119.235188 ,20) grid_x,
width_bucket(st_y(geom), 30.431278 , 32.614617, 20) grid_y,
count(*),
st_centroid(st_collect(geom)) geom,
array_agg(gid) gids
from tpoint
where
st_x(geom) between 117.057442 and 119.235188
and
st_y(geom) between 30.431278 and 32.614617
GROUP BY grid_x,grid_y
网格抽稀算法
select
grid_x,
grid_y,
name,
lon,
lat,
dense_rank
from (
select
grid_x,
grid_y,
name,
lon,
lat,
dense_rank() over(partition by grid_x order by name desc) dense_rank
from (
select
width_bucket(st_x(geom), 113.635763, 127.161163839957, 20) grid_x,
width_bucket(st_y(geom),17.7650105263925,35.6746737273545,20) grid_y,
name,
lon,
lat
from mytable) a) b
where dense_rank<=3
附上测试建表语句
CREATE TABLE mytable (
id SERIAL PRIMARY KEY,
geom GEOMETRY(Point, 4326),
name VARCHAR(128),
lon varchar(255),
lat varchar(255)
);
CREATE INDEX mytable_gix
ON mytable
USING GIST (geom);
INSERT INTO mytable (geom,lon,lat,name) VALUES (
ST_GeomFromText('POINT(113.667096 34.753029)', 4326),
'113.667096',
'34.753029',
'郑州站'
);
INSERT INTO mytable (geom,lon,lat,name) VALUES (
ST_GeomFromText('POINT(113.635763 34.758011)', 4326),
'113.635763',
'34.758011',
'碧沙岗'
);
INSERT INTO mytable(geom,lon,lat,name) VALUES (
ST_GeomFromText('POINT(113.636051 34.773431)', 4326),
'113.636051',
'34.773431',
'月季公园'
);
INSERT INTO mytable(geom,lon,lat,name)VALUES (
ST_GeomFromText('POINT(113.670258 34.755046)', 4326),
'113.670258',
'34.755046',
'万博商品城'
);
INSERT INTO mytable (geom,lon,lat,name) VALUES (
ST_GeomFromText('POINT(113.64913 34.724077)', 4326),
'113.64913',
'34.724077',
'二七万达'
);
查看当前数据库已上锁的语句
---查询当前被锁住的sql语句
select pid, state, usename, query, query_start
from pg_stat_activity
where pid in (
select pid from pg_locks l
join pg_class t on l.relation = t.oid
and t.relkind = 'r'
);
----kill有两种方式，第一种是：
SELECT pg_cancel_backend(PID);
这种方式只能kill select查询，对update、delete 及DML不生效)
----第二种是：
SELECT pg_terminate_backend(PID);
这种可以kill掉各种操作(select、update、delete、drop等)操作
