[OSGeoJapan-discuss] 【教えてください】大量のライン群の一部にフラグを立てたい

2020年 7月 1日 (水) 03:52:55 PDT

しまだ様

補足させていただきます。

その１
> ポリゴンに含まれるライン群にフラグ（あるカラムに999を入れる）

読み飛ばしておりましたが
既にフラグ用のカラムがあるとのことですので
---------------------------
UPDATE lines 
  SET あるカラム = 999 
  WHERE (SELECT ST_Contains(polygon.geom, lines.geom) FROM polygon) = 1;
---------------------------
でよいですね。

その２
> 空間インデックスを作成して走らせてみたら、わずか４秒で終了しました。

恐らくspatialite_gui上でSELECT文を実行した結果だと思います。
spatialite_guiはSELECT結果を勝手に500件でページングするため
結果が500件以上になる場合に全体の処理時間を測ることができません。


On Wed, 01 Jul 2020 13:17:50 +0900
尾崎　剛 <t-ozaki ＠ kanal.co.jp> wrote:

> しまだ様
> 
> まずは少し気になった点がありますので
> ご指摘させていただきます。
> 
> > ST_contains(lines.geom, polygon.geom)=1;
> 
> ST_containsは第1引数に第2引数が内包されているかどうかのフラグを数値で返します。
> ST_contains(polygon.geom, lines.geom)=1
> にしないと期待している動きにならないと思います。
> 
> 次に本題ですが
> linesテーブルにフラグ用カラム（例えばflag）を追加して
> 内包されているラインにフラグを付ける方法をとってはいかがでしょうか？
> 
> -- カラム追加
> ALTER TABLE lines ADD COLUMN flag integer;
> -- フラグ付与
> UPDATE lines SET flag = (SELECT ST_contains(polygon.geometry, lines.geometry) FROM polygon);
> 
> 参考になれば幸いです。
> 
> 
> 
> On Tue, 30 Jun 2020 17:36:30 +0900
> shimada yasu <yas.shimada35 ＠ gmail.com> wrote:
> 
> > 嘉山様
> > 
> > ありがとうございました。
> > 
> > 当初、素人でも試行錯誤でなんとかなるかなと思っていましたが、
> > お話を聞くと、やはりきちんとSQLを勉強すべきと感じました。
> > 
> > もう少し時間をかけてみます。ありがとうございました。
> > 
> > しまだ
> > 
> > 2020年6月30日(火) 16:59 Yoichi Kayama <yoichi.kayama ＠ gmail.com>:
> > >
> > > 嘉山です
> > >
> > > ジオメトリ付きのテーブルの場合メインキーになるユニークid が必要ですが、このSQLの
> > > 場合それがどうなるかちょいと心配です。
> > >
> > > lines,polygon の両方のテーブルからクエリかけたら掛け算になってlines 側のレコードの
> > > idが使われた場合でも同じidのレコードが複数できちゃって単純なクエリの場合は大丈夫
> > > だけどテーブルつくる話になっちゃうとぶつかっちゃってとかいうことはないでしょうかね。
> > >
> > > この場合クエリ結果がジオメトリつきテーブルとして自動的に認識されるかどうかがちょっと
> > > わからないとこですが（ジオメトリつきのテーブルとジオメトリカラムを管理する仕組み
> > > がSpatialiteとかPostGISとかGeoPackageにはあるのでこのクエリでそのあたりが自動的に
> > > 動いてくれるかどうかはわかりません。うまくいかない場合は後で手動でそのあたりの整合性
> > > をつくることはできると思います）
> > >
> > >
> > > 2020年6月30日(火) 16:05 shimada yasu <yas.shimada35 ＠ gmail.com>:
> > >>
> > >> 空間インデックスを作成して走らせてみたら、わずか４秒で終了しました。
> > >>
> > >> おお、これはすごい・・・と思ってcreate table kekka asをつけてみたら
> > >> 再び激遅になりました（いつ終わるか不明）
> > >>
> > >> create tableなので「書き込み速度」に依存しているのですかね。。。
> > >>
> > >> create table kekka as
> > >> select
> > >> *
> > >> from
> > >> lines, polygon
> > >> where
> > >> ST_contains(lines.geom, polygon.geom)=1;
> > >>
> > >> しまだ
> > >>
> > >> 2020年6月30日(火) 13:26 shimada yasu <yas.shimada35 ＠ gmail.com>:
> > >> >
> > >> > 嘉山様
> > >> >
> > >> > さっそくありがとうございました。
> > >> >
> > >> > 空間インデックスは作っていませんでした・・・。
> > >> >
> > >> > 試してみます。
> > >> >
> > >> > しまだ
> > >> >
> > >> > 2020年6月30日(火) 13:19 Yoichi Kayama <yoichi.kayama ＠ gmail.com>:
> > >> > >
> > >> > > 嘉山です
> > >> > >
> > >> > > それぞれのレイヤで空間インデックスは作成されていますか？
> > >> > > それがあるかないかではえらく処理速度は違いますが。
> > >> > >
> > >> > > 2020年6月30日(火) 13:12 shimada yasu <yas.shimada35 ＠ gmail.com>:
> > >> > >>
> > >> > >> みなさま
> > >> > >>
> > >> > >> いつも教えてもらってばかりで恐縮ですが、ご存じでしたらご指導ください。
> > >> > >>
> > >> > >> 約29万のライン群があり、この一部にポリゴン（ひとつ）がかかっています。
> > >> > >> ポリゴンに含まれるライン群にフラグ（あるカラムに999を入れる）を
> > >> > >> 立てようとしているのですが、時間ばかりかかり困っています。
> > >> > >>
> > >> > >> DB処理が速いと聞いたので、不慣れなspatialiteでやってみました。
> > >> > >>
> > >> > >> が、それでも4時間かかりました。
> > >> > >>
> > >> > >> もう少し効率的な手法があれば、教えて頂けないでしょうか・・？
> > >> > >>
> > >> > >> select
> > >> > >> *
> > >> > >> from
> > >> > >> lines, polygon
> > >> > >> where
> > >> > >> ST_contains(lines.geom, polygon.geom)=1;
> > >> > >>
> > >> > >>
> > >> > >> --
> > >> > >> 島田泰夫/一般財団法人日本気象協会/環境・エネルギー事業部/環境アセスメント事業課/〒170-6055豊島区東池袋3-1-1サンシャイン60-55F
> > >> > >> /TEL:03-5958-8160 FAX:03-5958-8157 /shimada ＠ jwa.or.jp
> > >> > >> /携帯:080-8018-1567
> > >> > >> _______________________________________________
> > >> > >> OSGeoJapan-discuss mailing list
> > >> > >> OSGeoJapan-discuss ＠ lists.osgeo.org
> > >> > >> https://lists.osgeo.org/mailman/listinfo/osgeojapan-discuss
> > >> >
> > >> >
> > >> >
> > >> > --
> > >> > 島田泰夫/一般財団法人日本気象協会/環境・エネルギー事業部/環境アセスメント事業課/〒170-6055豊島区東池袋3-1-1サンシャイン60-55F
> > >> > /TEL:03-5958-8160 FAX:03-5958-8157 /shimada ＠ jwa.or.jp
> > >> > /携帯:080-8018-1567
> > >>
> > >>
> > >>
> > >> --
> > >> 島田泰夫/一般財団法人日本気象協会/環境・エネルギー事業部/環境アセスメント事業課/〒170-6055豊島区東池袋3-1-1サンシャイン60-55F
> > >> /TEL:03-5958-8160 FAX:03-5958-8157 /shimada ＠ jwa.or.jp
> > >> /携帯:080-8018-1567
> > 
> > 
> > 
> > -- 
> > 島田泰夫/一般財団法人日本気象協会/環境・エネルギー事業部/環境アセスメント事業課/〒170-6055豊島区東池袋3-1-1サンシャイン60-55F
> > /TEL:03-5958-8160 FAX:03-5958-8157 /shimada ＠ jwa.or.jp
> > /携帯:080-8018-1567
> > _______________________________________________
> > OSGeoJapan-discuss mailing list
> > OSGeoJapan-discuss ＠ lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/osgeojapan-discuss
> 
> _______________________________________________
> OSGeoJapan-discuss mailing list
> OSGeoJapan-discuss ＠ lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/osgeojapan-discuss