遥想公瑾
探索云世界
遥想公瑾
个人专业是地理信息系统，拥有较丰富的GIS开发经验，对GIS在前端可视化和空间数据库（PostGIS，PostgreSQL）领域比较感兴趣。工作中致力于解决大量地理数据渲染问题，大量地理数据存储和空间查询优化。
一 案例背景 PostGIS提供了丰富的function用于GIS数据的存储，元数据描述，空间分析，测量，空间图形处理等等，这些函数基本上都很简单，遇到合适的场景时，很容易能知道应该选用哪种function去解决。但有时候的图形处理问题并不是很简单就能实现的，PostGIS核心成员就遇到了社区提出的一个问题： PostGIS是否有方法能将一个Polygon面切割成若干份小的Polygon面，且每一份的面积差不多大？ 其第一反应是： 不可以吧，如此复杂的问题不是sql能解决的。 打脸的是，PostGIS开发者Darafei Praliaskouski解决了这个问题，并分享了解决步骤。本文作者，也就是我，仅仅负责稍微整理下，搬运了下大神们的解决方案，非个人原创。要看原文的朋友，可访问原文：《PostGIS Polygon Splitting》 二 切割步骤 Darafei Praliaskouski提供的切割步骤如下： 使用ST_GeneratePoints方法将一个polygon转换成与面积成比例的一系列的点 (点越多，效果越好，大约1000个点为宜)。 假设计划将Polygon切成k等份，则使用ST_ClusterKMeans方法将这些转换后的点聚合成k簇。 使用ST_Centroid方法求出每一簇的的均值中心。 将求出的均值中心作为ST_VoronoiPolygons方法的输入参数，可以计算出每个点映射出的Polygon面。 使用ST_Intersection将这些映射的面和初始化的Polygon做切割处理，得到结果。 灵活使用PostGIS的方法，将如此复杂的问题，简单的解决了，堪称完美。 四 实践总结 百闻不如一见，百看不如一试试，本文作者就是我，看完觉得很赞，于是决定抄抄看看，如何将南京切割成大小相等的十个面，感兴趣的朋友可以按照我的步骤也可以测试测试。准备测试数据： 将测试数据写入临时表： create table nanjing as select name,geom from city where name='南京市'; 面转换为点： CREATE TABLE nanjing_points AS SELECT (ST_Dump(ST_GeneratePoints(geom, 2000))).geom AS geom FROM nanjing; 点聚合成簇（看原文方法的朋友请注意他的ST_ClusterKMeans写错了） CREATE TABLE nanjing_pts_clustered AS SELECT geom, ST_ClusterKMeans(geom, 10) over () AS cluster FROM nanjing_points; 获取每一簇的均值中心 CREATE TABLE nanjing_centers AS SELECT cluster, ST_Centroid(ST_collect(geom)) AS geom FROM nanjing_pts_clustered GROUP BY cluster; 使用voronoi算法生成面 CREATE TABLE nanjing_voronoi AS SELECT (ST_Dump(ST_VoronoiPolygons(ST_collect(geom)))).geom AS geom FROM nanjing_centers; 使用ST_Intersection方法切割 CREATE TABLE nanjing_divided AS SELECT ST_Intersection(a.geom, b.geom) AS geom FROM nanjing a CROSS JOIN nanjing_voronoi b; 一个个写sql生成的临时表写逻辑方便，但是使用起来比较费劲，我们可以写个function去处理，我使用了临时表，怕事务并发冲突，加了uuid后缀。其实这个逻辑不是很复杂的话，多套用几个with中间表也可以，但是写多了不是很清晰，我就暂时套用上面表的逻辑改成临时表做了个事务，测试通过了： create extension "uuid-ossp";--创建下uuid的扩展 create or replace function freegis_polygon_split( in split_geom geometry(Polygon),--输入的面 in split_num int,--分割的数量 out geom geometry(Polygon)--输出切割的面 ) returns setof geometry as $$ declare rec record; temp_points text; temp_ClusterKMeans text; temp_ClusterCentroid text; temp_VoronoiPolygons text; begin --防止并发的时候，临时表名称冲突 temp_points:='temp_points'||uuid_generate_v4(); temp_ClusterKMeans:='temp_ClusterKMeans'||uuid_generate_v4(); temp_ClusterCentroid:='temp_ClusterCentroid'||uuid_generate_v4(); temp_VoronoiPolygons:='temp_VoronoiPolygons'||uuid_generate_v4(); --生成点 execute format('create temp table "%s" on commit drop as SELECT row_number() over() as gid,(ST_Dump(ST_GeneratePoints($1, 2000))).geom',temp_points) using split_geom; --点成簇 execute format('create temp table "%s" on commit drop as SELECT t.geom, ST_ClusterKMeans(t.geom, $1) over () AS cluster from "%s" t',temp_ClusterKMeans,temp_points) using split_num; --簇的中心点 execute format('create temp table "%s" on commit drop as SELECT t.cluster, ST_Centroid(ST_collect(t.geom)) AS geom FROM "%s" t GROUP BY t.cluster',temp_ClusterCentroid,temp_ClusterKMeans); --voronoi构造面 execute format('create temp table "%s" on commit drop as SELECT (ST_Dump(ST_VoronoiPolygons(ST_collect(t.geom)))).geom AS geom FROM "%s" t',temp_VoronoiPolygons,temp_ClusterCentroid); --intersection切割 for rec in execute format('SELECT ST_Intersection($1, b.geom) AS geom FROM "%s" b',temp_VoronoiPolygons) using split_geom loop geom:=rec.geom; return next; end loop; return; end; $$ language plpgsql strict; 测试下通过了： 结语：PostGIS还是很强大的！！！另外一定要自己练习。。。
一 案例简介 电力行业在未来和气象领域的结合会越来越普及，衍生的各种复杂GIS分析也是很多的，如暴雨（气象）覆盖范围+地形分布区域（地质领域）+地貌分布区（植被覆盖分布）做叠加分析，如果地物是小土山，且植被覆盖率非常低，这里就特别容易发生滑坡，泥石流，而国网比如在复杂地形区新建一个电站，可以使用类似的分析，规避地质灾害，气象灾害频发区域，这些灾害之间好像还有相互关联性（GIS应该是做这种高级分析的，寻常定位高亮这种，怎么能算成GIS啊？）。 本文选择一个简单的例子，电力设施管理部门有很多地理位置的设施如杆塔（点数据），xx气象单位每天2点，8点，14点，20点定时推送气象预警分布图，业务要求快速解析图斑成矢量面，根据矢量的气象影响分布区域面叠加分析出受影响的电网设施（如杆塔）。 业务特点： 电力设施多，分布广。如杆塔可能上百万级别，分布在全国各地。 气象预警解析的矢量图斑，极其不规则。解析人员对每批次预警解析结果以MultiPolygon存储，如上图，暴雨图斑矢量化在表中其实是一条记录，但图形有很多散落独立的面状区域。 二 数据和分析现状描述 #电力设备测试数量 test=# select count(*) from dlsb; count ------- 99007 (1 行记录) # 测试的暴雨数据 test=# select count(*) from rain; count ------- 1 (1 行记录) # 暴雨图斑类型 test=# select St_GeometryType(geom) GeomType,ST_NumGeometries(geom) GeomCount from rain; geomtype | geomcount -----------------+----------- ST_MultiPolygon | 307 (1 行记录) 电力设备和暴雨矢量图斑分布见图3，从数据库可以看到，遍布全国的暴雨图斑其实就是一条记录，其图形类型是ST_MultiPolygon，子图形数量是307个，也就是这个ST_MultiPolygon是由307个Polygon组成的。作者使用win10单机部署的机器，稍微改了下postgresql.conf中几个内存相关参数其他没怎么动的原始机器测试： test=# select count(a.*) from dlsb a,rain b where st_intersects(a.geom,b.geom); count ------- 12814 (1 行记录) Time: 41401.309 ms (00:41.401) 分析一批次数据，需要耗时41s，更别说什么其他业务多表join之类的操作了，不可接受！ 三 优化之路 空间分析，主要是使用geom的gist索引去完成的，关于gist索引优化之路的原理，参考德哥的两篇博客： 《PostgreSQL 空间st_contains，st_within空间包含搜索优化 - 降IO和降CPU(bound box) (多边形GiST优化)》 《PostgreSQL 空间切割(st_split, ST_Subdivide)功能扩展 - 空间对象网格化 (多边形GiST优化)》 3.1 优化原则 作为业务开发者，我们在优化分析时，一定要遵循这样的原则： 1 尽可能的将复杂图形，拆分成简单图形。如MultiPoint,MultiLineString,MultiPolygon之类的对应拆分成单义图形类型Point，LineString，Polygon。 2 对于复杂的单个图形，尽可能使用合理的切割方法，切割成更小更简单的图形。（切割后，每个简单的图形的bbox更紧凑，大图形的bbox中无效分析区域尽可能被挤压掉，从而尽可能增加单个图形的图形面积与外接矩形面积的比重。这个比重越大代表图形有效面积越大，索引效果越好。） 3.2 优化路线 既然我们制定了空间分析的两个优化原则，优化路线严格遵循原则去实践。 3.2.1 耗时从41s减少到6s 基于原则一，将复杂图形拆分成简单图形: --新建一个新的表，存储拆分后的图形 CREATE TABLE rain_single ( gid serial primary key, geom geometry(Polygon,4326) ); --建立索引 CREATE INDEX rain_single_geom_idx ON rain_single USING gist (geom); --将拆分结果存入新的表中 INSERT INTO public.rain_single(geom) SELECT ST_GeometryN(a.geom, n) As geom FROM rain a CROSS JOIN generate_series(1,400) n WHERE n <= ST_NumGeometries(a.geom); 简单拆分后，分析步骤如下： test=# select count(a.*) from dlsb a,rain_single b where st_intersects(a.geom,b.geom); count ------- 12814 (1 行记录) Time: 6088.242 ms (00:06.088) 简简单单的，光看分析结果耗时就深受鼓舞！！！原理：该图片的边框就是初始的那个暴雨MultiPolygon图形的外接矩形。拆分成若干个单义图形之后，绿色部分是单义图形的外接矩形。拆分后的图形的外接矩形和原MultiPolygon图形的外接矩形的差异就是白色填充，蓝色斜线部分。拆分后，蓝色斜线部分都是被优化的查询空间，大大压缩了查询空间，降了io和cpu所以提升了整体效果。 3.2.2 耗时从6s到70ms 基于原则一成功的将Multi改变成单义图形之后，极大降低了io和cpu，大量无效分析空间被清除。但是通过上图可知，部分图形仍然非常零散细碎，外接矩形非常的大，空白（蓝绿色部分）区域还是很大。因此，我们考虑原则二，尽可能的去对这些复杂单个图形切割。 使用PostGIS你很幸运，不需要自己写算法了，其提供的ST_Split和ST_Subdivide都可以比较轻松的切割图形。ST_Split执行一次，会将一个图形切成两个部分，实际使用的时候，这种算法比较适合规整的图形（容易凭感觉切成几个部分那种图形，参考德哥的切割算法），而我这个暴雨区域，太过复杂，从东到西，由南至北，我也不知道切割成多少份，所以这种情况ST_Subdivide更适合。ST_SubdivideAPI说明： #输入图形，设置可选参数，切割的每一个图形最大的定点数，默认是256。 #返回被切割后形成的图形要素记录集。 setof geometry ST_Subdivide(geometry geom, integer max_vertices=256); 测试的时候，我们先设置最大定点数为20： #生成切割测试数据 test=# SELECT row_number() OVER() As id,geom into rain_Subdivide from (select ST_SubDivide(geom,20) geom from rain_single) as f(geom); alter table rain_Subdivide alter column geom type geometry(Polygon,4326) using geom:: geometry(Polygon,4326); create index rain_Subdivide_geom_idx on rain_Subdivide using gist(geom); #测试数据空间分析 test=# select count(a.*) from dlsb a,rain_Subdivide b where st_intersects(a.geom,b.geom); count ------- 12814 (1 行记录) Time: 1138.848 ms (00:01.139) 已经优化到1s钟完成分析了，我们可视化下切割图形： 从图上观察，还有很多切割后细碎的图形，我想，是不是切割的节点数设置大一点，可以合并部分区域，减少这种细碎部分？于是我设置成节点数为40： #生成切割测试数据 test=# drop table rain_Subdivide; test=# SELECT row_number() OVER() As id,geom into rain_Subdivide from (select ST_SubDivide(geom,40) geom from rain_single) as f(geom); alter table rain_Subdivide alter column geom type geometry(Polygon,4326) using geom:: geometry(Polygon,4326); create index rain_Subdivide_geom_idx on abc using gist(geom); #测试数据空间分析 test=# select count(a.*) from dlsb a,rain_Subdivide b where st_intersects(a.geom,b.geom); count ------- 12814 (1 行记录) 时间：69.127 ms 对比20节点数，发现的确实现了部分细碎图形消融了的结果，箭头处对比可知，所以速度提升了很多。oh my god，这简单的个例，竟然把41s的分析耗时，优化到了69ms，分析速度提升了约600倍！对于切割80,100节点作者没有尝试，切割节点既不能太大，否则图形很复杂；也不能太小，容易产生很多细窄狭长的小图形，很影响性能，建议根据实际数据情况多测试测试，找到一个较好的中间值作为切割最大节点数的阈值。 四 优化意义 优化之前，由于41s的查询，oltp出报表基本不可能，解决方案是离线预先叠加分析结果中间表，把结果中间表存储到业务表的数据库中，所以占用很多计算和存储资源，另外一个问题是，电网业务资源更新后，分析的结果和当前的资源明显结果和图形显示不一致了。 优化前 优化后 需要大量的io和cpu分析计算 很少量资源 该业务每天可能产生1G多的中间表 存储消耗0 业务数据变更与中间表对应不上 实时分析，一定一致 每天需要计算 不用计算，入库拆分原始面图形即可 耗时600单位 耗时1单位 综述：计算资源消耗的更少了，存储资源消耗的更少了，计算速度百倍提升，业务关系实时一致。
一 空间关系 数据库中判定数据之间的关系，使用的是比较操作符，如下： 操作符 描述 < 小于 | 大于<= |小于等于= |大于等于 = |等于<>或!=|不等于 但是在空间数据库中，由于空间数据的多维属性及其不同的几何特征，其判定关系与数值型字符型这些常用数据有非常大的概念性差异。对于GIS来说，空间数据库是核心，GIS开发人员对常用的基于sql比较操作符查询关系表的方式叫“属性查询”，对基于图形空间关系的判定查询叫“空间查询”。所以在说空间查询时，一定要写理清什么是空间关系。 任何涉及地理位置的数据，都具备如下关系： 空间关系 描述 ST_Intersects 相交关系，两个图形之间存在公共部分，比如公共点，公共线，公共面 ST_Disjoint 相离关系，两个图形无丝毫公共部分，与ST_Intersects完全相反 ST_Contains 包含关系，图形A包含图形B：ST_Contains(A,B)，如点在面内，线在面内。 ST_Within 被包含关系，图形A被B包含:ST_Within(A,B)，与ST_Contains完全相反。 ST_Covers 覆盖关系，图形A完全覆盖住了图形B：ST_Covers (A,B)，部分关系与ST_Contains重叠，但不是完全一样。 ST_Crosses 穿越关系，图形A与图形B有一部分公共内点，但不是全部。 ST_Equals 相等关系，两个图形完全相等。 ST_Overlaps 压盖关系 ST_Touches 相连关系，两个图形只有边界存在公共连接关系。 空间关系并非非此即彼的关系，不同空间关系之间肯能存在重叠部分，但又有些许差异，具体需要用户在实际应用时体会。实际应用中，St_Intersects是最常用的一个。更专业解释参考维基百科详情：https://en.wikipedia.org/wiki/DE-9IM 二 分析优化 正如第一节所说，空间关系之间，既可能是完全互斥的，如相离和相交，包含和被包含，又有似乎重叠的关系，如 包含与压盖，相交与相连。实际应用为了解决一个业务场景，可能有很多种空间方法可以解决问题，但不同的方法之间实际效率需要使用者测试和选择。 2.1 案例说明 某规划公司有两类点数据，一类是公交站点，地铁站点等交通站点数据，数据量约6200条，一类是居民小区房屋点位置数据，数据量约25000条。规划部门希望快速检索站点为中心约200米以外的所有房屋点数据。房屋测试数据： --创建房屋测试表 create table house( gid serial primary key, name text, geom geometry(Point,4326) ); create index house_geom_idx on house using gist(geom); --插入约25000的测试数据 insert into house(geom) SELECT st_setsrid((ST_Dump(p_geom)).geom,4326) from (select ST_GeneratePoints( ST_GeomFromText('Polygon((118.357442 31.231278,119.235188 31.231278,119.235188 32.614617,118.357442 32.614617,118.357442 31.231278))' ), 25000) as p_geom) as b; 站点测试数据： --创建站点测试表 create table station( gid serial primary key, name text, geom geometry(Point,4326) ); create index station_geom_idx on station using gist(geom); --插入测试数据 insert into station(geom) SELECT st_setsrid((ST_Dump(p_geom)).geom,4326) from (select ST_GeneratePoints( ST_GeomFromText('Polygon((118.357442 31.231278,119.235188 31.231278,119.235188 32.614617,118.357442 32.614617,118.357442 31.231278))' ), 6200) as p_geom) as b; 创建一个临时的站点缓冲数据表 create table station_buffer(gid serial primary key,geom geometry(Polygon,4326)); create index station_buffer_geom_idx on station_buffer using gist(geom); insert into station_buffer(geom) select st_buffer(geom,0.002) geom from station; 2.2 空间分析 2.2.1 相离算法 以每个站点为中心，200米为半径，建立缓冲区，合并所有的缓冲区，查询房屋不在这个合并缓冲区范围内的数据，即房屋点与合并缓冲区点相离，这个逻辑是最简单的： select count(a.*) from house a,( select st_union(geom) geom from station_buffer ) b where ST_Disjoint(a.geom,b.geom); 但是直接卡死了。。。。 2.2.1 相交反算 另外一个思路是把缓冲区内相交的房屋计算出来，然后根据结果反算不在相交数据集中的数据：方法一：把缓冲区union成一个大的图形，计算这个图形相交的house，然后反算。 select count(a.*) from house a where gid not in (select distinct(a.gid) from house a,(select st_union(geom) geom from station_buffer) b where ST_intersects(a.geom,b.geom)); count ------- 23434 (1 row) Time: 17857.083 ms (00:17.857) 方法一虽然花费了17s，但是比直接相离那个逻辑，也是快了不知道几百倍了。。。但还是很卡。方法二：与方法一基本一致，但是不合并缓冲区。 select count(a.*) from house a where gid not in ( select distinct(a.gid) from house a,station_buffer b where ST_intersects(a.geom,b.geom) ); count ------- 23434 (1 row) Time: 274.454 ms 方法二直接从16s优化到了274 ms了，质的飞越！但方法二这里使用了not in,只是为了表达逻辑性的，但not in其实也是很影响性能的，我们试着修改修改看看：方法三：与方法二一致，只是将not in优化成左连接了。。。 select count(*) from ( select a.*,b.gid as gid2 from house a left join ( select distinct(a.gid) from house a,station_buffer b where ST_intersects(a.geom,b.geom) ) as b on a.gid=b.gid) as c where gid2 is null; count ------- 23434 (1 row) Time: 62.825 ms PS：not in可以用not exists和左连接去优化，直接not in是很影响性能的。我们可以把执行计划贴下： explain select a.* from house a where gid not in ( select distinct(a.gid) from house a,station_buffer b where ST_intersects(a.geom,b.geom) ); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Seq Scan on house a (cost=5864.43..6385.93 rows=12500 width=68) Filter: (NOT (hashed SubPlan 1)) SubPlan 1 -> Unique (cost=0.46..5801.93 rows=25000 width=4) -> Gather Merge (cost=0.46..5692.71 rows=43688 width=4) Workers Planned: 2 -> Nested Loop (cost=0.43..5237.25 rows=18203 width=4) -> Parallel Index Scan using house_pkey on house a_1 (cost=0.29..531.05 rows=10417 width=36) -> Index Scan using station_buffer_geom_idx on station_buffer b (cost=0.15..0.44 rows=1 width=568) Index Cond: (a_1.geom && geom) Filter: _st_intersects(a_1.geom, geom) (11 rows) Time: 1.813 ms 不使用not in： explain select count(*) from ( select a.*,b.gid as gid2 from house a left join ( select distinct(a.gid) from house a,station_buffer b where ST_intersects(a.geom,b.geom) ) as b on a.gid=b.gid) as c where gid2 is null; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Aggregate (cost=4148.80..4148.81 rows=1 width=8) -> Hash Anti Join (cost=3741.88..4148.80 rows=1 width=0) Hash Cond: (a.gid = a_1.gid) -> Gather (cost=0.00..313.17 rows=25000 width=4) Workers Planned: 2 -> Parallel Seq Scan on house a (cost=0.00..313.17 rows=10417 width=4) -> Hash (cost=3429.38..3429.38 rows=25000 width=4) -> HashAggregate (cost=2929.38..3179.38 rows=25000 width=4) Group Key: a_1.gid -> Gather (cost=0.28..2820.16 rows=43688 width=4) Workers Planned: 2 -> Nested Loop (cost=0.28..2820.16 rows=18203 width=4) -> Parallel Seq Scan on station_buffer b (cost=0.00..502.83 rows=2583 width=568) -> Index Scan using house_geom_idx on house a_1 (cost=0.28..0.89 rows=1 width=36) Index Cond: (geom && b.geom) Filter: _st_intersects(geom, b.geom) (16 rows) 这个案例的优化，将相离改成了相交，not in改成了左连接，都起到了优化查询案例。 2.3 优化解释 相离为什么那么慢？ 站点200米缓冲区是红色部分，200米是很小的范围，受影响的house是很少的。数据库查询计划，对有索引的，只检索少部分数据才走索引。而相离运算，基本起不到过滤的作用，几乎100%的数据都是相离的，那么会全表查询，全表计算，所以非常卡。 为什么union缓冲区这么慢？相交反算的方法一，把缓冲区合并了一个图形，通过上图可知，红色的缓冲区几乎各个地方都有，合并后的图形的extent（外接矩形）基本是全局的，而无效部分非常大（红色区域之间都是无效部分），gist索引，首先也还是根据这个外接矩形去筛选的。实际应用中，对图形的extent中，无效面积过大的，反而还要去切割去优化io和扫描放大。 具体参考德哥的：《PostgreSQL 空间st_contains，st_within空间包含搜索优化 - 降IO和降CPU(bound box) (多边形GiST优化)》《PostgreSQL 空间切割(st_split, ST_Subdivide)功能扩展 - 空间对象网格化 (多边形GiST优化)》在空间分析中，合理使用gist索引，图形越简单，图形面积占外接矩形面积越大，检索效果越好。反之，这种union图形，图形变复杂，extent变大，图形面积占外接矩形面积越小，效果越差。 为啥不使用not in？这个是sql优化中常用的，使用not exists和左连接，的确起到了优化作用。（案例中gid是主键，有索引）。 空间查询在PostGIS中，也是sql查询，各种优化需要根据实际情况，如分割复杂图形，减少gist索引无效面积，合理使用空间分析的匹配关系，虽然很多逻辑都对，但是高效还是要开发者根据实际情况去调测的，另外其他基本的sql优化也是通用的。本文作者，水平一般般，但是稍微学了点皮毛就尝试了下，验证了一句话：“实践是检验真理的唯一标准”。
一 线性参考干啥用的 如果直接写个“高大上”的定义结果往往是一脸懵逼的，也不知道为什么要定义这么一个概念。其实线性参考技术在我们生活中是非常常见的，比如打开高德，百度地图的App，查看实时路况，道路被不同路况的颜色动态分段显示了；高速中发生交通事故，电视广播中常常对地点描述为“距离xx高速入口xx公里处”，地图是能非常精确的定位到这个地点的。生活中的两个例子的说明，其实他们的规律都是针对图形数据为线（LineString）的一种GIS应用。 线性参考一定要有线，线上一定要有权重值（也叫测量值M），上一段路况的测量值M就是路段上的传感器发送回来的车流通行速度，交通事故的测量值就是道路的实际距离。不同的测量值能做不同的事，如下图所示： 不同权重值.png 在不改变道路数据前提下，使用不同的维度（测量值M），分段展示不同的业务场景。 二 线性参考核心要素 转回严肃的工程技术范畴，我个人对线性参考总结包括以下几个要素： 线性数据：道路，河流，电网等一些列线性的业务基础数据。 测量值/M值：不同的业务部门，将自己的业务数据（路况啊，道路质量啊）叠加到线性基础数据上的过程，就是被称作“addMeasure”。 应用方法：一种是动态分段，一种是事件定位。 本质：不改变线性基础数据情况下，将业务测量值/M值数据动态赋予到了每个线性数据上，应用的时候，依据测量值，动态分段展示或者进行突然事件的快速定位。如下例子： 沿线距离.png 道路数据建立了完整测量值M后，用户想定位12公里处，就能快速提取出这个点的位置，用户想找到18-26公里处的区间，也能快速提取出这个线段。 三 PostGIS中的线性参考 以PostGIS2.4.0版本说明，通过查看API，可以发现对线性参考核心三要素是全部支持的，本章节具体阐述。 3.1 线性数据 PostGIS支持LineString，MultiLineString类型，可以将路网，河网，电网等各种LineString数据导入数据库。完全支持。 3.2 测量值/M值 ST_AddMeasure:用于对线性数添加测量值。函数定义： geometry ST_AddMeasure(geometry geom_mline, float8 measure_start, float8 measure_end); 参数说明：传入一个线图形，设置其起点测量值，终点测量值，返回一个建立了测量值的图形。使用示例： SELECT ST_AsText(ST_AddMeasure(ST_GeomFromText('LINESTRING(1 0, 2 0, 4 0)'),10,40)) As ewelev;; ewelev ---------------------------------------- LINESTRINGM(1 0 10,2 0 20,4 0 40) 3.3 动态分段，事件定位 PostGIS提供了ST_LocateBetween与ST_LocateBetweenElevations方式对带测量值的线进行动态分段提取，其中ST_LocateBetweenElevations是支持三维，四维数据的，简单的就以ST_LocateBetween（这个是用于对二维进行动态分段的）函数说明。ST_LocateBetween：二维线动态分段函数。函数定义： geometry ST_LocateBetween(geometry geomA, float8 measure_start, float8 measure_end, float8 offset); 参数说明：传入一个已建立测量值的线图形，选择一个起始测量M值与一个终点测量M值，返回测量值在这个区间内的动态截取线段图形。 至于offset，看了官网说明，也不知道是干嘛用的，请熟悉的朋友留言指点。使用示例: SELECT ST_AsText(the_geom) FROM (SELECT ST_LocateBetween( ST_GeomFromText('MULTILINESTRING M ((1 2 3, 3 4 2, 9 4 3), (1 2 3, 5 4 5))'),1.5, 3) As the_geom) As foo; ------------------------------------------------------------------------------------ GEOMETRYCOLLECTION M (LINESTRING M (1 2 3,3 4 2,9 4 3),POINT M (1 2 3)) 草图示意图.png 要求截取M值在1.5到3之间的图形，上图中(1 2 3, 3 4 2, 9 4 3)都满足，(1 2 3, 5 4 5)只有起点满足，所以输出结果符合预期。 ST_LocateAlong：事件定位函数，已知测量值，计算线性数据上的几何定位。函数定义: geometry ST_LocateAlong(geometry ageom_with_measure, float8 a_measure, float8 offset); 参数说明：传入一个已建立测量值的线图形，任意设置一个测量M值，返回该M值对应在线性数据上的几何位置。（如前文高速事故以这种事件点形式描述）。使用说明： SELECT ST_AsText((ST_Dump(the_geom)).geom) FROM (SELECT ST_LocateAlong( ST_GeomFromText('MULTILINESTRINGM((1 2 3, 3 4 2, 9 4 3), (1 2 3, 5 4 5))'),2.5) As the_geom) As foo; st_asewkt --------------- POINT M (2 3 2.5) POINT M (6 4 2.5) 草图示意图.png ST_InterpolatePoint：与ST_LocateAlong函数相反，计算线性数据上任意一点位置的测量M值。函数定义： float8 ST_InterpolatePoint(geometry line, geometry point); 参数说明：输入建立测量值M的line与已知线上的一点point，返回这个point点对应的测量值M的value。使用说明： SELECT ST_InterpolatePoint('LINESTRING M (0 0 0, 10 0 20)', 'POINT(5 5)'); st_interpolatepoint --------------------- 10 以上几个函数，是线性参考应用场景中所必需的，主要解决如何给线性数据添加测量值，如何定位，如何反算，如何动态获取。PostGIS线性参考章节剩余几个函数都是纯图形计算的。 ST_LineInterpolatePoint：geometry ST_LineInterpolatePoint(geometry a_linestring, float8 a_fraction)说明：输入LineString图形与一个分割百分比数，返回LineString从起点到终点之间任意一个0-1之间的百分比数a_fraction所处的点图形。 示例： route=# select ST_AsText(ST_LineInterpolatePoint(ST_GeomFromText('LineString(0 0,10 10)'),0.5)); st_astext ------------ POINT(5 5) (1 行记录) route=# select ST_AsText(ST_LineInterpolatePoint(ST_GeomFromText('LineString(0 0,10 10)'),0.3)); st_astext ------------ POINT(3 3) (1 行记录) ST_LineLocatePoint: float8 ST_LineLocatePoint(geometry a_linestring, geometry a_point)说明：输入LineString图形与线上任意一个Point（并非线的node节点，而是这个点肯定与线相交（intersects）），返回LineString从起点到终点之间任意一个0-1之间的百分比数a_fraction。示例： route=# select ST_LineLocatePoint(ST_GeomFromText('LineString(0 0,10 10)'),ST_GeomFromText('Point(5 5)')); st_linelocatepoint -------------------- 0.5 (1 行记录) ST_LineSubstring：geometry ST_LineSubstring(geometry a_linestring, float8 startfraction, float8 endfraction);说明:输入LineString图形与起点停靠值s_f与终点停靠值e_f，返回停靠值之间的线段，如果s_f是0，e_f是1，结果就是完整的线的本身。示例： select ST_AsText(ST_LineSubstring(ST_GeomFromText('LINESTRING(0 0, 10 10)'),0.5,1)); ---------------------------------------------------- LINESTRING(5 5,10 10) 四 线性参考实际案例 本章节我制作了一批测试数据模拟一些实际应用。场景模拟是：武汉军运会重点保障线路之一的“武汉火车站--江夏运动员村“，相关部门为了加强掌握道路通行情况，在该条线路上安装了很多rfid监控传感设备，实时上传该路段的道路通行速度。监管部门设置速度区间，如35-45区间路段是红色，45-55区间路段显示黄色，55-75区间路段是绿色。 rfid数据.png 道路与monitor图形.png 数据处理步骤方案是： 1 以rfid_monitor点将完整的线路打断，拆分成监控点两两组成的区间子路段。 2 使用ST_AddMeasure方法，对拆分的子路段建立线性参考。 3 建立线性参考后，根据动态分段的红黄绿动态分区，可视化显示即可。 以下数据处理脚本，根据监控点对路段进行了切割打断，每一个segment路段，起点终点都是一个传感器的位置，在建立线性参考的时候，只需要把segment的起点终点测量值M设置为对应的传感器的speed即可。 do language plpgsql $$ DECLARE rec record; route_line geometry; initinfo boolean:=true; start_fraction float; end_fraction float; line geometry; speed_s numeric; speed_e numeric; BEGIN --提取出重点线路的图形 select geom from routes limit 1 into route_line; initinfo:=true; for rec in select t1.gid,t1.speed,t1.geom from rfid_monitor t1 order by t1.gid loop if(initinfo=true) then initinfo:=false; --起点百分比 start_fraction:=ST_LineLocatePoint(route_line,rec.geom); end_fraction:=start_fraction; speed_s:=rec.speed; speed_e:=rec.speed; else speed_s:=speed_e; speed_e:=rec.speed; start_fraction:=end_fraction; end_fraction:=ST_LineLocatePoint(route_line,rec.geom); line:=ST_LineSubstring(route_line,start_fraction,end_fraction); --子路段提取，建立线性参考 insert into substring_road(seg,geom) values(rec.gid,ST_AddMeasure(line,speed_s,speed_e)); end if; end loop; end; $$; 查询下子路段数据并可视化下： 建立测量值.png 到这这一步，数据都处理完了，都建立了M值了。应用系统根据配置，动态显示就可以使用了。 根据速度动态分段： select ST_LocateBetween(geom,35,45) geom,'红色' as color from substring_road union select ST_LocateBetween(geom,45,55) geom,'黄色' as color from substring_road union select ST_LocateBetween(geom,55,75) geom,'绿色' as color from substring_road; 查询结果可视化如下： 动态分段渲染.png [ArcGIS线性参考帮助文档][1] [1]: http://desktop.arcgis.com/zh-cn/arcmap/10.3/guide-books/linear-referencing/what-is-linear-referencing.htm [PostGIS线性参考开发文档][2] [2]: http://postgis.net/docs/manual-2.4/reference.html#Linear_Referencing
一 前言 近期翻阅博客，看到社区大神一休哥的一篇《canvas 奇巧淫技（二）绘制箭头路径效果》文章，同样，该大神还展示过一个使用rbush库如何在前端快速从海量数据进行空间检索的案例：https://alex2wong.github.io/mapbox-plugins/examples/rbush/，很有分享精神的前端GIS专家，更多关于前端GIS检索数据的技术可参考搜狐的干货专访:《深入理解空间搜索算法 ——数百万数据中的瞬时搜索》。关于轨迹样式带导航箭头这种常见问题，笔者基于兴趣和朋友们的总结，也试着用熟悉的OpenLayers的StyleFunction去实现一个这样的玩具，在此分享给大家。 高德轨迹箭头.png 基于已知的一条轨迹，实现这样的一个导航轨迹箭头，需要解决三个问题： 在轨迹上根据固定像素间隔，计算当前地图分辨率下箭头总数量。 计算当前地图分辨率下，每个箭头的绘制位置。 计算好箭头的数量和位置后，要确定箭头的方向。 一 箭头数量 由高德轨迹箭头图可知，每隔固定像素，打上一个箭头。假设当前的线LineString地理长度为length，当前固定像素间隔stpes=n像素，在当前地图比例尺res已知的情况下，n像素地理距离是resn，那么箭头总数count=length/(resn): let length=line_geom.getLength();//线图形的地理长度 const steps=40;//每隔40像素打一个箭头点 let geo_steps=map_res*steps;//40像素长度在当前地图比例尺下地理长度。 let arrow_count=length*1.0/geo_steps; 多么浅显易懂的道理啊，第一个问题很顺利的解决了。 二 箭头位置 第一步得到了箭头的总数，在获取箭头位置时，一个重要的API是线条LineString的getCoordinateAt，利用它我们在轨迹线上获取箭头点的位置。 /* fraction:参考点的百分比，如0就是LineString的起点，1就是LineString的终点，0.5就是LineString的中点。 */ linestring.getCoordinateAt(fraction, opt_dest) 假如箭头总数为arrowsNum，那么arrowsNum个箭头的数量分别是 for(let i=1;i<arrowsNum;i++){ let arraw_coor=geometry.getCoordinateAt(i*1.0/arrowsNum); console.log(arraw_coor);//输出每个箭头的坐标 } 得到每个箭头的位置后，我们先可视化下吧，OpenLayers的地图样式完全由StyleFunction实现的，完整样式代码如下： /* feature:地图上的要素对象，既有属性，也有坐标图形。 res：当前地图分辨率参数。 return:返回一个定制的渲染样式 */ var styleFunction = function(feature,res){ //轨迹线图形 var trackLine= feature.getGeometry(); var styles = [ new ol.style.Style({ stroke: new ol.style.Stroke({ color: '#2E8B57', width: 10 }) }) ]; //轨迹地理长度 let length=trackLine.getLength(); //像素间隔步长 let stpes=40;//像素步长间隔 //将像素步长转实际地理距离步长 let geo_steps=stpes*res; //箭头总数 let arrowsNum=parseInt(length/geo_steps); for(let i=1;i<arrowsNum;i++){ let arraw_coor=trackLine.getCoordinateAt(i*1.0/arrowsNum); styles.push(new ol.style.Style({ geometry: new ol.geom.Point(arraw_coor), image: new ol.style.Circle({ radius: 7, fill: new ol.style.Fill({ color: '#ffcc33' }) }) })); } return styles; } 箭头位置计算与可视化结果.png 三 箭头方向 之前的逻辑，我们已经计算了一个轨迹样式的雏形了，把地图上箭头位置的黄点改成一个箭头图标，做下方向旋转就可以了。在说明此前，需要说明下轨迹，segment线段，箭头点之间的关系，如下图： 轨迹，segment,箭头位置之间的关系.png 观察示意图，总结如下： 一条完整的轨迹由多个连续的segment组成。 通过getCoordinateAt方法计算得到的箭头点，一定是在轨迹线上的某个点。 每个箭头点的方向是由箭头点落在的segment的方向决定的。 很显然，计算箭头方向其实就是计算每个箭头点到底落在了哪个segment上，将segme方向赋予箭头点。这里我们引入了rbush库构建空间索引，计算轨迹点与segment对应关系。 之所以我要引入rbush库，是解决循环计算问题，想象下如果不引入rbush库，只能使用如下的伪代码暴力计算了： for(let i=0;i<arrows.length;i++){ for(let j=0;j<segments.length;j++){ if(instersects(arrows[i],segments[j])===true){ // arrows[i]对应的segments是segments[j] break; } } } 感觉逻辑很简单啊，这样做难道不可以吗？想象下，箭头数量，segment的数量其实都是不可控的，一个复杂的轨迹线可能由成百上千的近万的segments，这样一个个循环去匹配，效率是不是就有问题了？所以引入了空间索引。这里查询，使用了rbush进行btree查询，查询的结果后再详细比对是否和箭头相交，累了，直接贴代码了，不详述了： var styleFunction = function(feature,res){ //轨迹线图形 var trackLine= feature.getGeometry(); var styles = [ new ol.style.Style({ stroke: new ol.style.Stroke({ color: '#2E8B57', width: 10 }) }) ]; //对segments建立btree索引 let tree= rbush();//路段数 trackLine.forEachSegment(function(start, end) { var dx = end[0] - start[0]; var dy = end[1] - start[1]; //计算每个segment的方向，即箭头旋转方向 let rotation = Math.atan2(dy, dx); let geom=new ol.geom.LineString([start,end]); let extent=geom.getExtent(); var item = { minX: extent[0], minY: extent[1], maxX: extent[2], maxY: extent[3], geom: geom, rotation:rotation }; tree.insert(item); }); //轨迹地理长度 let length=trackLine.getLength(); //像素间隔步长 let stpes=40;//像素步长间隔 //将像素步长转实际地理距离步长 let geo_steps=stpes*res; //箭头总数 let arrowsNum=parseInt(length/geo_steps); for(let i=1;i<arrowsNum;i++){ let arraw_coor=trackLine.getCoordinateAt(i*1.0/arrowsNum); let tol=10;//查询设置的点的容差，测试地图单位是米。如果是4326坐标系单位为度的话，改成0.0001. let arraw_coor_buffer=[arraw_coor[0]-tol,arraw_coor[1]-tol,arraw_coor[0]+tol,arraw_coor[1]+tol]; //进行btree查询 var treeSearch = tree.search({ minX: arraw_coor_buffer[0], minY: arraw_coor_buffer[1], maxX: arraw_coor_buffer[2], maxY: arraw_coor_buffer[3] }); let arrow_rotation; //只查询一个，那么肯定是它了，直接返回 if(treeSearch.length==1) arrow_rotation=treeSearch[0].rotation; else if(treeSearch.length>1){ let results=treeSearch.filter(function(item){ //箭头点与segment相交，返回结果。该方法实测不是很准，可能是计算中间结果 //保存到小数精度导致查询有点问题 // if(item.geom.intersectsCoordinate(arraw_coor)) // return true; //换一种方案，设置一个稍小的容差，消除精度问题 let _tol=1;//消除精度误差的容差 if(item.geom.intersectsExtent([arraw_coor[0]-_tol,arraw_coor[1]-_tol,arraw_coor[0]+_tol,arraw_coor[1]+_tol])) return true; }) if(results.length>0) arrow_rotation=results[0].rotation; } styles.push(new ol.style.Style({ geometry: new ol.geom.Point(arraw_coor), image: new ol.style.Icon({ src: '../static/content/images/arrowright.png', anchor: [0.75, 0.5], rotateWithView: true, rotation: -arrow_rotation }) })); } return styles; } 轨迹箭头效果图.png 看着还凑合吧，但其实要做到高德那个精细的样式，才万里第一步，祝诸君继续研究，期待更好的效果。
前言 PostGIS是PostgreSQL数据库中处理GIS数据的空间数据库扩展，提供大量的空间图形处理和分析函数。很多初学者肯定是安装Linux单机版做做研究学习，而企业级服务器一般会选择Linux环境，所以本文以简短的图文结合，阐述在两种典型环境中快速搭建PostGIS服务。当然本人文采有限，"文字不够图来凑"，尽量使小白都能看懂。 一 Windows环境 1.1 安装PostgreSQL 在PostgreSQL官网下载页，选择windows版本的下载。 图1-1 PostgreSQL下载页面 下载完成后，直接双击安装.exe文件，执行安装： 图1-2 安装首页 图1-3 安装目录 图1-4 安装组件 图1-5 设置数据目录 图1-6 设置数据库密码 图1-7 设置数据库端口 图1-8 高级设置 然后直接next直到安装结束。 1.2 安装PostGIS 介质获取方式： PostGIS官网Windows下载 图1-9 选择对应版本 PostGIS版本应该与当前的操作系统和安装的PostgreSQL版本对应，比如上文我们在Win上安装的PostgreSQL10版本，这里我们就选择针对PostgreSQL10的PostGIS版本。 Stack Builder下载 安装完成PG后，会自动有个Stack Builder的工具，用于下载一些PG基础的插件。 图1-10 Stack Builder位置 图1-11 打开Stack Builder 图1-12 下载PostGIS 等待介质下载完毕。 无论从哪种形式获取安装介质后，下载完成后，双击PostGIS的exe进行安装。 图1-13 PostGIS安装首页 安装组件的时候，不需要勾选创建空间数据库，这没什么用，我们会自己创建数据库，通过创建扩展形式建立，下文会给出示例。 图1-14 选择组件 PostGIS安装目录选择安装到PostgreSQL的安装目录中。 图1-14 安装目录 然后next安装，弹出对话框都选是即可。 1.3 验证安装 PostgreSQL安装后会自带一个PgAdmin4的客户端，使用该客户端即可操作数据库，另一种方式是psql的命令行客户端。PgAdmin4是图形界面操作，各位读者可以自己捣鼓捣鼓怎么使用这个工具，执行ACID的sql操作。为了简化说明，本文以psql来说明，打开cmd，输入以下命令： $ psql -U postgres psql (10.3) Type "help" for help. postgres=# create database test; CREATE DATABASE postgres=# \c test You are now connected to database "test" as user "postgres". test=# create extension postgis; CREATE EXTENSION 图1-15 验证.png 二 Linux环境 2.1 安装PostgreSQL 详见：《Centos7安装PostgreSQL》 2.2 安装PostGIS 详见：《CentOS 7源码安装PostGIS》
一 OpenLayers核心职责 OpenLayers最新大版本是openlayers4，它是一个基于h5的GIS前端库，地图渲染方式为Canvas和WebGL，常用Canvas展示二维地图，支持WebGL渲染显示出将来的OpenLayers有支持三维方向的可能。OpenLayers作为一个地图前端库主要负责GIS数据的展示与交互。 OpenLayers仅仅是开源GIS框架中的前端部分，并不等于是GIS系统，所以很多非GIS专业的前端使用OpenLayers常常会出现很多误区，如社区中每隔几天就有人问的问题： 请问怎样用ol加载几百万点啊？我现在加载了感觉很卡。 请问ol能实现路径分析吗？ol能实现缓冲区分析吗？ 提问的人忽略了ol的核心职责是展示与交互，实际项目中也不可能有将几千万几百万数据推到前端展示和交互的，一般这种都是后端渲染图刷到前端展示，或者使用矢量切片抽希数据到前端展示，正如普通的web开发中的表单需要分页查询和分页展示是一个道理。至于分析一般是服务端或者空间数据库负责分析，分析结果提交前端展示。业务常常是复杂的，但是每个工具的职责是清晰的，请将复杂的业务交由正确的工具去完成！ 二 OpenLayers的定位 GIS前端渲染库除了OpenLayers还有LeafLet和ESRI公司的ArcGIS API，同样能支持地图的前端库还有百度api，高德api，谷歌api等，还有Echarts，D3.js等，初学者常常不能理解他们之间的关系。常常听人说，路径分析我就用高德API不就可以了吗？展示数据我用下Echarts不可以吗？仍然是一句话，选择什么样的工具，完全是依据实际业务需求而定的。当前和地图相关的库大概分类如下： 在线地图lbs服务：这类库的代表是百度api，高德api，谷歌api，主要特点是：公网环境，开发者需要申请key，key的地图请求服务有次数限制。地图数据和服务都是百度高德提供的，开发者常常是将业务有限的点（几个点，几十个点，几百个点等）定到地图上定个位置。开发中使用它们主要是如招聘网站上公司位置的一个定位，互联网应用中的lbs服务，如各种快递，外卖等app中附近的餐馆影院等。在企业和政府应用中，业务非常复杂，在线地图服务提供的数据不是我们要的，提供的服务不能满足我们的应用，所以实际上基本不会在企业开发中使用。LBS！=GIS。 数据可视化库：Echarts，D3.js主要作用是web端实现数据可视化的，提供丰富的图表等展示和交互，由于地图的使用越来越普及，所以不可避免的他们也会支持数据在地图上的展示。但主要定位仍然是数据可视化，在开发中，常常指定某个div，用来展示和交互下数据，属于页面的一小部分业务。而一般的综合指挥调度系统的地图是一个应用，加载非常多的图层，可以随时通过地图向地图单元发送指挥命令。page！=application。 GIS地图库:ol,LeafLet,arcgis api等都属于企业级地图应用开发库，彼此之间大同小异。稍微的差异是arcgis api需要arcserver提供服务，离开了server基本没任何优势。leaflet主要优势还是在开发的第三方控件比较多，但是兼容性比较差。且以“体积小，对移动端友好”为著称，在ol2的年代的确如此，但个人认为API的结构不如ol好，且ol3之后版本支持自定义打包，也支持移动端应用，ol4版本实现es6的import语法，实现按需加载，足以胜任开发大型GIS应用的要求。 综述：OpenLayers是GIS地图库，定位于开发GIS应用，而非地图页面，用于复杂的展示和交互用户数据。 三 OpenLayers的主要结构 3.1 HelloWorld <!doctype html> <html lang="en"> <head> <link rel="stylesheet" href="https://openlayers.org/en/v4.6.4/css/ol.css" type="text/css"> <style> .map { height: 600px; width: 100%; } </style> <script src="https://openlayers.org/en/v4.6.4/build/ol.js" type="text/javascript"></script> <title>OpenLayers example</title> </head> <body> <div id="map" class="map"></div> <script type="text/javascript"> var map = new ol.Map({ target: 'map', controls:ol.control.defaults(), interactions:ol.interaction.defaults(), layers: [ new ol.layer.Tile({ source: new ol.source.OSM() }) ], view: new ol.View({ center: ol.proj.fromLonLat([118, 32]), zoom: 4 }) }); </script> </body> </html> helloworld.png 看上面最简单的例子可得到如下结论：ol地图需要绑定一个div，并且map对象由controls,interactions,layers,view四个一级对象组成。 controls：地图中的控件，控件支持自定义，目前自带的控件如下： ol.control.Attribution，用于绑定当前地图中引用了合作单位图标文字信息。 ol.control.FullScreen，该控件实现全屏展示。 ol.control.MousePosition，该控件实时显示鼠标当前指向的地理位置。 ol.control.OverviewMap，该控件用于鹰眼图。 ol.control.Rotate，该控件实现地图旋转角度。 ol.control.ScaleLine，该控件用于显示地图的比例尺。 ol.control.Zoom，该控件用于放大缩小地图。 ol.control.ZoomSlider，该控件使用类鱼骨条的形式实现地图放大缩小。 ol.control.ZoomToExtent，该控件用于将地图缩放到指定区域。 interactions：ol的交互器，主要负责地图交互，如用户绘制点线面，修改点线面，编辑图形时开启的捕捉，都属于交互类容，详细清单参考 [ol.interaction](http://openlayers.org/en/latest/apidoc/ol.interaction.html。同样的，交互器也是很方便让开发者自定义一个输入自己的交互器，参考自定义交互器 四 OpenLayer中矢量数据格式 OpenLayer支持GeoJSON,WKT,KML,GML,TopoJSON等典型GIS格式，入门而言，就以GeoJSON,WKT示例说明： 4.1 GeoJSON数据格式 点 { "type": "Point", "coordinates": [100.0, 0.0] } 线 { "type": "LineString", "coordinates": [ [100.0, 0.0], [101.0, 1.0] ] } 面 { "type": "Polygon", "coordinates": [ [ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0], [100.0, 1.0], [100.0, 0.0] ] ] } 带属性信息的图形 { "type": "Feature", "geometry": { "type": "Point", "coordinates": [125.6, 10.1] }, "properties": { "name": "Dinagat Islands" , "id":"23456" } } 4.2 WKT格式 点 'Point(118 32)' 线 'LineString(118 32,119 33)' 面 'Polygon((118 32,119 33,118 33,118 32))' 4.3 地图对象的组成 无论任何地图API，地图中的点线面都代表着一个 “要素”，地图开发中，这些要素约定俗称就叫做 “Feature”，举例说明：地图上绘制10个杆塔点，实际就是绘制10个Feature，绘制10条线路，实际上也是10个Feature。 Feature是地图中地物最基本的单元，空间数据库中的一条条记录，其实就是存储了 图形+属性 的一条记录。 image.png 但是Feature的可视化是由 “图形+属性+样式”三要素决定的。 图形决定绘制要素的形状，属性代表绘制地理要素承载哪些业务信息，样式一般和属性结合使用，比如属性字段中有 人口 字段，当人口在不同区间时，这个地理要素填充不同的颜色等专题场景。 4.4 常用坐标系 'EPSG:4326':WGS84经纬度球面坐标系，GPS坐标就是这种，如 118 32是南京。 'EPSG:3857':WGS84的墨卡托投影坐标系。 //相互之间可以转换 ol.proj.fromLonLat([118,32])=[13135699.913606284, 3763310.6271446524]; ol.proj.toLonLat([13135699.913606284, 3763310.6271446524])=[118,32]; 平常webgis中几乎都是用墨卡托投影的，如百度，高德，谷歌的地图都是墨卡托投影的地图。 5 Layer OpenLayer支持矢量和栅格等数据格式的图层。 矢量：ol.layer.Vector （矢量）ol.layer.VectorTile（矢量切片） 栅格：ol.layer.Tileol.layer.Image 5.1 如何加载一个栅格图层 5.2 如何加载xyz格式图层 https://www.jianshu.com/p/a4c1fe2a1e40 5.2 如何加载一个矢量图层 矢量图层的组成 数据源 source 样式 style 5.3 如何设置地图样式 style stylefunction 六 地图事件 http://blog.csdn.net/freeland1/article/details/50127427 七 结合Canvas的特效展示 抛砖引玉，ol渲染是canvas和webgl的，我们可以利用canvas的知识做很多扩展性的展示。http://openlayers.org/en/latest/examples/layer-swipe.html?q=Canvashttp://openlayers.org/en/latest/examples/layer-spy.html?q=spy 是否到此为止？http://blog.csdn.net/freeland1/article/details/50937020 只会ol不能算是会了GIS开发，但是会ol的前端可以利用Canvas,WebGL的知识，做出更酷炫的效果，放置到和业务系统中的地图一起展示却值得尝试。
以PostGIS2.4版本说明，当前PostGIS已经提供了4种空间聚类的方法，列表如下： ST_ClusterDBSCAN ST_ClusterIntersecting ST_ClusterKMeans ST_ClusterWithin 本文简单用图形化的方式简述一下这几种方法如何使用。 一 ST_ClusterDBSCAN ST_ClusterDBSCAN是一个窗口函数，基于DBSCAN算法，返回每一个输入的2D图形所在“簇”的id。 定义：integer ST_ClusterDBSCAN(geometry winset geom, float8 eps, integer minpoints); 参数说明： geom：输入的2d图形对象。 eps：输入图形之间的距离如果小于eps规定的距离，他们就被化为同一“簇”。 minpoints：每个“簇”中图形最小数量。 使用说明： # 查询buildings的面图层中，建筑之间距离小于20米（0.0002是度，约20米）分类，且每一类中数量不少于2。 gis_cluster=# SELECT gid,name, ST_ClusterDBSCAN(geom, eps:= 0.0002, minpoints := 2) over () AS cid FROM buildings; gid | name | cid -----+--------------+----- 1 | | 2 | | 2 3 | | 2 4 | | 2 5 | 大洋百货 | 6 | 商茂世纪广场 | 0 7 | 华威大厦 | 2 8 | 天安保险大厦 | 2 9 | | 2 10 | | 11 | 江苏交通大厦 | 2 12 | 阳光大厦 | 13 | | 0 14 | | 2 15 | | 2 16 | | 2 17 | | 2 18 | | 1 19 | | 1 20 | | 2 21 | | 2 22 | | 2 23 | | 2 24 | | 2 25 | | 2 26 | | 2 27 | | 2 -- More -- 可视化效果如下： ST_ClusterDBSCAN.png -1的是不属于任何“cluster”的图形，也就是查询中cid是null的数据，其他的数据都有自己的所属“cluster”的id值，即cid值。 二 ST_ClusterIntersecting ST_ClusterIntersecting是一个聚合函数，返回一个GeometryCollections数组，每个数组中的geom都是直接或间接能相交的图形。 定义：geometry[] ST_ClusterIntersecting(geometry set g); 参数说明： g:一组几何图形。 使用说明： 测试表中数据可视化如下： 测试数据buildings3.png gis_cluster=# select st_astext(unnest(ST_ClusterIntersecting(geom))) from buildings2; st_astext ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GEOMETRYCOLLECTION(POLYGON((118.77376608689 32.0424742122357,118.774542742962 32.0416518705121,118.773994515147 32.0411036426964,118.773172173423 32.0418802987687,118.77376608689 32.0424742122357)),POLYGON((118.774222943403 32.0429310687489,118.775898083951 32.0413320709528,118.775456455989 32.0407838431371,118.774542742962 32.0416518705121,118.77376608689 32.0424742122357,118.774222943403 32.0429310687489)),POLYGON((118.775898083951 32.0413320709528,118.777055453785 32.0402813009728,118.776629054372 32.0397635302578,118.775456455989 32.0407838431371,118.775898083951 32.0413320709528))) GEOMETRYCOLLECTION(POLYGON((118.771192461866 32.0393066737446,118.772684859809 32.0389259599839,118.772258460397 32.0379208756551,118.770705148252 32.0383320465169,118.771192461866 32.0393066737446)),POLYGON((118.772258460397 32.0379208756551,118.772684859809 32.0389259599839,118.774375228908 32.0384234178194,118.773964058046 32.0375097047933,118.772258460397 32.0379208756551))) GEOMETRYCOLLECTION(POLYGON((118.776766111326 32.0381188468106,118.778045309563 32.0377076759488,118.778167137966 32.0373117336375,118.777695052903 32.0366721345192,118.776080826556 32.0371746766835,118.776766111326 32.0381188468106)),POLYGON((118.778700137232 32.0380274755081,118.779857507065 32.0373878763897,118.779461564754 32.0368853342252,118.778730594333 32.0369614769774,118.778167137966 32.0373117336375,118.778045309563 32.0377076759488,118.778700137232 32.0380274755081))) (3 行记录) 已有数据被划分成了三类如下： 分类结果.png 聚合函数只返回了图形的集合出来，没带上记录的标记，该方法最好也能将记录的id返回比较好用。 三 ST_ClusterKMeans ST_ClusterKMeans：均值中心聚类，窗口函数，对每个输入的图形，根据图形之间的二维distance进行均值中心聚类，返回聚类的id。distance指的是图形之间的centroids。 定义：integer ST_ClusterKMeans(geometry winset geom, integer number_of_clusters); 参数说明： geom：输入的二维图形。 number_of_clusters：聚类的数量。 使用示例： #均值中心聚类，聚成3类 gis_cluster=# SELECT gid,ST_ClusterKMeans(geom,3) over () AS cid,geom FROM buildings; 均值中心为3类.png 同理，聚合成4类效果图如下： 均值中心为4类.png 使用也比较简单，也好理解。 四 ST_ClusterWithin ST_ClusterWithin：聚合函数，对输入的图形，指定一个笛卡尔距离（距离单位是图形srid对应的单位），图形之间的距离在指定的距离内，则归并为一类。最后返回聚类后的图形数组。 定义：geometry[] ST_ClusterWithin(geometry set g, float8 distance); 使用简介： create table buildings6( id serial primary key, cid int, geom geometry(Polygon,4326) ); do language plpgsql $$ DECLARE rec record; num int; i int; BEGIN truncate table buildings6; --距离设置为约50米，改成0.0003就是约30米 for rec in with a as (select unnest(ST_ClusterWithin(geom, 0.0005)) geom from buildings) select row_number() over() as id,a.geom from a loop num:=ST_NumGeometries(rec.geom); for i in 1..num loop insert into buildings6(cid,geom) select rec.id,ST_GeometryN(rec.geom, i); end loop; end loop; end; 50米.png 30米.png 聚类效果还是比较明显的。几个聚类函数比较常用，尤其在选址等应用上，不过具体的业务比简单的应用复杂多，笔者抛砖引玉，希望读者能有好的案例出现。
一 前言 工作中处理GIS数据时，最常规的操作是对多条线记录，他们的几何关系存在首尾相连的情况，需要合并成一个完整的线路。如路径分析中，路网都是一段一段的，显示的时候很不好看，合并成一个完整的显示比较好，如下图： 路径合并前.png 路径合并后.png PostGIS中，线路合并有几个相似的方法，如ST_Union,ST_LineMerge,ST_MakeLine方法，几种方法看起来很相似，实际应用的差别挺大。本文主要阐述这几种方法的异同点和使用场景。 二 ST_Union ST_Union是图形批处理中的一个函数，用于将图形联合（不是合并）成一条图形记录的方法。 #方法简介 geometry ST_Union(geometry set g1field); geometry ST_Union(geometry g1, geometry g2); geometry ST_Union(geometry[] g1_array); 输入参数：一般是图形的记录集或者图形的几何数组，也可以指定两个图形建立联合。 输出参数：返回的一般是Multi的geometry，或者geometry的collection。 使用场景：一般和聚合函数搭配使用。 使用示例：将数据中拥有相同start_id,end_id的线数据聚合成一条线路： 先查看下测试数据： dianzhou=# select start_vid,end_vid,st_astext(geom) from a; start_vid | end_vid | st_astext -----------+---------+----------------------------------------------------------------------------------------- 14178 | 43866 | LINESTRING(110.3359653 20.03026554,110.3365341 20.030855085) 14178 | 43866 | LINESTRING(110.3365341 20.030855085,110.33657406 20.03089509,110.33722278 20.031564555) 14178 | 43866 | LINESTRING(110.33722278 20.031564555,110.33745228 20.03179437) (3 行记录) 图形可视化如下： 待合并的线.png 执行ST_Union操作： dianzhou=# select start_vid,end_vid,st_astext(ST_Union(geom)) from a group by start_vid,end_vid; start_vid | end_vid | st_astext -----------+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 14178 | 43866 | MULTILINESTRING((110.33722278 20.031564555,110.33745228 20.03179437),(110.3359653 20.03026554,110.3365341 20.030855085),(110.3365341 20.030855085,110.33657406 20.03089509,110.33722278 20.031564555)) (1 行记录) 结果说明:拥有相同start_id,end_id的线数据聚合成一条线路，但是几何类型由LineString变成了MultiLineString。ST_Union函数只是将分离的图形不做太多的处理直接塞到了一起而已。 三 ST_LineMerge 上图可知，几条单独的线都是首尾相连的，按道理他们一条条连接起来形成完整的线是可能的，所以很多用户希望不仅仅是图形联合了，数据类型最好也是LineString（Multi类型的图形比较复杂在分析时不好处理），那么ST_LineMerge方法就可以起到用武之地了。 #方法简介 geometry ST_LineMerge(geometry amultilinestring); 该方法可以将MultiLineString中能合并的图形“缝补”到一起来，但是有的MultiLineString图形本来就是分离的，那就“缝补”不了了。 dianzhou=# select start_vid,end_vid,st_astext(ST_LineMerge(ST_Union(geom))) from a group by start_vid,end_vid; start_vid | end_vid | st_astext -----------+---------+------------------------------------------------------------------------------------------------------------------------------------------ 14178 | 43866 | LINESTRING(110.3359653 20.03026554,110.3365341 20.030855085,110.33657406 20.03089509,110.33722278 20.031564555,110.33745228 20.03179437) (1 行记录) 合并后的图形.png 如果是“缝补”不了的图形，如下： SELECT ST_AsText(ST_LineMerge( ST_GeomFromText('MULTILINESTRING((-29 -27,-30 -29.7,-36 -31,-45 -33),(-45.2 -33.2,-46 -32)) ')) ); st_astext ---------------- MULTILINESTRING((-45.2 -33.2,-46 -32),(-29 -27,-30 -29.7,-36 -31,-45 -33)) 缝补不了的话还是返回MULTILINESTRING类型。 四 ST_MakeLine ST_Union方法可以结合聚合把图形联合到一起，ST_LineMerge可以将联合的MultiLineString中能合并到一起的图形合并缝补起来。ST_MakeLine方法是图形构造函数中的一种，他可以直接从 点（Point），多义点（MultiPoint），线（LineString）这些图形集合里直接构造一条新的线。 #用法 geometry ST_MakeLine(geometry set geoms); geometry ST_MakeLine(geometry geom1, geometry geom2); geometry ST_MakeLine(geometry[] geoms_array); 细心的读者会发现和ST_Union方法的使用很相同，但是该方法只能接受点线，而ST_Union使用是没这种限制的，因为ST_Union方法并不仅仅是处理线的。 #案例说明 dianzhou=# select start_vid,end_vid,ST_AsText(ST_MakeLine(geom)) geom from a group by start_vid,end_vid; start_vid | end_vid | geom -----------+---------+------------------------------------------------------------------------------------------------------------------------------------------ 14178 | 43866 | LINESTRING(110.3359653 20.03026554,110.3365341 20.030855085,110.33657406 20.03089509,110.33722278 20.031564555,110.33745228 20.03179437) (1 行记录) 注意事项： ST_MakeLine合并的时候，线路方向要求是很严格的，如下图： 同一个方向.png ST_MakeLine结果.png 改变其中一条线的方向： dianzhou=# update a set geom=ST_Reverse(geom) where gid=2; UPDATE 1 改了其中一条线路方向.png ST_MakeLine结果.png 我们看到了一条乱线，通过示意图可以得知，ST_MakeLine的工作原理是先合并同方向的，然后合并反方向的。这并不是我们期望的结果。。。 我后来改成这种方法: dianzhou=# select st_linemerge(st_union(geom)) geom from a group by start_vid,end_vid; 这样倒是可以了。。。 五 总结 方法名 入参 出参 使用场景 ST_Union 任何图形 Multi类型，collection类型 聚合后的图形联合 ST_LineMerge MultiLineString LineString,MultiLineString 将MultiLineString中相连的线路合并成完整的LineString ST_MakeLine Point,MultiPoint,LineString LineString 构造新的线路 实际应用中，希望根据某些条件对相连的LineString聚合成一个新的LineString时，有两种方式： 1 ST_Union先聚合成MultiString再ST_LineMerge成LineString，效率差，但是能做到自动纠正方向。 2 ST_MakeLine直接对聚合的LineString构建成一个新的LineString，同向的效率快，但是不能做到对方向的纠正。 针对线路合并这种场景，按实际情况选择方案。。。
一 前言 前几天，某科研工作者咨询我，是否可以利用PostGIS实现线数据的汇总分析，目前他使用的Arcpy处理实在太慢，难以满足应用需求。他的需求如下图： 输入数据.png 输入数据：3条线路，权重值分别为1300，1200，1000。 输出数据.png 输出数据：消除重叠部分线路，重叠部分聚合为单一线路段且权重值累加，结果输入数据被切分成了绝不重叠的5段。 本着好奇心的态度，利用周末时间对此问题进行了研究。 二 解决方案一（逐条截取法） 首先映入脑海的思路是，一条条的线路单独处理，如先求取1300路段和1200路段的不重叠部分a，和重叠部分b。 逻辑说明.png 再用a,b这种被切分后的结果，与1000路段处理，a部分与1000路段无重叠，直接作为结果，b部分与1000路段重叠，被切割出不同部分b1和重叠部分c。 逻辑说明.png 最终的结果，1300路段被切分的结果是a,b1,c。 同理处理1200,1000路段，最后对所有切割的图形进行geom的聚合运算，得到结果，重要算法如下： #求取两个图形的不重叠部分 select ST_Difference(geom1,geom2); #求取两个图形重叠部分 select ST_Intersection(geom1,geom2); #根据图形聚合，权重累加，去除重叠图形 select sum(weight) weight,geom from result group by geom; 整体代码逻辑如下： do language plpgsql $$ DECLARE rec record; rec1 record; rec2 record; rec3 record; rec4 record; publicgeom geometry; privategeom geometry; geom_array geometry[]; geom_array_split geometry[]; temp_geom geometry; _temp_geom geometry; geomcount int; BEGIN for rec in select * from test_road loop --遍历路段表 geom_array:=array[rec.geom];--切割成果集合初始化 --从表中找出与处理的路段存在压盖关系且非自身的数据 for rec1 in select gid,geom from test_road where ST_Overlaps(geom,rec.geom) and gid!=rec.gid loop geom_array_split:='{}'; --对每次的切割成果迭代分析，求取公共部分，单独部分 FOREACH temp_geom IN ARRAY geom_array loop publicgeom:=ST_LineMerge(ST_Intersection(temp_geom,rec1.geom));--提取公共部分 privategeom:=ST_Difference(temp_geom,rec1.geom);--提取单独部分 if(ST_IsEmpty(publicgeom)=false) then for rec4 in select * from geomsplit(publicgeom) loop geom_array_split:=array_append(geom_array_split,rec4.geom); end loop; end if; if(ST_IsEmpty(privategeom)=false) then for rec4 in select * from geomsplit(privategeom) loop geom_array_split:=array_append(geom_array_split,rec4.geom); end loop; end if; end loop; geom_array:=geom_array_split;--每次处理完重叠和不重叠部分，结果形成新的待切割结果 end loop; --一条记录处理完毕，切割完毕的结果存入表 FOREACH temp_geom IN ARRAY geom_array loop execute format('INSERT INTO result_test_road( id, weight, geom) VALUES ($1,$2,$3)') using rec.gid,rec.weight,temp_geom; end loop; end loop; end; $$; --对切割成果进行聚合分析 select sum(weight),geom from result_test_road group by geom; 北京与各省会城市的路径.png 该算法在处理北京与各省会城市的路径时比较有效，样本数据30几条，很快处理完了。但是，处理北京与各县的路径时，数据量大概2500条，每切割完一条线路，消耗10分钟。（处理完大概333小时，oh my god） 北京与各县城市的路径.png 总结：该算法思路比较简单，就是对每条线路逐个的切割，每次切割完的结果再和相交的线路切割，直到相交线路处理完毕。该算法保证了结果的正确性，逻辑的简单性，但是明显循环实在太多，计算代切割集合会像滚雪球一样几何级别的增长，尤其在数据表记录较多的情况下，简直惨不忍睹。 三 解决方案二（原子聚合法） 该方法是将每条线上的点拆解下来，重叠线路部分的点位也是重合的，然后根据点位进行权重聚合，相同位置的话，权重累加，如下图： 步骤一 线化点.png 重叠部分点位聚合累加.png 点连成线.png id为5的线路被拆分成了权重是6,13的两部分，同理，另外一个线路也根据权重实现了拆分。 重叠去重，最终成果.png 代码如下： --线拆分成点，该表存储所有节点 CREATE TABLE road_pt( gid serial primary key, id integer, weight numeric, ptindex int, geom geometry(Point,3857) ); --将每个线路拆分点存入road_pt表 do language plpgsql $$ DECLARE rec record; ptcount int; BEGIN for rec in select * from test_road loop raise notice '正在处理gid:%',rec.gid; ptcount:=ST_NPoints(rec.geom); insert into road_pt(id,weight,ptindex,geom) values(rec.id,rec.weight,generate_series(1,ptcount),ST_PointN(rec.geom,generate_series(1,ptcount))); end loop; end; $$; --消耗50s --创建空间索引 CREATE INDEX road_pt_geom_idx ON road_pt USING gist (geom); --Query returned successfully with no result in 01:14 minutes. --相同的图形点，权重累加，权重聚合后的点数据存入表road_pt_group select sum(weight) weight,geom into road_pt_group from road_pt group by geom; --Query returned successfully: 76035 rows affected, 17.1 secs execution time. --创建空间索引 CREATE INDEX road_pt_group_geom_idx ON road_pt_group USING gist (geom); --3s --将点表中，与聚合图形相同的点位，统一更新新的点位权重 update road_pt t2 set weight=t1.weight from road_pt_group t1 where ST_Equals(t2.geom,t1.geom); --Query returned successfully: 2237228 rows affected, 02:26 minutes execution time. --重新点拼接成线 --点合成线，存储进入road_split CREATE TABLE road_split( gid serial primary key, id integer, weight numeric, geom geometry(LineString,3857) ); --线的拆分点，合并成路段 do language plpgsql $$ DECLARE rec record; i int; ptcount int; geomarr geometry[]; beforerec record; isstart boolean:=true; BEGIN for rec in select * from road_pt order by id,ptindex loop if(isstart=true) then beforerec:=rec; isstart:=false; geomarr:=array[rec.geom]; continue; end if; if(rec.id!=beforerec.id) then insert into road_split(id,weight,geom) values (beforerec.id,beforerec.weight,st_makeline(geomarr)); geomarr:=array[rec.geom]; else if(rec.weight>beforerec.weight) then geomarr:=array_append(geomarr,rec.geom); insert into road_split(id,weight,geom) values (rec.id,beforerec.weight,st_makeline(geomarr)); geomarr:=array[rec.geom];--开启新篇章 elsif(rec.weight<beforerec.weight) then insert into road_split(id,weight,geom) values (rec.id,beforerec.weight,st_makeline(geomarr)); geomarr:=array[beforerec.geom,rec.geom]; else geomarr:=array_append(geomarr,rec.geom); end if; end if; beforerec:=rec; end loop; insert into road_split(id,weight,geom) values (beforerec.id,beforerec.weight,st_makeline(geomarr)); end; $$; --30 s --创建表，用于合并路段 CREATE TABLE road_merge( gid serial primary key, weight numeric, geom geometry(LineString,3857) ); --根据权重，图形聚合，去掉重复的，结果就是需要的 insert into road_merge(weight,geom) select weight,geom from road_split group by weight,geom; --1.4s 该算法的灵感认为：点的图形计算比线极大简化+空间索引的查询效率。请注意程序中每次的中间步骤表都有gist索引的创建！用于聚合，相等等判断快速完成。 处理结果.png 最终，将每条线处理耗时10分钟（处理完333小时），优化到全部处理完成5min，且仍有可优化空间。 四 思考 shp数据不应做项目应用，正如这位科研工作者的arcpy处理shp很慢的结果，参考《shp与PostGIS在项目应用中比较》。 批量空间数据处理，尤其大批量计算和分析，肯定应在空间数据引擎中实现而非后台，充分利用索引。 复杂分析，可能的话，可将图形抽象成点去分析，极大提升分析速度。 事情完成不代表做好，在某个数据量下可行不代表在任何情况下可行，思路要开阔，技术要多比较。 坚信GIS未来是空间数据的时代，PostGIS+大数据数据平台大有可为。
前文《基于PgRouting的GIS网络分析--数据准备》描述了如何进行数据准备工作，入门的朋友可以参考如何搭建环境，导入数据和建立索引等准备。pgrouting是postgis的插件，主要做网络分析等业务使用，一般一个地区，一个城市几万级别的路网，查询是非常快速的，但是全国路网动辄几百万，几千万的路网规模，默认查询就非常的慢了。于是，本文主要以dijkstra算法，安装pg的单机默认配置，重点阐述如何“动脑经”加速路径查询速度，而不是单纯依靠机器配置（毕竟再牛逼的机器也架不住无脑的大量运算啊），当然本文的方法并不是非常规范和标准，但提供了一个解决问题的思路，即大量路网的复杂查询优化一定要避免全表查询，尽量减少计算！！！ 一 全表路径分析查询 北京路网.png 查询路网的线数据规模： network=# select count(*) from ways; count --------- 1250371 (1 row) 以dijkstra算法示例查询： 规划A_B的路径查询.png 如上图：A点坐标[115.2,39.8]，B点坐标[115.4,40]，A点的附近对应道路的gid是487371，B点附近对应道路的gid是62553（gid事先查询好的，测试就不写如何获取坐标附近的道路gid），考虑到道路有单行道的关系，所以有通行权重cost和反向权重reverse_cost ，查询语句如下： SELECT * FROM pgr_dijkstra('SELECT gid as id,snodeid as source,enodeid as target,length::float as cost,rev_length::float as reverse_cost FROM ways ',487371,62553,true); 返回93行记录，平均耗时5.8s，但是如图可知，其实AB两点比较近，而大部分路网其实对他们的计算根本就没关系，于是我们考虑在一开始查询时就规避无效路网。 二 矩形范围过滤 矩形过滤 我们发现，AB之间范围及其附近的路网就足够分析出路径，而大量其他数据是没有任何影响作用的，我们设AB两点构成一个矩形，然后缓冲2km作为备用参与分析道路（即红色斜线部分），语句如下： SELECT * FROM pgr_dijkstra('SELECT gid as id,snodeid as source,enodeid as target,length::float as cost,rev_length::float as reverse_cost FROM ways where st_intersects(geom,st_buffer(ST_PolygonFromText(''POLYGON((115.2 39.8,115.4 39.8,115.4 40,115.2 40,115.2 39.8))'',4326),0.02)) ',487371,62553,true); 返回93行记录，平均耗时150 ms。 实验结果证明：与全表查询的分析结果一致，但全表查询是矩形查询耗时的 5800/150约49倍，明显优化速度还是很明显的。 矩形问题.png 但是矩形查询也存在一个问题，当AB两点的经度接近（极端情况就是一致），那么两点只能构成一个 水平或者垂直的 线段（无法构造矩形区域），绝大部分形成一个细长的面区域如上图，这种情况下，因不能构造矩形筛选区域，或者说构造的区域过于狭窄无法满足路径查询的要求，采用“线性过滤”会更恰当些。 三 线性范围过滤 线性过滤示意图.png AB两点坐标接近垂直或水平时，可选用线性查询。举例：AB两点不变，根据AB两点坐标构成线，缓冲5公里（线比矩形那个要大，尽量将可能的道路加入分析），查询语句如下： SELECT * FROM pgr_dijkstra('SELECT gid as id,snodeid as source,enodeid as target,length::float as cost,rev_length::float as reverse_cost FROM ways where st_intersects(geom,st_buffer(ST_LineFromText(''LineString(115.2 39.8,115.4 40)'',4326),0.05)) ',487371,62553,true); 耗时：180ms，效果仍然比较明显。 四 网格筛选过滤 三四节作者猜想了以矩形和线性做查询筛选（线性更通用，不推荐矩形查询），但是他们只能处理两点比较近的时候，筛选出一小部分区域作分析标本，随着两点朝相反对角线拉大，以上图形构成的查询区域也随之变得很大，即使有索引，但是pg的查询优化器发现查询的数量非常大而不是小部分时，可能不走索引，也就是说，随着两点距离变大，越来越接近于全表查询（甚至比全表查询还慢）。这种情况下，作者采用网格对路段分组，如下图： 城市路网网格化.png 图形可视化，形象生动可见路网和格网的关系，但我们还是客观具体的看下表中数据的关系如下。 查询网格： network=# \d maps 数据表 "public.maps" 栏位 | 类型 | 修饰词 -------+------------------------+-------- mapid | integer | 非空 geom | geometry(Polygon,4326) | 索引： "maps_pkey" PRIMARY KEY, btree (mapid) "maps_geom_index" gist (geom) network=# select * from maps limit 10; mapid | geom --------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 595756 | 0103000020E6100000010000000500000065BDBD10976F5D402753A278D7DF434065BDBD10976F5D407D776D6786EA4340C85C19549B775D407D776D6786EA4340C85C19549B775D402753A278D7DF434065BDBD10976F5D402753A278D7DF4340 555571 | 0103000020E61000000100000005000000463150849AC75C4025A93394E69F4240463150849AC75C40AB6B40C694AA42406270E6BA9DCF5C40AB6B40C694AA42406270E6BA9DCF5C4025A93394E69F4240463150849AC75C4025A93394E69F4240 615867 | 0103000020E6100000010000000500000074D6E1C79CB75D40CA83DB771895444074D6E1C79CB75D4024EAEC01C69F44407C61536399BF5D4024EAEC01C69F44407C61536399BF5D40CA83DB771895444074D6E1C79CB75D40CA83DB7718954440 615707 | 0103000020E61000000100000005000000E144B24F99775D4091A8275E2B554440E144B24F99775D403459CC9DD35F4440EE76FF50977F5D403459CC9DD35F4440EE76FF50977F5D4091A8275E2B554440E144B24F99775D4091A8275E2B554440 605752 | 0103000020E610000001000000050000007EEB1E34964F5D40911DA72A253544407EEB1E34964F5D40D20A5FA1873E4440A3C87B5192575D40D20A5FA1873E4440A3C87B5192575D40911DA72A253544407EEB1E34964F5D40911DA72A25354440 555451 | 0103000020E61000000100000005000000A71F798C97875C4093814DE7948A4240A71F798C97875C40BA5D58CC429542406E34FC7E9C8F5C40BA5D58CC429542406E34FC7E9C8F5C4093814DE7948A4240A71F798C97875C4093814DE7948A4240 595632 | 0103000020E610000001000000050000009238F1F69C0F5D40308FCA877FCA43409238F1F69C0F5D40FE13F9812DD5434081CFEE149B175D40FE13F9812DD5434081CFEE149B175D40308FCA877FCA43409238F1F69C0F5D40308FCA877FCA4340 625533 | 0103000020E6100000010000000500000098C4D5D890D75C40E3B5BF7161CA444098C4D5D890D75C40F392BDAD0DD54440F0826F3794DF5C40F392BDAD0DD54440F0826F3794DF5C40E3B5BF7161CA444098C4D5D890D75C40E3B5BF7161CA4440 615633 | 0103000020E610000001000000050000008947A0C997175D406F0490771A7544408947A0C997175D406A69B0A1C27F44407B9CEDFA9A1F5D406A69B0A1C27F44407B9CEDFA9A1F5D406F0490771A7544408947A0C997175D406F0490771A754440 615663 | 0103000020E610000001000000050000007DAF424697175D407346393D149544407DAF424697175D407CCE61E7BB9F44400418F9699A1F5D407CCE61E7BB9F44400418F9699A1F5D407346393D149544407DAF424697175D407346393D14954440 (10 行记录) 查询路网 network=# \d ways 数据表 "public.ways" 栏位 | 类型 | 修饰词 ------------+---------------------------+--------------------------------------------- gid | integer | 非空 默认 nextval('ways_gid_seq'::regclass) name | character varying(128) | pyname | character varying(128) | mapid | integer | id | character varying(13) | kind_num | character varying(2) | kind | character varying(30) | width | character varying(3) | direction | character varying(1) | toll | character varying(1) | const_st | character varying(1) | undconcrid | character varying(13) | snodeid | integer | enodeid | integer | funcclass | character varying(2) | detailcity | character varying(1) | through | character varying(1) | unthrucrid | character varying(13) | ownership | character varying(1) | road_cond | character varying(1) | special | character varying(1) | admincodel | character varying(6) | admincoder | character varying(6) | uflag | character varying(1) | onewaycrid | character varying(13) | accesscrid | character varying(13) | speedclass | character varying(1) | lanenums2e | character varying(2) | lanenume2s | character varying(2) | lanenum | character varying(1) | vehcl_type | character varying(32) | elevated | character varying(1) | structure | character varying(1) | usefeecrid | character varying(13) | usefeetype | character varying(1) | spdlmts2e | character varying(4) | spdlmte2s | character varying(4) | spdsrcs2e | character varying(1) | spdsrce2s | character varying(1) | dc_type | character varying(1) | nopasscrid | character varying(13) | geom | geometry(LineString,4326) | length | double precision | rev_length | double precision | x1 | double precision | y1 | double precision | x2 | double precision | y2 | double precision | 索引： "ways_pkey" PRIMARY KEY, btree (gid) "mapid_index" btree (mapid) "ways_enodeid_idx" btree (enodeid) "ways_geom_idx" gist (geom) "ways_snodeid_idx" btree (snodeid) network=# select a.gid,a.mapid from ways a,(select mapid from maps limit 1) b where a.mapid=b.mapid limit 10; gid | mapid ---------+-------- 112434 | 595756 112440 | 595756 117555 | 595756 23611 | 595756 1041239 | 595756 1193746 | 595756 694218 | 595756 735844 | 595756 739260 | 595756 740230 | 595756 (10 行记录) 网格和路网之间已经根据mapid建立了关系，路网中mapid建立了索引，格网中mapid是主键。 根据图形之前的关系，我们的思路是：根据AB两点建立直线，对该直线建立一定范围内的缓冲面，缓冲面查询与哪些网格有相交关系（相交意味着这些网格是有效的分析网格，其他网格就没任何关系了），直接把这些有效的网格中的路段，作为分析的样本。 测试范围.png 全表查询AB路径如下： --测试样本 network=# select count(*) from ways; count --------- 1250371 (1 行记录) network=# SELECT * FROM pgr_dijkstra('SELECT gid as id,snodeid as source,enodeid as target,length::float as cost,rev_length::float as reverse_cost FROM ways' ,647331,856772,true); --耗时8.1s 线性查询AB路径如下： network=# select count(*) from ways where geom&&st_buffer(ST_LineFromText('LineString(114.53247 37.34692,118.125 39.82983)',4326),0.08); count -------- 678892 (1 行记录) network=# SELECT * FROM pgr_dijkstra('SELECT gid as id,snodeid as source,enodeid as target,length::float as cost,rev_length::float as reverse_cost FROM ways where geom&&st_buffer(ST_LineFromText(''LineString(114.53247 37.34692,118.125 39.82983)'',4326),0.08) ',647331,856772,true); --耗时4.5s 网格查询AB路径如下： network=# select count(*) from ways where mapid in ( select mapid from maps where st_intersects(geom,st_buffer(ST_LineFromText('LineStr ing(114.53247 37.34692,118.125 39.82983)',4326),0.08))); count -------- 127914 (1 行记录) network=# SELECT * FROM pgr_dijkstra('SELECT gid as id,snodeid as source,enodeid as target,length::float as cost,rev_length::float as reverse_cost FROM ways where mapid in (select mapid from maps where st_intersects(geom,st_buffer(ST_LineFromText(''LineString(114.53247 37.34692,118.125 39.82983)'',4326),0.08))) ',647331,856772,true); --耗时1.8s 表格对比如下： 查询方式 查询数据量 查询时间 全表查询 1250371 8.1 s 线性查询 678892 4.5 s 网格查询 127914 1.8 s 虽然不是太满意结果，但是结果的确体现了“智慧的结晶”了，还算欣慰。 五 道路属性过滤 以全国路网分析简介（未测试），虽然几百万路网，但是 高速，快速路这种道路并不是很多，在做远距离路径分析时，优先选择起点最近的高速A1，终点附近的高速B1，那么先规划AA1，再A1B1，再B1B，将全国路网全表查询细分成高速路路径分析，高速和城市道路分析，细节部分也可采用范围叠加规划。同样的，对完整路网拆分成高速国道的一级查询，省道县道的二级查询，乡道的三级查询等等，尽可能将路网从整体分析中抽离出去。 五 总结 在近距离路径分析时，可考虑范围进行过滤，然后查询过滤后的路网。在远距离路径分析时，可以实际的条件做过滤。在城市道路时，远距离规划车辆路径，应优先规划起点到绕城，绕城出口到终点。 当然，以上测试都是单机测试，全部思路是减少查询路网数量，是动脑经的结果，没有在数据库的配置，硬件的优化上下功夫。作者将在适当时机，移入pgxl进行规划分析测试，看集群是否有利于查询速度提升。
一 Puppeteer简介 Puppeteer.js是谷歌官方推出的一个nodejs库，它提供了一组用来操纵Chrome的API（默认headless也就是无UI的chrome，也可以配置为有UI），有点类似于PhantomJS，但Puppeteer是Chrome官方团队进行维护的，前景更好。使用Puppeteer，相当于同时具有Linux和Chrome的能力，应用场景会非常多。就爬虫领域来说，远比一般的爬虫工具功能更丰富，性能分析、自动化测试也不在话下。官方列出的主要功能如下： 利用网页生成PDF、图片 爬取SPA应用，并生成预渲染内容（即“SSR” 服务端渲染）' 可以从网站抓取内容 自动化表单提交、UI测试、键盘输入等 帮你创建一个最新的自动化测试环境（chrome），可以直接在此运行测试用例 捕获站点的时间线，以便追踪你的网站，帮助分析网站性能问题 安装如下很简单： npm install puppeteer 二 输出地图图片 在工程项目中，常常有导出地图图片保存的需求，这种需求很多时候，直接在前端canvas渲染图片导出即可，参考：OpenLayers3关于Map Export的Canvas跨域，但很多时候，Map的Layer来源很多，有公网第三方网站的，有工程自己服务器的，并不是每个地图服务器都支持cors操作，canvas渲染常常无法使用，而使用服务端请求出图也是一种可选方案。 测试以本地的一个web页面为准： 测试页面.png 废话不多说，直接上代码： const puppeteer = require('puppeteer'); (async () => { const browser = await puppeteer.launch(); const page = await browser.newPage(); //登录地图网页 await page.goto('http://localhost:63342/%E7%89%B9%E6%95%88demo/Gansu.html'); //根据页面上下文，获取map div的宽高 let result = await page.evaluate((divid) => { let mapdiv=document.querySelector(`#${divid}`); let width= parseInt(mapdiv.offsetWidth); let height=parseInt(mapdiv.offsetHeight); return Promise.resolve([width,height]); }, 'map'); console.log(result); //等待1.2秒 await sleep(1200); //print png await page.screenshot({ path: 'clickbd.png', //fullPage与clip使用时互斥 //fullPage: true, //整页输出 //只clip出map div的范围输出地图 clip:{ x:0, y:0, width:result[0], height:result[1] } }); browser.close(); })(); function sleep(ms) { return new Promise(resolve => setTimeout(resolve, ms)); } 以上代码保存为工程中，起名叫print.js，然后执行： node print.js 等待之后，输出结果如下： 结果.png 本文只简单介绍下Puppeteer，可以预见，将来会越来越火，毕竟是chrome官方维护的库，比同类产品牌子更大。
前文作者讲述了BottledWater-PG安装部署，并在pg中实现了数据改变，向kafka发送消息的案例，详细参考《BottledWater-PG:PostgreSQL集成Kafka的实时数据交换平台》。此前作者写过一篇pg的异步消息实现的实时地图应用案例《postgres+socket.io+nodejs实时地图应用实践》，本文将改用BottledWater-PG实现一遍。 一 服务器端 var fs = require('fs'); var http = require('http'); var socket = require('socket.io'); var Kafka = require('node-rdkafka'); var server = http.createServer(function(req, res) { res.writeHead(200, { 'Content-type': 'text/html'}); res.end(fs.readFileSync(__dirname + '/index.html')); }).listen(8081, function() { console.log('Listening at: http://localhost:8081'); }); //注册socket.io var socketio=socket.listen(server); socketio.on('connection', function (socketclient) { console.log('已连接socket:'); //socketclient.broadcast.emit('GPSCoor', data.payload);//广播给别人 //socketclient.emit('GPSCoor', data.payload);//广播给自己 }); var consumer = new Kafka.KafkaConsumer({ //'debug': 'all', 'metadata.broker.list': '192.168.43.27:9092', 'group.id': 'node-rdkafka-consumer-flow-example', 'enable.auto.commit': false }); var topicName = 'gps'; //logging debug messages, if debug is enabled consumer.on('event.log', function(log) { console.log(log); }); //打印错误 consumer.on('error', function(err) { console.error('Error from consumer'); console.error(err); }); consumer.on('ready', function(arg) { console.log('consumer ready.' + JSON.stringify(arg)); consumer.subscribe([topicName]); //准备消费消息 consumer.consume(); }); consumer.on('data', function(m) { console.log(m); let _data; if(m.value==null)//delete操作发送来的消息 { _data=JSON.parse(m.key); _data.tg_op='delete'; } else{ _data=m.value.toString(); _data=JSON.parse(_data); } console.log(_data); socketio.emit('GPSCoor', _data);//广播给所有的客户端 }); consumer.on('disconnected', function(arg) { console.log('consumer disconnected. ' + JSON.stringify(arg)); }); //启动 consumer.connect(); 二 客户端 <html> <head> <meta charset='utf-8'> <title>实时地图应用</title> <link rel="stylesheet" href="http://openlayers.org/en/v3.18.2/css/ol.css" type="text/css"> <script src="http://openlayers.org/en/v3.18.2/build/ol.js"></script> <script src="/socket.io/socket.io.js"></script> <script> var wktform=new ol.format.WKT();//wkt解析 var gpsSource=new ol.source.Vector(); function init(){ var gpsLayer=new ol.layer.Vector({ source:gpsSource, style:new ol.style.Style({ image: new ol.style.Icon(({ anchor: [0.5, 1], src: 'http://openlayers.org/en/v3.18.2/examples/data/icon.png' })) }) }); var map = new ol.Map({ layers : [ new ol.layer.Tile({ title : '街道图', visible : true, source : new ol.source.XYZ({ url : 'http://www.google.cn/maps/vt?pb=!1m5!1m4!1i{z}!2i{x}!3i{y}!4i256!2m3!1e0!2sm!3i342009817!3m9!2szh-CN!3sCN!5e18!12m1!1e47!12m3!1e37!2m1!1ssmartmaps!4e0&token=32965' }) }), gpsLayer ], target : 'map', controls : ol.control.defaults({ attributionOptions : ({ collapsible : false }) }), view : new ol.View({ center : [0, 0], zoom : 2 }) }); var iosocket = io.connect(); //接受服务端消息 iosocket.on('GPSCoor', function(data) { console.log(data); var id=data.id.int; var feature; if(data.tg_op=='delete'){ feature=gpsSource.getFeatureById(id); if(feature) gpsSource.removeFeature(feature);//删除点 } else{ var geom=data.geom.string; geom=wktform.readGeometry(geom); geom.transform('EPSG:4326','EPSG:3857'); feature=gpsSource.getFeatureById(id); if(feature) feature.setGeometry(geom);//修改已有点 else{ feature=new ol.Feature({ geometry:geom }); feature.setId(id); gpsSource.addFeature(feature);//地图新增点 } } }); } </script> </head> <body onload="init()"> <div id="map"></div> </body> </html> 三 测试成果 3.1 新增 mcsas=# insert into gps(name,geom) values ('opy','Point(118 31.5)'); INSERT 0 1 mcsas=# insert into gps(name,geom) values ('ty','Point(117 30.5)'); INSERT 0 1 新增成果.png 3.2 修改 mcsas=# update gps set geom='Point(115 40)' where name='opy'; UPDATE 1 修改成果.png 3.3 删除 mcsas=# delete from gps where name='opy'; DELETE 1 删除结果.png 四 总结 BottledWater-PG主要作用是将pg库中的表的增删改的消息都发往了kafka，应用程序并没有直接连接数据库，而是直接去消费kafka的消息。在表发生insert,update,delete能获取消息，但是truncate table并未向kafka生成消息，不知是否是我哪里遗漏。 作者之前曾使用pg自带的notify与listen实现异步消息发送，该方法借助了表的触发器实现。应用程序是直连数据库且数据增删改都会走触发器。 匆忙中，作者并未对比两者之间孰优孰劣，但一个直连库，一个间接消费，在不同需求中可选择一个比较符合要求的方案而加以应用。
众所周知，GeoServer是一个地理服务器，提供了管理页面进行服务发布，样式，切片，图层预览等一系列操作，但是手动进行页面配置有时并不满足业务需求，所以GeoServer同时提供了丰富的rest接口可供用户自己组织业务逻辑进行自动化管理。 本文以气象文件的NetCDF自动化发布的需求，阐述如何以rest接口实现用户多样性需求。气象文件特殊性在于几乎每隔一段时间就会更新，甚至逐小时或半小时的更新频率，用户如果手动发布了气象文件的若干图层作为专题服务，一旦获取到最新的气象文件，用户希望立马可以看到新的数据源上的专题图，而人工即时更新现有的图层服务几乎是不现实的，类似这种定时或者即时响应的需求应该交由自动化完成，本文实现NetCDF气象文件自动发布便是为了解决此类需求。 一 NetCDF插件安装 选择对应版本的下载地址：http://geoserver.org/release/2.11.0/ NetCDF插件.png 下载插件，解压，将jar文件全部复制到geoserver中的webapps\geoserver\WEB-INF\lib目录中，重启geoserver即可。 二 rest示例 2.1 发布nc文件数据存储 将E:\xxx.nc该文件发布成栅格数据存储，发布到cite工作区，数据存储名称为netcdfstore。 curl -v -u admin:geoserver -XPOST -H "Content-type: text/xml" -d "<coverageStore><name>netcdfstore</name><type>NetCDF</type><enabled>true</enabled><workspace><name>cite</name></workspace><__default>false</__default><url>file://E://xxx.nc</url></coverageStore>" http://localhost:8090/geoserver/rest/workspaces/cite/coveragestores/netcdfstore 注意路径格式是：file://E://xxx.nc，而不是file://E:\xxx.nc或file://E:\\xxx.nc，这应该是该插件的一个bug。 2.2 修改nc文件数据存储 将netcdfstore的数据存储位置由E:\xxx.nc指向D:\xxv.nc。 curl -v -u admin:geoserver -XPUT -H "Content-type: text/xml" -d "<coverageStore><name>netcdfstore</name><type>NetCDF</type><enabled>true</enabled><workspace><name>cite</name></workspace><__default>false</__default><url>file://D://xxc.nc</url></coverageStore>" http://localhost:8090/geoserver/rest/workspaces/cite/coveragestores/netcdfstore 2.3 发布栅格图层 将netcdfstore数据存储中的RH2图层发布 curl -v -u admin:geoserver -XPOST -H "Content-type: text/xml" -d "<coverage><nativeCoverageName>RH2</nativeCoverageName><name>RH2</name></coverage>" http://localhost:8090/geoserver/rest/workspaces/cite/coveragestores/netcdfstore/coverages 2.4 绑定图层样式 将发布的RH2样式绑定已经发布的一个名称叫RH2Style的样式。 curl -v -u admin:geoserver -XPUT -H "Content-type: text/xml" -d "<layer><defaultStyle><name>RH2Style</name></defaultStyle></layer>" http://localhost:8090/geoserver/rest/layers/RH2 三 自动化发布 var child_process = require('child_process'); var async = require('async'); //构造一个netcdf管理类 function NetCDFManager(options){ this.ip=options.ip; this.port=options.port; this._geoserverurl=`http://${this.ip}:${this.port}/geoserver/rest`; this.user=options.user;//geoserver的用户名密码 this.password=options.password; this.layerlist=options.layerlist; this.ws=(options.ws!==undefined)?options.ws:'netcdf';//工作区间，默认是netcdf工作区间 this.storename=(options.storename!==undefined)?options.storename:'netcdfstore';//netcdf数据存储名称，默认是netcdfstore } //根据名称获取栅格数据存储 NetCDFManager.prototype.getCoverageStorebyName=function(cb){ let storename=this.storename; let url=this._geoserverurl+`/workspaces/${this.ws}/coveragestores/${storename}.json`; var cmd=`curl -v -u ${this.user}:${this.password} -XGET ${url}`; child_process.exec(cmd, function(err,stdout,stderr) { if(stdout.indexOf('No such')>-1){ cb(false); return; } if(JSON.parse(stdout).coverageStore.name===storename) cb(true); else cb(false); }); } //发布一个栅格数据存储 NetCDFManager.prototype.publishCoverageStore = function(netcdffile,cb){ netcdffile=netcdffile.replace(/\\/g,'//'); var xml=`<coverageStore><name>${this.storename}</name><type>NetCDF</type><enabled>true</enabled><workspace><name>${this.ws}</name></workspace><__default>false</__default><url>file://${netcdffile}</url></coverageStore>`; var cmd=`curl -v -u ${this.user}:${this.password} -XPOST -H "Content-type: text/xml" -d "${xml}" ${this._geoserverurl}/workspaces/${this.ws}/coveragestores`; child_process.exec(cmd, function(err,stdout,stderr) { if(stdout=='') cb(true); else cb(false); }); } //修改已发布的数据存储 NetCDFManager.prototype.updateCoverageStore = function(netcdffile,cb){ netcdffile=netcdffile.replace(/\\/g,'//'); var xml=`<coverageStore><name>${this.storename}</name><type>NetCDF</type><enabled>true</enabled><workspace><name>${this.ws}</name></workspace><__default>false</__default><url>file://${netcdffile}</url></coverageStore>`; var cmd=`curl -v -u ${this.user}:${this.password} -XPUT -H "Content-type: text/xml" -d "${xml}" ${this._geoserverurl}/workspaces/${this.ws}/coveragestores/${this.storename}`; child_process.exec(cmd, function(err,stdout,stderr) { if(stdout=='') cb(true); else cb(false); }); } //发布一个图层 NetCDFManager.prototype.publishCoverage = function(coverage_name,cb){ let xml=`<coverage><nativeCoverageName>${coverage_name}</nativeCoverageName><name>${coverage_name}</name></coverage>`; let url=`${this._geoserverurl}/workspaces/${this.ws}/coveragestores/${this.storename}/coverages`; var cmd=`curl -v -u ${this.user}:${this.password} -XPOST -H "Content-type: text/xml" -d "${xml}" ${url}`; child_process.exec(cmd, function(err,stdout, stderr) { if(stdout=='') cb(true); else cb(false); }); } //给发布的图层赋予样式 NetCDFManager.prototype.setLayerStyle = function(layername,stylename,cb){ let xml=`<layer><defaultStyle><name>${stylename}</name></defaultStyle></layer>`; let url=`${this._geoserverurl}/layers/${layername}`; var cmd=`curl -v -u ${this.user}:${this.password} -XPUT -H "Content-type: text/xml" -d "${xml}" ${url}`; child_process.exec(cmd, function(err,stdout, stderr) { if(stdout=='') cb(true); else cb(false); }); } /* 伪逻辑代码 1 根据数据存储名称，判定是否有该数据存储。没有，publishCoverageStore一个，接步骤2.有，updateCoverageStore即可，end！ 2 publishCoverageStore发布数据存储后，将规定要发布的图层逐一发布publishCoverage，逐一赋予样式setLayerStyle 注意都是异步的，需要后台代码转同步，js中的async库负责处理异步陷阱，其他语言自行百度。 */ var netCDFManager=new NetCDFManager({ ip:'localhost', port:'8090', user:'admin', password:'geoserver', ws:'netcdf', storename:'netcdfstore', layerlist:['RH2','SKT','TP','V10','VIS'] }); function publish(ncfile) { async.waterfall([ //查询是否已经存在命名为netcdfstore的数据存储 function (done) { netCDFManager.getCoverageStorebyName(function (info) { done(null, info); }); }, function (info, done) { //已存在数据存储，直接替换其数据源为新的nc文件 if (info) { console.log('指定的数据存储已存在，直接进行更新操作'); netCDFManager.updateCoverageStore(ncfile, function (info) { if (info) { console.log('数据存储已经更新成功！'); done(null, info); } else { console.log('数据存储已经更新失败！'); done(info, null); } }); } //不存在数据存储，新发布 else { console.log('指定的数据存储不存在，发布数据存储'); publishNC(ncfile, done); } } ], function (error, result) { if (error) console.log('自动发布存在错误!'); else console.log('自动发布完成!'); }) } function publishNC(ncfile,cb){ async.waterfall([function (done) { netCDFManager.publishCoverageStore(ncfile,function(info){ if(info) { console.log('数据存储已经发布成功！'); done(null, info); } else{ console.log('数据存储已经发布失败！'); done(info, null); } }); }, function (resule,done) { //发布图层 publishLayers(netCDFManager.layerlist,done); },function (result,done) { //发布样式 publishStyles(netCDFManager.layerlist,done); }],function (error, result) { if(error){ console.log('自动发布存在错误!'); cb(error,null); } else{ console.log('自动发布完成!'); cb(null,result); } }) } //自动发布一些列图层 function publishLayers(layerlist,cb){ let asyncs={}; for(let i=0;i<layerlist.length;i++){ asyncs[i]=function(done){ let layername=layerlist[i]; netCDFManager.publishCoverage(layername,function(info){ if(info) { console.log(`${layername}发布成功！`); done(null, info); } else{ console.log(`${layername}发布失败！`); done(info, null); } }); } } async.parallel(asyncs, function (error, result) { if(error) cb(error,null); else cb(null,result); }) } //修改指定图层为指定样式 function publishStyles(stylelist,cb){ let asyncs={}; for(let i=0;i<stylelist.length;i++){ asyncs[i]=function(done){ let layername=stylelist[i]; netCDFManager.setLayerStyle(layername,layername,function(info){ if(info) { console.log(`${layername}样式发布成功！`); done(null, info); } else{ console.log(`${layername}样式发布失败！`); done(info, null); } }); } } async.parallel(asyncs, function (error, result) { if(error) cb(error,null); else cb(null,result); }) } publish('D:\\G_2017070419.nc'); 执行node app.js后， 发布流程输出.png 发布结果.png 图层预览结果.png perfect!
一 安装 BottledWater-PG的安装前文已经表述，本文不赘述直接进入集成应用阶段。 二 启动KafKa #启动zookeeper [root@bogon kafka_2.11-0.10.2.0]# bin/zookeeper-server-start.sh config/zookeeper.properties #启动kafka服务端 [root@bogon kafka_2.11-0.10.2.0]# bin/kafka-server-start.sh config/server.properties 三 配置PostgreSQL 3.1 配置读取权限 Bottled Water会连接到postgresql获取相关数据，连接的账户需要有replication权限，pg中数据库的变化存储在WAL中，至少需要replication权限才能读取WAL。 编辑$PGDATA目录中postgresql.conf和pg_hba.conf文件。 vi $PGDATA/postgresql.conf #编辑内容如下： listen_addresses = '*' port = 5432 wal_level = logical max_wal_senders = 8 wal_keep_segments = 4 max_replication_slots = 4 vi $PGDATA/pg_hba.conf #编辑内容如下： # IPv4 local connections: host all all 0.0.0.0/0 md5 # replication privilege. local replication freerep trust host replication freerep 127.0.0.1/32 trust host replication freerep ::1/128 trust 编辑完保存，重启数据库服务： pg_ctl restart psql postgres=# CREATE ROLE freerep WITH REPLICATION PASSWORD 'password' LOGIN; CREATE ROLE 配置完毕！ 3.2 Bottled Water使用演示 3.2.1 创建测试库表 创建一个测试数据库，建立测试表 postgres=# create database mcsas; postgres=# \c mcsas; mcsas=# create extension bottledwater; mcsas=# create extension postgis; #赋予public下的表给freerep角色，要创建如下语句，否则建立的表freerep没有读取权限 mcsas=# alter default privileges in schema public grant all on tables to freerep; mcsas=# create table gps(gid serial primary key,name text,geom text); mcsas=# create index gps_geom_idx on gps using gist(ST_GeomFromText(geom,4326)); 在另一个终端启动bottledwater可执行程序: source /home/postgres/.bashrc cd /opt/bottledwater-pg-master/kafka [root@localhost kafka]# ./bottledwater -d postgres://freerep:password@127.0.0.1/mcsas -b 192.168.43.27:9092 -f json 启动结果如下： [root@bogon kafka]# ./bottledwater -d postgres://freerep:password@127.0.0.1/mcsas -b 192.168.43.27:9092 -f json [INFO] Writing messages to Kafka in JSON format [INFO] Created replication slot "bottledwater", capturing consistent snapshot "0000DA72-1". INFO: bottledwater_export: Table public.spatial_ref_sys is keyed by index spatial_ref_sys_pkey INFO: bottledwater_export: Table public.mark is keyed by index mark_pkey [INFO] Registering metadata for table spatial_ref_sys (relid 24263) [INFO] Opening Kafka topic "spatial_ref_sys" for table "spatial_ref_sys" [INFO] Storing key schema for table 24263 [INFO] Storing row schema for table 24263 [INFO] Snapshot complete, streaming changes from 0/AB016F30. 代表启动成功了。 3.2.2 监听数据改变消息 插入数据 mcsas=# insert into gps(name,geom) values ('china','Point(118 32)'); INSERT 0 1 mcsas=# insert into gps(name,geom) values ('england','Point(118 12)'); INSERT 0 1 启动监听topic bin/kafka-console-consumer.sh --bootstrap-server 192.168.43.27:9092 --topic gps --from-beginning {"gid": {"int": 1}, "name": {"string": "china"}, "geom": {"string": "Point(118 32)"}} {"gid": {"int": 2}, "name": {"string": "england"}, "geom": {"string": "Point(118 12)"}} 每当插入或者更新，收听的消息会源源不断的输出出来，这样，pg与kafka集成就完毕了。
一 前言 Bottled Water是Confluent公司开发的一款可以将postgresql数据库转换为kafka events的工具。主要用于监视PG的数据增删改的变化，将变化的数据实时推送到kafka消息队列，其他关心数据变化的平台只要监听kafka的消息，就能得到改变的数据。具体参考德哥的博客《实时数据交换平台 - BottledWater-pg with confluent》，本文具体的安装步骤也参考了《Bottled Water: 实时集成postgresql与kafka》。 二 安装准备 安装完成PostgreSQL，参考《Centos7安装PostgreSQL9.6》； 安装完成Kafka，参考《Kafka部署》； 此外还依赖avro-c，Jansson，libcurl，librdkafka 三 安装步骤bottledwater-pg 3.1 安装libjansson wget http://www.digip.org/jansson/releases/jansson-2.9.tar.bz2 tar -jxvf jansson-2.9.tar.bz2 cd jansson-2.9 ./configure --prefix=/home/free/jansson make make install export PKG_CONFIG_PATH=/home/free/jansson/lib/pkgconfig:$PKG_CONFIG_PATH pkg-config --cflags --libs jansson -I/home/free/jansson/include -L/home/free/jansson/lib -ljansson 3.1 安装avro wget http://mirrors.hust.edu.cn/apache/avro/avro-1.8.1/avro-src-1.8.1.tar.gz tar -zxvf avro-src-1.8.1.tar.gz cd avro-src-1.8.1/lang/c mkdir build cd build cmake .. -DCMAKE_INSTALL_PREFIX=/home/free/avro -DCMAKE_BUILD_TYPE=Release -DTHREADSAFE=true make make test make install export PKG_CONFIG_PATH=/home/free/avro/lib/pkgconfig:$PKG_CONFIG_PATH 在make这一步骤报错如下： opt/avro-src-1.8.1/lang/c/src/schema.c:31:21: fatal error: jansson.h: No such file or directory #include "jansson.h" ^ compilation terminated. make[2]: *** [src/CMakeFiles/avro-shared.dir/schema.o] Error 1 make[1]: *** [src/CMakeFiles/avro-shared.dir/all] Error 2 make: *** [all] Error 2 这一步骤应该是编译缺少了头文件，先加上去，然后重新编译。 export C_INCLUDE_PATH=/home/free/jansson/include:$C_INCLUDE_PATH 3.3 安装libcurl wget https://curl.haxx.se/download/curl-7.54.0.tar.gz tar -zxvf curl-7.54.0.tar.gz cd curl-7.54.0 ./configure --prefix=/home/free/curl make make install export PKG_CONFIG_PATH=/home/free/curl/lib/pkgconfig:$PKG_CONFIG_PATH 3.4 安装librdkafka git clone https://github.com/edenhill/librdkafka cd librdkafka ./configure --prefix=/home/free/librdkafka #假如报错如下： #[root@bogon librdkafka]# ./configure --prefix=/home/freegis/librdkafka #-bash: ./configure: Permission denied #赋予执行权限 #[root@bogon librdkafka]# chmod 777 configure make -j 32 make install export PKG_CONFIG_PATH=/home/free/librdkafka/lib/pkgconfig:$PKG_CONFIG_PATH 在make的时候，64位的linux会报错如下： /bin/ld:librdkafka.lds:1: syntax errer in VERSION script 只需要在Makefile.config里面的WITH_LDS=y这一行注释掉重新make就可以了。 3.5 安装bottledwater-pg 在环境变量中新增如下依赖： vi /etc/profile #编辑内容如下 export CURLHOME=/home/freegis/curl PATH=$PATH:$HOME/.local/bin:$HOME/bin:$GCCHOME/bin:$CURLHOME/bin # wq!保存 source /etc/profile 下载安装文件并编译部署： git clone https://github.com/confluentinc/bottledwater-pg cd bottledwater-pg #启用postgres的环境变量 source /home/postgres/.bashrc make make install make可能报错如下： ake[1]: Entering directory `/opt/bottledwater-pg-master/kafka' gcc bottledwater.c -c -std=c99 -D_POSIX_C_SOURCE=200809L -I../client -I../ext -I/home/postgres/include -I/home/postgres/include/server -I/home/freegis/avro/include -DAVRO_1_8 -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels -Wmissing-format-attribute -Wformat-security -o bottledwater.o In file included from table_mapper.h:4:0, from json.h:5, from bottledwater.c:2: registry.h:4:32: fatal error: librdkafka/rdkafka.h: No such file or directory #include <librdkafka/rdkafka.h> ^ compilation terminated. 这种情况仍然是头文件找不到，为了麻烦，把用到的头文件都指定。 export C_INCLUDE_PATH=/home/free/jansson/include:$C_INCLUDE_PATH export C_INCLUDE_PATH=/home/free/avro/include:$C_INCLUDE_PATH export C_INCLUDE_PATH=/home/free/librdkafka/include:$C_INCLUDE_PATH export C_INCLUDE_PATH=/home/free/curl/include:$C_INCLUDE_PATH 继续编译后报错： /usr/bin/ld: cannot find -lrdkafka 详细如下： 缺失依赖.png 建立依赖软连接： ln -s /home/free/librdkafka/lib/librdkafka.so /home/postgres/lib/librdkafka.so 检查安装情况 su - postgres psql create extension bottledwater; 如果创建扩展报错如下： mcsas=# create extension bottledwater; ERROR: could not load library "/home/postgres/lib/bottledwater.so": libavro.so.23.0.0: cannot open shared object file: No such file or directory 查看下bottledwater.so的依赖： [postgres@bogon lib]$ ldd bottledwater.so linux-vdso.so.1 => (0x00007fffc41fe000) libavro.so.23.0.0 => /home/freegis/avro/lib/libavro.so.23.0.0 (0x00007f1f26769000) libc.so.6 => /lib64/libc.so.6 (0x00007f1f263a8000) libjansson.so.4 => /lib64/libjansson.so.4 (0x00007f1f2619c000) libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f1f25f7f000) /lib64/ld-linux-x86-64.so.2 (0x00007f1f26bba000) 发现所有的依赖都正确。 后经大神提示，重启数据库，然后执行扩展。 pg_ctl restart psql \c mcsas create extension bottledwater; CREATE EXTENSION
一 介绍 Kafka是一种高吞吐量的分布式发布订阅消息系统，它可以处理消费者规模的网站中的所有动作流数据，下载地址如下：https://www.apache.org/dyn/closer.cgi?path=/kafka/0.10.2.0/kafka_2.11-0.10.2.0.tgz 二 启动 #启动zookeeper bin/zookeeper-server-start.sh config/zookeeper.properties #更改配置 vi config/server.properties #编辑内容如下 delete.topic.enable=true listeners=PLAINTEXT://192.168.43.27:9092 #保存退出 #启动kafka服务端 bin/kafka-server-start.sh config/server.properties 三 创建主题 bin/kafka-topics.sh --create --zookeeper localhost:2181 --replication-factor 1 --partitions 1 --topic test # 列出主题列表 bin/kafka-topics.sh --list --zookeeper localhost:2181 # 删除主题 bin/kafka-topics.sh --delete --zookeeper 192.168.43.27:2181 --topic road 四 创建消息 bin/kafka-console-producer.sh --broker-list localhost:9092 --topic test #发送消息 This is a message This is another message 五 接收消息 bin/kafka-console-consumer.sh --bootstrap-server localhost:9092 --topic test --from-beginning this is a message this is a another message 六 遇到的问题 在生产消息时，发生如下问题： WARN Error while fetching metadata with correlation id 1 : {test=LEADER_NOT_AVAILABLE} (org.apache.kafka.clients.NetworkClient) 查看日志如下： Registered broker 0 at path /brokers/ids/0 with addresses: PLAINTEXT -> EndPoint(218.30.64.194,9092,PLAINTEXT) (kafka.utils.ZkUtils) ip是218.30.64.194不是localhost，没有绑定Kafka启动监听的host信息，只需更改配置文件绑定即可。 vi config/server.properties #修改配置如下： #原配置：listeners=PLAINTEXT://:9092 listeners=PLAINTEXT://localhost:9092 重修启动zookeeper和kafka即可。
最近遇到一个PgPool连接阻塞问题，PgPool刚开启是能成功连接的，过段时间就连接不上了。查看PgPool日志，启动成功，连接数据库节点成功，健康检查成功。然后怀疑是并发数过多导致阻塞。 一开始，更改了pgpool.conf的max_pool,num_init_children参数然后重启，结果仍然阻塞。查资料可知： num_init_children：pgPool允许的最大并发数，默认32。 max_pool：连接池的数量，默认4。 pgpool需要的数据库连接数=num_init_children*max_pool； 后检查Postgresql数据库的postgresql.conf文件的max_connections=100，superuser_reserved_connections=3。 pgpool的连接参数应当满足如下公式： num_init_children*max_pool<max_connections-superuser_reserved_connections 当需要pgpool支持更多的并发时，需要更改num_init_children参数，同时要检查下num_init_children*max_pool是否超过了max_connections-superuser_reserved_connections，如果超过了，可将max_connections改的更大。
一 前言 众所周知，Node可以很轻松的连接PostgreSQL,MySQL,Redis,Mogondb等开源数据库，但是想使用Node连接Oracle一直非常难，不仅仅在Windowns操作系统上，即使是Linux上也非常难编译或运行成功，笔者经过多次失败，最近找到一个oracle官方提供的库oracledb，之所以叫oracledb，是之前npm上已经存在一个山寨的oracle包，安装过程如下： npm install oracle 当然并不怎么好编译通过和使用，不过oralce官方也没办法了，只能自己叫oracledb了。下文讲述如何安装和简单使用，以linux为例。 二 安装 2.1 安装前提 本机要预先安装gcc4.7及以上版本（编译node需要使用）。 2.2 安装nodejs 假设把node的安装包下载在/opt目录。 cd /opt # 解压 tar -Jxf node-v6.9.4-linux-x64.tar.xz # 设置环境变量 vi /etc/profile #编辑文件内如如下： export NODEHOME=/opt/node-v7.9.0-linux-x64 PATH=$PATH:$HOME/.local/bin:$HOME/bin:$NODEHOME/bin #保存退出 #重启用profile文件 source /etc/profile # 检测node安装情况 [root@localhost testoracle]# node -v v7.9.0 [root@localhost testoracle]# npm -v 4.2.0 如上步骤，安装完毕，是不是很简单？ 2.3 安装Oracle Instant Client 从oralce官网下载 'Basic' and 'SDK'，假如下载位置在/opt/oracle。 cd /opt/oracle unzip instantclient-basic-linux.x64-12.2.0.1.0.zip unzip instantclient-sdk-linux.x64-12.2.0.1.0.zip mv instantclient_12_2 instantclient cd instantclient ln -s libclntsh.so.12.1 libclntsh.so # 设置环境变量 vi /etc/profile #编辑内容如下 export LD_LIBRARY_PATH=/opt/oracle/instantclient:$LD_LIBRARY_PATH export OCI_LIB_DIR=/opt/oracle/instantclient export OCI_INC_DIR=/opt/oracle/instantclient/sdk/include #保存退出 #重启用profile文件 source /etc/profile 2.4 安装oracledb 之前安装都已经完成了，这一步会非常简单。 [root@localhost opt]# mkdir oracletest [root@localhost opt]# cd oracletest [root@localhost oracletest]# npm install oracledb > oracledb@1.13.1 install /opt/oracletest/node_modules/oracledb > node-gyp rebuild gyp WARN EACCES user "root" does not have permission to access the dev dir "/root/.node-gyp/7.9.0" gyp WARN EACCES attempting to reinstall using temporary dev dir "/opt/aa/node_modules/oracledb/.node-gyp" make: Entering directory `/opt/aa/node_modules/oracledb/build' CXX(target) Release/obj.target/oracledb/src/njs/src/njsOracle.o CXX(target) Release/obj.target/oracledb/src/njs/src/njsPool.o CXX(target) Release/obj.target/oracledb/src/njs/src/njsConnection.o CXX(target) Release/obj.target/oracledb/src/njs/src/njsResultSet.o CXX(target) Release/obj.target/oracledb/src/njs/src/njsMessages.o CXX(target) Release/obj.target/oracledb/src/njs/src/njsIntLob.o CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiEnv.o CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiEnvImpl.o CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiException.o CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiExceptionImpl.o CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiConnImpl.o CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiDateTimeArrayImpl.o CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiPoolImpl.o CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiStmtImpl.o CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiUtils.o CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiLob.o CXX(target) Release/obj.target/oracledb/src/dpi/src/dpiCommon.o SOLINK_MODULE(target) Release/obj.target/oracledb.node COPY Release/oracledb.node make: Leaving directory `/opt/oracletest/node_modules/oracledb/build' /opt/oracletest └─┬ oracledb@1.13.1 └── nan@2.5.1 npm WARN enoent ENOENT: no such file or directory, open '/opt/oracletest/package.json' npm WARN oracletest No description npm WARN oracletest No repository field. npm WARN oracletest No README data npm WARN oracletest No license field. 稍等片刻，哗哗的安装全部结束了。 三 测试 在/opt/oracletest中新建一个测试的js文件，用于访问oracle数据库。 var oracledb = require('oracledb'); var config = { user:'mytest', password:'password', connectString : "192.168.0.107:1521/orcl" }; oracledb.getConnection( config, function(err, connection) { if (err) { console.error(err.message); return; } connection.execute("SELECT * from aa", function(err, result) { if (err) { console.error(err.message); doRelease(connection); return; } //打印返回的表结构 console.log(result.metaData); //打印返回的行数据 console.log(result.rows); }); }); function doRelease(connection) { connection.close( function(err) { if (err) { console.error(err.message); } }); } 然后执行 node xx.js查看结果 四 约束 node_oracledb支持以下各种格式： blob,buffer,clob,cursor,date,default,number,string。 明显，对于GIS中的oraclespatial数据类型不直接支持： NJS-010: unsupported data type in select list 可以考虑将geom转wkt,gml等输出查询。 五 可能遇到的报错 有时候报错：ORA-24454: client host name is not set，这属于非主流错误，原因是本机的hosts设置问题。 可能遇到的错误.png 编辑对应服务器的hosts文件： image.png Root@后面的是服务器的名称，所以如下配置: image.png 保存退出即可解决问题。
一 前言 PgRouting是基于开源空间数据库PostGIS用于网络分析的扩展模块，最初它被称作pgDijkstra，因为它只是利用Dijkstra算法实现最短路径搜索，之后慢慢添加了其他的路径分析算法，如A算法，双向A算法，Dijkstra算法，双向Dijkstra算法，tsp货郎担算法等，然后被更名为pgRouting[1]。该扩展库依托PostGIS自身的gist索引，丰富的坐标系与图形类型，强大的几何处理能力，如空间查询，空间处理，线性参考等优势，能保障在较大数据级别下的网络分析效果更快更好。 PostGIS早已奠定了最优秀的开源空间数据库地位，在新时代GIS中的应用将会越来越普遍。其次，网络分析算法很多服务端语言如java，C#等虽能实现，但基于真实城市道路数据量较大且查询分析操作步骤复杂与数据库交互频繁，以这类服务端频繁访问数据库导致数据库开销压力较大，分析较慢，故选择PgRouting在数据库内部实现算法，提升分析效率。最后，路径分析不仅仅是最短路径，在实际应用中还有最短耗时，最近距离，道路对车辆类型限制，道路对速度限制等因素，交通事故、市政事故导致的交通障碍点等问题，所有的问题本质其实是对路径分析权重（Weight）的设置问题。 二 PgRounting安装 windows安装过程比较简单，安装PostgreSQL，PostGIS一直Next即可，安装完自带PgRouting扩展，这里主要以Centos7介绍安装过程： 安装PostgreSQL9.6，参考 《Centos7安装PostgreSQL9.6》； 安装PostGIS，参考CentOS 7源码安装PostGIS； 安装PgRouting PgRouting依赖项如下： C and C++0x compilers * g++ version >= 4.8 Postgresql version >= 9.1 PostGIS version >= 2.0 The Boost Graph Library (BGL). Version >= 1.46 CMake >= 2.8.8 CGAL >= 4.2 PgRouting安装步骤如下： # 解压 [root@localhost opt]# tar -zxvf pgrouting-2.4.1 # 检查是否安装Cmake，未安装自行安装 [root@localhost pgrouting-2.4.1]# cmake --help # 引入Postgres账户的环境变量 [root@localhost pgrouting-2.4.1]# source /home/postgres/.bashrc # 安装CGAL #下载地址：https://github.com/CGAL/cgal#readme [root@localhost opt]# cd cgal-master [root@localhost cgal-master]# mkdir build [root@localhost cgal-master]# cd build [root@localhost build]# cmake .. [root@localhost build]# make & make install [root@localhost build]# cd /opt/pgrouting-2.4.1 [root@localhost pgrouting-2.4.1]# mkdir build [root@localhost pgrouting-2.4.1]# cd build [root@localhost build]# cmake .. [root@localhost build]# make & make install 三 搭建网络分析库 3.1 创建测试数据库 [root@localhost opt]# su - postgres [postgres@localhost ~]$ psql psql (9.6.1) Type "help" for help. postgres=# create database network; CREATE DATABASE postgres=# \c network You are now connected to database "network" as user "postgres". network=# create extension postgis; CREATE EXTENSION network=# create extension pgrouting; CREATE EXTENSION 3.2 导入测试路网数据 PgRouting需要使用道路线型数据，建立道路连通性topo关系。由于路网分析的特殊性，只支持LineString类型，不支持MultiLineString类型。测试数据从OSM下载得来。 数据下载.png 一般下载shp，使用PostGIS自带的shp2pgsql导入即可。其他格式可以使用osm2pgrouting工具，本文不做详述。笔者下载后，将路网数据使用ArcMap只截取了南京市范围内路网后，从epsg:4326转换成了epsg:3857坐标系，然后导入数据库，做测试数据。 [postgres@localhost ~]$ shp2pgsql -c -g geom -D -s 3857 -S -i -I /opt/roads.shp road | psql -d network Shapefile type: Arc Postgis type: LINESTRING[2] SET SET BEGIN CREATE TABLE ALTER TABLE addgeometrycolumn ---------------------------------------------------- public.road.geom SRID:3857 TYPE:LINESTRING DIMS:2 (1 row) COPY 9731 CREATE INDEX COMMIT ANALYZE 关于shp2pgsql参数问题，参考http://www.jianshu.com/p/1251fdc603ac。 注意：使用shp2pgsql工具，shp路径不要太深，不要有中文。对于含有中文乱码的，可使用-W gbk等转码。 3.3 创建网络分析topo PgRouting提供pgr_createTopology方法，对道路数据创建拓扑关系，比如单一线要素，建立与其连通的source,tartget连通点。详细步骤如下： #在network数据库中对导入的road表建立source,target字段 network=# alter table road add column source int; ALTER TABLE network=# alter table road add column target int; ALTER TABLE #创建连通性topo #road是表名称，geom是该表的图形字段名称，gid是改变的主键id。 #一般我们使用shp2pgsql工具会自动创建gid为主键，geom为图形。 #如果是自己其他形式建立的表，注意参数写自己对应的字段 network=# SELECT pgr_createTopology('road', 0.00001, 'geom', 'gid'); 正常情况下顺利完成以上步骤。 由于在网络分析中频繁读取source,target字段的topo关系值，为了提升查询效率，需要对这两个字段添加索引： network=# create index road_source_idx on road("source"); network=# create index road_target_idx on road("target"); 3.4 路网元数据说明 到此为止，全部数据准备工作已经完成了，查看本文用于测试路径分析的数据描述如下： network=# \d road Table "public.road" Column | Type | Modifiers -----------+---------------------------+---------------------------------------------------- gid | integer | not null default nextval('road_gid_seq'::regclass) direction | character varying(1) | roadname | character varying(40) | oneway | character varying(50) | geom | geometry(LineString,3857) | source | integer | target | integer | Indexes: "road_pkey" PRIMARY KEY, btree (gid) "road_geom_idx" gist (geom) "road_source_idx" btree (source) "road_target_idx" btree (target) 由此看出，road表拥有主键索引，geom的gist索引，source，target节点处索引，还有一般性的道路名称字段，比较重要的是direction和oneway字段（有一个即可），这两个字段都是说明道路真实方向的。 direction oneway 含义 0,1 空值 道路双向通行 2 FT 道路真实方向与数字化方向一致 3 TF 道路真实方向与数字化方向相反 4 N 道路禁止通行 3.5 通行成本权重设置 在算法中分为有向图，无向图，图的path长度一般设置为权重，网络分析中，具体到比如交通领域，也分为双向通行道路，单向通行道路，交通事故导致的临时交通阻塞无法通行（障碍点），不同等级道路对车辆类型限制，比如高架，高速只允许机动车，乡间道路允许非机动车（条件限制因素），本节只是举个例子说明下不同的条件下如何设置通行成本权重： 双向通行： update road set length=st_length(geom),rev_length=st_length(geom) where oneway is null; --采用真实地理距离是这样： update road set lenght=st_length(st_transform(geom),4326),true),rev_length=st_length((st_transform(geom),4326),true) where oneway is null; 单向通行 #FT是道路方向与数字化方向一致，那么正向通行成本为道路长度，反向成本为正无穷（以极大值代替） update road set length=st_length(geom),rev_length=99999999999 where oneway='FT'; update road set length=99999999999,rev_length=st_length(geom) where oneway='TF'; 障碍点 #假设gid=20的道路因事故，修路暂时不能通行 update road set lenght=99999999999,rev_length=99999999999 where gid=20; 限制通行 假设当前是一辆大货车，通过有限高限重的道路，在为他做规划时，先获取车辆类型，再查询road表中是否有对其限制的因素（以下纯逻辑描述sql） #假设道路表有字段restrict，该字段是array，记录了不可通行的车辆类型 update road set lenght=99999999999,rev_length=99999999999 where 'lorry'=any(restrict); 四 路径分析 言归正传，本文从入门角度只阐述最简单的单向，双向通行道路的例子，其他概不设置限制。 4.1 创建cost alter table road add column length numeric; alter table road add column rev_length numeric; update road set length=ST_Length(ST_TransForm(geom,4326),true),rev_length=ST_Length(ST_TransForm(geom,4326),true) where oneway is null; update road set length=st_length(ST_TransForm(geom,4326),true),rev_length=99999999999 where oneway='FT'; update road set length=99999999999,rev_length=st_length(ST_TransForm(geom,4326),true) where oneway='TF'; 4.2 创建路径分析方法 单点到单点 select
CSS Style是GeoServer的一个扩展插件，使用CSS写起来的地图渲染策略文件相比较SLD而言，非常的简洁，本文根据GeoServer用户手册，稍微改写，便于该知识点的推广。 一 CSS Style安装 1 从geoserver下载页面下载 对应版本的geoserver-A.B.C-css-plugin.zip。A.B.C对应的是GeoServer的版本号。 2 解压geoserver-A.B.C-css-plugin.zip，将解压后的jar文件，复制到对应geoserver版本的WEB-INF/lib目录中。 3 重启GeoServer即可。 在新建Style页面看到Format有CSS选项即代表可以正常使用了。 新建CSS Style.png 二 CSS应用基础 CSS Style和SLD一样是一个地图渲染策略文件，新建样式，绑定图层等操作和sld是一模一样的，只是写起来更加简洁。 SLD的策略文件举例如下： <?xml version="1.0" encoding="ISO-8859-1"?> <StyledLayerDescriptor version="1.0.0" xmlns="http://www.opengis.net/sld" xmlns:ogc="http://www.opengis.net/ogc" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:gml="http://www.opengis.net/gml" xsi:schemaLocation="http://www.opengis.net/sld http://schemas.opengis.net/sld/1.0.0/StyledLayerDescriptor.xsd "> <NamedLayer> <Name>USA states population</Name> <UserStyle> <Name>population</Name> <Title>Population in the United States</Title> <Abstract>A sample filter that filters the United States into three categories of population, drawn in different colors</Abstract> <FeatureTypeStyle> <Rule> <Title>< 2M</Title> <ogc:Filter> <ogc:PropertyIsLessThan> <ogc:PropertyName>PERSONS</ogc:PropertyName> <ogc:Literal>2000000</ogc:Literal> </ogc:PropertyIsLessThan> </ogc:Filter> <PolygonSymbolizer> <Fill> <!-- CssParameters allowed are fill (the color) and fill-opacity --> <CssParameter name="fill">#4DFF4D</CssParameter> <CssParameter name="fill-opacity">0.7</CssParameter> </Fill> </PolygonSymbolizer> </Rule> <Rule> <Title>2M - 4M</Title> <ogc:Filter> <ogc:PropertyIsBetween> <ogc:PropertyName>PERSONS</ogc:PropertyName> <ogc:LowerBoundary> <ogc:Literal>2000000</ogc:Literal> </ogc:LowerBoundary> <ogc:UpperBoundary> <ogc:Literal>4000000</ogc:Literal> </ogc:UpperBoundary> </ogc:PropertyIsBetween> </ogc:Filter> <PolygonSymbolizer> <Fill> <!-- CssParameters allowed are fill (the color) and fill-opacity --> <CssParameter name="fill">#FF4D4D</CssParameter> <CssParameter name="fill-opacity">0.7</CssParameter> </Fill> </PolygonSymbolizer> </Rule> <Rule> <Title>> 4M</Title> <!-- like a linesymbolizer but with a fill too --> <ogc:Filter> <ogc:PropertyIsGreaterThan> <ogc:PropertyName>PERSONS</ogc:PropertyName> <ogc:Literal>4000000</ogc:Literal> </ogc:PropertyIsGreaterThan> </ogc:Filter> <PolygonSymbolizer> <Fill> <!-- CssParameters allowed are fill (the color) and fill-opacity --> <CssParameter name="fill">#4D4DFF</CssParameter> <CssParameter name="fill-opacity">0.7</CssParameter> </Fill> </PolygonSymbolizer> </Rule> <Rule> <Title>Boundary</Title> <LineSymbolizer> <Stroke> <CssParameter name="stroke-width">0.2</CssParameter> </Stroke> </LineSymbolizer> <TextSymbolizer> <Label> <ogc:PropertyName>STATE_ABBR</ogc:PropertyName> </Label> <Font> <CssParameter name="font-family">Times New Roman</CssParameter> <CssParameter name="font-style">Normal</CssParameter> <CssParameter name="font-size">14</CssParameter> </Font> <LabelPlacement> <PointPlacement> <AnchorPoint> <AnchorPointX>0.5</AnchorPointX> <AnchorPointY>0.5</AnchorPointY> </AnchorPoint> </PointPlacement> </LabelPlacement> </TextSymbolizer> </Rule> </FeatureTypeStyle> </UserStyle> </NamedLayer> </StyledLayerDescriptor> 改写CSS样式如下： [PERSONS < 2000000] { fill: #4DFF4D; fill-opacity: 0.7; stroke-width: 0.2; label: [STATE_ABBR]; label-anchor: 0.5 0.5; font-family: "Times New Roman"; font-fill: black; font-style: normal; font-size: 14; } [PERSONS >= 2000000] [PERSONS < 4000000] { fill: #FF4D4D; fill-opacity: 0.7; stroke-width: 0.2; label: [STATE_ABBR]; label-anchor: 0.5 0.5; font-family: "Times New Roman"; font-fill: black; font-style: normal; font-size: 14; } [PERSONS >= 4000000] { fill: #4D4DFF; fill-opacity: 0.7; stroke-width: 0.2; label: [STATE_ABBR]; label-anchor: 0.5 0.5; font-family: "Times New Roman"; font-fill: black; font-style: normal; font-size: 14; } 一个rule对应css的一个{}，注意{}后面没有;,等符号。 注意观察可知，每个规则，仅仅fill是不同的，其他的参数都是一样的，可以考虑将公共的样式部分，放到通用规则里，通用规则是 *{}，改写如下： [PERSONS < 2000000] { fill: #4DFF4D; } [PERSONS > 2000000] [PERSONS < 4000000] { fill: #FF4D4D; } [PERSONS > 4000000] { fill: #4D4DFF; } * { fill-opacity: 0.7; stroke-width: 0.2; label: [STATE_ABBR]; label-anchor: 0.5 0.5; font-family: "Times New Roman"; font-fill: black; font-style: normal; font-size: 14; } 每一个完整的规则，都是规则+*（通用）规则组成完整的样式策略。 对比sld可知，文件写起来更简单，可读性更强。 三 CSS应用提高 3.1 使用Scale sld中，常常比如说某个样式，在scale大于某个比例尺下才显示，在小于某个比例尺下不显示。CSS Style中使用@scale来标志，例子如下： [@scale >= 20000000]{ label:''; } [@scale < 20000000] { label: [STATE_ABBR]; label-anchor: 0.5 0.5; font-family: "Times New Roman"; font-fill: black; font-style: normal; font-size: 14; } 该例子说明，在scale >= 20000000不显示地图标注，在scale < 20000000显示设置的标注。 3.2 使用图例 图例的描述性信息以/*@title */说明，如下： /* @title Population < 2M */ [PERSONS < 2000000] { fill: #4DFF4D; } Legend.png 描述的信息就会作用在Legend上。 3.3 规则嵌套 * { stroke: black; stroke-width: 0.2; fill-opacity: 0.7; /* @title Population < 2M */ [PERSONS < 2000000] { fill: #4DFF4D; }; /* @title 2M < Population < 4M */ [PERSONS >= 2000000] [PERSONS < 4000000] { fill: #FF4D4D; }; /* @title Population > 4M */ [PERSONS >= 4000000] { fill: #4D4DFF; }; /* Labelling */ [@scale < 20000000] { label: [STATE_ABBR]; label-anchor: 0.5 0.5; font-family: "Times New Roman"; font-fill: black; font-style: normal; font-size: 14; } } 长话短说，这里使用了 常规样式+条件过滤样式+scale比例尺 三个规则组合嵌套。 3.4 条件筛选 3.4.1 or与and 条件中常常使用多个条件组合应用。 and应用如下： [rainfall>12] [lakes>1] { fill: black; } and时，条件之间是空格，无符号。 or应用如下： [rainfall>12], [lakes>1] { fill: blue; } or时，条件之间是逗号，也可以写成如下： [rainfall>12 or lakes>1] { fill: blue; } 3.4.2 运算符号 =,<>,>,<,>=,<=,LIKE等操作。 3.4.3 根据图层名称Filter 这个用的不多，除非把若干个图层的渲染策略写到了一个文件。举例说明如下： line1 { stroke: black; } line2 { stroke: red; } line3 { stroke: blue; } line1，line2，line3是三个图层的名字，这三个图层都绑定了这个样式文件。那么使用Filter图层定义不同的图层分别的渲染策略。 3.4.4 根据Feature的ID Filtering #states.2 { stroke: black; } 选择states图层中，id为2的要素。 3.4.5 根据symbols Filtering 当图形组合内联时，有时需要对一些符号做些可选设置。 官网说明.png 举例如下： * { stroke: #333333, symbol("shape://vertline"); stroke-width: 3px; :nth-stroke(2) { size: 12; stroke: #333333; stroke-width: 1px; } } 铁路.png 该线是由 #333333, symbol("shape://vertline")两个线样式组合而来，其中，选择symbol("shape://vertline")并进行设置，选择symbol就是使用:nth-stroke这种格式来选择symbol。 更多更丰富的使用详细见官网，后续会有具体使用说明。
Oracle_fdw是PG的一个外部数据接口，可以使PostgreSQL轻松跨库操作Oracle。Oracle_fdw的作用有以下两点： PG可以跨库增删改查Oracle中的表，可以查询Oracle的视图，可以使PG中的表和Oracle中表/视图作Join查询，类似dblink的功能。 快速将Oralce表迁移进入PostgreSQL。 本文简单介绍下Oracle_fdw的安装和使用。 一 Oracle_fdw安装 官方地址：http://pgxn.org/dist/oracle_fdw/ ，选择一个版本下载。 1.1 安装Oracle Instant Client 从oralce官网下载 'Basic' and 'SDK'，假如下载后文件所在位置在/opt/oracle中。 cd /opt/oracle unzip instantclient-basic-linux.x64-12.2.0.1.0.zip unzip instantclient-sdk-linux.x64-12.2.0.1.0.zip mv instantclient_12_2 instantclient cd instantclient #建立一下软连接 ln -s libclntsh.so.12.1 libclntsh.so #设置环境变量 vi /etc/profile #边界内容如下： #oracle_home一定要写，否则编译会报错 export ORACLE_HOME=/opt/oracle/instantclient export OCI_LIB_DIR=$ORACLE_HOME export OCI_INC_DIR=$ORACLE_HOME/sdk/include #保存退出 #重启用profile文件 source /etc/profile 1.2 编译oracle_fdw 启用postgres用户环境变量 [root@bogon opt]# source /home/postgres/.bashrc 解压oracle_fdw [root@bogon opt]# unzip oracle_fdw-1.5.0.zip 编译安装oracle_fdw [root@bogon opt]# cd oracle_fdw-1.5.0 #编译 [root@bogon oracle_fdw-1.5.0]# make #安装 [root@bogon oracle_fdw-1.5.0]# make install 没报错的话，代表安装成功了，有时候会报一找不到.h头文件的错误，比如： fatal err:oci.h:No such file or directory #或者 fatal err:stdio.h:No such file or directory 错误截图1.png 都证明ORACLE_HOME没指定或没有正确配置，需检查环境变量及其文件对应是否正确。 二 创建oracle_fdw扩展 postgres=# create extension oracle_fdw; CREATE EXTENSION 代表创建成功，如果遇到下面这个问题： postgres=# create extension oracle_fdw; ERROR: could not load library "/home/postgres/lib/oracle_fdw.so": libclntsh.so: cannot open shared object file: No such file or directory 是缺少so文件了，有时候编译成功了，还是会缺不少文件，用ldd查看下oracle_fdw.so的依赖： [postgres@localhost lib]$ ldd oracle_fdw.so linux-vdso.so.1 => (0x00007fff5973b000) libclntsh.so.12.1 => not found libc.so.6 => /lib64/libc.so.6 (0x00007fa9c8185000) libmql1.so => not found libipc1.so => not found libnnz12.so => not found libons.so => not found libdl.so.2 => /lib64/libdl.so.2 (0x00007fa9c7f6d000) libm.so.6 => /lib64/libm.so.6 (0x00007fa9c7c6b000) libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fa9c7a4f000) libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fa9c7835000) librt.so.1 => /lib64/librt.so.1 (0x00007fa9c762d000) libaio.so.1 => /lib64/libaio.so.1 (0x00007fa9c742b000) libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fa9c7210000) /lib64/ld-linux-x86-64.so.2 (0x00007fa9cc20e000) libclntshcore.so.12.1 => not found libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fa9c6ffa000) 对于这些not found的so文件，我们在ORACLE_HOME目录中发现是存在的，如下图： ORACLE_HOME.png 因此需要手动建立一下软连接： ln -s /opt/oracle/instantclient/libclntsh.so.12.1 /home/postgres/lib/libclntsh.so.12.1 ln -s /opt/oracle/instantclient/libmql1.so /home/postgres/lib/libmql1.so ln -s /opt/oracle/instantclient/libipc1.so /home/postgres/lib/libipc1.so ln -s /opt/oracle/instantclient/libnnz12.so /home/postgres/lib/libnnz12.so ln -s /opt/oracle/instantclient/libons.so /home/postgres/lib/libons.so ln -s /opt/oracle/instantclient/libclntshcore.so.12.1 /home/postgres/lib/libclntshcore.so.12.1 再次创建oracle_fdw： postgres=# create extension oracle_fdw; CREATE EXTENSION 应该就能创建成功了。 三 使用oracle_fdw postgres=# create server oradb_215 foreign data wrapper oracle_fdw options(dbserver '10.144.15.215:1521/mcsas'); postgres=# grant usage on foreign server oradb_215 to postgres; postgres=# create user mapping for postgres server oradb_215 options(user 'MG_APP',password 'QWERasdf'); postgres=# create foreign table ZWGK_SJJC_FBYJ_GTSJHD123 ( OBJ_ID VARCHAR(42) not null, XLMC VARCHAR(50), DYDJ VARCHAR(50), GTXH VARCHAR(50), SJFBHD VARCHAR(50), SSBQ VARCHAR(50), BNHD VARCHAR(50), SSWS VARCHAR(50), PMSGTID VARCHAR(150), PMSGTBH VARCHAR(150), SFCL VARCHAR(150) ) server oradb_215 options(schema 'MG_APP',table 'ZWGK_SJJC_FBYJ_GTSJHD'); postgres=# select * from ZWGK_SJJC_FBYJ_GTSJHD123 limit 10; 这样，将oracle中MG_APP.ZWGK_SJJC_FBYJ_GTSJHD表“映射”到pg了，可以查询了。 四 可能遇到的错误 4.1 OCIEnvCreate错误 OCIEnvCreate错误.png 解决方法： 1 检查 /etc/profile中ORACLE_HOME配置及其 export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH 2 检查home/postgres/.bashrc也有: export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH 环境变量.png 3 postgres用户下检查oracle_fdw.so的执行权限： image.png Xshell下是绿色的，要是灰色，就 chmod 777 $PGHOME/lib/oracle_fdw.so 4 全部检查完毕后一定要重启pg服务。 4.2 client host name is not set 有时候报错：ORA-24454: client host name is not set，这属于非主流错误，原因是本机的hosts设置问题。 可能遇到的错误.png 编辑对应服务器的hosts文件： image.png Root@后面的是服务器的名称，所以如下配置: image.png 保存退出即可解决问题。
Openlayers3中有个ol.source.XYZ类，可以加载Tile瓦片图层，本文只是做个说明，介绍如何识别以及如何正确加载这些图层。 一 基础XYZ-谷歌离线切片 明显的名称.png 观察这些Tile命名，xyz是很明显的，这是下载的谷歌切片，直接加载如下： var layer= new ol.layer.Tile({ source : new ol.source.XYZ({ url : 'http://localhost:8080/baseMap/{z}/{x}/{y}.png' }) }); 二 变体XZY-谷歌在线切片 谷歌在线图的url.png 打开谷歌在线图，观察请求的url时，虽然看上去一大串一大串很长，似乎什么也看不懂，但是注意看红框出，在放大缩小时，1i是会变的，而且变化的值和地图的等级很相似，那么，我们很容易猜想，1i是z（level），2i，3i就是x,y（或者y,x）自己调调就可以了。 很明显，这些url也是基本的xyz，只是不是十分明显，需要心细的你仔细观察剥离出来参数即可。 代码如下： var layer= new ol.layer.Tile({ source : new ol.source.XYZ({ url : 'http://www.google.cn/maps/vt?pb=!1m5!1m4!1i{z}!2i{x}!3i{y}!4i256!2m3!1e0!2sm!3i342009817!3m9!2szh-CN!3sCN!5e18!12m1!1e47!12m3!1e37!2m1!1ssmartmaps!4e0&token=32965' }) }); 三 变形的XYZ-ArcServer切片 ArcServer切片.png 这是ArcServer的切片，感觉xyz不明显，但是组织形式也和xyz是一样的，其实，稍微想想，除了名字不一样，岂不都是一样，那么我们认为本质就是一样的，只是有个障眼法，问题就出在，ArcServer的图片是16进制的，位数是行R8位数，列C8位数，余位补0。 这里需要对图片名称转下，通过正常的url“翻译”到ArcServer的16进制。 var road= new ol.layer.Tile({ source : new ol.source.XYZ({ tileUrlFunction : function (xyz, obj1, obj2) { var z = xyz[0]; var x = Math.abs(xyz[1]); var y = Math.abs(xyz[2]) - 1; var x = 'C' + padLeft(8, x.toString(16)); var y = 'R' + padLeft(8, y.toString(16)); var z = 'L' + padLeft(2, z); var url = 'ArcServerTile/' + z + '/' + y + '/' + x + '.png'; return url; } }) }) //将10进制转16进制，余位补0，凑成ArcServer的切片格式 function padLeft(num, val) { return (new Array(num).join('0') + val).slice(-num); } 通过tileUrlFunction重写url获取函数，构造成正确的，这里y-1不一定的，有的需要加1，有的需要z+-1，如果图片组合混乱的话，对z，y微微加减一调整下，应该很快就能凑齐地图。 四 变幻的XYZ-GeoWebCache切片 GeoWebCache切片.png 变幻而不是变形，说明万变不离其宗啊，GeoWebCache切片看着虽然很复杂，但是也是xyz吗，只不过变幻了，难道就认不出了？明显epsg_900913是坐标系，后面的_10是z。其他的虽然也是摸不着头脑，可以了解下GeoWebCache的切片组织规范，比如看下源码，然后理解了，稍微调整即可。 var road= new ol.layer.Tile({ source : new ol.source.XYZ({ tileUrlFunction :function (xyz, obj1, obj2) { if (!xyz) return ""; var z=xyz[0]; var x=Math.abs(xyz[1]); var y=Math.abs(xyz[2]); var xyz_convert= convert_(z,x,y); x=xyz_convert[0]; y=xyz_convert[1]; z=xyz_convert[2]; var shift = z / 2; var half = 2 << shift; var digits = 1; if (half > 10) digits = parseInt(Math.log(half)/Math.log(10)) + 1; var halfx = parseInt(x / half); var halfy = parseInt(y / half); x=parseInt(x); y=parseInt(y)+1; var url=tileRoot+"/EPSG_900913"+"_"+padLeft_(2,z)+"/"+padLeft_(digits,halfx)+"_"+padLeft_(digits,halfy)+"/"+padLeft_(2*digits,x)+"_"+padLeft_(2*digits,y)+"."+format; return url; } }) }); //字符截取 var padLeft_ = function(num, val) { return (new Array(num).join('0') + val).slice(-num); }; //xy行列转换 var convert_=function(zoomLevel, x, y) { var extent = Math.pow(2, zoomLevel); if (x < 0 || x > extent - 1) { console.log("The X coordinate is not sane: " + x); return; } if (y < 0 || y > extent - 1) { console.log("The Y coordinate is not sane: " + y); return; } var gridLoc = [x, extent - y - 1, zoomLevel]; return gridLoc; } 这个的确有点复杂了。。。 综述：本文只是大概介绍下常见的XYZ格式和变体，对于简单的XYZ可以直接format加载，对于复杂的需要使用tileUrlFunction 甚至tilegrid组合，重写url，将标准的xyz，“翻译”成脾气不同的切片们，让他们乖乖听话，组成我们需要的切片。
pgAgent是PostgreSQL的一个job引擎，可以执行数据库job任务。本文简述其安装步骤 一 安装cmake #检查是否安装 [root@bogon ~]# cmake --version cmake version 2.8.11 #如果未安装执行下面的语句 wget http://www.cmake.org/files/v2.8/cmake-2.8.5.tar.gz tar -zxvf cmake-2.8.5.tar.gz cd /root/src/cmake-2.8.5 ./bootstrap make make install 二 安装wxGTK-2.8.12 wget https://github.com/wxWidgets/wxWidgets/releases/download/v2.8.12/wxGTK-2.8.12.tar.gz #注意编译的时候一定要支持unicode 与 静态编译 ./configure --enable-shared=no --enable-unicode=yes make make install ./configure可能报错如下： *** Could not run GTK+ test program, checking why... *** The test program failed to compile or link. See the file config.log for the *** exact error that occured. This usually means GTK+ is incorrectly installed. configure: error:The development files for GTK+ were not found. For GTK+ 2, please 需要安装gkt+，笔者源码安装又发现一堆依赖，这里先不管了，直接yum安装 yum install gtk2-devel 然后编译就通过了。 三 安装pgAgent #在root账户下执行 # tar -zxvf pgAgent-3.0.1-Source.tar.gz # source /home/postgres/.bashrc #引入postgres的环境变量 # cmake ./ # make # make install cmake可能报错如下： /bin/ld:cannot find -lcrypto 简单的执行以下语句： yum install openssl-devel 成功后用pgsql 用户登录数据库执行 [postgres@bogon ~]$ psql psql (9.6.0) Type "help" for help. postgres=# \i /usr/local/share/pgagent.sql postgres=# \i /usr/local/share/pgagent_upgrade.sql
地图中坐标系是非常多的，最常用的是EPSG:3857（等于谷歌的900913，等于esri的102100）的web墨卡托投影和GPS的EPSG:4326的WGS84坐标系。业务场景中，很多时候并不是这些常用坐标系，比如要使用北京54，西安80等坐标系怎么办咧？本文仅仅抛砖引玉，介绍方法，阐述如何应用自定义坐标系。 一 定义坐标系 每个坐标系都是有一个EPSG定义，本文准备以EPSG:3395举例。 首先在http://epsg.io/网站，查找坐标系定义。 自定义坐标系边界.png 坐标系定义.png 这里我们是定义ol3的，于是选择proj4js的，复制粘贴下来。 var projection_3395 = new ol.proj.Projection({ code: 'EPSG:3395', extent:[-20026376.39,-15496570.74,20026376.39,18764656.23], units: 'm', axisOrientation: 'neu' }); //定义3395坐标系，且与其他4326,3857的互相转换 proj4.defs("EPSG:3395","+proj=merc +lon_0=0 +k=1 +x_0=0 +y_0=0 +datum=WGS84 +units=m +no_defs"); 二 坐标系转换 说白了，其实是通过proj4对坐标系转换，然后作为匿名回调重写ol.proj的坐标转换功能，以下代码定义了3395转4326,3857方法。 //结合proj4在ol3中自定义坐标系 ol.proj.addProjection(projection_3395); ol.proj.addCoordinateTransforms("EPSG:4326", "EPSG:3395", function(coordinate) { return proj4("EPSG:4326","EPSG:3395",coordinate); }, function(coordinate) { return proj4("EPSG:3395","EPSG:4326",coordinate);; } ); ol.proj.addCoordinateTransforms("EPSG:3857", "EPSG:3395", function(coordinate) { return proj4("EPSG:3857","EPSG:3395",coordinate); }, function(coordinate) { return proj4("EPSG:3395","EPSG:3857",coordinate);; } ); 三 测试坐标系转换 ol.proj.transform([118,32],'EPSG:4326','EPSG:3395'); //这种如果测试成功，代表定义成功了。
工作中会用到很多小工具，特此记录。 批量导出表 只导出insert语句 pg_dump -h host -p 5432 -U postgres -a -t t1 -t t2 --inserts -f /opt/temp.sql -d mcsas 导出全部表结构和模式 pg_dump -h host -p 5432 -U postgres -t t1 -t t2 -f /opt/temp.sql -d mcsas 数据库备份 pg_dump -h master -p 5432-U postgres -w -f /home/postgres/test.backup test 数据库还原 pg_restore -h master -p 5432-U postgres -w -d test /home/postgres/test.backup 如果是文本格式的dump，直接使用 psql的 \i xxx.backup shp导入pg 使用pgadmin3的可视化工具，也可以通过命令行 用法：shp2pgsql [<options>] <shapefile> [[<schema>.]<table>] OPTIONS: -s ： [<from>:]<srid> 设置 SRID字段，默认0，不能和 -D一起使用。 (-d|a|c|p) 常用的互斥操作选项 -d 删除之前的表，重建一个表导入shp数据。 -a 将shp数据追加到已有的表，在同一个schema下。 -c 创建一个新表，然后导入shp数据，不指定操作选项会默认这个。 -p 预备模式，只创建表，不导入数据。 -g <geocolumn> 指定表的图形列，（更多用在append模式下） -D Use postgresql dump format (defaults to SQL insert statements). -e 独立执行，不使用事务。.和-D不相容。 -G Use geography type (requires lon/lat data or -s to reproject). -k Keep postgresql identifiers case. -i 对dbf中所有的integer子弹使用int4类型 -I 对geocolumn创建空间索引。 -m <filename> Specify a file containing a set of mappings of (long) column names to 10 character DBF column names. The content of the file is one or more lines of two names separated by white space and no trailing or leading space. For example: COLUMNNAME DBFFIELD1 AVERYLONGCOLUMNNAME DBFFIELD2 -S 创建单图形而不是MULTI 图形。 -t <dimensionality> 强制指定图形为 '2D', '3DZ', '3DM', or '4D' -w Output WKT instead of WKB. Note that this can result in coordinate drift. -W <encoding> Specify the character encoding of Shape's attribute column. (default: "UTF-8") -N <policy> 空图形策略 (insert*,skip,abort). -n 只导入DBF文件 -T <tablespace> Specify the tablespace for the new table. Note that indexes will still use the default tablespace unless the -X flag is also used. -X <tablespace> Specify the tablespace for the table's indexes. This applies to the primary key, and the spatial index if the -I flag is used. -? Display this help screen. 举例如下，导入一个shp，指定geomcolumn名称为geom，建立空间字段，图形类型是单义图形。 shp2pgsql -c -g geom -D -s 4326 -S -i -I shaperoads.shp myschema.roadstable | psql -d roadsdb pg导出shp 用法: pgsql2shp [<options>] <database> [<schema>.]<table> pgsql2shp [<options>] <database> <query> OPTIONS: -f <filename> 导出文件名称 -h <host> 数据库host -p <port> 数据库port -P <password> 指定密码 -u <user> 指定用户 -g <geometry_column> 指定输出geom列名称 -b Use a binary cursor. -r Raw mode. Do not assume table has been created by the loader. This would not unescape attribute names and will not skip the 'gid' attribute. -k Keep PostgreSQL identifiers case. -m <filename> Specify a file containing a set of mappings of (long) column names to 10 character DBF column names. The content of the file is one or more lines of two names separated by white space and no trailing or leading space. For example: COLUMNNAME DBFFIELD1 AVERYLONGCOLUMNNAME DBFFIELD2 -? Display this help screen. 举例如下，将testdb数据中public的schema中test表导出为shp。 pgsql2shp -h host -p 5432 -u postgres -f /opt/test.shp testdb public.test 数据库备库 pg_basebackup -D $PGDATA -Fp -Xs -v -P -h master -p 5432 -U repuser 数据库同步时间线 pg_rewind --target-pgdata=/home/postgres/data --source-server='host=slave port=5432 user=postgres dbname=postgres' 删除表重复数据 DELETE FROM weather WHERE ctid NOT IN ( SELECT max(ctid) FROM weather GROUP BY city, temp_lo, temp_hi, prcp, date ); 跨表更新 update test t1 set field1=t2.field1 from test2 t2 where t1.id=t2.id 新建事务临时表 create temp table tt(id int,name text) on commit drop; --事务结束就消失 create temp table tt(id int,name text) on commit delete rows; --事务结束数据消失 create temp table tt(id int,name text) on commit preserver rows; --数据存在整个会话周期中 赋予用户读取schema权限 alter default privileges in schema public grant all on tables to freerep; alter default privileges in schema public revoke all on tables to freerep; pgpool强制由master执行sql /*NO LOAD BALANCE*/ select * from abc; 强制断开所有连接 select pg_terminate_backend(pg_stat_activity.pid) from pg_stat_activity where datname='tt'; copy导入csv copy sexit from 'e:/sexit.csv' delimiter as '|' csv quote as '''';
基于PG的流复制能实现热备切换，但是是要手动建立触发文件实现，对于一些HA场景来说，需要当主机down了后，备机自动切换，经查询资料知道pgpool-II可以实现这种功能。本文基于PG流复制基础上 ，以pgpool-II实现主备切换。在配置pgpool之前需分别在两台规划机上安装好pg数据库，且配置好了流复制环境，关于流复制配置参考前文：http://www.jianshu.com/p/12bc931ebba3。 pgpool双机集群架构图.png 基于PGPool的双机集群如上图所示：pg主节点和备节点实现流复制热备，pgpool1，pgpool2作为中间件，将主备pg节点加入集群，实现读写分离，负载均衡和HA故障自动切换。两pgpool节点可以委托一个虚拟ip节点作为应用程序访问的地址，两节点之间通过watchdog进行监控，当pgpool1宕机时，pgpool2会自动接管虚拟ip继续对外提供不间断服务。 一 主机规划 主机名 | IP | 角色 | 端口 :----:|:----:|:----:|:----:|:----:|:----: master| 192.168.0.108 |PGMaster|5432 | 192.168.0.108|pgpool1|9999 slave|192.168.0.109|PGSlave|5432 | 192.168.0.109|pgpool2|9999 vip|192.168.0.150|虚拟ip|9999 建立好主机规划之后，在master，slave上两台机器设置下host [root@localhost ~]# vi .bashrc #编辑内容如下： 192.168.0.108 master 192.168.0.109 slave 192.168.0.150 vip 二 配置ssh秘钥 在master,slave机器上都生成ssh如下： [root@localhost ~]# su - postgres [postgres@localhost ~]$ ssh-keygen -t rsa [postgres@localhost ~]$ cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys [postgres@localhost ~]$ chmod 600 ~/.ssh/authorized_keys 分别将master的公钥复制到slave，slave的公钥复制到master。 #master端 [postgres@localhost ~]$ scp ~/.ssh/authorized_keys postgres@slave:~/.ssh/ #slave端 [postgres@localhost ~]$ scp ~/.ssh/authorized_keys postgres@master:~/.ssh/ 验证下ssh配置是否成功 #master端 [postgres@slave ~]$ ssh postgres@slave Last login: Tue Dec 20 21:22:50 2016 from master #slave端 [postgres@slave ~]$ ssh postgres@master Last login: Tue Dec 20 21:22:50 2016 from slave 证明ssh信任关系配置成功。 三 安装pgpool 中文配置地址可参考http://pgpool.projects.pgfoundry.org/pgpool-II/doc/pgpool-zh_cn.html # 下载pgpool [root@master opt]# wget http://www.pgpool.net/mediawiki/images/pgpool-II-3.6.0.tar.gz # 解压 [root@master opt]# tar -zxvf pgpool-II-3.6.0.tar.gz # 文件权限设置为postgres（其实并非一定装在postgres账户，只不过之前ssh设置都在postgres下，为了方便） [root@master opt]# chown -R postgres.postgres /opt/pgpool-II-3.6.0 [root@master ~]# su - postgres [postgres@master opt]$ cd pgpool-II-3.6.0 [postgres@master pgpool-II-3.6.0]$ ./configure –prefix=/opt/pgpool -with-pgsql=path -with-pgsql=/home/postgres [postgres@master pgpool-II-3.6.0]$ make [postgres@master pgpool-II-3.6.0]$ make install 安装pgpool相关函数，并非强制，可选安装，为了系统稳定，建议安装 安装pg_reclass,pg_recovery [postgres@master pgpool-II-3.6.0]$ cd src/sql [postgres@master sql]$ make [postgres@master sql]$ make install [postgres@master sql]$ psql -f insert_lock.sql 安装全部结束。 四 配置pgpool 4.1 配置pgpool环境变量 pgpool装在了postgres账户下，在该账户中添加环境变量，master,slave节点都执行。 [postgres@master ~]$ cd /home/postgres [postgres@master ~]$ vim .bashrc #编辑内容如下 PGPOOLHOME=/opt/pgpool export PGPOOLHOME PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/bin:$PGPOOLHOME/bin export PATH 4.2 配置pool_hba.conf pool_hba.conf是对登录用户进行验证的，要和pg的pg_hba.conf保持一致，要么都是trust，要么都是md5验证方式，这里采用了md5验证方式如下设置： [postgres@master ~]$ cd /opt/pgpool/etc [postgres@etc~]$ cp pool_hba.conf.sample pool_hba.conf [postgres@etc~]$ vim pool_hba.conf #编辑内容如下 # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 0.0.0.0/0 md5 host all all 0/0 md5 4.3 配置pcp.conf pcp.conf配置用于pgpool自己登陆管理使用的，一些操作pgpool的工具会要求提供密码等，配置如下： [postgres@master ~]$ cd /opt/pgpool/etc [postgres@etc~]$ cp pcp.conf.sample pcp.conf # 使用pg_md5生成配置的用户名密码 [postgres@etc~]$ pg_md5 nariadmin 6b07583ba8af8e03043a1163147faf6a #pcp.conf是pgpool管理器自己的用户名和密码，用于管理集群。 [postgres@etc~]$ vim pcp.conf #编辑内容如下 postgres:6b07583ba8af8e03043a1163147faf6a #保存退出！ #在pgpool中添加pg数据库的用户名和密码 [postgres@etc~]$ pg_md5 -p -m -u postgres pool_passwd #数据库登录用户是postgres,这里输入登录密码，不能出错 #输入密码后，在pgpool/etc目录下会生成一个pool_passwd文件 4.4 配置系统命令权限 配置 ifconfig, arping 执行权限 ，执行failover_stream.sh需要用到，可以让其他普通用户执行。 [root@master ~]# chmod u+s /sbin/ifconfig [root@master ~]# chmod u+s /usr/sbin 4.5 配置pgpool.conf 查看本机网卡，配置后面的delegate_IP需要 [postgres@etc~]$ ifconfig 网卡名称.png 配置master上的pgpool.conf: [postgres@master ~]$ cd /opt/pgpool/etc [postgres@etc~]$ cp pgpool.conf.sample pgpool.conf [postgres@etc~]$ vim pgpool.conf 编辑内容如下： # CONNECTIONS listen_addresses = '*' port = 9999 pcp_listen_addresses = '*' pcp_port = 9898 # - Backend Connection Settings - backend_hostname0 = 'master' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/home/postgres/data' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = 'slave' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/home/postgres/data' backend_flag1 = 'ALLOW_TO_FAILOVER' # - Authentication - enable_pool_hba = on pool_passwd = 'pool_passwd' # FILE LOCATIONS pid_file_name = '/opt/pgpool/pgpool.pid' replication_mode = off load_balance_mode = on master_slave_mode = on master_slave_sub_mode = 'stream' sr_check_period = 5 sr_check_user = 'repuser' sr_check_password = 'repuser' sr_check_database = 'postgres' #------------------------------------------------------------------------------ # HEALTH CHECK 健康检查 #------------------------------------------------------------------------------ health_check_period = 10 # Health check period # Disabled (0) by default health_check_timeout = 20 # Health check timeout # 0 means no timeout health_check_user = 'postgres' # Health check user health_check_password = 'nariadmin' #数据库密码 # Password for health check user health_check_database = 'postgres' #必须设置，否则primary数据库down了，pgpool不知道，不能及时切换。从库流复制还在连接数据，报连接失败。 #只有下次使用pgpool登录时，发现连接不上，然后报错，这时候，才知道挂了，pgpool进行切换。 #主备切换的命令行配置 #------------------------------------------------------------------------------ # FAILOVER AND FAILBACK #------------------------------------------------------------------------------ failover_command = '/opt/pgpool/failover_stream.sh %H ' #------------------------------------------------------------------------------ # WATCHDOG #------------------------------------------------------------------------------ # - Enabling - use_watchdog = on # - Watchdog communication Settings - wd_hostname = 'master' # Host name or IP address of this watchdog # (change requires restart) wd_port = 9000 # port number for watchdog service # (change requires restart) # - Virtual IP control Setting - delegate_IP = 'vip' # delegate IP address # If this is empty, virtual IP never bring up. # (change requires restart) if_cmd_path = '/sbin' # path to the directory where if_up/down_cmd exists # (change requires restart) if_up_cmd = 'ifconfig eth1:0 inet $_IP_$ netmask 255.255.255.0' # startup delegate IP command # (change requires restart) # eth1根据现场机器改掉 if_down_cmd = 'ifconfig eth1:0 down' # shutdown delegate IP command # (change requires restart) # eth1根据现场机器改掉 # -- heartbeat mode -- wd_heartbeat_port = 9694 # Port number for receiving heartbeat signal # (change requires restart) wd_heartbeat_keepalive = 2 # Interval time of sending heartbeat signal (sec) # (change requires restart) wd_heartbeat_deadtime = 30 # Deadtime interval for heartbeat signal (sec) # (change requires restart) heartbeat_destination0 = 'slave' # Host name or IP address of destination 0 # for sending heartbeat signal. # (change requires restart) heartbeat_destination_port0 = 9694 # Port number of destination 0 for sending # heartbeat signal. Usually this is the # same as wd_heartbeat_port. # (change requires restart) heartbeat_device0 = 'eth1' # Name of NIC device (such like 'eth0') # used for sending/receiving heartbeat # signal to/from destination 0. # This works only when this is not empty # and pgpool has root privilege. # (change requires restart) # eth1根据现场机器改掉 # - Other pgpool Connection Settings - other_pgpool_hostname0 = 'slave' #对端 # Host name or IP address to connect to for other pgpool 0 # (change requires restart) other_pgpool_port0 = 9999 # Port number for othet pgpool 0 # (change requires restart) other_wd_port0 = 9000 # Port number for othet watchdog 0 # (change requires restart) 配置slave上的pgpool.conf: # CONNECTIONS listen_addresses = '*' port = 9999 pcp_listen_addresses = '*' pcp_port = 9898 # - Backend Connection Settings - backend_hostname0 = 'master' backend_port0 = 5432 backend_weight0 = 1 backend_data_directory0 = '/home/postgres/data' backend_flag0 = 'ALLOW_TO_FAILOVER' backend_hostname1 = 'slave' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/home/postgres/data' backend_flag1 = 'ALLOW_TO_FAILOVER' # - Authentication - enable_pool_hba = on pool_passwd = 'pool_passwd' # FILE LOCATIONS pid_file_name = '/opt/pgpool/pgpool.pid' replication_mode = off load_balance_mode = on master_slave_mode = on master_slave_sub_mode = 'stream' sr_check_period = 5 sr_check_user = 'repuser' sr_check_password = 'repuser' sr_check_database = 'postgres' #------------------------------------------------------------------------------ # HEALTH CHECK 健康检查 #------------------------------------------------------------------------------ health_check_period = 10 # Health check period # Disabled (0) by default health_check_timeout = 20 # Health check timeout # 0 means no timeout health_check_user = 'postgres' # Health check user health_check_password = 'nariadmin' #数据库密码 # Password for health check user health_check_database = 'postgres' #必须设置，否则primary数据库down了，pgpool不知道，不能及时切换。从库流复制还在连接数据，报连接失败。 #只有下次使用pgpool登录时，发现连接不上，然后报错，这时候，才知道挂了，pgpool进行切换。 #主备切换的命令行配置 #------------------------------------------------------------------------------ # FAILOVER AND FAILBACK #------------------------------------------------------------------------------ failover_command = '/opt/pgpool/failover_stream.sh %H ' #------------------------------------------------------------------------------ # WATCHDOG #------------------------------------------------------------------------------ # - Enabling - use_watchdog = on # - Watchdog communication Settings - wd_hostname = 'slave' #本端 # Host name or IP address of this watchdog # (change requires restart) wd_port = 9000 # port number for watchdog service # (change requires restart) # - Virtual IP control Setting - delegate_IP = 'vip' # delegate IP address # If this is empty, virtual IP never bring up. # (change requires restart) if_cmd_path = '/sbin' # path to the directory where if_up/down_cmd exists # (change requires restart) if_up_cmd = 'ifconfig eth1:0 inet $_IP_$ netmask 255.255.255.0' # startup delegate IP command # (change requires restart) # eth1根据现场机器改掉 if_down_cmd = 'ifconfig eth1:0 down' # shutdown delegate IP command # (change requires restart) # eth1根据现场机器改掉 # -- heartbeat mode -- wd_heartbeat_port = 9694 # Port number for receiving heartbeat signal # (change requires restart) wd_heartbeat_keepalive = 2 # Interval time of sending heartbeat signal (sec) # (change requires restart) wd_heartbeat_deadtime = 30 # Deadtime interval for heartbeat signal (sec) # (change requires restart) heartbeat_destination0 = 'master' #对端 # Host name or IP address of destination 0 # for sending heartbeat signal. # (change requires restart) heartbeat_destination_port0 = 9694 # Port number of destination 0 for sending # heartbeat signal. Usually this is the # same as wd_heartbeat_port. # (change requires restart) heartbeat_device0 = 'eth1' # Name of NIC device (such like 'eth0') # used for sending/receiving heartbeat # signal to/from destination 0. # This works only when this is not empty # and pgpool has root privilege. # (change requires restart) # eth1根据现场机器改掉 # - Other pgpool Connection Settings - other_pgpool_hostname0 = 'master' #对端 # Host name or IP address to connect to for other pgpool 0 # (change requires restart) other_pgpool_port0 = 9999 # Port number for othet pgpool 0 # (change requires restart) other_wd_port0 = 9000 # Port number for othet watchdog 0 # (change requires restart) 配置文件里，故障处理配置的是failover_command = '/opt/pgpool/failover_stream.sh %H '，因此，需要在/opt/pgpool目录中写个failover_stream.sh脚本： [postgres@master ~]$ cd /opt/pgpool [postgres@pgpool~]$ touch failover_stream.sh [postgres@pgpool~]$ vim failover_stream.sh 注意这里使用了promote 而不是触发文件，触发文件来回切换有问题，编辑内容如下： #! /bin/sh # Failover command for streaming replication. # Arguments: $1: new master hostname. new_master=$1 trigger_command="$PGHOME/bin/pg_ctl promote -D $PGDATA" # Prompte standby database. /usr/bin/ssh -T $new_master $trigger_command exit 0; 如果是其他用户创建的，需要赋予postgres可执行权限，例如 [root@opt ~]$ chown -R postgres.postgres /opt/pgpool [root@opt ~]]$ chmod 777 /opt/pgpool/failover_stream.sh 五 PGPool集群管理 启动之前在master,slave节点创建两个日志文件： [root@master ~]# mkdir /var/log/pgpool [root@master ~]# chown -R postgres.postgres /var/log/pgpool [root@master ~]# mkdir /var/run/pgpool [root@master ~]# chown -R postgres.postgres /var/run/pgpool 5.1 启动集群 分别启动primary,standby的pg库 #master上操作 [postgres@master ~]$ pg_ctl start -D $PGDATA #slave上操作 [postgres@slave ~]$ pg_ctl start -D $PGDATA 分别启动pgpool命令： #master上操作 # -D会重新加载pg nodes的状态如down或up [postgres@master ~]$ pgpool -n -d -D > /var/log/pgpool/pgpool.log 2>&1 & [1] 3557 #slave上操作 [postgres@slave ~]$ pgpool -n -d -D > /var/log/pgpool/pgpool.log 2>&1 & [1] 3557 注意快速终止pgpool命令： [postgres@ ~]$ pgpool -m fast stop 启动pgpool后，查看集群节点状态: [postgres@master ~]$ psql -h vip -p 9999 psql (9.6.1) #提示输入密码： Type "help" for help. postgres=# show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay ---------+----------+------+--------+-----------+---------+------------+-------------------+------------------- 0 | master | 5432 | up | 0.500000 | primary | 0 | false | 0 1 | slave | 5432 | up | 0.500000 | standby | 0 | true | 0 (2 rows) #在slave上节点也是psql -h vip -p 9999，双pgpool使用虚拟ip，做到高可用。 发现当前主备节点都是正常的up状态。 5.2 Pgpool的HA 5.2.1 模拟master端pgpool宕机 在master节点上停止pgpool服务 [postgres@master ~]$ pgpool -m fast stop #稍等片刻后，访问集群 [postgres@master ~]$ psql -h vip -p 9999 psql (9.6.1) #提示输入密码： Type "help" for help. postgres=# show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay ---------+----------+------+--------+-----------+---------+------------+-------------------+------------------- 0 | master | 5432 | up | 0.500000 | primary | 0 | false | 0 1 | slave | 5432 | up | 0.500000 | standby | 0 | true | 0 (2 rows) #访问成功，在master节点上的pgpool宕机后，由slave节点的pgpool接管vip和集群服务，并未中断应用访问。 #在master上重新启动pgpool后，定制slave上的pgpool服务，结果一样。 5.2.2模拟master端pg primary宕机 [postgres@master ~]$ pg_ctl stop #master端打印 2017-07-24 18:52:37.751 PDT [28154] STATEMENT: SELECT pg_current_xlog_location() 2017-07-24 18:52:37.760 PDT [2553] LOG: received fast shutdown request 2017-07-24 18:52:37.760 PDT [2553] LOG: aborting any active transactions 2017-07-24 18:52:37.762 PDT [28156] FATAL: canceling authentication due to timeout 2017-07-24 18:52:37.763 PDT [2555] LOG: shutting down 2017-07-24 18:52:37.768 PDT [28158] FATAL: the database system is shutting down 2017-07-24 18:52:37.775 PDT [28159] FATAL: the database system is shutting down 2017-07-24 18:52:39.653 PDT [2553] LOG: database system is shut down #slave端打印 2017-07-24 18:52:41.455 PDT [2614] LOG: invalid record length at 0/2A000098: wanted 24, got 0 2017-07-24 18:52:47.333 PDT [2614] LOG: received promote request 2017-07-24 18:52:47.333 PDT [2614] LOG: redo done at 0/2A000028 2017-07-24 18:52:47.333 PDT [2614] LOG: last completed transaction was at log time 2017-07-24 18:17:00.946759-07 2017-07-24 18:52:47.336 PDT [2614] LOG: selected new timeline ID: 10 2017-07-24 18:52:47.841 PDT [2614] LOG: archive recovery complete 2017-07-24 18:52:47.851 PDT [2613] LOG: database system is ready to accept connections #日志清楚看到主机down机了，slave切换了。 #稍等片刻后，访问集群 [postgres@master ~]$ psql -h vip -p 9999 Password: psql (10beta1) Type "help" for help. postgres=# show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay ---------+----------+------+--------+-----------+---------+------------+-------------------+------------------- 0 | master | 5432 | down | 0.500000 | standby | 0 | false | 0 1 | slave | 5432 | up | 0.500000 | primary | 0 | true | 0 (2 rows) #slave已经被切换成primary，且master节点状态是down 5.2.3 修复master节点重新加入集群 master节点down机后，slave节点已经被切换成了primary，修复好master后应重新加入节点，作为primary的standby。 修复master端并启动操作： [postgres@master ~]$ cd $PGDATA [postgres@master data]$ mv recovery.done recovery.conf #一定要把.done改成.conf [postgres@master data]$ pg_ctl start 在pgpool集群中加入节点状态: #注意master的node_id是0，所以-n 0 [postgres@master data]$ pcp_attach_node -d -U postgres -h vip -p 9898 -n 0 #提示输入密码，输入pcp管理密码。 #查看当前状态 postgres=# show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay ---------+----------+------+--------+-----------+---------+------------+-------------------+------------------- 0 | master | 5432 | up | 0.500000 | standby | 0 | false | 0 1 | slave | 5432 | up | 0.500000 | primary | 0 | true | 0 (2 rows) 5.2.4 主机直接down机 当前slave节点是primay，我们直接将slave服务器直接关机后，发现实现了主备切换，slave已经down了，而master已经被切换成了primary： [postgres@master ~]$ psql -h vip -p 9999 Password: psql (10beta1) Type "help" for help. postgres=# show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay ---------+----------+------+--------+-----------+---------+------------+-------------------+------------------- 0 | master | 5432 | up | 0.500000 | primary | 0 | true | 0 1 | slave | 5432 | down | 0.500000 | standby | 0 | false | 0 (2 rows) 5.3 数据线同步 在主备切换时，修复节点并重启后，由于primary数据发生变化，或修复的节点数据发生变化再按照流复制模式加入集群，很可能报时间线不同步错误： #slave机器重启后，由于master或slave数据不同步产生了 [postgres@slave data]$ mv recovery.done recovery.conf [postgres@slave data]$ pg_ctl start waiting for server to start....2017-07-24 19:31:44.563 PDT [2663] LOG: listening on IPv4 address "0.0.0.0", port 5432 2017-07-24 19:31:44.563 PDT [2663] LOG: listening on IPv6 address "::", port 5432 2017-07-24 19:31:44.565 PDT [2663] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2017-07-24 19:31:44.584 PDT [2664] LOG: database system was shut down at 2017-07-24 19:31:30 PDT 2017-07-24 19:31:44.618 PDT [2664] LOG: entering standby mode 2017-07-24 19:31:44.772 PDT [2664] LOG: consistent recovery state reached at 0/2D000098 2017-07-24 19:31:44.772 PDT [2663] LOG: database system is ready to accept read only connections 2017-07-24 19:31:44.772 PDT [2664] LOG: invalid record length at 0/2D000098: wanted 24, got 0 2017-07-24 19:31:44.798 PDT [2668] LOG: fetching timeline history file for timeline 11 from primary server 2017-07-24 19:31:44.826 PDT [2668] FATAL: could not start WAL streaming: ERROR: requested starting point 0/2D000000 on timeline 10 is not in this server's history DETAIL: This server's history forked from timeline 10 at 0/2B0001B0. 2017-07-24 19:31:44.826 PDT [2664] LOG: new timeline 11 forked off current database system timeline 10 before current recovery point 0/2D000098 done 产生这种情况，需要根据pg_rewind工具同步数据时间线，具体分5步走。 5.3.1停掉需要做同步的节点pg服务 [postgres@slave ] pg_ctl stop 5.3.2 同步master节点上时间线 [postgres@slave data]$ pg_rewind --target-pgdata=/home/postgres/data --source-server='host=master port=5432 user=postgres dbname=postgres password=nariadmin' servers diverged at WAL location 0/2B0001B0 on timeline 10 rewinding from last common checkpoint at 0/2B000108 on timeline 10 Done! 5.3.3 修改pg_hba.conf与 recovery.done文件 #pg_hba.conf与 recovery.done都是同步master上来的，要改成slave自己的 [postgres@slave ] cd $PGDATA [postgres@slave data]$ mv recovery.done recovery.conf [postgres@slave data]$ vi pg_hba.conf #slave改成master（相当于slave的流复制对端） host replication repuser master md5 [postgres@slave data]$ vi recovery.conf #slave改成master（相当于slave的流复制对端） primary_conninfo = 'host=master port=5432 user=repuser password=repuser' 5.3.4 重启pg服务 [postgres@slave data]$ pg_ctl start waiting for server to start....2017-07-24 19:47:06.821 PDT [2722] LOG: listening on IPv4 address "0.0.0.0", port 5432 2017-07-24 19:47:06.821 PDT [2722] LOG: listening on IPv6 address "::", port 5432 2017-07-24 19:47:06.907 PDT [2722] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2017-07-24 19:47:06.930 PDT [2723] LOG: database system was interrupted while in recovery at log time 2017-07-24 19:25:42 PDT 2017-07-24 19:47:06.930 PDT [2723] HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. 2017-07-24 19:47:06.961 PDT [2723] LOG: entering standby mode 2017-07-24 19:47:06.966 PDT [2723] LOG: redo starts at 0/2B0000D0 2017-07-24 19:47:06.971 PDT [2723] LOG: consistent recovery state reached at 0/2B01CA30 2017-07-24 19:47:06.972 PDT [2722] LOG: database system is ready to accept read only connections 2017-07-24 19:47:06.972 PDT [2723] LOG: invalid record length at 0/2B01CA30: wanted 24, got 0 2017-07-24 19:47:06.982 PDT [2727] LOG: started streaming WAL from primary at 0/2B000000 on timeline 11 done server started 5.3.5 重新加入集群 #注意slave的node_id是1，所以-n 1 [postgres@slave data]$ pcp_attach_node -d -U postgres -h vip -p 9898 -n 1 Password: #提示输入密码，输入pcp管理密码。 DEBUG: recv: tos="m", len=8 DEBUG: recv: tos="r", len=21 DEBUG: send: tos="C", len=6 DEBUG: recv: tos="c", len=20 pcp_attach_node -- Command Successful DEBUG: send: tos="X", len=4 5.3.6 查看集群节点状态 [postgres@slave data]$ psql -h vip -p 9999 Password: psql (10beta1) Type "help" for help. postgres=# show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay ---------+----------+------+--------+-----------+---------+------------+-------------------+------------------- 0 | master | 5432 | up | 0.500000 | primary | 0 | true | 0 1 | slave | 5432 | up | 0.500000 | standby | 0 | false | 0 (2 rows) 全部恢复工作完成。
关于pg热备与主备切换网上很多内容都有了，本文仅为自己测试使用，特意记录过程，或对其他人提供参考。本文作者选择pg9.6.1版本作为测试。 一 主备机器规划 主机名 | IP | 角色 | 端口 :----:|:----:|:----:|:----:|:----:|:----: master| 192.168.0.108 |Master|5432 slave|192.168.0.109|Slave|5432 前提：分别在两台主机上安装好pg数据库，安装过程参考之前博客说明http://www.jianshu.com/p/639ebb43bfb4。 二 创建流复制 2.1 设置host master,slave两节点都要操作。 [root@bogon ~]# vim /etc/hosts #编辑内容如下： 192.168.43.127 master 192.168.43.243 slave 按esc，wq!保存退出。 2.2 初始化master数据库 以下操作在master下执行： #切换到postgres账户 [root@bogon ~]# su - postgres #初始化data [postgres@bogon ~]$ initdb -D $PGDATA # 启动master数据库 [postgres@bogon ~]$ pg_ctl start -D $PGDATA #创建流复制用户 [postgres@bogon ~]$ psql psql (9.6.1) Type "help" for help. postgres=# CREATE USER repuser replication LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD 'repuser'; CREATE ROLE 2.3 配置pg_hba.conf 在master的pg_hba.conf最后一行增加如下: host all all 0.0.0.0/0 md5 host replication repuser slave md5 2.4 配置postgresql.conf 在master端配置如下: listen_addresses = '*' port = 5432 max_wal_senders = 1 wal_level = replica archive_mode = on archive_command = 'cd ./' hot_standby = on wal_keep_segments = 64 full_page_writes = on wal_log_hints = on 配置完成后，重启master数据库 [postgres@bogon ~]$ pg_ctl restart -D $PGDATA 2.5 pg_basebackup 创建备库 在slave端的postgres账户下执行： #切换到postgres账户 [root@bogon ~]# su - postgres #从主库备份创建备库 [postgres@bogon ~]$ pg_basebackup -D $PGDATA -Fp -Xs -v -P -h master -p 5432 -U repuser transaction log start point: 0/2000060 on timeline 1 pg_basebackup: starting background WAL receiver 22806/22806 kB (100%), 1/1 tablespace transaction log end point: 0/2000130 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: base backup completed 修改slave中data目录下的pg_hba.conf最后一行修改如下: host all all 0.0.0.0/0 md5 host replication repuser master md5 2.6 配置recovery.conf Master端配置如下： [postgres@bogon ~]$ ls bin data gdal geos include lib proj4 share [postgres@bogon ~]$ cp share/recovery.conf.sample data/recovery.done [postgres@bogon ~]$ vim data/recovery.done #编辑内容如下 recovery_target_timeline = 'latest' standby_mode = on primary_conninfo = 'host=slave port=5432 user=repuser password=repuser' trigger_file = '/home/postgres/data/trigger_file' Salve端配置如下： [postgres@bogon ~]$ ls bin data gdal geos include lib proj4 share [postgres@bogon ~]$ cp share/recovery.conf.sample data/recovery.conf [postgres@bogon ~]$ vim data/recovery.conf #编辑内容如下 recovery_target_timeline = 'latest' standby_mode = on primary_conninfo = 'host=master port=5432 user=repuser password=repuser' trigger_file = '/home/postgres/data/trigger_file' 2.7 配置.pgpass master上配置访问slave参数 [postgres@bogon ~]$ vim .pgpass slave:5432:postgres:repuser:repuser slave上配置访问master参数 [postgres@bogon ~]$ vim .pgpass master:5432:postgres:repuser:repuser 2.8 流复制数据同步测试 分别启动master，slave数据库 在master上创建一个数据库和临时表 [postgres@bogon data]$ psql psql (9.6.1) Type "help" for help. postgres=# \password #创建数据库密码 #创建测试数据库 postgres=# create database test; CREATE DATABASE postgres=# \c test You are now connected to database "test" as user "postgres". test=# create table tt(id serial not null,name text); CREATE TABLE test=# insert into tt(name) values ('china'); INSERT 0 1 在slave上查询刚才创建的表和数据，判定是否有数据同步 [postgres@bogon data]$ psql psql (9.6.1) Type "help" for help. postgres=# \c test You are now connected to database "test" as user "postgres". test=# select * from tt; id | name ----+------- 1 | china (1 row) 很明显，从库已经同步了主库的数据，到此可以说PG流复制热备已经创建结束了。以下对流复制做一些简单的应用。 三 主备切换 一般可以通过若干命令查询数据库的主备属性，主数据库是读写的，备数据库是只读的。当主数据库宕机了，可以通过建立触发文件，备数据库将被提升为主数据库，实现一些基本的HA应用。 3.1 查询主备 3.1.1 pg_controldata 主机 [postgres@localhost ~]$ pg_controldata pg_control version number: 960 Catalog version number: 201608131 Database system identifier: 6362107256088627972 Database cluster state: in production 备机 pg_control version number: 960 Catalog version number: 201608131 Database system identifier: 6362107256088627972 Database cluster state: in archive recovery 主机的cluster state是in production，备机的cluster state是in archive recovery。 3.1.2 字典表pg_stat_replication 在主机字典表中是能查到记录，备机中是查询不到的。 postgres=# select pid,application_name,client_addr,client_port,state,sync_state from pg_stat_replication; pid | application_name | client_addr | client_port | state | sync_state -------+------------------+---------------+-------------+-----------+------------ 17131 | walreceiver | 192.168.0.105 | 55734 | streaming | async (1 row) 3.1.3 进程信息识别 进程中显示wal sender的是主机，显示wal receiver的是备机 master: 主机进程.png slave: 备机进程.png 3.1.4 通过pg函数 备机是t，主机是f。 主机 postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row) 备机 postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row) 3.2 备机切换成主机 主机宕机前： 宕机前备机进程.png 主机执行pg_ctl stop 命令。 宕机后： 主机宕机后.png 备机报错，说不能连接主机了。 之前曾配置过： trigger_file = '/home/postgres/datatrigger_file' 备机切换到主机，在备机上执行 [postgres@localhost ]$ touch /home/postgres/data/trigger_file; 再去备机上查看进程： 备机进程.png 备机已经切换到主机了。 3.3 原来主机切换成备机 在当前主机（现在是slave了，主机切换了）执行插入语句 postgres=# insert into tt(name) values('sdf'); 如果master上data目录中的recovery.done没有变成recovery.conf，可以手动强制更改。 [postgres@data]$ mv recovery.done recovery.conf 如果已经是recovery.conf就直接执行下面的。 #启动数据库 [postgres@data]$ pg_ctl start [postgres@bogon data]$ ERROR: requested starting point 0/6000000 on timeline 1 is not in this server's history DETAIL: This server's history forked from timeline 1 at 0/4000098. ERROR: requested starting point 0/6000000 on timeline 1 is not in this server's history DETAIL: This server's history forked from timeline 1 at 0/4000098. ERROR: requested starting point 0/6000000 on timeline 1 is not in this server's history #其实还有很多时间线不一致等 原因是当前主机slave数据发生变化了，原来的master数据和当前数据不一致了，要确保数据时间线一致。我们使用pg_rewind来同步时间线。 在master（从机上操作） #从slave上拉取最新时间线和数据给当前的master [postgres@bogon ~]$ pg_rewind --target-pgdata=/home/postgres/data --source-server='host=slave port=5432 user=postgres dbname=postgres' target server must be shut down cleanly Failure, exiting 发现报错了，说target server 必须关闭，source-server是slave，那么target server就是master，那么停掉master上pg服务。 #先停止服务 [postgres@bogon ~]$ pg_ctl stop #再拉取数据 [postgres@bogon ~]$ pg_rewind --target-pgdata=/home/postgres/data --sourceserver='host=slave port=5432 user=postgres dbname=postgres' servers diverged at WAL position 0/4000098 on timeline 1 rewinding from last common checkpoint at 0/4000028 on timeline 1 Done! #重启服务 [postgres@bogon ~]$ pg_ctl start 可以检查下，现在的master上数据和slave是一致的了。
一 Canvas跨域现象 地图导出是地图中常用的功能，并且OpenLayers3中也提供了两个地图导出的例子:http://openlayers.org/en/latest/examples/export-map.html http://openlayers.org/en/latest/examples/export-pdf.html。 看到这两个例子我们都很兴奋，直接copy过来不就实现导出地图了吗？so easy，妈妈再也不用担心我导出不了地图图片啦！ 但当我们抄好代码执行时，现实就是这么赤裸裸的打脸： 我的代码如下： //街道图 Layer.streetLayer=new ol.layer.Tile({ source: new ol.source.XYZ({ url: 'http://www.google.cn/maps/vt?pb=!1m5!1m4!1i{z}!2i{x}!3i{y}!4i256!2m3!1e0!2sm!3i342009817!3m9!2szh-CN!3sCN!5e18!12m1!1e47!12m3!1e37!2m1!1ssmartmaps!4e0&token=32965' }) }); 例子的图层代码如下： layer=new ol.layer.Tile({ source: new ol.source.OSM() }); 没多写一行代码的飘逸，抄过来直接运行，报错如下： 跨域错误.png 二 问题排查 检查案例源码发现和我抄袭的代码几乎一模一样，唯一区别是我使用的是谷歌底图，例子使用的是osm的source。 new ol.layer.Tile({ source: new ol.source.OSM() }), 为了一看究竟，我们查看osm的source源码如下： osm souce源码 恍然大悟，原来只需要添加这 crossOrigin:'anonymous'就可以了。 于是改写重置自己的谷歌图层代码如下： //街道图 Layer.streetLayer=new ol.layer.Tile({ source: new ol.source.XYZ({ crossOrigin: 'anonymous', url: 'http://www.google.cn/maps/vt?pb=!1m5!1m4!1i{z}!2i{x}!3i{y}!4i256!2m3!1e0!2sm!3i342009817!3m9!2szh-CN!3sCN!5e18!12m1!1e47!12m3!1e37!2m1!1ssmartmaps!4e0&token=32965' }) }); 这下打印正常了。 三 加载自己的wms底图还是跨域错误 我们上面通过对谷歌地图加crossOrigin: 'anonymous'实现了地图输出了，解决canvas跨域问题了，但一般我们地图是底图+业务底图（如wms）的，这时候打印发现还是报错。 Layer.streetLayer=new ol.layer.Tile({ source: new ol.source.XYZ({ crossOrigin: 'anonymous', url: 'http://www.google.cn/maps/vt?pb=!1m5!1m4!1i{z}!2i{x}!3i{y}!4i256!2m3!1e0!2sm!3i342009817!3m9!2szh-CN!3sCN!5e18!12m1!1e47!12m3!1e37!2m1!1ssmartmaps!4e0&token=32965' }) }); Layer.wmsship = new ol.layer.Tile({ source: new ol.source.TileWMS({ url: geoserverhost+'/gwc/service/wms', params: {'FORMAT': 'image/png', 'VERSION': '1.1.1', tiled: true, STYLES: '', LAYERS: 'ships:ta_pos_latest' } }) }); 这个例子我们使用了谷歌底图叠加自己的wms/gwc等瓦片图，发现export还是报跨域错误，理所当让的，我给Layer.wmsship设置crossOrigin: 'anonymous'，以为就可以了，但不幸的是仍然报跨域错误，一点没反应。 四 设置服务器cors彻底解决 我们发现，谷歌，osm设置crossOrigin就可以，我们自己wms设置的crossOrigin还是不行。问题出在哪里咧？查阅资料可知，osm,google的服务器一定设置了cors，所以客户端设置crossOrigin才会起作用，而我们自己的wms或者瓦片所在的服务器没有设置cors，所以客户端设置或者不设置crossOrigin，都是无效的。也就是说，只有自己的服务器设置了cors，crossOrigin才会起到作用。 我们地图是geoserver发布的wms或者gwc，对geoserver设置cors，参考之前的博客：http://blog.csdn.net/freeland1/article/details/41204485 ，根据第三节的cors设置下自己的服务器。 服务器设置完毕后，代码改为如下 Layer.streetLayer=new ol.layer.Tile({ visible: true, source: new ol.source.XYZ({ crossOrigin: 'anonymous', url: 'http://www.google.cn/maps/vt?pb=!1m5!1m4!1i{z}!2i{x}!3i{y}!4i256!2m3!1e0!2sm!3i342009817!3m9!2szh-CN!3sCN!5e18!12m1!1e47!12m3!1e37!2m1!1ssmartmaps!4e0&token=32965' }) }); Layer.wmsship = new ol.layer.Tile({ source: new ol.source.TileWMS({ crossOrigin: 'anonymous', url: geoserverhost+'/gwc/service/wms', params: {'FORMAT': 'image/png', 'VERSION': '1.1.1', tiled: true, STYLES: '', LAYERS: 'ships:ta_pos_latest' } }) }); 地图输出终于不报错了，perfect！
本文认为已安装PostgreSQL9.6，安装步骤如 Centos7安装PostgreSQL9.6。 注意，作者将Pg9.6直接安装到了postgres用户下如下图： Postgresql数据库安装全目录 下文其他依赖编译到pg的目录下最好，自行对照自己的修改下。 从源码安装，需要安装/编译GEOS, Proj.4, GDAL,LibXML2 和JSON-C，所以一步步安装。 一 编译GEOS $ cd ~ $ wget http://download.osgeo.org/geos/geos-3.5.0.tar.bz2 $ tar -jxvf geos-3.5.0.tar.bz2 $ cd geos-3.5.0 $ ./configure --prefix=/home/postgres/geos --安装到PostgreSQL的账户里 $ make -j 32 $ make install 作者在执行make -j 32时报错如下： g++: command not found 于是直接yum先装上再说： yum install gcc-c++ 但是执行make -j 32时仍然报错，报错显示： error:#error "Can not compile without isnan function or macro" 报错截图.png 经过网友指点如下： # 在geos解压的文件中，找到/include/config.h # 编辑该文件，取消#undef HAVE_ISNAN的注释，保存退出 #然后重新配置geos如下： $ ./configure --prefix=/home/postgres/geos $ make -j 32 #等待编译完成时间蛮长的。 $ make install #正常编译完成 config.h文件目录位置 取消对HAVE_ISNAN的注释 二 编译Proj $ cd ~ $ wget http://download.osgeo.org/proj/proj-4.9.2.tar.gz $ tar -zxvf proj-4.9.2.tar.gz $ cd proj-4.9.2 $ ./configure --prefix=/home/postgres/proj4 $ make -j 32 $ make install 三 编译GDAL $ cd ~ $ wget http://download.osgeo.org/gdal/2.1.1/gdal-2.1.1.tar.gz $ tar -zxvf gdal-2.1.1.tar.gz $ cd gdal-2.1.1 #以下编译安装要花很长时间，想死 $ ./configure --prefix=/home/postgres/gdal --with-pg=/home/postgres/bin/pg_config $ make -j 32 $ make install 四 LibXML2 json-c 等等 # yum install -y libtool libxml2 libxml2-devel libxslt libxslt-devel json-c json-c-devel cmake gmp gmp-devel mpfr mpfr-devel boost-devel pcre-devel 五 安装PostGIS 看PostGIS官网的描述很简单，直接就典型的make makeinstall编译安装，很简洁，但是实际安就装会发现很多依赖库根本找不到如下图某个could not find GDAL这样，即使明明安装了，也找不到。 会报各种配置依赖找不到错误，典型的是gdal.png 这种问题一般都是链接库找不到，需要手动进行软连接等各种配置，正确配置后才能正确编译。 5.1 配置依赖库软连接 #编辑ld.so.conf文件。 [root@pg1 postgres]# vim /etc/ld.so.conf #边界内容如下 /home/postgres/lib /home/postgres/geos/lib /home/postgres/proj4/lib /home/postgres/gdal/lib #编辑完成后wq!保存退出 [root@pg1 postgres]# ldconfig #保存配置生效 5.2 配置Postgres用户环境变量 [root@pg1 postgres]# su - postgres [root@pg1 postgres]# vim .bashrc #原先这个用户环境变量配置了Postgres的东西，现在加进一些依赖进来 PGHOME=/home/postgres export PGHOME PGDATA=$PGHOME/data export PGDATA export LD_LIBRARY_PATH=/home/postgres/geos/lib:/home/postgres/proj4/lib:/home/postgres/gdal/lib::$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/bin export PATH #编辑完成wq!保存退出。 [root@pg1 postgres]# source .bashrc #重启配置生效 5.3 编译安装PostGIS $ wget http://download.osgeo.org/postgis/source/postgis-2.3.0.tar.gz $ tar -zxvf postgis-2.3.0.tar.gz $ cd postgis-2.3.0 $ ./configure --prefix=/home/postgres --with-gdalconfig=/home/postgres/gdal/bin/gdal-config --with-pgconfig=/home/postgres/bin/pg_config --with-geosconfig=/home/postgres/geos/bin/geos-config --with-projdir=/home/postgres/proj4 $ make $ make install 六 创建postgis扩展 [root@pg1 postgres]# psql Test Test=# create extension postgis; CREATE EXTENSION #在测试数据库中创建扩展成功。
一 安装必备软件 1.1 安装SCWS 下载scws：http://www.xunsearch.com/scws/down/scws-1.2.2.tar.bz2 #解压 [root@pg1 opt]# tar -jxvf scws-1.2.2.tar.bz2 #编译&安装 [root@pg1 opt]# cd scws-1.2.2 [root@pg1 scws-1.2.2]# ./configure [root@pg1 scws-1.2.2]# make [root@pg1 scws-1.2.2]# make install 1.2 安装zhparser gtihub主页:https://github.com/amutu/zhparser zhparser地址：https://codeload.github.com/amutu/zhparser/zip/master # 解压 [root@pg1 opt]# unzip zhparser-master.zip # 进入文件目录 [root@pg1 opt]# cd zhparser-master # 设置path目录 [root@pg1 zhparser-master]# export PATH=$PATH:/home/postgres/bin # 编译安装 [root@pg1 zhparser-master]# SCWS_HOME=/usr/local make && make install 1.3 安装神器RUM 关于PostgreSQL9.6的全文搜索神器RUM，可以查看德哥的博客：PostgreSQL 全文检索加速 快到没有朋友 - RUM索引接口PostgreSQL 文本数据分析实践之 - 相似度分析 rum下载地址：https://codeload.github.com/postgrespro/rum/zip/master # 解压，比如在 /opt/rum-master目录下 [root@pg1 opt]# unzip rum-master.zip # 赋予文件权限 [root@pg1 opt]# chown -R postgres.postgres /opt/rum-master # 切换到postgres账户 [root@pg1 opt]# su - postgres # 进入文件目录 [postgres@pg1 ~]$ cd /opt/rum-master # 编译 [postgres@pg1 rum-master]$ make USE_PGXS=1 [postgres@pg1 rum-master]$ make USE_PGXS=1 install [postgres@pg1 rum-master]$ make USE_PGXS=1 installcheck 二 中文分词扩展安装配置 2.1 安装扩展 # 创建一个新数据库 [postgres@pg1 rum-master]$ psql psql (9.6.0) Type "help" for help. postgres=# create database knowledge; CREATE DATABASE # 切换到knowledge数据库 postgres=# \c knowledge; You are now connected to database "knowledge" as user "postgres". # 创建zhparser扩展 knowledge=# create extension zhparser; CREATE EXTENSION # 创建rum扩展 knowledge=# create extension rum; CREATE EXTENSION # 查看安装的解析器 knowledge=# \dFp List of text search parsers Schema | Name | Description ------------+----------+--------------------- pg_catalog | default | default word parser public | zhparser | (2 rows) # 查看zhparser将中文切分成的26种token knowledge=# select ts_token_type('zhparser'); ts_token_type ----------------------------------------- (97,a,adjective) (98,b,"differentiation (qu bie)") (99,c,conjunction) (100,d,adverb) (101,e,exclamation) (102,f,"position (fang wei)") (103,g,"root (ci gen)") (104,h,head) (105,i,idiom) (106,j,"abbreviation (jian lue)") (107,k,head) (108,l,"tmp (lin shi)") (109,m,numeral) (110,n,noun) (111,o,onomatopoeia) (112,p,prepositional) (113,q,quantity) (114,r,pronoun) (115,s,space) (116,t,time) (117,u,auxiliary) (118,v,verb) (119,w,"punctuation (qi ta biao dian)") (120,x,unknown) (121,y,"modal (yu qi)") (122,z,"status (zhuang tai)") (26 rows) 2.2 创建使用zhparser作为解析器的全文搜索的配置 knowledge=# CREATE TEXT SEARCH CONFIGURATION testzhcfg (PARSER = zhparser); CREATE TEXT SEARCH CONFIGURATION 2.3 往全文搜索配置中增加token映射 knowledge=# ALTER TEXT SEARCH CONFIGURATION testzhcfg ADD MAPPING FOR n,v,a,i,e,l WITH simple; ALTER TEXT SEARCH CONFIGURATION 三 中文分词测试 # 由于客户端和服务器端字符集不同，一般中文会报错 knowledge=# select to_tsvector('testzhcfg','南京人民政府'); ERROR: invalid byte sequence for encoding "UTF8": 0xc4 0xcf # 解决方法方法有两种，一种是在psql中输入“\encoding GBK” # 另一种是设置环境变量“export PGCLIENTENCODING=GBK” knowledge=# \encoding GBK knowledge=# select to_tsvector('testzhcfg','南京人民政府'); to_tsvector ----------------------- '人民政府':2 '南京':1 (1 row) 以下配置在PG9.2及以上版本使用,这些选项是用来控制字典加载行为和分词行为的,这些选项都不是必须的,默认都为false(即如果没有在配置文件中设置这些选项，则zhparser的行为与将下面的选项设置为false一致)。 zhparser.punctuation_ignore = f zhparser.seg_with_duality = f zhparser.dict_in_memory = f zhparser.multi_short = f zhparser.multi_duality = f zhparser.multi_zmain = f zhparser.multi_zall = f 建议初始 zhparser.multi_short=on 设置为on。 knowledge=# set zhparser.multi_short=on; SET knowledge=# select to_tsvector('testzhcfg','南京人民政府'); to_tsvector ----------------------------------------- '人民':3 '人民政府':2 '南京':1 '政府':4 开启后，果然匹配的更多了。 四 行级全文检索 在一些应用程序中，可能需要对表的所有字段进行检索，有些字段可能需要精准查询，有些字段可能需要模糊查询或全文检索。 比如检索T表，某个字段存在一个名称为'a'的返回： select * from t where name1='a' or name1= 'a' or name2='a' or ......; 首先，or查询会不走索引，效率低下，其次如果加上like，效率更低。 在pg中就可以使用行级全文检索，如： create table Test( id serial not null, name text, name1 text ) insert into Test(name,name1) values ('中国','南京'); select t::text from Test t; #结果如下： knowledge=# select t::text from test t; t --------------- (1,中国,南京) (1 row) 将整行转成大文本，可以从中继续分词查询满足关键字的，研究未完待续。 五 其他专家提出的问题，这里引用如下： # ‘南大被忽视了’ knowledge=# select to_tsvector('testzhcfg','南大') ; to_tsvector ------------- (1 row) knowledge=# select to_tsvector('testzhcfg','南大 北大 东大 西大') ; to_tsvector ---------------------------- '东大':2 '北大':1 '西大':3 (1 row) 调查发现原因在于它们被SCWS解析出来的token类型不同： knowledge=# select ts_debug('testzhcfg','南大 北大 东大 西大') ; ts_debug ----------------------------------------- (j,"abbreviation (jian lue)",南大,{},,) (n,noun,北大,{simple},simple,{北大}) (n,noun,东大,{simple},simple,{东大}) (n,noun,西大,{simple},simple,{西大}) (4 rows) '南大'被识别为j(简略词)，而之前并没有为j创建token映射。现在加上j的token映射，就可以了。 knowledge=# ALTER TEXT SEARCH CONFIGURATION testzhcfg ADD MAPPING FOR j WITH simple; ALTER TEXT SEARCH CONFIGURATION knowledge=# select to_tsvector('testzhcfg','南大 北大 东大 西大') ; to_tsvector ------------------------------------- '东大':3 '北大':2 '南大':1 '西大':4 (1 row)
最新版本的9.6出来了，新增一大堆令人垂涎欲滴的新功能。本文按照套路给个安装教程，仅仅做自己参考使用。 1 安装必要基本软件 [root@pg1 ]# yum install -y gcc.x86_64 glibc.x86_64 glibc-devel.x86_64 vim-enhanced.x86_64 gcc-java apr apr-devel openssl openssl-devel libgcc.x86_64 java-1.8.0-openjdk.x86_64 java-1.8.0-openjdk-devel.x86_64 perl-Module-Install.noarch 安装readline-devel,不装的话编译的时候会有错误提示 [root@pg1 ]# yum install -y readline-devel.x86_64 去postgresql官网下载最新的9.6源码：https://ftp.postgresql.org/pub/source/v9.6.0/postgresql-9.6.0.tar.gz 2 创建postgres用户 [root@pg1 ]# adduser postgres 3 解压编译安装 下载uuid ossp库，下载地址：ftp://ftp.ossp.org/pkg/lib/uuid/uuid-1.6.2.tar.gz #解压安装uuid库 [root@pg1 opt]# tar -zxvf uuid-1.6.2.tar.gz [root@pg1 opt]# cd uuid-1.6.2 [root@pg1 uuid-1.6.2]# ./configure --with-uuid=ossp [root@pg1 uuid-1.6.2]# make [root@pg1 uuid-1.6.2]# make install #解压tar -zxvf postgresql-9.6.0.tar.gz [root@pg1 opt]# tar -zxvf postgresql-9.6.0.tar.gz #进入解压目录 [root@pg1 opt]# cd postgresql-9.6.0 #配置prefix是程序放哪里 [root@pg1 postgresql-9.6.0]# ./configure --prefix=/home/postgres --enable-thread-safety --with-uuid=ossp #编译&安装 [root@pg1 postgresql-9.6.0]# make [root@pg1 postgresql-9.6.0]# make install # 安装contrib工具包 [root@pg1 postgresql-9.6.0]# cd contrib [root@pg1 contrib]# make [root@pg1 contrib]# make install #等待安装完成 # 配置uuid的软连接 [root@pg1 lib64]# find / -name libuuid.so.16 /usr/local/lib/libuuid.so.16 /opt/uuid-1.6.2/.libs/libuuid.so.16 # 将/usr/local/lib/libuuid.so.16建立软连接到postgres的lib目录 [root@pg1 lib64]# ln -s /usr/local/lib/libuuid.so.16 /home/postgres/lib 4 设置权限 #把程序目录全部赋权给postgres用户 [root@pg1 ]# chown -R postgres.postgres /home/postgres/ 5 配置环境变量 编辑用户目录下.bashrc文件,主要是设置PGDATA变量 # 切换到postgres账户 [root@pg1 ]# su - postgres # 编辑用户下配置文件 [postgres@pg1 ]# vim .bashrc 编辑内容如下: PGHOME=/home/postgres export PGHOME PGDATA=$PGHOME/data export PGDATA PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/bin export PATH 编辑完成，按esc，输入 wq!保存退出，重新启用下配置文件 [postgres@pg1 ]# source .bashrc 6 初始化数据库 #在postgres账户下执行 [postgres@pg1 ]# initdb -D $PGDATA 7 启动数据库 [postgres@pg1 ]# pg_ctl start -D $PGDATA 8 设置用户密码 #使用postgres账户进入控制台(现在密码应该是空) [postgres@pg1 ]# psql -U postgres postgres=# \password Enter new password: <123456> Enter it again: <123456> #把密码设置成123456可以使用\q命令退出控制台 9 设置监听 修改postgres/data目录下的pg_hba.conf [postgres@pg1 ~]$ vim $PGDATA/pg_hba.conf 修改IPv4 一行内容如下: # IPv4 local connections: host all all 0.0.0.0/0 trust 修改postgresql.conf： [postgres@pg1 ~]$ vim $PGDATA/postgresql.conf 修改监听一节如下: # - Connection Settings - listen_addresses = '*' port = 5432 wq!保存退出。 重启pg服务生效 [postgres@pg1 ~]$ pg_ctl restart -D $PGDATA
nodejs一直以异步io著称，其语言特性尤其擅长于在realtime应用中，如聊天室等。在进行实时应用开发时，必不可少的需要用到 socket.io库，可以说，nodejs+socket.io在实时应用中具有较好的表现能力。 本文既然选择以实时地图应用做个小例子，那么选择经典的PostgreSQL/PostGIS作为地图的数据库。希望实现的是模拟数据库数据插入了新的GPS坐标，而一旦数据发生改变，立刻将插入的GPS坐标广播到服务端，服务端广播到所有的客户端地图上，进行定位展示。早期作者使用的是redis的广播/订阅机制，最近发现Pg数据库的listen/notify也具备这种消息传递机制。 本文主要的socke.io广播/订阅参考官网，Pg的listen/notify自行谷歌，作者仅简述一下自己如何考虑应用的。 一 服务器端 var fs = require('fs'); var http = require('http'); var socket = require('socket.io'); var pg = require('pg'); var util=require('util'); var constr=util.format('%s://%s:%s@%s:%s/%s', 'postgres','postgres','123456','192.168.43.125',5432,'Test'); var server = http.createServer(function(req, res) { res.writeHead(200, { 'Content-type': 'text/html'}); res.end(fs.readFileSync(__dirname + '/index.html')); }).listen(8081, function() { console.log('Listening at: http://localhost:8081'); }); var pgClient = new pg.Client(constr);//数据库连接 var socketio=socket.listen(server);//socketio socketio.on('connection', function (socketclient) { console.log('已连接socket:'); //socketclient.broadcast.emit('GPSCoor', data.payload);//广播给别人 //socketclient.emit('GPSCoor', data.payload);//广播给自己 }); var sql = 'LISTEN gps'; //监听数据库的gps消息 var query = pgClient.query(sql);//开始数据库消息监听 //数据库一旦获取通知，将通知消息通过socket.io发送到各个客户端展示。 pgClient.on('notification', function (data) { console.log(data.payload); //socketio.sockets.emit('GPSCoor', data.payload); //与下面的等价 socketio.emit('GPSCoor', data.payload);//广播给所有的客户端 }); pgClient.connect(); 二 数据库端 建立一个测试表如下： create table t_gps( id serial not null, geom geometry(Point,4326), constraint t_gps_pkey primary key (id) ); --建立索引 create index t_gps_geom_idx on t_gps using gist(geom); 对表的增删改建立一个触发器，触发器中发送变化数据出去: CREATE OR REPLACE FUNCTION process_t_gps() RETURNS TRIGGER AS $body$ DECLARE rec record; BEGIN IF (TG_OP = 'DELETE') THEN --插入的GPS都是4326的经纬度，我们将在3857的谷歌底图上显示数据，发送转换后的3857出去 select TG_OP TG_OP,OLD.id,ST_AsText(ST_Transform(OLD.geom,3857)) geom into rec; perform pg_notify('gps',row_to_json(rec)::text); RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN select TG_OP TG_OP,NEW.id,ST_AsText(ST_Transform(NEW.geom,3857)) geom into rec; perform pg_notify('gps',row_to_json(rec)::text); RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN select TG_OP TG_OP,NEW.id,ST_AsText(ST_Transform(NEW.geom,3857)) geom into rec; perform pg_notify('gps',row_to_json(rec)::text); RETURN NEW; END IF; RETURN NULL; END; $body$ LANGUAGE plpgsql; CREATE TRIGGER T_GPS_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON T_GPS FOR EACH ROW EXECUTE PROCEDURE process_t_gps(); 三 客户端 <html> <head> <meta charset='utf-8'> <title>实时地图应用</title> <link rel="stylesheet" href="http://openlayers.org/en/v3.18.2/css/ol.css" type="text/css"> <script src="http://openlayers.org/en/v3.18.2/build/ol.js"></script> <script src="/socket.io/socket.io.js"></script> <script> var wktform=new ol.format.WKT();//wkt解析 var gpsSource=new ol.source.Vector(); function init(){ var gpsLayer=new ol.layer.Vector({ source:gpsSource, style:new ol.style.Style({ image: new ol.style.Icon(({ anchor: [0.5, 1], src: 'http://openlayers.org/en/v3.18.2/examples/data/icon.png' })) }) }); var map = new ol.Map({ layers : [ new ol.layer.Tile({ title : '街道图', visible : true, source : new ol.source.XYZ({ url : 'http://www.google.cn/maps/vt?pb=!1m5!1m4!1i{z}!2i{x}!3i{y}!4i256!2m3!1e0!2sm!3i342009817!3m9!2szh-CN!3sCN!5e18!12m1!1e47!12m3!1e37!2m1!1ssmartmaps!4e0&token=32965' }) }), gpsLayer ], target : 'map', controls : ol.control.defaults({ attributionOptions : ({ collapsible : false }) }), view : new ol.View({ center : [0, 0], zoom : 2 }) }); var iosocket = io.connect(); //接受服务端消息 iosocket.on('GPSCoor', function(data) { data=JSON.parse(data); switch(data.tg_op){ case 'INSERT': var feature=new ol.Feature({ geometry:wktform.readGeometry(data.geom) }); feature.setId(data.id); gpsSource.addFeature(feature);//地图新增点 break; case 'UPDATE': var geom=wktform.readGeometry(data.geom); var feature=gpsSource.getFeatureById(data.id); if(feature) feature.setGeometry(geom);//修改已有点 break; case 'DELETE': var feature=gpsSource.getFeatureById(data.id); if(feature) gpsSource.removeFeature(feature);//删除点 break; } }); } </script> </head> <body onload="init()"> <div id="map"></div> </body> </html> 客户端接收到消息后，改变当前地图上的图标gps坐标位置。 四 测试与结果 连开三个客户端连接如下： 初始化三个客户端.png 服务器端socket连接成功.png 4.1 数据库新增GPS坐标 insert into t_gps(geom) values (st_geomfromtext('Point(0 0)',4326)); insert into t_gps(geom) values (st_geomfromtext('Point(118 32)',4326)); insert into t_gps(geom) values (st_geomfromtext('Point(-118 -32)',4326)); 页面自动响应效果如下： 服务器端监听到的数据库消息.png 服务器端socket到客户端的效果.png 4.2 数据库修改GPS坐标 查看下当前的数据如下： Test=# select id,st_astext(geom) from t_gps; id | st_astext ----+----------------- 24 | POINT(0 0) 25 | POINT(118 32) 26 | POINT(-118 -32) (3 rows) 将id=25的坐标改成 150,40: Test=# update t_gps set geom=st_geomfromtext('Point(150 40)',4326) where id=25; UPDATE 1 服务器端打印如下： 显示一条更新语句.png 更新效果.png 4.3 数据库删除GPS坐标 Test=# delete from t_gps where id=25; DELETE 1 显示一条删除.png 删除效果.png 所有以上操作，只是数据的增删改指令，服务器和客户端都是自动响应的。 结论：本文实现了，数据库一旦广播了消息，服务器端监听，并继续以sockeio广播到客户端。全部过程，只是数据库发送了一个坐标消息无任何其他操作。pg的notify和listen消息机制，真实应用一般比如写在触发器中，触发器监听是否有数据采集终端将新坐标写入或者更新，然后在触发器中notify消息，这样，前端实时响应。可以做到将终端应用位置无任何操作的一波流发送到全部客户端实时展示。
一 属性查询 笔者使用一模一样的osm数据，数据量8.7万个点，该数据有一个name列存储点的名称，本次实验，笔者使用 name='练塘镇'作为查询条件， 查询语句如下： http://localhost:8090/geoserver/wfs?service=WFS&version=1.0.0&request=GetFeature&typename=cite:places1&srsname=EPSG:4326 &outputFormat=application/json&cql_filter=name='练塘镇' 查询结果如下： 1.1 shp格式查询效率 1.png 1.2 PostGIS不建立索引查询效率 2.png 1.3 PostGIS建立索引查询效率 3.png 结论：属性查询这方面，以本例说明，Postgresql的表查询不建立索引比shp格式的数据存储快125倍，建立索引快338倍。 二 空间查询 用一个多边形与点图层做叠加分析，查询多边形范围内的点，查询语句如下： http://localhost:8090/geoserver/wfs?service=WFS&version=1.0.0&request=GetFeature&typename=cite:places&srsname=epsg:4326 &outputFormat=application/json&cql_filter=Intersects(geom,POLYGON((115 29,115 30,116 30,116 29,115 29))) 2.1 shp空间查询 4.png 2.2 PostGIS空间查询 5.png 结论：空间查询方面差别不是特别明显，初次查询pg的比shp快8倍，以后查询差不多，但pg仍然比shp快。个人猜测，esri针对shp格式的图形查询进行了优化。 三 多表关联 在数据库中存储的话，多表之间可以建立join关系，建立视图，建立存储过程等等，可以在不同业务之间的多表与图形join得到专题报表。 shp除了用后台获取数据，自行拼装以外，不能多个shp产生join这种操作。 实际项目业务表很多，一般和图形表关联使用，shp不能胜任这个任务。 结论：项目开发，shp只定位是数据中介，起到临时存储的作用，实际应用时，应导入空间数据库中。
一 Postgres-XL简介 Postgres的-XL是一个基于PostgreSQL数据库的横向扩展开源SQL数据库集群，具有足够的灵活性来处理不同的数据库工作负载: 完全ACID，保持事务一致性 OLTP 写频繁的业务 需要MPP并行性商业智能/大数据分析 操作数据存储 Key-value 存储 GIS的地理空间 混合业务工作环境 多租户服务提供商托管环境 Web 2.0 Postgres-XL架构 二 组件简介 Global Transaction Monitor (GTM) 全局事务管理器，确保群集范围内的事务一致性。 GTM负责发放事务ID和快照作为其多版本并发控制的一部分。 集群可选地配置一个备用GTM，以改进可用性。此外，可以在协调器间配置代理GTM， 可用于改善可扩展性，减少GTM的通信量。 GTM Standby GTM的备节点，在pgxc,pgxl中，GTM控制所有的全局事务分配，如果出现问题，就会导致整个集群不可用，为了增加可用性，增加该备用节点。当GTM出现问题时，GTM Standby可以升级为GTM，保证集群正常工作。 GTM-Proxy GTM需要与所有的Coordinators通信，为了降低压力，可以在每个Coordinator机器上部署一个GTM-Proxy。 Coordinator 协调员管理用户会话，并与GTM和数据节点进行交互。协调员解析，并计划查询，并给语句中的每一个组件发送下一个序列化的全局性计划。 为节省机器，通常此服务和数据节点部署在一起。 Data Node 数据节点是数据实际存储的地方。数据的分布可以由DBA来配置。为了提高可用性，可以配置数据节点的热备以便进行故障转移准备。 总结：gtm是负责ACID的，保证分布式数据库全局事务一致性。得益于此，就算数据节点是分布的，但是你在主节点操作增删改查事务时，就如同只操作一个数据库一样简单。Coordinator是调度的，将操作指令发送到各个数据节点。datanodes是数据节点，分布式存储数据。 更多介绍参考：《Postgres-XL：基于PostgreSQL的开源分布式实现》 三 Postgres-XL环境配置与安装 3.1 集群规划 准备三台Centos7服务器（或者虚拟机），集群规划如下： 主机名 IP 角色 端口 nodename 数据目录 gtm 192.168.0.125 GTM 6666 gtm /nodes/gtm GTM Slave 20001 gtmSlave /nodes/gtmSlave datanode1 192.168.0.127 Coordinator 5432 coord1 /nodes/coord Datanode 5433 node1 /nodes/dn_master Datanode Slave 15433 node1_slave /nodes/dn_slave GTM Proxy 6666 gtm_pxy1 /nodes/gtm_pxy datanode2 192.168.0.128 Coordinator 5432 coord2 /nodes/coord Datanode 5433 node2 nodes/dn_master Datanode Slave 15433 node2_slave /nodes/dn_slave GTM Proxy 6666 gtm_pxy2 /nodes/gtm_pxy 在每台机器的 /etc/hosts中加入以下内容： 192.168.0.125 gtm 192.168.0.126 datanode1 192.168.0.127 datanode2 gtm上部署gtm，gtm_sandby测试环境暂未部署。 Coordinator与Datanode节点一般部署在同一台机器上。实际上，GTM-proxy,Coordinator与Datanode节点一般都在同一个机器上，使用时避免端口号与连接池端口号重叠！规划datanode1,datanode2作为协调节点与数据节点。 3.2 系统环境设置 以下操作，对每个服务器节点都适用。 关闭防火墙： [root@localhost ~]# systemctl stop firewalld.service [root@localhost ~]# systemctl disable firewalld.service selinux设置: [root@localhost ~]#vim /etc/selinux/config 设置SELINUX=disabled，保存退出。 # This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: # enforcing - SELinux security policy is enforced. # permissive - SELinux prints warnings instead of enforcing. # disabled - No SELinux policy is loaded. SELINUX=disabled # SELINUXTYPE= can take one of three two values: # targeted - Targeted processes are protected, # minimum - Modification of targeted policy. Only selected processes are protected. # mls - Multi Level Security protection. 安装依赖包： [root@localhost ~]# yum install -y flex bison readline-devel zlib-devel openjade docbook-style-dsssl 重启服务器！一定要重启！ 3.3 新建用户 每个节点都建立用户postgres，并且建立.ssh目录，并配置相应的权限： [root@localhost ~]# useradd postgres [root@localhost ~]# passwd postgres [root@localhost ~]# su - postgres [root@localhost ~]# mkdir ~/.ssh [root@localhost ~]# chmod 700 ~/.ssh 3.4 ssh免密码登录 仅仅在gtm节点配置如下操作： [root@localhost ~]# su - postgres [postgres@localhost ~]# ssh-keygen -t rsa [postgres@localhost ~]# cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys [postgres@localhost ~]# chmod 600 ~/.ssh/authorized_keys 将刚生成的认证文件拷贝到datanode1到datanode2中，使得gtm节点可以免密码登录datanode1~datanode2的任意一个节点： [postgres@localhost ~]# scp ~/.ssh/authorized_keys postgres@datanode1:~/.ssh/ [postgres@localhost ~]# scp ~/.ssh/authorized_keys postgres@datanode2:~/.ssh/ 对所有提示都不要输入，直接enter下一步。直到最后，因为第一次要求输入目标机器的用户密码，输入即可。 3.5 Postgres-XL安装 pg1-pg3每个节点都需安装配置。切换回root用户下，执行如下步骤安装 [root@localhost ~]# cd /opt [root@localhost ~]# git clone git://git.postgresql.org/git/postgres-xl.git [root@localhost ~]# cd postgres-xl [root@localhost ~postgres-xl]# ./configure --prefix=/home/postgres/pgxl/ [root@localhost ~postgres-xl]# make [root@localhost ~postgres-xl]# make install [root@localhost ~postgres-xl]# cd contrib/ [root@localhost ~contrib]# make [root@localhost ~contrib]# make install cortrib中有很多postgres很牛的工具，一般要装上。如ltree,uuid,postgres_fdw等等。 3.6 配置环境变量 进入postgres用户，修改其环境变量，开始编辑 [root@localhost ~]#su - postgres [postgres@localhost ~]#vi .bashrc 在打开的文件末尾，新增如下变量配置： export PGHOME=/home/postgres/pgxl export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH export PATH=$PGHOME/bin:$PATH 按住esc，然后输入:wq!保存退出。输入以下命令对更改重启生效。 [root@localhost ~]# source .bashrc #输入以下语句，如果输出变量结果，代表生效 [root@localhost ~]# echo $PGHOME #应该输出/home/postgres/pgxl代表生效 如上操作，除特别强调以外，是datanode1-datanode2节点都要配置安装的。 四 集群配置 4.1 生成pgxc_ctl配置文件 [postgres@localhost ~]# pgxc_ctl PGXC prepare ---执行该命令将会生成一份配置文件模板 PGXC ---按ctrl c退出。 4.2 配置pgxc_ctl.conf 在pgxc_ctl文件夹中存在一个pgxc_ctl.conf文件，编辑如下： pgxcInstallDir=$PGHOME pgxlDATA=$PGHOME/data pgxcOwner=postgres #---- GTM Master ----------------------------------------- gtmName=gtm gtmMasterServer=gtm gtmMasterPort=6666 gtmMasterDir=$pgxlDATA/nodes/gtm gtmSlave=y # Specify y if you configure GTM Slave. Otherwise, GTM slave will not be configured and # all the following variables will be reset. gtmSlaveName=gtmSlave gtmSlaveServer=gtm # value none means GTM slave is not available. Give none if you don't configure GTM Slave. gtmSlavePort=20001 # Not used if you don't configure GTM slave. gtmSlaveDir=$pgxlDATA/nodes/gtmSlave # Not used if you don't configure GTM slave. #---- GTM-Proxy Master ------- gtmProxyDir=$pgxlDATA/nodes/gtm_proxy gtmProxy=y gtmProxyNames=(gtm_pxy1 gtm_pxy2) gtmProxyServers=(datanode1 datanode2) gtmProxyPorts=(6666 6666) gtmProxyDirs=($gtmProxyDir $gtmProxyDir) #---- Coordinators --------- coordMasterDir=$pgxlDATA/nodes/coord coordNames=(coord1 coord2) coordPorts=(5432 5432) poolerPorts=(6667 6667) coordPgHbaEntries=(0.0.0.0/0) coordMasterServers=(datanode1 datanode2) coordMasterDirs=($coordMasterDir $coordMasterDir) coordMaxWALsernder=0 #没设置备份节点，设置为0 coordMaxWALSenders=($coordMaxWALsernder $coordMaxWALsernder) #数量保持和coordMasterServers一致 coordSlave=n #---- Datanodes ---------- datanodeMasterDir=$pgxlDATA/nodes/dn_master primaryDatanode=node1 # 主数据节点 datanodeNames=(node1 node2) datanodePorts=(5433 5433) datanodePoolerPorts=(6668 6668) datanodePgHbaEntries=(0.0.0.0/0) datanodeMasterServers=(datanode1 datanode2) datanodeMasterDirs=($datanodeMasterDir $datanodeMasterDir) datanodeMaxWalSender=4 datanodeMaxWALSenders=($datanodeMaxWalSender $datanodeMaxWalSender) datanodeSlave=n #将datanode1节点的slave做到了datanode2服务器上，交叉做了备份 datanodeSlaveServers=(datanode2 datanode1) # value none means this slave is not available datanodeSlavePorts=(15433 15433) # value none means this slave is not available datanodeSlavePoolerPorts=(20012 20012) # value none means this slave is not available datanodeSlaveSync=y # If datanode slave is connected in synchronized mode datanodeSlaveDirs=($datanodeSlaveDir $datanodeSlaveDir) datanodeArchLogDirs=( $datanodeArchLogDir $datanodeArchLogDir) 如上配置，都没有配置slave，具体生产环境，请阅读配置文件，自行配置。 4.3 集群初始化，启动，停止 第一次启动集群，需要初始化，初始化如下： [postgres@pg1 pgxc_ctl]$ pgxc_ctl -c /home/postgres/pgxc_ctl/pgxc_ctl.conf init all 初始化后会直接启动集群。 /bin/bash Installing pgxc_ctl_bash script as /home/postgres/pgxc_ctl/pgxc_ctl_bash. Installing pgxc_ctl_bash script as /home/postgres/pgxc_ctl/pgxc_ctl_bash. Reading configuration using /home/postgres/pgxc_ctl/pgxc_ctl_bash --home /home/postgres/pgxc_ctl --configuration /home/postgres/pgxc_ctl/pgxc_ctl.conf Finished reading configuration. ******** PGXC_CTL START *************** Current directory: /home/postgres/pgxc_ctl Initialize GTM master ERROR: target directory (/home/postgres/pgxl/data/nodes/gtm) exists and not empty. Skip GTM initilialization 1:1430554432:2017-07-11 23:31:14.737 PDT -FATAL: lock file "gtm.pid" already exists 2:1430554432:2017-07-11 23:31:14.737 PDT -HINT: Is another GTM (PID 2823) running in data directory "/home/postgres/pgxl/data/nodes/gtm"? LOCATION: CreateLockFile, main.c:2099 waiting for server to shut down.... done server stopped Done. Start GTM master server starting Initialize all the gtm proxies. Initializing gtm proxy gtm_pxy1. Initializing gtm proxy gtm_pxy2. The files belonging to this GTM system will be owned by user "postgres". This user must also own the server process. fixing permissions on existing directory /home/postgres/pgxl/data/nodes/gtm_pxy ... ok creating configuration files ... ok Success. The files belonging to this GTM system will be owned by user "postgres". This user must also own the server process. fixing permissions on existing directory /home/postgres/pgxl/data/nodes/gtm_pxy ... ok creating configuration files ... ok Success. Done. Starting all the gtm proxies. Starting gtm proxy gtm_pxy1. Starting gtm proxy gtm_pxy2. server starting server starting Done. Initialize all the coordinator masters. Initialize coordinator master coord1. Initialize coordinator master coord2. The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /home/postgres/pgxl/data/nodes/coord ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... creating cluster information ... ok syncing data to disk ... ok freezing database template0 ... ok freezing database template1 ... ok freezing database postgres ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /home/postgres/pgxl/data/nodes/coord ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... creating cluster information ... ok syncing data to disk ... ok freezing database template0 ... ok freezing database template1 ... ok freezing database postgres ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. Done. Starting coordinator master. Starting coordinator master coord1 Starting coordinator master coord2 2017-07-11 23:31:31.116 PDT [3650] LOG: listening on IPv4 address "0.0.0.0", port 5432 2017-07-11 23:31:31.116 PDT [3650] LOG: listening on IPv6 address "::", port 5432 2017-07-11 23:31:31.118 PDT [3650] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2017-07-11 23:31:31.126 PDT [3650] LOG: redirecting log output to logging collector process 2017-07-11 23:31:31.126 PDT [3650] HINT: Future log output will appear in directory "pg_log". 2017-07-11 23:31:31.122 PDT [3613] LOG: listening on IPv4 address "0.0.0.0", port 5432 2017-07-11 23:31:31.122 PDT [3613] LOG: listening on IPv6 address "::", port 5432 2017-07-11 23:31:31.124 PDT [3613] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2017-07-11 23:31:31.132 PDT [3613] LOG: redirecting log output to logging collector process 2017-07-11 23:31:31.132 PDT [3613] HINT: Future log output will appear in directory "pg_log". Done. Initialize all the datanode masters. Initialize the datanode master datanode1. Initialize the datanode master datanode2. The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /home/postgres/pgxl/data/nodes/dn_master ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... creating cluster information ... ok syncing data to disk ... ok freezing database template0 ... ok freezing database template1 ... ok freezing database postgres ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /home/postgres/pgxl/data/nodes/dn_master ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting dynamic shared memory implementation ... posix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... creating cluster information ... ok syncing data to disk ... ok freezing database template0 ... ok freezing database template1 ... ok freezing database postgres ... ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. Success. Done. Starting all the datanode masters. Starting datanode master datanode1. Starting datanode master datanode2. 2017-07-11 23:31:37.013 PDT [3995] LOG: listening on IPv4 address "0.0.0.0", port 5433 2017-07-11 23:31:37.013 PDT [3995] LOG: listening on IPv6 address "::", port 5433 2017-07-11 23:31:37.014 PDT [3995] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433" 2017-07-11 23:31:37.021 PDT [3995] LOG: redirecting log output to logging collector process 2017-07-11 23:31:37.021 PDT [3995] HINT: Future log output will appear in directory "pg_log". 2017-07-11 23:31:37.008 PDT [3958] LOG: listening on IPv4 address "0.0.0.0", port 5433 2017-07-11 23:31:37.008 PDT [3958] LOG: listening on IPv6 address "::", port 5433 2017-07-11 23:31:37.009 PDT [3958] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433" 2017-07-11 23:31:37.017 PDT [3958] LOG: redirecting log output to logging collector process 2017-07-11 23:31:37.017 PDT [3958] HINT: Future log output will appear in directory "pg_log". Done. ALTER NODE coord1 WITH (HOST='datanode1', PORT=5432); ALTER NODE CREATE NODE coord2 WITH (TYPE='coordinator', HOST='datanode2', PORT=5432); CREATE NODE CREATE NODE datanode1 WITH (TYPE='datanode', HOST='datanode1', PORT=5433, PRIMARY, PREFERRED); CREATE NODE CREATE NODE datanode2 WITH (TYPE='datanode', HOST='datanode2', PORT=5433); CREATE NODE SELECT pgxc_pool_reload(); pgxc_pool_reload ------------------ t (1 row) CREATE NODE coord1 WITH (TYPE='coordinator', HOST='datanode1', PORT=5432); CREATE NODE ALTER NODE coord2 WITH (HOST='datanode2', PORT=5432); ALTER NODE CREATE NODE datanode1 WITH (TYPE='datanode', HOST='datanode1', PORT=5433, PRIMARY); CREATE NODE CREATE NODE datanode2 WITH (TYPE='datanode', HOST='datanode2', PORT=5433, PREFERRED); CREATE NODE SELECT pgxc_pool_reload(); pgxc_pool_reload ------------------ t (1 row) Done. EXECUTE DIRECT ON (datanode1) 'CREATE NODE coord1 WITH (TYPE=''coordinator'', HOST=''datanode1'', PORT=5432)'; EXECUTE DIRECT EXECUTE DIRECT ON (datanode1) 'CREATE NODE coord2 WITH (TYPE=''coordinator'', HOST=''datanode2'', PORT=5432)'; EXECUTE DIRECT EXECUTE DIRECT ON (datanode1) 'ALTER NODE datanode1 WITH (TYPE=''datanode'', HOST=''datanode1'', PORT=5433, PRIMARY, PREFERRED)'; EXECUTE DIRECT EXECUTE DIRECT ON (datanode1) 'CREATE NODE datanode2 WITH (TYPE=''datanode'', HOST=''datanode2'', PORT=5433, PREFERRED)'; EXECUTE DIRECT EXECUTE DIRECT ON (datanode1) 'SELECT pgxc_pool_reload()'; pgxc_pool_reload ------------------ t (1 row) EXECUTE DIRECT ON (datanode2) 'CREATE NODE coord1 WITH (TYPE=''coordinator'', HOST=''datanode1'', PORT=5432)'; EXECUTE DIRECT EXECUTE DIRECT ON (datanode2) 'CREATE NODE coord2 WITH (TYPE=''coordinator'', HOST=''datanode2'', PORT=5432)'; EXECUTE DIRECT EXECUTE DIRECT ON (datanode2) 'CREATE NODE datanode1 WITH (TYPE=''datanode'', HOST=''datanode1'', PORT=5433, PRIMARY, PREFERRED)'; EXECUTE DIRECT EXECUTE DIRECT ON (datanode2) 'ALTER NODE datanode2 WITH (TYPE=''datanode'', HOST=''datanode2'', PORT=5433, PREFERRED)'; EXECUTE DIRECT EXECUTE DIRECT ON (datanode2) 'SELECT pgxc_pool_reload()'; pgxc_pool_reload ------------------ t (1 row) Done. 以后启动，直接执行如下命令： [postgres@pg1 pgxc_ctl]$ pgxc_ctl -c /home/postgres/pgxc_ctl/pgxc_ctl.conf start all 停止集群如下： [postgres@pg1 pgxc_ctl]$ pgxc_ctl -c /home/postgres/pgxc_ctl/pgxc_ctl.conf stop all 这几个主要命令暂时这么多，更多请从pgxc_ctl --help中获取更多信息。 五 Postgres-XL集群测试 5.1 插入数据 在datanode1节点，执行psql -p 5432进入数据库操作。 [postgres@localhost]$ psql -p 5432 psql (PGXL 10alpha1, based on PG 10beta1 (Postgres-XL 10alpha1)) Type "help" for help. postgres=# select * from pgxc_node; node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id -----------+-----------+-----------+-----------+----------------+------------------+------------- coord1 | C | 5432 | datanode1 | f | f | 1885696643 coord2 | C | 5432 | datanode2 | f | f | -1197102633 datanode1 | D | 5433 | datanode1 | t | t | 888802358 datanode2 | D | 5433 | datanode2 | f | f | -905831925 (4 rows) postgres=# create table test1(id it,name text); postgres=# insert into test1(id,name) select generate_series(1,8),'测试'; 5.2 查看数据分布 在datanode1节点上查看数据 [postgres@bogon ~]$ psql -p 5433 psql (PGXL 10alpha1, based on PG 10beta1 (Postgres-XL 10alpha1)) Type "help" for help. postgres=# select * from test1; id | name ----+------ 1 | 测试 2 | 测试 5 | 测试 6 | 测试 8 | 测试 (5 rows) 在datanode2节点上查看数据 postgres=# select * from test1; id | name ----+------ 3 | 测试 4 | 测试 7 | 测试 (3 rows) 注意：由于所有的数据节点组成了完整的数据视图，所以一个数据节点down机，整个pgxl都启动不了了，所以实际生产中，为了提高可用性，一定要配置数据节点的热备以便进行故障转移准备。 六 集群应用与管理 6.1 建表说明 REPLICATION表：各个datanode节点中，表的数据完全相同，也就是说，插入数据时，会分别在每个datanode节点插入相同数据。读数据时，只需要读任意一个datanode节点上的数据。 建表语法： postgres=# CREATE TABLE repltab (col1 int, col2 int) DISTRIBUTE BY REPLICATION; DISTRIBUTE ：会将插入的数据，按照拆分规则，分配到不同的datanode节点中存储，也就是sharding技术。每个datanode节点只保存了部分数据，通过coordinate节点可以查询完整的数据视图。 postgres=# CREATE TABLE disttab(col1 int, col2 int, col3 text) DISTRIBUTE BY HASH(col1); 模拟部分数据，插入测试数据： #任意登录一个coordinate节点进行建表操作 [postgres@gtm ~]$ psql -h datanode1 -p 5432 -U postgres postgres=# INSERT INTO disttab SELECT generate_series(1,100), generate_series(101, 200), 'foo'; INSERT 0 100 postgres=# INSERT INTO repltab SELECT generate_series(1,100), generate_series(101, 200); INSERT 0 100 查看数据分布结果： #DISTRIBUTE表分布结果 postgres=# SELECT xc_node_id, count(*) FROM disttab GROUP BY xc_node_id; xc_node_id | count ------------+------- 1148549230 | 42 -927910690 | 58 (2 rows) #REPLICATION表分布结果 postgres=# SELECT count(*) FROM repltab; count ------- 100 (1 row) 查看另一个datanode2中repltab表结果 [postgres@datanode2 pgxl9.5]$ psql -p 5433 psql (PGXL 9.5r1.3, based on PG 9.5.4 (Postgres-XL 9.5r1.3)) Type "help" for help. postgres=# SELECT count(*) FROM repltab; count ------- 100 (1 row) 结论：REPLICATION表中，datanode1,datanode2中表是全部数据，一模一样。而DISTRIBUTE表，数据散落近乎平均分配到了datanode1,datanode2节点中。 6.2新增datanode节点与数据重分布 6.2.1 新增datanode节点 在gtm集群管理节点上执行pgxc_ctl命令 [postgres@gtm ~]$ pgxc_ctl /bin/bash Installing pgxc_ctl_bash script as /home/postgres/pgxc_ctl/pgxc_ctl_bash. Installing pgxc_ctl_bash script as /home/postgres/pgxc_ctl/pgxc_ctl_bash. Reading configuration using /home/postgres/pgxc_ctl/pgxc_ctl_bash --home /home/postgres/pgxc_ctl --configuration /home/postgres/pgxc_ctl/pgxc_ctl.conf Finished reading configuration. ******** PGXC_CTL START *************** Current directory: /home/postgres/pgxc_ctl PGXC 在PGXC后面执行新增数据节点命令: Current directory: /home/postgres/pgxc_ctl # 在服务器datanode1上，新增一个master角色的datanode节点，名称是dn3 # 端口号暂定5430，pool master暂定6669 ，指定好数据目录位置，从两个节点升级到3个节点，之后要写3个none # none应该是datanodeSpecificExtraConfig或者datanodeSpecificExtraPgHba配置 PGXC add datanode master dn3 datanode1 5430 6669 /home/postgres/pgxl9.5/data/nodes/dn_master3 none none none 等待新增完成后，查询集群节点状态： [postgres@gtm ~]$ psql -h datanode1 -p 5432 -U postgres psql (PGXL 9.5r1.3, based on PG 9.5.4 (Postgres-XL 9.5r1.3)) Type "help" for help. postgres=# select * from pgxc_node; node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id -----------+-----------+-----------+-----------+----------------+------------------+------------- coord1 | C | 5432 | datanode1 | f | f | 1885696643 coord2 | C | 5432 | datanode2 | f | f | -1197102633 node1 | D | 5433 | datanode1 | f | t | 1148549230 node2 | D | 5433 | datanode2 | f | f | -927910690 dn3 | D | 5430 | datanode1 | f | f | -700122826 (5 rows) 可以发现节点新增完毕。 6.2.2 数据重分布 之前我们的DISTRIBUTE表分布在了node1,node2节点上，如下： postgres=# SELECT xc_node_id, count(*) FROM disttab GROUP BY xc_node_id; xc_node_id | count ------------+------- 1148549230 | 42 -927910690 | 58 (2 rows) 新增一个节点后，将sharding表数据重新分配到三个节点上，将repl表复制到新节点： # 重分布sharding表 postgres=# ALTER TABLE disttab ADD NODE (dn3); ALTER TABLE # 复制数据到新节点 postgres=# ALTER TABLE repltab ADD NODE (dn3); ALTER TABLE 查看新的数据分布： postgres=# SELECT xc_node_id, count(*) FROM disttab GROUP BY xc_node_id; xc_node_id | count ------------+------- -700122826 | 36 -927910690 | 32 1148549230 | 32 (3 rows) 登录dn3(新增的时候，放在了datanode1服务器上，端口5430)节点查看数据： [postgres@gtm ~]$ psql -h datanode1 -p 5430 -U postgres psql (PGXL 9.5r1.3, based on PG 9.5.4 (Postgres-XL 9.5r1.3)) Type "help" for help. postgres=# select count(*) from repltab; count ------- 100 (1 row) 很明显,通过 ALTER TABLE tt ADD NODE (dn)命令，可以将DISTRIBUTE表数据重新分布到新节点，重分布过程中会中断所有事务。可以将REPLICATION表数据复制到新节点。 6.2.3 从datanode节点中回收数据 postgres=# ALTER TABLE disttab DELETE NODE (dn3); ALTER TABLE postgres=# ALTER TABLE repltab DELETE NODE (dn3); ALTER TABLE 6.3 删除数据节点 Postgresql-XL并没有检查将被删除的datanode节点是否有replicated/distributed表的数据，为了数据安全，在删除之前需要检查下被删除节点上的数据，有数据的话，要回收掉分配到其他节点，然后才能安全删除。删除数据节点分为四步骤： 查询要删除节点dn3的oid postgres=# SELECT oid, * FROM pgxc_node; oid | node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id -------+-----------+-----------+-----------+-----------+----------------+------------------+------------- 11819 | coord1 | C | 5432 | datanode1 | f | f | 1885696643 16384 | coord2 | C | 5432 | datanode2 | f | f | -1197102633 16385 | node1 | D | 5433 | datanode1 | f | t | 1148549230 16386 | node2 | D | 5433 | datanode2 | f | f | -927910690 16397 | dn3 | D | 5430 | datanode1 | f | f | -700122826 (5 rows) 查询dn3对应的oid中是否有数据 testdb=# SELECT * FROM pgxc_class WHERE nodeoids::integer[] @> ARRAY[16397]; pcrelid | pclocatortype | pcattnum | pchashalgorithm | pchashbuckets | nodeoids ---------+---------------+----------+-----------------+---------------+------------------- 16388 | H | 1 | 1 | 4096 | 16397 16385 16386 16394 | R | 0 | 0 | 0 | 16397 16385 16386 (2 rows) 有数据的先回收数据 postgres=# ALTER TABLE disttab DELETE NODE (dn3); ALTER TABLE postgres=# ALTER TABLE repltab DELETE NODE (dn3); ALTER TABLE postgres=# SELECT * FROM pgxc_class WHERE nodeoids::integer[] @> ARRAY[16397]; pcrelid | pclocatortype | pcattnum | pchashalgorithm | pchashbuckets | nodeoids ---------+---------------+----------+-----------------+---------------+---------- (0 rows) 安全删除dn3 PGXC$ remove datanode master dn3 clean 6.4 coordinate节点管理 同datanode节点相似，列出语句不做测试了： # 新增coordinate PGXC$ add coordinator master coord3 localhost 30003 30013 $dataDirRoot/coord_master.3 none none none # 删除coordinate,clean选项可以将相应的数据目录也删除 PGXC$ remove coordinator master coord3 clean 6.5 故障切换 查看当前数据集群 postgres=# SELECT oid, * FROM pgxc_node; oid | node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id -------+-----------+-----------+-----------+-----------+----------------+------------------+------------- 11819 | coord1 | C | 5432 | datanode1 | f | f | 1885696643 16384 | coord2 | C | 5432 | datanode2 | f | f | -1197102633 16385 | node1 | D | 5433 | datanode1 | f | t | 1148549230 16386 | node2 | D | 5433 | datanode2 | f | f | -927910690 (4 rows) 模拟node1节点故障 PGXC$ stop -m immediate datanode master node1 Stopping datanode master node1. Done. 测试集群查询 postgres=# SELECT xc_node_id, count(*) FROM disttab GROUP BY xc_node_id; ERROR: Failed to get pooled connections postgres=# SELECT xc_node_id, * FROM disttab WHERE col1 = 3; xc_node_id | col1 | col2 | col3 ------------+------+------+------ -927910690 | 3 | 103 | foo (1 row) 测试发现，查询范围如果涉及到故障的node1节点，会报错，而查询的数据范围不在node1上的话，仍然可以查询。 手动切换node1的slave PGXC$ failover datanode node1 # 切换完成后，查询集群 postgres=# SELECT oid, * FROM pgxc_node; oid | node_name | node_type | node_port | node_host | nodeis_primary | nodeis_preferred | node_id -------+-----------+-----------+-----------+-----------+----------------+------------------+------------- 11819 | coord1 | C | 5432 | datanode1 | f | f | 1885696643 16384 | coord2 | C | 5432 | datanode2 | f | f | -1197102633 16386 | node2 | D | 5433 | datanode2 | f | f | -927910690 16385 | node1 | D | 15433 | datanode2 | f | t | 1148549230 (4 rows) 发现node1节点的ip和端口都已经替换为配置的slave了。 七 部署遇到的问题 在配置的时候一定要细心，避免端口号之类的配置冲突等错误。 错误一： postgres=# create table test1(id integer,name varchar(20)); LOG: failed to connect to node, connection string (host=192.168.0.125 port=1925 dbname=postgres user=postgres application_name=pgxc sslmode=disable options='-c remotetype=coordinator -c parentnode=coord1 -c DateStyle=iso,mdy -c timezone=prc -c geqo=on -c intervalstyle=postgres -c lc_monetary=C'), connection error (fe_sendauth: no password supplied ) WARNING: can not connect to node 16384 WARNING: Health map updated to reflect DOWN node (16384) LOG: Pooler could not open a connection to node 16384 LOG: failed to acquire connections STATEMENT: create table test1(id integer,name varchar(20)); ERROR: Failed to get pooled connections HINT: This may happen because one or more nodes are currently unreachable, either because of node or network failure. Its also possible that the target node may have hit the connection limit or the pooler is configured with low connections. Please check if all nodes are running fine and also review max_connections and max_pool_size configuration parameters STATEMENT: create table test1(id integer,name varchar(20)); 原因：这个是由于某些环境或配置出了问题，我的就是pg_hba.conf配置出了问题，Ipv4要改成 0:0:0:0/0 trust才行。 但这仅仅是一个问题，开发者搭建环境遇到这个错误，一定要检查如下: ** 各个机器的防火墙是否关闭？** ** 各个机器的SELINUX状态是否是disabled？** ** 各个机器的ssh免密登录是否成功？** ** 各个节点的pg_hba.conf,postgresql.conf是否配置为信任登录？是否有IP限制？** 超过某些节点的最大连接数？（对于我们测试环境来说，肯定不会是这个问题） 作者搭建pgxl是为地理大数据做技术预研的，使用postgis作为空间数据，欢迎postgis开发者参与交流。
自定义编译背景 OpenLayers3地图开发库功能非常强大，所以有个问题，就是它非常的大。体积大一向吃了很多亏，比如百度“适合移动端的js地图库”，很多人都会向你推荐Leaflet，原因是小巧。这正是冤枉啊，岂不知道ol3重新的，是要加强对移动端的支持啊，所有，没有mousemove之类的事件，定义为pointemove事件啊，这是为了pc与移动端都能使用啊，我们再也不能忽视ol3开发者们的良苦用心。其次由于很多webgis只使用了ol3部分功能，用不到其所有的库，那么遵照“按需加载”原则，是否可以只将项目中引用到的ol3依赖库重新编译出来？这当然是可以的，而且自定义编译一定是个瘦身的过程，那么究竟如何操作，请看以下步骤。 自定义编译步骤 安装nodejs windows安装后一路next，linux其他参考官网。 安装完成，在Node.js command prompt控制台中输入 node -v 打印nodejs安装版本号，确定环境具备。 下载OpenLayers3 在Node.js command prompt控制台中，比如选择在E盘下载： $ cd e: $ e: $ mkdir openlayers3 --新建一个文件夹 $ npm install openlayers --下载ol 等待npm下载完成。 选择依赖的包 仔细看下面的例子 var map = new ol.Map({ target: 'map', layers: [ new ol.layer.Tile({ source: new ol.source.OSM() }) ], controls: ol.control.defaults({ attributionOptions: { collapsible: false } }), view: new ol.View({ center: [0, 0], zoom: 4 }) }); 简例中使用了ol.Map,ol.View,ol.control.defaults,ol.layer.Tile,ol.source.OSM，那么我们就抽取使用的库，自定义一个json文件ol-custom.json，放到openlayers/node_modules/openlayers/build中，内容如下： { "exports": [ "ol.Map", "ol.View", "ol.control.defaults", "ol.layer.Tile", "ol.source.OSM" ], "compile": { "externs": [ "externs/oli.js", "externs/olx.js" ], "define": [ "goog.DEBUG=false" ], "extra_annotation_name": [ "api", "observable" ], "compilation_level": "ADVANCED", "manage_closure_dependencies": true } } 自定义打包 $ cd openlayers/node_modules/openlayers $ node tasks/build.js build/ol-custom.json build/ol-custom.js 在build中可以看到打包后的文件大小。 自定义参数说明 exports:程序中使用到的包。 externs:依赖的外部的一些包，oli.js，olx.js是OpenLayers 3 API的外部文件，在自定义编译时，这两个文件始终是需要的。 "externs/proj4js.js","externs/tilejson.js","externs/topojson.js"等等如果在代码开发中使用到了，需要在externs加进来。 define：设置一些常量为false，这样的话，编译的包会更小。比如默认情况下，ol3的三种渲染形式，以及所有的layer类型都会打包进来，但是，我们只需要一种渲染，一种图层类型，本定义便是为此而生。"compilation_level": "ADVANCED"模式，会将默认的没用上的渲染和图层类型移除，所以可以设置以下常量为false： "ol.ENABLE_DOM=false", "ol.ENABLE_WEBGL=false", "ol.ENABLE_PROJ4JS=false", "ol.ENABLE_IMAGE=false", "ol.ENABLE_VECTOR=false", 然后运行脚本编译。 其他编译选项:manage_closure_dependencies始终是需要的。
安装gdal的话，将shp转geojson会非常简单。本文因项目需要，以java-gdal讲述如何实现。ps：当然只要装有gdal就都可以实现功能。 ogr2ogr命令 在控制台执行如下语句： D:\PostgreSQL\9.5\bin> ogr2ogr -f GeoJson test.geojson test.shp gdal中的ogr2ogr工具非常好用，很多开源工具都集成了该命令，所以虽然作者并未安装gdal，但是由于postgresql的空间扩展postgis集成了ogr2ogr命令，所以我们直接使用即可。 参数说明： -f：输出格式，本文指定是GeoJson dst_datasource_name:转换目标数据源名称，本文要求将shp转储为名称为test.geojson src_datasource_name：转储源数据，本文要转的shp名称 java-gdal代码实现 java-gdal安装配置 下载地址：https://yunpan.cn/cB2tdzVMQ3nLB 访问密码 d03f 1 将java-gdal文件夹中所有的dll复制到Java\jre7\bin文件夹中。 2 将java-gdal文件夹中的gdal.jar在java工程中引用即可完成配置。 转换主要代码 package GIS; import org.gdal.*; import org.gdal.ogr.DataSource; import org.gdal.ogr.Driver; import org.gdal.ogr.ogr; import org.gdal.gdal.*; public class Test { public static void main(String[] args) { // 注册所有的驱动 ogr.RegisterAll(); // 为了支持中文路径，请添加下面这句代码 gdal.SetConfigOption("GDAL_FILENAME_IS_UTF8","YES"); // 为了使属性表字段支持中文，请添加下面这句 gdal.SetConfigOption("SHAPE_ENCODING",""); String strVectorFile = "D:\\data\\fibercable.shp"; //打开数据 DataSource ds = ogr.Open(strVectorFile,0); if (ds == null) { System.out.println("打开文件失败！" ); return; } System.out.println("打开文件成功！" ); Driver dv = ogr.GetDriverByName("GeoJSON"); if (dv == null) { System.out.println("打开驱动失败！" ); return; } System.out.println("打开驱动成功！" ); dv.CopyDataSource(ds, "D:\\data\\fibercable.geojson"); System.out.println("转换成功！" ); } }
一 gdal.open解析 gdal.open.png 阅读api可知，gdal.open方法用于创建和打开数据集dataset，如果以‘w’模式的话，会将更改从内存提交磁盘，否则，更改将被丢弃无效。 path:创建数据集的路径，如“e:/shp”。 mode："r","r+","w"三种模式。 drivers：数据集的驱动，字符或数组类型。 那么问题来了，如果我要创建一个shp，path和mode都很容易确定，而drivers是个什么鬼？查询api文档查不到，于是，使用以下代码，用于浏览下到底有哪些驱动： var gdal=require('gdal'); gdal.drivers.forEach(function(drive,i){ console.log(drive.description); }) 输出结果如下： VRT GTiff NITF RPFTOC ECRGTOC HFA SAR_CEOS CEOS JAXAPALSAR GFF ELAS AIG AAIGrid GRASSASCIIGrid SDTS DTED PNG JPEG MEM JDEM ESAT XPM BMP DIMAP AirSAR RS2 PCIDSK ILWIS SGI SRTMHGT Leveller Terragen ISIS3 ISIS2 PDS VICAR TIL ERS L1B FIT RMF RST INGR GSAG GSBG GS7BG COSAR TSX COASP R MAP KMLSUPEROVERLAY PNM DOQ1 DOQ2 GenBin PAux MFF MFF2 FujiBAS GSC FAST BT LAN CPG IDA NDF EIR DIPEx LCP GTX LOSLAS NTv2 CTable2 ACE2 SNODAS KRO ROI_PAC ENVI EHdr ISCE USGSDEM NWT_GRD NWT_GRC ADRG SRP BLX SAGA XYZ HF2 CTG E00GRID ZMap NGSGEOID IRIS ESRI Shapefile MapInfo File UK .NTF OGR_SDTS S57 DGN OGR_VRT REC Memory BNA CSV GML GPX KML GeoJSON OGR_GMT WAsP OpenFileGDB XPlane DXF Geoconcept GeoRSS GPSTrackMaker PGDUMP GPSBabel SUA OpenAir OGR_PDS HTF AeronavFAA EDIGEO SVG Idrisi SEGUKOOA SEGY SXF AVCBin AVCE00 注意，输出的驱动描述有ESRI Shapefile和OpenFileGDB，分别对应esri的shp和gdb。通过描述可知，新建一个shp的构造函数应该如下：var dataset=gdal.open('b.shp',"w",'ESRI Shapefile'); 二 创建shp 2.1 创建图层 dataset是由layers组成的，我们创建了一个shp的数据集，于是，在该数据集下创建layer，查看layers.create的api如下： create( name,srs,geomType,creation_options) 新增图层。 参数: name String类型，图层名称。 srs gdal.SpatialReference| Null，图层投影坐标系。 geomType Integer | FunctionGeometry，图层几何类型。 creation_options String[] | Object 特殊选项，可选。 返回值: gdal.Layer 示例: dataset.layers.create('layername', null, gdal.Point); 创建点图层示例如下：dataset.layers.create('b',null, gdal.Point); 2.2 图层坐标系 新建的图层，坐标系是null，我们希望建立的shp图层坐标系是4326的wgs84坐标，上文创建点图层如下： var ref = gdal.SpatialReference.fromEPSGA(4326); dataset.layers.create('b',ref, gdal.Point); 2.3 新增字段 var layer = dataset.layers.get(0); layer.fields.add(new gdal.FieldDefn('name', gdal.OFTString)); var feature = new gdal.Feature(layer); //给字段赋值 feature.fields.set('name', '测试'); //构造图形并赋值 var pt = new gdal.Point(118.5,32.1) feature.setGeometry(pt); layer.features.add(feature); layer.flush(); 运行之后，发现name值“测试”是中文乱码： result.png 理所当然，我们认为是字符集的问题。 2.4 设置shp字符集 //GBK的code page是CP936 gdal.config.set('SHAPE_ENCODING','CP936'); 本来设置属性是gbk，报错。非要设置gbk的codepage代码，涨姿势了。 三 结果 完整代码如下： var gdal=require('gdal'); //GBK的code page是CP936 gdal.config.set('SHAPE_ENCODING','CP936'); var dataset=gdal.open('b.shp',"w",'ESRI Shapefile'); var ref = gdal.SpatialReference.fromEPSGA(4326); dataset.layers.create('b',ref, gdal.Point); var layer = dataset.layers.get(0); layer.fields.add(new gdal.FieldDefn('name', gdal.OFTString)); var feature = new gdal.Feature(layer); //给字段赋值 feature.fields.set('name', '测试'); //构造图形并赋值 var pt = new gdal.Point(118.5,32.1) feature.setGeometry(pt); layer.features.add(feature); layer.flush(); 可视化结果如下： b.shp.png 后人看也许很简单，因为前人填了很多坑，无论坐标系还是模式还是中文乱码，是耗尽心血点滴测试出来的，且看且珍惜啊。
gdal用于读写矢量和栅格数据集，gdal常常使用c，c++做开发，也有部分其他语言扩展如py，node。但由于其他语言的官方例子不多，且存在很多问题，所以入门非常难。本文主要面向使用node语言做工具且对GIS软件有二次开发基础的同学，介绍使用gdal读写经典shp的入门教程。 一 node-gdal安装 Github地址：https://github.com/naturalatlas/node-gdal 使用nodejs安装：npm install gdal --save。 注意--save不能漏，笔者一开始未加，导致安装失败。 二 HelloWorld api文档地址：http://naturalatlas.github.io/node-gdal/classes/gdal.html 由于资料很少，一切唯有官网api可供参考。本篇是个高级的helloword，用于初步描述如何使用node-gdal。下面贴出node的测试脚本gdal.js代码说明如何操作： var gdal=require('gdal'); //r只读 r+读取修改增加 w可写 var dataset = gdal.open("test.shp",'w'); //获取数据集中第一个图层 var layer = dataset.layers.get(0); //添加一个字段叫name，字符型 layer.fields.add(new gdal.FieldDefn('name', gdal.OFTString)); //构造一个feature var feature = new gdal.Feature(layer); //给字段赋值 feature.fields.set('name', '测试'); //构造图形并赋值 var lineString = new gdal.LineString(); lineString.points.add(new gdal.Point(118.5,32.1)); lineString.points.add(new gdal.Point(118.6,32.4)); feature.setGeometry(lineString); //图层添加构造的feature layer.features.add(feature); //将对shp图层的更改写入磁盘文件 layer.flush(); console.log("图形json串:"+layer.features.next().getGeometry().toJSON()); console.log("图层中要素数量: " + layer.features.count()); console.log("图层字段名称: " + layer.fields.getNames()); console.log("边界范围: " + JSON.stringify(layer.getExtent())); console.log("图层坐标系: " + (layer.srs ? layer.srs.toWKT() : 'null')); node执行输出如下： E:\gdal>node gdal.js 图形json串:{ "type": "LineString", "coordinates": [ [ 118.5, 32.1 ], [ 118.6, 32.4 ] ] } 图层中要素数量: 1 图层字段名称: Id,name 边界范围: {"minX":0,"minY":0,"maxX":118.6,"maxY":32.4} 图层坐标系: GEOGCS["GCS_WGS_1984",DATUM["WGS_1984",SPHEROID["WGS_84",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]] 在ArcMap中打开shp，效果如下： 效果图.png 注意： 如上文：var dataset = gdal.open("test.shp",'w');，w模式代表可写，否则增加字段，增加Feature会失败，提示 当前数据源是只读数据。
发表了文章
2018-09-25
发表了文章
2018-09-17
发表了文章
2018-09-14
发表了文章
2016-06-24
发表了文章
2016-06-24
发表了文章
2016-07-13
发表了文章
2016-08-09
发表了文章
2016-08-27
发表了文章
2016-09-26
发表了文章
2016-09-26
发表了文章
2016-10-23
发表了文章
2016-10-23
发表了文章
2016-11-02
发表了文章
2016-12-07
发表了文章
2016-12-11
发表了文章
2016-12-21
发表了文章
2016-12-29
发表了文章
2017-01-03
发表了文章
2017-01-04
发表了文章
2017-01-19
