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(REPLACE(REPLACE(REPLACE(el_to, 'https://tools.wmflabs.org/quickstatements/index_old.html#v1=',''),'%09','|'),'%0A','|'),'S4656|\'"https://commons.wikimedia.org/wiki/File:',page_title,'"')
#SELECT replace(replace(el_to, '//tools.wmflabs.org/geohack/geohack.php?pagename=$page&params=',''), '_N_','/')
SELECT REGEXP_REPLACE(el_to,'[^&]*&params=([^_]+)_N_([^_]*).*','\\1/\\2')
FROM externallinks
JOIN page ON page_id = el_from
JOIN categorylinks ON cl_from = page_id
WHERE 
    page_namespace = 6 
	AND cl_to='Media_with_locations' 
    AND  el_to LIKE '//tools.wmflabs.org/geohack/geohack.php%'
limit 100

SQL

Checking query status...