Scrape coordinates

Fork of Capture QuickStatement external links of {{Artwork}} templates by Jarekt

This query is marked as a draft This query has been published by Jarekt.

query which strips coordinates and camera heading from geohack URL, for files in [[Category:Pages_with_local_coordinates_and_missing_SDC_coordinates]], and formats the output in format of QuickStatements

SQL

use commonswiki_p;
SELECT concat("M",page_id) AS MID, 
	'P1259',
	REGEXP_REPLACE(el_to,'[^&]*&params=([^_]+)_N_([^_]*).*','@\\1/\\2') AS coordinates, 
    'P7787', 
    concat(replace(REGEXP_SUBSTR(el_to, '_heading:([^_&]*)'),'_heading:',''),'U28390') as heading, 
    '/* extracted from {{Location}} template */',
    el_to
FROM page
JOIN externallinks ON page_id = el_from AND  el_to LIKE '//tools.wmflabs.org/geohack/geohack.php%'
JOIN categorylinks cl1 ON cl1.cl_from = page_id AND cl1.cl_to='Pages_with_local_coordinates_and_missing_SDC_coordinates' 
JOIN categorylinks cl2 ON cl2.cl_from = page_id AND cl2.cl_to like 'Photographs_by_Jarek_Tuszyński' 
WHERE 
    page_namespace = 6 
limit 100

SQL

Checking query status...