postgis sql语句备忘

创建触发器，在更新或新增前取geom多边形中心点存入xy字段

CREATE OR REPLACE FUNCTION update_xy_on_insert_block()
RETURNS TRIGGER AS $$
BEGIN
    NEW.lat := st_y(st_centroid(NEW.geom));
    NEW.lng := st_x(st_centroid(NEW.geom));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_xy_on_block
BEFORE INSERT OR UPDATE ON block_info
FOR EACH ROW
EXECUTE FUNCTION update_xy_on_insert_block();

更新geom字段的空间参考

– 先更新geom字段srid
SELECT UpdateGeometrySRID(‘land_change’,‘geom’,3857);
– 检查一下
select st_srid(geom) from xzqh_shi_qd limit 1;
– 更新字段
update land_change set geom = st_transform(ST_SetSRID(geom,4326),3857);
–没有就设置
select st_setsrid(geom,3857) from corn;

新增geom字段

SELECT AddGeometryColumn ('public','data_result_geom', 'geom', 3857, 'MultiPolygon', 2,false);

距离点最近的道路

-- The nearest road
with input as (select st_transform(ST_Buffer('SRID=3857;POINT(8193767.174781749 4786678.39110056)', 49918),4326) as i_geom)
SELECT id, name,ST_Distance(
    geom::geography,
    input_geom::geography
    ) as distance
FROM (
  SELECT
    road.id,
    road.name,
    road.geom,
    input.i_geom as input_geom
  FROM
    tb_base_road_high_china road,input
  ORDER BY
    road.geom <#>
    input.i_geom
  LIMIT 10
) as closest_candidates
ORDER BY
  ST_Distance(
    geom,
    input_geom
    );

距离点最近的点

with input as (select st_transform(geom,4326) as i_geom from average_slope_grid_china where id = 77403)
SELECT id, name,
       ST_Distance(
    tb_base_poi_medical_china.geom::geography,
           input.i_geom::geography
    ) AS distance
FROM tb_base_poi_medical_china,input
ORDER BY geom <#> input.i_geom
LIMIT 1;

修改表权属

alter table file_storage
    owner to aceshorers;

修改时区

alter database aceshorers set timezone = 'PRC';

json字段查询

select attributes::json as jsondata from data_result_geom where result_id = 8 limit 1;

select json_object_keys(tb.jsondata) as fields from (select attributes::json as jsondata from data_result_geom where result_id = 8 limit 1) as tb;

备份数据库

pg_dump --username=postgres --no-password --host=localhost --port=5432 --dbname=grainmonitoring --format=custom --file=./db.dump --verbose