Clever Geek Handbook

PostGis。空間クエリでエラーを見つけるにはどうすればよいですか？

画像


こんにちは！Gems開発の開発者であるVictorです。私たちのチームは毎日、さまざまな複雑さと品質の空間データを処理しています。PostgresqlでPostgisとの空間交差操作を実行すると、次のエラーが発生しました。



XX000: GEOSIntersects: TopologyException: side location conflict at 10398.659 3844.9200000000001



エラーの原因となるリクエストは次のようになります。 



select q1.key,st_asGeoJson(geoloc)
    from usahalinsk.V_GEO_OOPT q1 
        where ST_Intersects(geoloc,
                ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":
                    [[[11165.15,2087.5],[11112,2066.6],[11127.6,2022.5],
                    [11122.6,2020.7],
                    [11122.25,2021.2],[11107.07,2015.7],
                    [11121,1947],[11123.48,1922.99],[11128.42,1874.4],
                    [11131.5,1875],[11140.96,1876.81],[11160.73,1880.59],
                    [11201.04,1888.3],[11194.2,1908],[11221.93,1916.57],
                    [11223.3,1917],[11165.15,2087.5]]]}'))


この問題の解決策は、データに関するレポートの作成を許可せず、サービスを提供する作業を遅くするため、ユーザーの作業をブロックします。私たちが開発しているシステムの多くのアクション、たとえば、土地区画のレイアウトの準備、土地区画の都市計画計画の準備などは、このような空間操作を使用します。



問題が正しくないジオメトリであると仮定しましょう。多くの場合、このエラーは、クエリに関係するオブジェクトに自己交差または重複ポイントがある場合、交差操作によって生成されます。これらのジオメトリエラーの例を以下に示します。 （ポリゴンの境界線はそれ自体と交差し、線には2つの同一の座標があります）





エラーの原因を特定するために独自の調査を実施しましたので、そのことをお伝えしたいと思います。

現在、Postgis2.4とPostgresql9.6を使用しています。まっすぐ練習に行きましょう。定数ジオメトリの妥当性をチェックして、すべてが正しく機能することを確認しましょう。





問題は、交差点を探しているテーブル（ビュー）usahalinsk.V_GEO_OOPTにあると想定できます。仮説を確認するために、これらのデータも確認します。





しかし、ここでもエラーは見つかりません。また、サンプルにはデータが一切含まれていませんでした。もしそうなら、Postgis st_makeValid関数を介して見つかったエントリを修正することにより、タスクは解決されます。



ただし、ビューにエラーはなく、要求は実行されません。彼の計画を見ることをお勧めします。





注：実際のモデルでは、ジオメトリに3つの列（ポリゴン、ライン、ポイント）を使用しますが、簡潔にするために、これをgeolocフィールドと呼びます。ジオメトリを格納してビューに表示します。



ビューusahalinsk.V_GEO_OOPTは、空間データusahalinsk.d_geometryを使用してテーブルから選択して作成され、ジオメトリを使用してフィールドに空間インデックスが作成されます。



これは、クエリを実行するときにインデックスが読み取られ、テーブルのどこかで選択に含まれず、インデックスに含まれていた無効な空間データがあることを意味します。テーブル全体に構築されます。



インデックスを削除してみましょう： 



DROP INDEX usahalinsk.d_geometry_cs1_all_sx;


そして、問題のある要求に応えてみましょう。





エラーなしで実行されました。問題がインデックスにあることを確認します。インデックスを返すことができますが、正しいジオメトリの条件があります。 



CREATE INDEX d_geometry_cs1_all_sx
  ON usahalinsk.d_geometry
  USING gist(geoloc)
  where st_isvalid(geoloc)=true;


実装を確認して計画を見てみましょう。





リクエストはエラーなしで実行され、プランのインデックスも使用されます。このソリューションの欠点は、挿入/更新の速度が低下する可能性があるためです。さらに、インデックスを再構築するときに条件がチェックされます。



