select current_timestamp as "最終更新日";

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