日本国内のジオタグが付いたCommons上の新規ファイル

SQL

use commonswiki_p;
select 
  concat( "[[File:", replace( rc_title, "_", " " ), "]]" ) as "ファイルリンク",
  concat( "[[User:", rc_user_text, "]]" ) as "アップロード者",
  left( concat( date( rc_timestamp ), "T", time( rc_timestamp ) ), 19) as "アップロード日時 (UTC)",
  concat( "[http://www.google.co.jp/maps/@", gt_lat, ",", gt_lon, ",14z]" ) as "Googleマップ上の位置"
from geo_tags
inner join(
  select rc_user_text, rc_title, rc_cur_id, rc_timestamp
  from recentchanges
  where rc_log_type = "upload"
  limit 1
) as newfile on gt_page_id = rc_cur_id
where
  gt_globe = "earth" and 
  (
    ((gt_lon between 139.131 and 146.030) and (gt_lat between 20.879 and 45.737)) or
    ((gt_lon between 131.199 and 139.131) and (gt_lat between 18.229 and 38.668)) or
    ((gt_lon between 129.023 and 131.199) and (gt_lat between 21.657 and 34.913)) or
    ((gt_lon between 122.739 and 129.023) and (gt_lat between 21.821 and 33.073))
  )
order by rc_timestamp desc;
set @allfile = count( rc_timestamp );
select current_timestamp as "最終更新日";
SQL

Checking query status...
