.. _demogeosot1_label: Yukon中GeoSOT 编码的基本能力 ----------------------------- 示例数据:`demo-geosot.udbx <../../_static/files/demo-geosot.udbx>`__ ,包含二维点、线、面、三维模型(精模和BIM); SQL客户端:DBeaver或其他postgre客户端工具 可视化工具:DBeaver、QGIS或SuperMap iDesktopX;本文为展示三维场景效果,使用SuperMap iDesktopX。 二维线、面数据编码 ~~~~~~~~~~~~~~~~~~~ 对二维点、线、面数据的geometry列计算geosotgrid,并转换为geometry对象; 示例数据:building_point_demo、builing_line_demo、building_region_demo。 .. code:: sql ---1. 对二维点对象编码 ---1.1 创建表存储二维网格结果:building_point_grid22 create table building_point_grid22 (smid serial4 not null , smgeometry geometry(polygon, 4490), asText varchar(765), grid GeoSOTGrid,idGeo int4) ; ---1.2 为 building_point_demo 表的smgeometry列构造22层网格,并将格网转换成geometry对象,写入 with a as (select smid,ST_GeoSOTGrid(smgeometry,22) as grids from building_point_demo ) insert into building_point_grid22(smgeometry,asText,grid,idGeo) select ST_GeomFromGeoSOTGrid(unnest(grids)),ST_AsText(unnest(grids)),unnest(grids),smid from a; ---2. 对二维线对象编码 ---2.1 创建表存储二维网格结果:building_line2d_grid22 create table building_line2d_grid22 (smid serial4 not null , smgeometry geometry(polygon, 4490), asText varchar(765), grid GeoSOTGrid,idGeo int4) ; ---2.2 为 builing_line_demo 表的smgeometry列构造22层网格,并将格网转换成geometry对象,写入 with a as (select smid,ST_GeoSOTGrid(smgeometry,22) as grids from builing_line_demo ) insert into building_line2d_grid22(smgeometry,asText,grid,idGeo) select ST_GeomFromGeoSOTGrid(unnest(grids)),ST_AsText(unnest(grids)),unnest(grids),smid from a; ---3. 对二维面对象编码 ---3.1 创建表存储二维网格结果:building_region2d_grid22 create table building_region2d_grid22 (smid serial4 not null, smgeometry geometry(polygon, 4490), asText varchar(765), grid GeoSOTGrid,idGeo int4); ---3.2 为 building_region_demo 表的 smgeometry 列构造22层网格,并将格网转换成geometry对象,写入 with a as (select smid,ST_GeoSOTGrid(smgeometry,22) as grids from building_region_demo ) insert into building_region2d_grid22(smgeometry,asText,grid,idGeo) select ST_GeomFromGeoSOTGrid(unnest(grids)),ST_AsText(unnest(grids)),unnest(grids),smid from a; 新生成的building_point_grid22、building_line2d_grid22和building_region2d_grid22表格可视化效果如下: |geosot_PointLineRegion_22| 三维模型对象编码 ~~~~~~~~~~~~~~~~~~~ 精模数据编码 >>>>>>>>>>>>>> 取三维模型对象的包围盒,计算三维编码,并转换为geometry对象; 示例数据:building_1。 .. code:: sql ---4. 对三维模型对象编码 ---4.1 创建表存储三维网格:building_1_grid22 create table building_1_grid22 (smid serial4 not null , smgeometry geometry(multipolygonz, 4490),asText varchar(765),grid GeoSOTGrid,idGeo int4); ---4.2 为 building_1 表的 smgeometry 列构造22层网格,并将格网转换成geometry对象,写入 --- 注:st_boundary(geomodel)方法得到的Box3D,z方向起算点在地心,GeoSOT的z方向起算点在地表,所以需要进行平移处理 with a as (select smid,ST_GeoSOTGrid(st_translate(st_setsrid(st_boundary(smgeometry),4490), 0, 0, -6378137),22) as grids from building_1 ) insert into building_1_grid22(smgeometry,asText,grid,idGeo) select ST_GeomFromText(regexp_replace(st_astext( ST_GeomFromGeoSOTGrid(unnest(grids)) ),'POLYHEDRALSURFACE','MULTIPOLYGON')),ST_AsText(unnest(grids)),unnest(grids),smid from a; 新生成的building_1_grid22表格与原始三维模型叠加后可视化效果如下: |geosot_geomodel_22| BIM数据编码 >>>>>>>>>>>>>> 步骤同上。示例数据: 效果图如下: |geosot_geomodel_bim| 网格层级聚合 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 从已有的15层网格生成12层网格。 示例数据:bj_l。 .. code:: sql -- 5. 生成粗糙层网格 -- 5.1 创建表存储15层级网格数据:building_line_grid15 create table building_line_grid15 (smid serial4 not null, smgeometry geometry(polygon, 4490),asText varchar(765),grid GeoSOTGrid,idGeo int4); -- 5.1 为 bj_l表的smgeometry列构造15层网格,并将格网转换成geometry对象,写入 with a as (select smid,ST_GeoSOTGrid(smgeometry,15) as grids from bj_l ) insert into building_line_grid15(smgeometry,asText,grid,idGeo) select ST_GeomFromGeoSOTGrid(unnest(grids)),ST_AsText(unnest(grids)),unnest(grids),smid from a; -- 5.2 创建表存储12层级网格数据:building_line_grid12 create table building_line_grid12 (smid serial4 not null, smgeometry geometry(polygon, 4490),asText varchar(765),grid GeoSOTGrid,idGeo int4); -- 5.3 从步骤5.1的15层网格building_line_grid15生成12层网格 with a as (select ST_Aggregate(grid,12) grid , idGeo from building_line_grid15 ) insert into building_line_grid12 (smgeometry,asText,grid,idGeo) (select ST_GeomFromGeoSOTGrid(grid), ST_AsText(grid), grid,idGeo from a ); geosot编码的第12层为不规则网格,本例中新生成的12层网格可视化效果如下: |geosot_degenerate_12| 带洞的面对象编码 ~~~~~~~~~~~~~~~~~~~~~~~ 对带洞的面对象编码处理过程与普通geometry一致;示例数据:hole。 .. code:: sql -- 对 hole 里的对象进行编码,并转成geometry存在tb_18表中 CREATE SEQUENCE public.tb_18_id_seq INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE; CREATE TABLE public.tb_18 (id int4 NOT NULL DEFAULT nextval('tb_18_id_seq'::regclass) primary key, g geometry); insert into tb_18( g) select ST_GeomFromGeoSOTGrid(unnest(ST_GeoSOTGrid(smgeometry, 18) ) ) from hole h ; 网格化前后效果如下: |geosot-hole| 带飞地的面对象编码 ~~~~~~~~~~~~~~~~~~~~~~~ 对带飞地的面对象编码处理过程与普通geometry一致;示例数据:hebei。 .. code:: sql -- 对示例数据里的对象进行编码,并转成geometry存在tb_13表中 CREATE SEQUENCE public.tb_13_id_seq INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE; CREATE TABLE public.tb_13 (id int4 NOT NULL DEFAULT nextval('tb_13_id_seq'::regclass) primary key, g geometry); insert into tb_13( g) select ST_GeomFromGeoSOTGrid(unnest(ST_GeoSOTGrid(smgeometry, 13) ) ) from hebei ; 网格化前后效果如下: |geosot-hebei| .. |geosot_PointLineRegion_22| image:: /../_static/images/demo_geosot/1.geosot_PointLineRegion_22.gif :width: 500px .. |geosot_geomodel_22| image:: /../_static/images/demo_geosot/3.geosot_geomodel_22.gif :width: 500px .. |geosot_geomodel_bim| image:: /../_static/images/demo_geosot/3.geosot_geomodel_bim.gif :width: 500px .. |geosot_degenerate_12| image:: /../_static/images/demo_geosot/2.geosot_degenerate_12.gif :width: 500px .. |geosot-hole| image:: /../_static/images/demo_geosot/4.geosot-hole.png :width: 500px .. |geosot-hebei| image:: /../_static/images/demo_geosot/4.geosot-hebei.png :width: 500px