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.
TIGER is public domain
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.
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.
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.
The other information from the shapefiles is either irrelevant metadata, duplication of geodata or blank.
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.
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
Completed. Import changesets: https://www.openstreetmap.org/browse/changeset/14082505
Pre-import cleaning changesets: