use dewiki_p;

set @iso1:='CH';

#set @iso2:='' or code

set @iso2:='ZH';

​

#describe geo_tags;

#select distinct gt_country, gt_region from geo_tags order by 1,2 asc limit 10;

​

select sqrt((gt_lat - cen_gt_lat)*(gt_lat - cen_gt_lat) + (gt_lon - cen_gt_lon)*(gt_lon - cen_gt_lon)) as distanzungefaehr, 

       gt_lat, gt_lon, gt_page_id, gt_name,

       (select page_title from page where page_id = gt_page_id) as title,

       gt_country,gt_region from geo_tags,

       (select avg(gt_lat) cen_gt_lat,avg(gt_lon) cen_gt_lon,count(*) from geo_tags

        where 

            case 

                when @iso2=''

                then gt_country=@iso1 and gt_region is null

                else gt_country=@iso1 and gt_region=@iso2

                end) as zentrumdavon

        where

            case

                when @iso2=''

                then gt_country=@iso1 and gt_region is null

                else gt_country=@iso1 and gt_region=@iso2

                end

       order by 1 desc limit 50;

​