USE svwiki_p;

DESCRIBE geo_tags;

/*

SELECT CONCAT('[[', page_title, ']]') AS Sida, gt_name, gt_type, gt_lat, gt_lon FROM geo_tags

INNER JOIN page ON gt_page_id = page_id

WHERE gt_country = 'AX' AND gt_type = 'mountain' 

ORDER BY page_title

LIMIT 1000;

*/

​

/*

SELECT gt_country, COUNT(*) AS antal FROM geo_tags

LEFT OUTER JOIN page on page_id = gt_page_id

WHERE page_namespace = 0 AND NOT (gt_type = 'country' or gt_type = 'satellite' or gt_type = 'adm1st' or gt_type = 'adm2nd' or gt_type = 'adm3rd' or gt_type = 'state' or gt_type LIKE 'city%' or gt_type = 'airport' or gt_type = 'mountain' or gt_type = 'isle' or gt_type = 'waterbody' or gt_type = 'forest' or gt_type = 'river' or gt_type = 'glacier' or gt_type = 'event' or gt_type = 'edu' or gt_type = 'pass' or gt_type = 'railwaystation' or gt_type = 'landmark')

GROUP BY gt_country

ORDER BY gt_country

LIMIT 1000;

/*

SELECT gt_type, gt_id, gt_name, gt_lat, gt_lon, gt_country, page_id, page_title FROM geo_tags

LEFT OUTER JOIN page on page_id = gt_page_id

WHERE page_namespace = 0 AND NOT (gt_type = 'country' or gt_type = 'satellite' or gt_type = 'adm1st' or gt_type = 'adm2nd' or gt_type = 'adm3rd' or gt_type = 'state' or gt_type LIKE 'city%' or gt_type = 'airport' or gt_type = 'mountain' or gt_type = 'isle' or gt_type = 'waterbody' or gt_type = 'forest' or gt_type = 'river' or gt_type = 'glacier' or gt_type = 'event' or gt_type = 'edu' or gt_type = 'pass' or gt_type = 'railwaystation' or gt_type = 'landmark')

ORDER BY page_title

LIMIT 10000;

/*

SELECT gt_type, page_namespace, page_title FROM geo_tags

LEFT OUTER JOIN page on page_id = gt_page_id

WHERE gt_type = '' or gt_type = '0' or gt_type = '1' or gt_type = '2' or gt_type = '3' or gt_type= '4' or gt_type = '5' or gt_type = '6' or gt_type = '7' or gt_type = '8' or gt_type= '9' 

ORDER BY page_title

LIMIT 40000;

*/

/*

SELECT gt_type, COUNT(*) AS Antal FROM geo_tags

LEFT OUTER JOIN page on page_id = gt_page_id

WHERE page_namespace = 0

GROUP BY gt_type

ORDER BY Antal DESC

*/

SELECT page_title, gt_type, gt_name, gt_lat, gt_lon, gt_country, gt_region FROM geo_tags

LEFT OUTER JOIN page ON page_id = gt_page_id

WHERE page_namespace = 0 AND gt_type = ''

ORDER BY page_title

LIMIT 50000

​

/*

SELECT IF(gt_region,CONCAT(gt_country,'-',gt_region),gt_country) AS Land_Region, COUNT(*) AS Antal FROM geo_tags

LEFT OUTER JOIN page on page_id = gt_page_id

WHERE page_namespace = 0

GROUP BY Land_Region

ORDER BY Land_Region

LIMIT 10000;

​

/*

SELECT CONCAT(100+FLOOR((gt_lat+90)/5),':',100+FLOOR((gt_lon+180)/5)) AS Ruta, COUNT(*) AS Antal FROM geo_tags

WHERE gt_globe = 'earth'

GROUP BY Ruta

ORDER BY Ruta

LIMIT 3000;

*/

​