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 '%错%'