首页 文章

Postgres - 检测2个表中的重叠行

提问于
浏览 次
0

问题：我有2张 table ，上面有一个国家（波兰）的自行车和徒步旅行路线 .

Table bicycle_merge

way      | route  | name        |network    |osmc_color| state  
geometry | text   | text        |text       |text      | text
----------------------------------------------------------------
{geo1}   |bicycle |szlak1       |ncn        |blue      |proposed
{geo2}   |bicycle |szlak2       |lcn        |red       |null
{geo3}   |bicycle |szlak3       |ncn        |green     |proposed
{geo4}   |bicycle |szlak4       |rcn        |blue      |proposed
{geo5}   |bicycle |szlak5       |lcn        |blue      |null
{geo6}   |bicycle |szlak6       |ncn        |yellow    |proposed
....and so on

Table hiking_merge

way      | route  | name        |network    |osmc_color| state  
geometry | text   | text        |text       |text      | text
----------------------------------------------------------------
{geo1}   |hiking  |szlak1       |ncn        |blue      |proposed
{geo2}   |hiking  |szlak2       |ncn        |red       |null
{geo3}   |hiking  |szlak3       |ncn        |green     |proposed
{geo4}   |hiking  |szlak4       |ncn        |blue      |proposed
{geo5}   |hiking  |szlak5       |ncn        |blue      |null
{geo6}   |hiking  |szlak6       |ncn        |yellow    |proposed
...and so on

，等是每条路径的独特几何形状 .

每条小径都有独特的几何形状，但有时自行车和远足路线重叠 . 下图显示了QGIS中的示例：我想删除重叠的部分，因为我已将它们放在不同的数据库中 .

编辑：我尝试过Julia Leder建议的解决方案：

create table intersections as
(select st_intersection(b.way, h.way) as overlapping_section
from bicycle_merge b, hiking_merge h
where st_intersects(g.way, h.way));

并非所有重叠线都被检测到 . 我不确定为什么？这是一张显示问题的图片：

sql postgresql overlap

3 回答

  • 0

    你在找这个：

    (SELECT way,route,name,color,description,network FROM bicycle_merge
UNION
SELECT way,route,name,color,description,network FROM hiking_merge)
EXCEPT
(SELECT way,route,name,color,description,network FROM bicycle_merge
INTERSECT
SELECT way,route,name,color,description,network FROM hiking_merge);

    这是MS SQL语法 . 对于Oracle，请写入 MINUS 而不是 EXCEPT .

    回复于
  • 0
    select *
from
    (
        select *
        from bicycle_merge bm
        where not exists (
            select 1
            from hiking_merge
            where geometry = bm.geometry
        )
    )
    union
    (
        select *
        from hicking_merge hm
        where not exists (
            select 1
            from bicycle_merge
            where geometry = hm.geometry
        )
    )
    回复于
  • 0

    使用PostGIS功能可以相对轻松地完成此操作 . 您需要找到两个几何之间的重叠部分，这可以通过以下方式完成

    ST_Intersection(a.geometry, b.geometry)

    然后你需要从原始几何中减去骑自行车或远足几何中的那个部分，这将完成

    ST_Difference(a.geometry, b.geometry)

    把这一切放在一起：

    create table intersections as
(select st_intersection(st_buffer(b.way,10), st_buffer(h.way,10)) as overlapping_section
from bicycle_merge b, hiking_merge h
where st_intersects(st_buffer(g.way, 10), st_buffer(h.way,10)));


update bicycle_merge b 
set way = st_difference(b.way, overlapping_section)
from intersections where st_intersects(b.way, overlapping_section);

update hiking_merge h 
set way = st_difference(h.way, overlapping_section)
from intersections where st_intersects(h.way, overlapping_section);

    这可能需要根据几何类型以及您希望如何存储它们进行一定程度的调整，但上述内容应该可以为您提供 .

    st_buffer函数的第二个参数取缓冲区的半径 . 此数字的单位取决于几何的srid . 它们可以是英尺，米或度，具体取决于您可以检查的几何体的SRID

    select st_srid(way) from biking_merge limit 0;
    回复于

相关问题