« MySQL | メイン

2008年11月21日

重複している行から条件に一致した最初の一行を抽出したい(DISTINCT ON)

ログデータの集計などで、条件に一致した最初の一行(最初に現れた一行)のみ抽出したい場合が多々ある。普通であれば、自己結合・サブクエリーなどを使用するが、PostgreSQLであれば、「DISTINCT ON」を使った方が直感的にすっきり書けるし、パフォーマンスも(たぶん)いい(未検証)。

PostgreSQL 8.3.4文書 : SELECT

---
DISTINCT ON ( expression [, ...] )は各行集合の中で、指定した式が等しいと評価した最初の行のみを保持します。

(中略)

例えば、次の例は各地点の最新の気象情報を取り出します。

SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;
---

想定しているのは、このような一部データが欠落しているロギングデータ。location項目が重複しており、11時の時点の金沢のデータがないというのがやっかいだ。

locationtimereport
札幌9時はれ
東京9時くもり
金沢9時くもり
福岡9時あめ
札幌10時はれ
東京10時くもり
金沢10時あめ
福岡10時あめ
札幌11時くもり
東京11時あめ
福岡11時くもり

と紹介してみたものの、なかなか知識の適応は難しい...。頭の片隅でも、ということで。

余談になるが、サブクエリーも使えない場合の代替案としては、

(1)特定の制約がある項目を含んだ一時テーブルを作成して、INSERTしながら重複を抑制...。

(2)プログラム側のハッシュ配列で、既に任意条件であるキーが存在するか検査をして、重複を抑制...。

(3)ハッシュすら使わず、for文でループ回してif文で重複検査...。

うーん、(3)はリアルで見たことがあり、もう愕然としたのをよく覚えている。

2008年9月25日

PostGISで日本測地系(Tokyo)から世界測地系(WGS84)への変換ができない場合

いきなりPostGISのお話。

PostGISを利用して、位置情報を使ったアプリケーションのプロトタイプを書いていたところ、どうも実際の位置と合わない。元データは日本測地系の緯度経度で、世界測地系へ変換(ST_Transform)してジオメトリカラムに格納していた。クエリーで投げていたのは世界測地系の緯度経度だったのだが、帰ってくる結果が400mほどずれる。地図で確認すると、まさに日本測地系と世界測地系の誤差。

プログラムを見直して、最終的に行き着いたのは実際に測地系を変換している(であろう)ST_Transform。

SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT(139.00 35.00)', 4301), 4326));

としてみると、

139.00 35.00

という値がしれーっと帰ってくる。「おめー、仕事しろYO!」。

解決にあたって参考になったのは、下記の2つのページ。意外に情報が少ない。

Re: PostGISへのデータ入力時の投影法変換|freeml
くりてぃかるひっと!はてな分室:PostGIS の測地系変換

UPDATE spatial_ref_sys 
SET proj4text='+proj=longlat +ellps=bessel towgs84=-147.54,507.26,680.47'
WHERE srid=4301;

とすれば、

SELECT proj4text FROM spatial_ref_sys WHERE srid=4301;

前:+proj=longlat +ellps=bessel +no_defs
後:+proj=longlat +ellps=bessel towgs84=-147.54,507.26,680.47

となる。検索結果にも満足。無事解決。

念のためバージョンも記述する。
PostgreSQL:8.1.11
PostGIS:1.3.3
proj:4.6.1
geos:3.0.0


↓GIS全般の解説、PostGISの取り扱い、日本の測地系に関する補足あり

入門Webマッピング―自分で作るオリジナルのデジタル地図
テイラー ミッチェル
オライリージャパン
売り上げランキング: 51738