use dewiki_p;

set @iso1:='US';

#set @iso2:='' or code

set @iso2:='AK';

set @offset:=360;

​

select  gt_country, (case when gt_lon<0 then gt_lon+@offset else gt_lon end) as gt_lon2 where gt_country='NZ' from geo_tags;

​

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

       gt_lat, (case when gt_lon<0 then gt_lon+@offset else gt_lon end) as gt_lon2, 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_lon2) cen_gt_lon,count(*) from geo_tags

        where 

            case 

                when @iso2=''

                then gt_country=@iso1

                else gt_country=@iso1 and gt_region=@iso2

                end) as zentrumdavon

        where

            case

                when @iso2=''

                then gt_country=@iso1 

                else gt_country=@iso1 and gt_region=@iso2

                end

       order by 1 desc limit 100;

#and gt_region is null