关于postgre数据库部分函数的使用

1. 通过函数实现gis点位聚合

   1. WIDTH_BUCKET 的原理是将对应的经度、纬度范围值划分为多少份，这个参数就是地图四至。比如高德地图，它的可见范围，从西至东（经度范围），从南至北（纬度范围），正确的四至是一个数组表示的值如，[minX,minY,maxX,MaxY]，下面将经度分为16份，纬度分为9份。

   2. <- 可以根据查询出的gids数据进行处理，当前网格内随机一个点展示到地图上 ->
      SELECT 
      width_bucket(st_x(geom), 115.057442 ,117.235188 ,16) grid_x, 
      width_bucket(st_y(geom), 30.431278 , 31.540414, 9) grid_y,  
      count(*),
      st_centroid(st_collect(geom)) geom,  
      array_agg(gid) gids
      from camera 
       
      where 
      st_x(geom) between 115.057442 and 117.235188
      and 
      st_y(geom) between 30.431278  and 31.540414 
       
      GROUP BY grid_x,grid_y 
      

2. 通过wkt和半径来进行圈选查询

   1. 首先将前端传给我们的经纬度拼接为wkt值："POINT(" + longitude +" " + latitude + " 0.000000000000000)";

   2. 圈选查询：

                 SELECT *
              FROM camera_info
              WHERE st_contains(st_transform(st_buffer(st_geomfromtext('${wkt}', 4326) :: geography,
                                                       '${radius}') :: geometry, 4326), geom)
      

   3. 常用函数：

      --将点位信息经纬度转换为 geometry 类型
      SELECT
      	st_geometryfromtext ( 'POINT(116.095658 30.12045546)', 4326 )
      --将 geometry 转换为经纬度信息
      SELECT
      	st_astext ( '0101000020E61000007366BB421F065D408EA9A2156B0F4440' )
      --查找两个geom之间的距离，返回笛卡尔距离
      SELECT
      	st_distance ( '0101000020E61000007366BB421F065D401B53452BD61E3E40', '0101000020E61000007366BB421F065D408EA9A2156B0F4440' )
      --查找缓冲区范围（一般应用于圈选查询）
      SELECT
      	st_buffer ( st_geomfromtext ( 'POINT(116.095658 30.12045546)', 4326 ) :: geography, 1000 )
      --坐标转换函数
      SELECT
      	st_transform ( st_buffer ( st_geomfromtext ( 'POINT(116.095658 40.12045546)', 4326 ) :: geography, 1000 ) :: geometry, 4326 ) 
      --圈选查询
      SELECT ID
      	,
      	longitude,
      	latitude 
      FROM
      	camera_info 
      WHERE
      	st_contains ( st_transform( st_buffer ( st_geomfromtext ( 'POINT(116.095658 40.12045546)', 4326 ) :: geography, 1000 ) :: geometry, 4326) ,geom   )
      --框选查询
      SELECT ID
      	,
      	longitude,
      	latitude 
      FROM
      	camera_info 
      WHERE
      	st_contains ( st_geomfromtext ('POLYGON((116.095658	40.12045546,
      116.1340469	40.13593115,
      116.1372947	40.16292292,
      116.095658	40.12045546))', 4326) ,geom   )
      --查看两个几何对象是否在某一个范围内(米)
      SELECT
      	st_dwithin ( '0101000020E61000007366BB421F065D401B53452BD61E3E40', '0101000020E61000007366BB421F065D408EA9A2156B0F4440', 10 )
      --查看缓冲区范围
      SELECT
      	st_buffer ( st_geomfromtext ( 'POINT(116.095658 40.12045546)', 4326 ) :: geography, 1000 )
      --查看A是否包含B
      SELECT st_contains('0101000020E61000007366BB421F065D408EA9A2156B0F4440', '0101000020E6100000C99CDB421F065D408EA9A2156B0F4440')
      --根据经度 纬度返回空间数据（geom）
      SELECT st_point(116.095658, 40.12045546)
      --查询几何对象的中心
      SELECT st_centroid('0101000020E61000007366BB421F065D408EA9A2156B0F4440')
      

      postgresql如果查询出的数据为空，则设为0的方法

      select COALESCE(b.price, 0) as price from fruit_sale b
      

   4. 设置geom
      UPDATE camera_info set geom = ST_GeometryFromText(concat('point(',longitude,' ',latitude,')')) where longitude is not null and longitude != '0.0' and longitude != ''
      
      设置空间引用标识符
      UPDATE public.camera_info
      	SET geom = st_geometryfromtext(st_astext(geom),4326)
      
      查询空间标识符
      select st_srid(geom) from camera_info where longitude is not null and longitude != '0.0' and longitude != ''
      
      修改所属市州
      update public.camera_info c
      set shijxzqh = a.zzjgmc
      from (SELECT c.id,p.zzjgmc from camera_info c LEFT JOIN polciestation_area p on st_contains ( P.geom,C.geom ) WHERE c.geom is not null AND P.ssjgdm = '630000000000') a
      WHERE c.id = a."id"
      
      修改所属区县
      update public.camera_info c
      set xianjxzqh = a.zzjgmc
      from (SELECT c.id,p.zzjgmc from camera_info c LEFT JOIN polciestation_area p on st_contains ( P.geom,C.geom ) WHERE c.geom is not null AND P.ssjgdm != '630000000000' and zzjgmc like'%公安%') a
      WHERE c.id = a."id"