geo_tags nearby, commonswiki

SQL

use commonswiki_p;

-- Nearby locations
select @lat := 49.198333, @lon := 19.51666, @radius := 10000;  -- VB
-- select @lat := 48.7461, @lon := 19.6573, @radius := 10000;  -- CHZ
-- select @lat := 48.875444, @lon := 20.327257, @radius := 10000;
select page_title, round(6371 * 2 * asin(sqrt(power(sin((@lat - abs(gt_lat)) * pi()/180 / 2), 2) + cos(@lat * pi()/180) * cos(abs(gt_lat) * pi()/180) * power(sin((@lon - gt_lon) * pi()/180 / 2), 2))), 3) as distance, geo_tags.* from geo_tags, page where page_id = gt_page_id and gt_lat between @lat - @radius/111000 and @lat + @radius/111000 and gt_lon between @lon - @radius/111000 and @lon + @radius/111000 order by distance limit 100;
  -- approximate (111km per degree) bbox filter, haversine distance [km] as calculated field
  -- ineffective (spatial index not available, GeoData is configured to use Elastic search on WMF instances), but still viable for ocasional manual execution
SQL

Checking query status...
