Replicas browser and optimizer
This query is marked as a draft
This query has been published
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;
By running queries you agree to the
and you irrevocably agree to release your SQL under
All SQL code is licensed under
Checking query status...
Report a bug!
Wikimedia Cloud VPS
, written in