postgis-skill

ドキュメント

• すべての create 文または CTE の前に、内容を説明するコメント -- を付けてください。
• 将来、何が要件だったのかを把握でき、無駄な試行錯誤を繰り返さずに済むよう、十分なコメントを記述してください。
• すべての機能について、その近くに最新かつ包括的なドキュメントを用意してください。

スタイル

• PostGIS 関数は、マニュアルに記載されている表記に従ってください（st_segmentize → ST_Segmentize）。
• 指示がない限り、SQL は小文字で記述してください。
• データベースやレイヤーの値は、可能な限り絶対値にしてください。「年齢」ではなく、「誕生日」や「建築日」を保存してください。
• コード内でタブとスペースを混在させないでください。
• 論理的なまとまりの間には空行を入れてください。
• コードは読みやすく、一貫した形式に整えてください。
• ジオメトリ列は geom、地理列は geog としてください。

インデックス作成

• アドホッククエリに使用する大きなテーブルを作成するときは、すべての列に対して BRIN インデックスを作成してください。
• 主キーを持つキャッシュテーブルがある場合は、より高速に検索できるよう、同じインデックスの including に値を追加するとよいでしょう。

デバッグ

• 開発者向けのエラーメッセージは、「500 Internal server error」だけで済ませず、より分かりやすくしてください。
• とんでもないフォールバック（緯度・経度を 0 にするなど）で仮の実装をしないでください。代わりに、データがない場合でも残りのコードが動作するようにし、ユーザーにその旨を知らせてください。
• SQL ファイルは冪等にしてください。テーブルが存在する場合は削除してから作成し、クエリをすばやく理解できるようコメントも追加してください。
• 新しいマイグレーションを作成するときは、反復しやすいように「up」と「down／rollback」の両方を作成してください。
• select postgis_full_version(); を実行して、すべてのアップグレードが正常に完了したか確認してください。
• ある SQL ファイルから別の SQL ファイルを実行しないでください。相対ファイルパスが絡み、すぐに混乱のもとになります。

ラスター

• ファイルシステム上で GDAL を使わないでください。データをデータベースにインポートし、そこで処理してください。

SQL の注意点

• sum(case when A then 1 else 0 end) は、単に count() filter (where A) と書けます。
• row_number() ... = 1 は、おそらく order by + limit 1（必要に応じて distinct on または lateral を使用）に置き換えられます。
• exists(select 1 from ...) は、単に exists(select from ...) と書けます。
• tags ->> 'key' = 'value' は、単に tags @> '{"key": "value"} と書けます。こちらのほうがインデックスで高速に処理できます。
• 順序付けしてテーブルを作成しても、order by なしでスキャン時にその順序が維持されるとは限りません。

PostGIS の注意点

• 要求されない限り、ジオメトリの typmod（geometry(multilinestring, 4326) など）は使用せず、代わりに通常の geometry または geography を使用してください。これにより、ST_Multi による冗長さや ST_SetSRID によるエラーをなくせます。
• ST_UnaryUnion(ST_Collect(geom)) は単に ST_Union(geom) です。
• ST_Buffer(geom, 0) の代わりに ST_MakeValid(geom) を使用してください。
• select min(ST_Distance(..)) の代わりに select ST_Distance() ... order by a <-> b limit 1 を使用すると、KNN GiST を利用できます。
• order by ST_Distance(c.geog, t.geog) の代わりに order by c.geog <-> t.geog を使用してください。
• ST_UnaryUnion を使っている場合、何か誤ったことをしている兆候です。
• ST_MakeValid を使っている場合、前の手順に誤りがある兆候です。
• ST_SetSRID を呼び出すときは特に注意してください。入力データの実際の投影法を確認し、入力時に正しく設定できるか（ST_GeomFromGeoJSON、EWKT 形式の SRID=4326;POINT(...、EWKB で対応できます）を確認してください。代わりに ST_Transform が必要かどうかも確認してください。
• 点とポリゴンの関係を調べる場合は、他のトポロジー述語より ST_Intersects を優先してください。
• ラスターやグリッドをたどって複雑なジオメトリを生成する場合は、ST_Simplify(geom, 0) が役立つことがあります。
• 予測可能なサイズの近傍を生成するには、k=2 と max_radius に距離を設定して ST_ClusterKMeans を使用してください。
• バイナリ表現には、SRID を保持するため ST_AsWKB ではなく ST_AsEWKB を使用してください。
• 投影法の選択： 入力または出力が経度・緯度で、座標値をユーザーに表示する場合は SRID=4326（2D 緯度経度）を使用します。 出力をウェブマップに表示する場合、ST_AsMVT を使用する場合、または短距離の 2D KNN リクエストを実行する場合は SRID=3857（2D 球面メルカトル）を使用します。 内部計算、見通し、クラスタリング、日付変更線をまたぐ平均化を行う場合は SRID=4978（3D XYZ）を使用します。3D 対応の操作のみを使用し、これに対して ST_Transform を呼び出す前に、2D CRS データに ST_Force3DZ を適用してください。
• ST_Hexagon／ST_HexagonGrid の代わりに h3 拡張機能を使用してください。
• データをバイナリ形式でダンプし、gzip 圧縮して転送することがわかっている場合は、精度が既知であれば ST_QuantizeCoordinates の使用を検討してください。