Hive空間演算 -8-


Hive 0.11以降分析関数が増えてる
LAGとかも使えるみたいだし、だいぶ使い勝手は向上している模様

SELECT
 ROW_NUMBER() OVER (PARTITION BY addr.KEN ORDER BY ST_Distance(ST_Point(135,35), ST_Point(addr.lon, addr.lat)) ASC) rownum
 ,addr.KEN
 ,addr.SHIKUTYOSON 
 ,addr.OOAZATYOU
 ,addr.GAIKU
 ,ST_Distance(ST_Point(135,35), ST_Point(addr.lon, addr.lat)) dist
FROM
 addrwkt addr
ORDER BY
 rownum ASC
LIMIT 100
;
...
1       兵庫県  西脇市  上比延町        330     0.0012340927841963895
...
Time taken: 349.919 seconds, Fetched: 100 row(s)

集計して最近接取りたいときとかはこんな感じか。

SELECT
 alltab.*
FROM
(
SELECT
  hg.KEN
 ,hg.SHIKUTYOSON 
 ,hg.OOAZATYOU
 ,hg.GAIKU
 ,ST_Distance(ST_GeomFromWKB(hg.shp),ST_GeomFromWKB(pref.shp))
 ,pref.daihyo
 ,ROW_NUMBER() OVER (PARTITION BY pref.daihyo ORDER BY ST_Distance(ST_GeomFromWKB(hg.shp),ST_GeomFromWKB(pref.shp)) ASC) rownum
FROM
(
SELECT
  addr.KEN
 ,addr.SHIKUTYOSON 
 ,addr.OOAZATYOU
 ,addr.GAIKU
 ,ST_AsBinary(ST_Point(addr.lon, addr.lat)) shp
FROM
 ( 
    SELECT
      jpn71.shape shp
     ,jpn71.SIKUCHOSON 
    FROM
     jpn71
    WHERE
     jpn71.KEN LIKE  '%兵庫%'
  ) jpn
JOIN
 addrwkt addr
WHERE
 ST_Contains(jpn.shp, ST_Point(addr.lon, addr.lat))
) hg
JOIN
 (SELECT
     jpn71.SIKUCHOSON daihyo
     ,ST_AsBinary(ST_Centroid(jpn71.shape)) shp
    FROM
     jpn71
    WHERE
     jpn71.KEN LIKE  '%兵庫%'
  ) pref
ON
 (1=1)
) alltab
WHERE
 alltab.rownum = 1
;
...
兵庫県  神戸市西区      美賀多台四丁目  5       1.6317390312645973E-4   西区    1
兵庫県  高砂市  伊保崎四丁目    16      8.199398440527985E-5    高砂市  1
Time taken: 1006.153 seconds, Fetched: 49 row(s)

集計関数と関係ないが、点-線のST_DIST試しておく。
ST_Boundaryの最近接 (面の構成線との近接)

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/jpntest2' 
SELECT
  hg.KEN
 ,hg.SHIKUTYOSON 
 ,hg.OOAZATYOU
 ,hg.GAIKU
 ,ST_Distance(ST_GeomFromWKB(hg.shp),ST_GeomFromWKB(pref.shp))
 ,pref.daihyo
FROM
(
SELECT
  addr.KEN
 ,addr.SHIKUTYOSON 
 ,addr.OOAZATYOU
 ,addr.GAIKU
 ,ST_AsBinary(ST_Point(addr.lon, addr.lat)) shp
FROM
 ( 
    SELECT
      jpn71.shape shp
     ,jpn71.SIKUCHOSON 
    FROM
     jpn71
    WHERE
     jpn71.KEN LIKE  '%兵庫%'
  ) jpn
JOIN
 addrwkt addr
WHERE
 ST_Contains(jpn.shp, ST_Point(addr.lon, addr.lat))
) hg
JOIN
 (SELECT
     jpn71.SIKUCHOSON daihyo
     ,ST_AsBinary(ST_Boundary(jpn71.shape)) shp
    FROM
     jpn71
    WHERE
     jpn71.KEN LIKE  '%兵庫%'
  ) pref
ON
 (1=1)
;
Time taken: 1791.794 seconds
カテゴリー: 開発, 設計 タグ: , パーマリンク