SQL queries for finding CLC meadows for cleanup

clc_cleanup.sql

CREATE TEMPORARY TABLE clc_ways AS SELECT id AS way_id,nodes
    FROM ways w
      LEFT JOIN relation_members rm ON (w.id = rm.member_id AND rm.member_type='W')
    WHERE tags ? 'CLC:id' 
      AND tags @> hstore('landuse','meadow') 
      AND version=1
      AND rm.relation_id IS NULL;
CREATE INDEX ON clc_ways (way_id) WITH (fillfactor=100);
ANALYZE clc_ways;

SELECT potential_nodes.node_id 
  FROM (SELECT way_id,unnest(nodes) AS node_id FROM clc_ways) AS potential_nodes
    LEFT JOIN 
    (
      SELECT all_nodes.node_id
        FROM (SELECT way_id,unnest(nodes) AS node_id FROM clc_ways) AS all_nodes
          JOIN way_nodes wn ON (all_nodes.node_id = wn.node_id) -- find parent ways
          LEFT JOIN clc_ways cw ON (wn.way_id = cw.way_id) -- Match against the original list
        WHERE
          cw.way_id IS NULL -- We only want nodes that are used by other ways
    ) AS used_nodes ON (used_nodes.node_id = potential_nodes.node_id) -- Match up against nodes used by other ways
    LEFT JOIN
    (
      SELECT all_nodes.node_id
        FROM (SELECT way_id,unnest(nodes) AS node_id FROM clc_ways) AS all_nodes
          JOIN relation_members rm ON (all_nodes.node_id = rm.member_id AND rm.member_type='N') -- used by any relation
    ) AS relation_used_nodes ON (relation_used_nodes.node_id = potential_nodes.node_id) -- Match up against the original list
  WHERE
    used_nodes.node_id IS NULL -- We only want nodes not used by other ways
    AND relation_used_nodes IS NULL
  LIMIT 5;