POSTGIS数据库操作

数据库操作记录    SQL语句    
更新kind_new字段为道路级别(范围1-10，1为高速) 

UPDATE public.r_road_polyline SET kind_new=1  WHERE kind like '01%';    
裁取北京范围数据导入新表  

 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)    
添加道路抽稀的后存储的字段    

SELECT AddGeometryColumn('public', 'r_road_test', 'geom_simple', 4326, 'LineString', 3)    
更新道路抽稀后字段    

UPDATE public.r_road_test SET geom_simple=ST_SimplifyPreserveTopology(geom , 0.000006);     
比较抽稀前后样本数据大小(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;"    
添加r_lake_simple抽稀的后存储的字段    SELECT AddGeometryColumn('public', 'r_lake_simple', 'geom_simple', 4326, 'Polygon', 2)    
更新水系抽稀后字段    UPDATE public.r_lake_simple SET geom_simple=ST_SimplifyPreserveTopology(geom , 0.01);     
        
        
查询二进制字段数据字节长度    select pg_column_size(tile_data) from *_tile    
所有湖泊的过滤条件    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 '%错%'     
        
查询所有级别的最大切片长度(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;"    
        
查看指定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;    
查看指定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;    
查询表占用的大小    select pg_size_pretty(pg_relation_size('表名'));    select pg_size_pretty(pg_total_relation_size('quanqiu_map_1214_tile'));
查询表占用的大小    select pg_size_pretty(pg_database_size('数据库名'));    
显示修改字段类型    alter table r_road_polyline alter funcclass type int using funcclass::int;    
        
简化geom为2d并转投影为3857    UPDATE public.r_road_polyline SET geom_new=ST_Transform(ST_Force2d(geom), 3857) ;     
经纬度转墨卡托查询    SELECT st_asgeojson(ST_Transform(ST_SetSRID(ST_Point(70.364370,0.556136),4326) ,3857))