ポリゴンが重なっているレコードにフラグを追加し取得するSQL

結果(SQL文)

WITH
    TargetRecords AS (
        SELECT *
        FROM t_geo_check_overlap
        WHERE id IN (1,4,9,11)
    ),
    OverlapRecords AS (
        SELECT t1.id, COUNT(*) AS counter
        FROM TargetRecords t1 , TargetRecords t2
        WHERE 
            ST_Intersects(t1.geom, t2.geom) = TRUE AND
            t1.id <> t2.id
        GROUP BY t1.id
    )
SELECT
    t1.*,
    CASE
        WHEN counter IS NOT NULL THEN '!!!重なり発生!!!'
        ELSE '重なりなし'
    END AS is_Intersects
FROM TargetRecords t1 LEFT JOIN OverlapRecords t2 ON t1.id = t2.id;

前提

テーブル定義

• ポリゴンデータを格納するテーブル
  

• カラム説明

  • id:主キー、 serial型
  • geom:ポリゴンデータを格納、geometory型
    ※PostGISのデータ型、ジオメトリタイプはPOLYGON、SRIDは4612を指定
  • description:ポリゴンの状態説明、text型

• DDL

      CREATE TABLE public.t_geo_check_overlap (
        id serial not null, 
        geom geometry(POLYGON,4612),
        desciption text, 
        primary key (id)
      );
  

  public.t_geo_check_overlapは{スキーマ名}.{作成テーブル名}

格納されているデータについて

前提に記載のデータを図示した際のイメージ

左側のポリゴンは1列目(左列)がID=1~4, 2列目(右列)がID=5~8
右側のポリゴンは上から、ID=9,10,11が振られている

結果(実行結果)

ポリゴンの指定をしない場合(全ポリゴンを対象とした場合)

• 結果
  

• 実行SQL

  WITH
      TargetRecords AS (
          SELECT *
          FROM t_geo_check_overlap
      ),
      OverlapRecords AS (
          SELECT t1.id, COUNT(*) AS counter
          FROM TargetRecords t1 , TargetRecords t2
          WHERE 
              ST_Intersects(t1.geom, t2.geom) = TRUE AND
              t1.id <> t2.id
          GROUP BY t1.id
      )
  SELECT
      t1.*,
      CASE
          WHEN counter IS NOT NULL THEN '!!!重なり発生!!!'
          ELSE '重なりなし'
      END AS is_Intersects
  FROM TargetRecords t1 LEFT JOIN OverlapRecords t2 ON t1.id = t2.id;
  

重なりが発生しているポリゴンを選択した場合(ID=1,2,5,6,9,10)

• 期待結果

  • ID=1,2,5,6,9,10のレコードのみが抽出されている
  • ID=9と10に重なりが存在するため(格納されているデータについて参照)、9と10のレコードに"!!!重なり発生!!!"が設定されている、その他のレコードは"重なりなし"になっている

• 結果
  

• 実行SQL

  WITH
      TargetRecords AS (
          SELECT *
          FROM t_geo_check_overlap
          WHERE id IN (1,2,5,6,9,10)
      ),
      OverlapRecords AS (
          SELECT t1.id, COUNT(*) AS counter
          FROM TargetRecords t1 , TargetRecords t2
          WHERE 
              ST_Intersects(t1.geom, t2.geom) = TRUE AND
              t1.id <> t2.id
          GROUP BY t1.id
      )
  SELECT
      t1.*,
      CASE
          WHEN counter IS NOT NULL THEN '!!!重なり発生!!!'
          ELSE '重なりなし'
      END AS is_Intersects
  FROM TargetRecords t1 LEFT JOIN OverlapRecords t2 ON t1.id = t2.id;
  

重なりが発生していないポリゴンを選択した場合(ID=1,2,5,6,9,11)

• 期待結果

  • ID=1,2,5,6,9,11のレコードのみが抽出されている
  • ID=9と11は重なりが存在しないため(格納されているデータについて参照)、9と11のレコードを含めすべてのレコードが"重なりなし"になっている

• 結果
  

• 実行SQL

  WITH
      TargetRecords AS (
          SELECT *
          FROM t_geo_check_overlap
          WHERE id IN (1,2,5,6,9,11)
      ),
      OverlapRecords AS (
          SELECT t1.id, COUNT(*) AS counter
          FROM TargetRecords t1 , TargetRecords t2
          WHERE 
              ST_Intersects(t1.geom, t2.geom) = TRUE AND
              t1.id <> t2.id
          GROUP BY t1.id
      )
  SELECT
      t1.*,
      CASE
          WHEN counter IS NOT NULL THEN '!!!重なり発生!!!'
          ELSE '重なりなし'
      END AS is_Intersects
  FROM TargetRecords t1 LEFT JOIN OverlapRecords t2 ON t1.id = t2.id;
  

まとめ

自分の知識と経験ではこれが限界でした
お手数ですが冗長な部分などありましたらご指摘いただけますと幸いです