geo_tags queries, skwiki

SQL

USE skwiki_p;

-- Nearby locations
select @lat := 49.198333, @lon := 19.51666, @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 on small wiki
  
-- Coords by title list
-- select page_title, geo_tags.* from geo_tags, page where page_id = gt_page_id and page_title in ('Veľké_Borové', 'Srňacie', 'Annina_dolina', 'Prosečné_(vrch)', 'Svorad_(plošina)');

-- Latest geo-tagged pages
select page_title, rf.rev_timestamp as rev_first, rfa.actor_name as rev_first_user, geo_tags.* from geo_tags, page p, revision rf, actor rfa where rf.rev_page = p.page_id and rf.rev_parent_id = 0 and rfa.actor_id = rf.rev_actor and page_id = gt_page_id order by rf.rev_timestamp desc limit 100;

-- Basic index statistics
select count(*) from geo_tags;
select gt_primary, count(*) as count from geo_tags group by gt_primary order by count desc;
select gt_type, count(*) as count from geo_tags group by gt_type order by count desc;
select gt_country, count(*) as count from geo_tags group by gt_country order by count desc;
SQL

Checking query status...
