manitod的博客

postgresql安装部署实用教程

GIS相关的空间计算函数

1、批量插入，重复更新 insert into cg_aoi_task (guid, city_code, sfadcode, x, y, zno_code, aoi_name, aoi_type, type, keywords, source, address, task_src, task_status, create_date, aoi_id) VALUES (,,,),(,,,,) ON CONFLICT(guid, task

1、度/秒互转 UPDATE 表名 SET geom = st_scale(shape, 1/3600.0,1/3600.0) 2、geom转wkt字符串 SELETC st_astext(t.shape) 3、wkt字符串转geom 并设置坐标系 SELECT st_setsrid(ST_GeomFromText('POINT(428626.908 110737.8)'), 4326) 4、获取图形中心点坐标 SELECT ST_X(st_centroid(c.shape)) AS x , ST_Y

1、分组统计 WITH T AS ( SELECT a.qc_date, a.task_type, b.rd_code, a.qc_flag, a.task_status, COUNT ( * ) AS C FROM cg_aoi_qc a, rd_code b WHERE b.citycode = a.city_code and a.qc_date = 20201027 and a.task_type = 4 GROUP BY a.qc_date, a.task_type

1、创建临时表 CREATE TABLE "test04" AS ( select * from testdemo); 2、修改表名 alter table table_name(表名) rename to new_table_name(新表名) 3、新增列并添加注释 alter table exchange_statistics add sys_id int4(32); COMMENT ON COLUMN exchange_statistics.sys_id IS '系统id'; 4、修改列属性 a