关于postgres数据库导入osm图层后，QGIS多边形显示不正常详细处理方案

客官°小女子只卖身不卖艺 2022-11-26 07:49 119阅读 0赞

# 关于postgres数据库导入osm图层后，QGIS多边形显示不正常详细处理方案 #

本文章只介绍SQL部分，如有其它需要请联系小编。

### SQL处理过程如下: ###

-- 1、如要在QGIS中显示查询结果需要在查询前建表，SQL如下，此时表university查询的是含大学字样的地点
    CREATE TABLE `university` AS SELECT * from planet_osm_polygon_copy WHERE "name" LIKE '%大学%' ;
    
    
    -- 2、将不规则的地图区域(SRID格式数据)转换为经纬度
    SELECT  name,st_astext( st_transform(st_geomfromtext(st_astext(way),900913),4326)) 
    from planet_osm_polygon WHERE "name"='广东海洋大学';
    
    
    -- 3、创建存放地名name和way字段的表
    create table test1 as 
    SELECT  name,way geom 
    from planet_osm_polygon WHERE "name"='广东海洋大学';
    
    
    -- 4、此时需要将步骤2中的经纬度取出，并去除明显不合规律的点
    -- 值得注意的是，POLYGON(())所有点所组成的多边形区域中，是需要闭环的，即，第一条经纬度=最后一条经纬度
    POLYGON((
    110.290382236835 21.1545987265939,
    110.290720003382 21.1582395806786,
    110.291253153503 21.1613698276182,
    110.291884938643 21.1608358514636,
    110.293444863133 21.1603755949769,
    110.297887481371 21.1588472885543,
    110.299842215429 21.1580001494574,
    110.301041466334 21.1571249404358,
    110.302191938718 21.1554221761739,
    110.304008422054 21.1498609331605,
    110.301342851111 21.1490138264302,
    110.300790027885 21.1483877300917,
    110.298677280169 21.1478905710176,
    110.297808519457 21.1477431153596,
    110.296658765725 21.1481304374931,
    110.296087796531 21.1480707850912,
    110.295381630886 21.1479094218724,
    110.29233912685 21.1478536233352,
    110.291391314394 21.149105985443,
    110.290950061926 21.1506515673226,
    110.290382236835 21.1545987265939))
    
    
    -- 5、回查，将test1表全转换为经纬度显示
    SELECT  name,st_astext(st_transform(st_geomfromtext(st_astext(geom),900913),4326)) 
    from test1;
    
    
    -- 6、将更改后的数据(由经纬度转换为SRID格式)插入临时表test1测试更改后的多边形区域是否显示正常
    insert into test1 values('广东海洋大学4',st_transform(st_geomfromtext('POLYGON((110.290382236835 21.1545987265939,110.290720003382 21.1582395806786,110.291253153503 21.1613698276182,110.291884938643 21.1608358514636,110.293444863133 21.1603755949769,110.297887481371 21.1588472885543,110.299842215429 21.1580001494574,110.301041466334 21.1571249404358,110.302191938718 21.1554221761739,110.304008422054 21.1498609331605,110.301342851111 21.1490138264302,110.300790027885 21.1483877300917,110.298677280169 21.1478905710176,110.297808519457 21.1477431153596,110.296658765725 21.1481304374931,110.296087796531 21.1480707850912,110.295381630886 21.1479094218724,110.29233912685 21.1478536233352,110.291391314394 21.149105985443,110.290950061926 21.1506515673226,110.290382236835 21.1545987265939))',4326),900913))
    
    
    ------ 插入 ------
    -- 7、确认无误后，取出有问题大学的osm_id，例如，广东海洋大学osm_id=-3961772109 
    SELECT * from planet_osm_polygon_ WHERE "name" LIKE '%广东海洋大学%' ;
    
    
    -- 8、备份osm库原表planet_osm_polygon为planet_osm_polygon_copy并插入原表数据
    SELECT * INTO planet_osm_polygon_copy FROM planet_osm_polygon;
    
    
    -- 9、在备份表中替换有问题的大学数据
    update planet_osm_polygon_copy set way='010300002031BF0D00010000001F0000009C703DFAA7A066418214AE4762404A417A8FC24DAAA06641A71E852BB4404A4143B81E6DB9A06641E3A3707D86414A4105D7A3001DA16641E3A3707D86414A41550AD7533AA16641626666E601414A41FB51B8CE48A16641AF1E856BA6404A41120000D84DA166417E3D0A7798404A41623D0A5F4CA16641AD1E856B73404A41618FC2F558A166410FD7A3F037404A41570AD76B5AA166416B3D0A77F43F4A418266662E46A1664139333353B33F4A41CA1E85033BA1664160B81E05AA3F4A418D14AE8F1FA16641353333B3BB3F4A41C047E1F20CA16641353333B3BB3F4A41A647E16200A166417FC2F528A83F4A412CE17A74FFA0664135E17A94443F4A419047E1C208A1664175EB5178103F4A41B51E85EBFFA06641B91E85ABCA3E4A415F3D0A2700A16641FCFFFF1F743E4A41969999C1FEA0664184EB5158133E4A41365C8F8AEDA06641E851B81EEE3D4A41B8CCCC64DBA06641265C8F02FD3D4A413E333323D8A06641F551B81E313E4A41100000C0D8A06641C6F528FC5F3E4A41490AD7C3D9A066413E3333D3AD3E4A41EF285CFFD9A0664145B81E656F3F4A413E333343DBA06641716666E6FA3F4A415EE17A0CC8A06641F151B8DEFA3F4A415EB81EC5ADA0664110D7A3D0FA3F4A415FE17A24A9A0664100295CCFFA3F4A419C703DFAA7A066418214AE4762404A41' 
    WHERE osm_id=-3882289431 ;
    
    
    -- 10、新建表并连接QGIS观察更改后的大学多边形显示是否正常
    CREATE TABLE `university_test` AS SELECT * from planet_osm_polygon_copy WHERE "name" LIKE '%大学%';