この変更を元に戻し、インデックス内のどのオブジェクトがクエリの失敗を引き起こしているのかを調べてみましょう。 



DROP INDEX usahalinsk.d_geometry_cs1_all_sx;
 
CREATE INDEX d_geometry_cs1_all_sx
  ON usahalinsk.d_geometry
  USING gist
  (geoloc);


エラーの場所の座標があることを思い出してください。



XX000: GEOSIntersects: TopologyException: side location conflict at 10398.659 3844.9200000000001



ただし、データを検索する場合、またはエラーの理由を返すIsValidReason関数の結果として検索した場合、同様のものは見つかりません。 



select key,ST_IsValidReason(geoloc)
from usahalinsk.d_geometry 
    where st_isvalid(geoloc)!=true
        and ST_AsText(geoloc) like '%3844.9200000000001%';
        
select key,ST_IsValidReason(geoloc)
from usahalinsk.d_geometry 
    where st_isvalid(geoloc)!=true
        and ST_IsValidReason(geoloc) like '%3844.9200000000001%';


次のスクリプトを使用して、クエリに影響を与えるオブジェクトを見つけることができます。テーブル内の各オブジェクトをチェックし、目的の定数と交差させます。実行中に、例外をキャッチしてその内容を確認します。エラーに必要な座標が含まれている場合、これが問題のジオメトリです。 



do
$$
declare
    tKey bigint;
    rec record;
    error_text text;
    -- 
    error_info text:='GEOSIntersects: TopologyException: side location conflict at 10398.659 3844.9200000000001';
begin
    --    
    for rec in(select key from usahalinsk.d_geometry)
    loop
        begin
            select key into tKey
            from (select * from usahalinsk.d_geometry q1 
                                --   
                        where q1.key=rec.key
                            and ST_Intersects(geoloc,
                                    -- 
                                    ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[11165.15,2087.5],
                                    [11112,2066.6],[11127.6,2022.5],[11122.6,2020.7],
                                    [11122.25,2021.2],[11107.07,2015.7],[11121,1947],                                                    [11123.48,1922.99],[11128.42,1874.4],
[11131.5,1875],[11140.96,1876.81],                                    [11160.73,1880.59],[11201.04,1888.3],
[11194.2,1908],[11221.93,1916.57],[11223.3,1917],
                                    [11165.15,2087.5]]]}'))) geoQ;        
        exception when others then
                --    
              GET STACKED DIAGNOSTICS error_text = MESSAGE_TEXT;
            --    ,     
            if error_text=error_info then
                raise info '%',rec.key;    
            end if;                  
        end;
    end loop;
end$$;


その結果、簡単に修正できる3つのジオメトリキーが得られます。 



update usahalinsk.d_geometry 
set cs1_geometry_polygone=st_collectionextract(st_makevalid(geoloc),3)
where key in(
1000010001988961,
1000010001989399,
1000010004293508);


発生する質問に答えます。「理由を選択的に検索しないように、テーブル内のすべての誤ったジオメトリを修正することが不可能なのはなぜですか？」 ..。



実際のところ、空間データはさまざまなソース（Rosreestrを含む）からシステムに送られ、すべてのデータの修正（原則として歪みを伴う）を実行することはできません。必要なキーを受け取ったら、それらが表すデータと、それらを修正できるかどうかを分析します。



エラーの原因を見つけるという些細な作業は、最後に修正スクリプトを使用して全体的な調査に変えることができます。



問題のより複雑なバージョン：交差が定数ではなく別のテーブルで実行された場合はどうなりますか？または、最初のテーブルの参加オブジェクトのそれぞれを、2番目のテーブルのすべてのオブジェクトと交差させます。そして、例外をキャッチします。



ジオメトリの問題が発生する頻度と、空間データの品質をどのように確保しますか？


More articles:


All Articles