Alaska/TIGER Counties

From OpenStreetMap Wiki
Jump to: navigation, search

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

source=TIGER 2012 import=yes

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

http://lists.openstreetmap.org/pipermail/talk-us/2012-November/009566.html

http://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: http://www.openstreetmap.org/browse/changeset/14082505

Pre-import cleaning changesets: http://www.openstreetmap.org/browse/changeset/14079853
http://www.openstreetmap.org/browse/changeset/14079348
http://www.openstreetmap.org/browse/changeset/14078661
http://www.openstreetmap.org/browse/changeset/14078041
http://www.openstreetmap.org/browse/changeset/14077503