POSTGIS数据库操作

1、数据库操作记录  

1.1 更新kind_new字段为道路级别(范围1-10，1为高速) 

UPDATE public.r_road_polyline SET kind_new=1  WHERE kind like '01%';

 1.2 裁取北京范围数据导入新表  

select * INTO r_road_test from public.r_road_polyline  WHERE "geom" && ST_SetSRID('BOX3D(115.37499996 39.41667,117.50000004 41.08332996)'::box3d, 4326);

1.3 添加道路抽稀的后存储的字段    

SELECT AddGeometryColumn('public', 'r_road_test', 'geom_simple', 4326, 'LineString', 3);

1.4 更新道路抽稀后字段    

UPDATE public.r_road_test SET geom_simple=ST_SimplifyPreserveTopology(geom , 0.000006);

1.5 比较抽稀前后样本数据大小(MB)   

select sum(geomLength)/1024/1024.0 as sum_geomLength, sum(geomSimpleLength)/1024/1024.0 as sum_geoSimpLength from (SELECT pg_column_size(geom) as geomLength, pg_column_size(geom_simple) as geomSimpleLength
  FROM public.r_road_test) as sumTable;

1.6 添加r_lake_simple抽稀的后存储的字段    

SELECT AddGeometryColumn('public', 'r_lake_simple', 'geom_simple', 4326, 'Polygon', 2);

1.7 更新水系抽稀后字段  

 UPDATE public.r_lake_simple SET geom_simple=ST_SimplifyPreserveTopology(geom , 0.01);

1.8 查询草原天路线路数据保存至另一张表

SELECT * INTO t_tianlu_polyline from r_road_polyline WHERE name like '%草原天路%';

2、数据查询

2.1 查询二进制字段数据字节长度    

select pg_column_size(tile_data) from *_tile  

2.2 查询所有级别的最大切片长度(KB)  

 "select max(geomLength)/1024/1024.0 as max_geomLength,  maxTable.z from (SELECT pg_column_size(tile_data) as geomLength, x, y, z
  FROM public.beijing_map_1115_tile) as maxTable group by maxTable.z order by maxTable.z;"   
        

2.3 查看指定schema 里所有的表大小，按从大到小的顺序排列

select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;    

2.4 查看指定schema 里所有的索引大小，按从大到小的顺序排列  

select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;    

3、空间查询

3.1 查询表占用的大小    

select pg_size_pretty(pg_relation_size('表名'));    select pg_size_pretty(pg_total_relation_size('quanqiu_map_1214_tile'));

3.2 查询表占用的大小  

select pg_size_pretty(pg_database_size('数据库名'));    

3.3 显示修改字段类型    

alter table r_road_polyline alter funcclass type int using funcclass::int;    

4、其他

4.1 简化geom为2d并转投影为3857  

 UPDATE public.r_road_polyline SET geom_new=ST_Transform(ST_Force2d(geom), 3857) ;     

4.2 经纬度转墨卡托查询    

SELECT st_asgeojson(ST_Transform(ST_SetSRID(ST_Point(70.364370,0.556136),4326) ,3857))  

4.3 所有湖泊的过滤条件  

 NAME like '%青海湖%' or  "NAME" like '%色林错%'  or "NAME" like '%纳木错%'  or  "NAME" like '%艾比湖%'  or  "NAME" like '%博斯腾湖%'  or  "NAME" like '%洞庭湖%'  or  "NAME" like '%太湖%'  or  "NAME" like '%鄱阳湖%'   or  "NAME" like '%洪泽湖%'  or  "NAME" like '%呼伦湖%'  or  "NAME" like '%兴凯湖%'  or  "NAME" like '%微山湖%'  or  "NAME" like '%昭阳湖%'  or  "NAME" like '%独山湖%'  or  "NAME" like '%南阳湖%'  or  "NAME" like '%成子湖%'  or  "NAME" like '%高邮湖%'  or  "NAME" like '%巢湖%'  or  "NAME" like '%乌伦古湖%'  or  "NAME" like '%赛里木湖%'  or  "NAME" like '%鄂陵湖%'  or  "NAME" like '%扎陵湖%'  or  "NAME" like '%龙羊峡水库%'  or  "NAME" like '%库%'  or  "NAME" like '%湖%'  or  "NAME" like '%错%'