Ideas for future validations

[nicolas-raoul]

A few ideas for more validation.
Low urgency so no need to integrate them into ValidationReport.java now, this can be a memo for one-time SQL queries for now.

Global checks

Find listings with a wikipedia value but no wikidata value:

SELECT article, title FROM wikivoyage_listings WHERE wikipedia != "" AND wikidata == "";

Find listings with duplicate coordinates:

SELECT
    article, title, latitude, longitude, COUNT(*)
FROM
    wikivoyage_listings
GROUP BY
    latitude, longitude
HAVING 
    COUNT(*) > 1
ORDER BY
    COUNT(*) DESC;

Listings with same name in same article:

SELECT
    article, title, COUNT(*)
FROM
    wikivoyage_listings
WHERE
    title != ""
GROUP BY
    article, title
HAVING 
    COUNT(*) > 1
ORDER BY
    COUNT(*) DESC;

URL validation

?utm_source= is always unnecessary in URLs, it is a tracking code.