Alaska/TIGER Counties
Proposed Import
Pnorman is proposing to import tl_2012_us_county from TIGER to replace the current county/borough/CPD data.
This data will be merged with the border data for admin_level=2 and admin_level=4 (Alaska/BC and Alaska/Yukon borders).
This data is strictly speaking not counties, but boroughs or CPDs which are county equivalents.
Copyright
TIGER is public domain
Accuracy
As the existing data is unedited old TIGER this data should be more accurate as it is more recent TIGER data.
Alaska introduced new boroughs in 2007 and 2008 which are not reflected in the existing data.
Existing contributions
While editing the admin_level=2 border Pnorman found that the data was largely unedited from the original import, therefore there are no existing contributions to preserve. When importing the existing data will be checked to see if it has been modified.
Tagging
ways
admin_level=6 (or other values on the Alaska border)
boundary=administrative
Changeset
Relations
admin_level=6
boundary=administrative
name=NAME
official_name=NAMELSAD
nist:fips_code=STATEFP + COUNTYFP
nist:state_fips=STATEFP
type=boundary
NAME is what an area would commonly be called (e.g. Aleutians West). NAMELSAD is the full legal name (e.g. Aleutians West Census Area).
STATEFP is 02 for all of Alaska.
Where the county border is the same as the city border another relation will be created with the same tags except for admin_level=8 and nist:fips_code=*.
The other information from the shapefiles is either irrelevant metadata, duplication of geodata or blank.
Discussion
https://lists.openstreetmap.org/pipermail/talk-us/2012-November/009566.html
https://lists.openstreetmap.org/pipermail/imports/2012-November/001590.html
Concerns were expressed that the data be integrated with existing boundaries which is planned. There was discussion about the correct state border but that is not part of this import.
SQL used
A significant portion of the import is removing old county borders. To do this a pgsnapshot db was used with JOSM's download object command. Mistakes in SQL would result in conflicts when the objects were deleted, not in damaged data.
The actual SQL used was slightly different.
CREATE TEMP TABLE admin_ways (id INTEGER);
INSERT INTO admin_ways
SELECT DISTINCT ON (ways.id) ways.id FROM relations
LEFT JOIN relation_members ON (relations.id = relation_members.relation_id) AND relation_members.member_type = 'W'
LEFT JOIN ways ON relation_members.member_id = ways.id
WHERE relations.tags @> hstore('nist:state_fips', '2')
AND NOT ways.tags @> hstore('admin_level','2')
AND NOT ways.tags @> hstore('admin_level','4');
INSERT INTO admin_ways
SELECT ways.id FROM ways LEFT JOIN admin_ways ON ways.id=admin_ways.id
WHERE (ST_Within(linestring, 'SRID=4326;POLYGON((171.0 54.0,180.0 52.0,180.0 50.0,171.0 52.0,171.0 54.0))'::geometry) OR
ST_Within(linestring, 'SRID=4326;POLYGON((-130.531200 54.671880,-129.797600 55.311420,-129.999000 56.161710,-135.325300 59.874720,-136.579900 59.603790,-136.601500 59.274670,-137.407000 59.038670,-137.443000 59.289360,-138.982100 60.102240,-138.895800 60.430420,-140.791900 60.459660,-140.657300 69.863210,-153.481400 74.911790,-168.429600 69.685440,-169.522800 65.172880,-172.072800 64.076870,-180 55.396990,-180 50.211860,-146.317100 56.572710,-132.641500 54.415150,-130.531200 54.671880))'::geometry))
AND (
(tags @> hstore('boundary','administrative')
AND NOT tags @> hstore('admin_level','2')
AND NOT tags @> hstore('admin_level','4')
AND NOT tags @> hstore('admin_level','8')
AND NOT tags @> hstore('place','locality'))
OR tags @> hstore('admin_level','6'))
AND admin_ways IS NULL;
\o ways.txt
SELECT * FROM admin_ways;
\o
Download objects in JOSM then while downloading run more SQL
\o relations.txt
SELECT relation_id FROM admin_ways
LEFT JOIN relation_members ON admin_ways.id = relation_members.member_id
WHERE member_type = 'W';
\o
Download objects in JOSM.
Select ways to be deleted in JOSM and add delete=yes tag.
SET enable_mergejoin=FALSE;
SET enable_hashjoin=FALSE;
\o other_ways.txt
SELECT DISTINCT ON (other_nodes.way_id) other_nodes.way_id AS id
FROM admin_ways
LEFT JOIN way_nodes AS admin_nodes ON admin_nodes.way_id=admin_ways.id
LEFT JOIN way_nodes AS other_nodes ON admin_nodes.node_id = other_nodes.node_id
LEFT JOIN admin_ways AS other_ways ON other_nodes.way_id = other_ways.id
WHERE other_ways.id IS NULL;
\o
Delete ways with delete=yes
Import progress
Completed. Import changesets: https://www.openstreetmap.org/browse/changeset/14082505
Pre-import cleaning changesets:
https://www.openstreetmap.org/browse/changeset/14079853
https://www.openstreetmap.org/browse/changeset/14079348
https://www.openstreetmap.org/browse/changeset/14078661
https://www.openstreetmap.org/browse/changeset/14078041
https://www.openstreetmap.org/browse/changeset/14077503