Standardize use of name and FIPS code in state and county fields

[bahoo]

When I do a SELECT DISTINCT state FROM 'precincts-2163' I get a list that is a mix of full state names, and FIPS codes:

01
02
04
Arkansas
06
08
09
10
11
12
Georgia
Hawaii
16
17
18
Iowa
Kansas
Kentucky
22
23
24
25
26
Minnesota
28
(null)
Missouri
30
31
32
33
34
35
36
37
38
39
40
41
Pennsylvania
45
46
47
48
49
50
Virginia
Washington
West Virginia
55
Wyoming
72

The county field is similar. I feel the need to standardize, and certainly see the value in having, potentially, both forms (if we'd want, then — state, state_fips, county, and county_fips).

Any thoughts?

Potential solutions could include:

1. Hard coding state name or state FIPS code in each command
2. Doing a SQLite join to a static CSV file with name and FIPs codes; e.g. ogr2ogr -F CSV state_fips.csv render/cb_2013_us_state_20m-2163.shp -dialect SQLite -sql "SELECT name AS name, statefp AS fips FROM 'cb_2013_us_state_20m-2163'"
3. Similar approach but for county codes.

Happy to run with this and submit a PR, but would love to have more input, first.

Thanks!

[nvkelso]

I’m down with always using FIPS.

…

On Mar 5, 2018, at 15:05, Jon Culver ***@***.***> wrote: When I do a SELECT DISTINCT state FROM 'precincts-2163' I get a list that is a mix of full state names, and FIPS codes: 01 02 04 Arkansas 06 08 09 10 11 12 Georgia Hawaii 16 17 18 Iowa Kansas Kentucky 22 23 24 25 26 Minnesota 28 (null) Missouri 30 31 32 33 34 35 36 37 38 39 40 41 Pennsylvania 45 46 47 48 49 50 Virginia Washington West Virginia 55 Wyoming 72 The county field is similar. I feel the need to standardize, and certainly see the value in having, potentially, both forms (if we'd want, then — state, state_fips, county, and county_fips). Any thoughts? Potential solutions could include: Hard coding state name or state FIPS code in each command Doing a SQLite join to a static CSV file with name and FIPs codes; e.g. ogr2ogr -F CSV state_fips.csv render/cb_2013_us_state_20m-2163.shp -dialect SQLite -sql "SELECT name AS name, statefp AS fips FROM 'cb_2013_us_state_20m-2163'" Similar approach but for county codes. Happy to run with this and submit a PR, but would love to have more input, first. Thanks! — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.

[happyjack27]

What I've done is have a hard-coded array of FIPS - abbreviation - state name. This was for personal use. Gor largee distribution I think .csv of the same would be more appropriate On Mar 5, 2018 5:52 PM, "Nathaniel V. KELSO" <notifications@github.com> wrote:

…

I’m down with always using FIPS. > On Mar 5, 2018, at 15:05, Jon Culver ***@***.***> wrote: > > When I do a SELECT DISTINCT state FROM 'precincts-2163' I get a list that is a mix of full state names, and FIPS codes: > > 01 > 02 > 04 > Arkansas > 06 > 08 > 09 > 10 > 11 > 12 > Georgia > Hawaii > 16 > 17 > 18 > Iowa > Kansas > Kentucky > 22 > 23 > 24 > 25 > 26 > Minnesota > 28 > (null) > Missouri > 30 > 31 > 32 > 33 > 34 > 35 > 36 > 37 > 38 > 39 > 40 > 41 > Pennsylvania > 45 > 46 > 47 > 48 > 49 > 50 > Virginia > Washington > West Virginia > 55 > Wyoming > 72 > The county field is similar. I feel the need to standardize, and certainly see the value in having, potentially, both forms (if we'd want, then — state, state_fips, county, and county_fips). > > Any thoughts? > > Potential solutions could include: > > Hard coding state name or state FIPS code in each command > Doing a SQLite join to a static CSV file with name and FIPs codes; e.g. ogr2ogr -F CSV state_fips.csv render/cb_2013_us_state_20m-2163.shp -dialect SQLite -sql "SELECT name AS name, statefp AS fips FROM 'cb_2013_us_state_20m-2163'" > Similar approach but for county codes. > Happy to run with this and submit a PR, but would love to have more input, first. > > Thanks! > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub, or mute the thread. > — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#135 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AB7NQe67xMtLEetxUhumkfSNPNizIu_xks5tbc-rgaJpZM4Sd0oH> .

[migurski]

👍 to FIPS always.

[nvkelso]

Let’s switch to always FIPS then.

…

On Mar 5, 2018, at 19:53, Michal Migurski ***@***.***> wrote: 👍 to FIPS always. — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.

[nvkelso]

The state portion of this was taken care of in #146.

[sigpwned]

I've done a little work on the data set and I believe I've got (a) all precincts assigned to a county; and (b) all counties standardized to FIPS and name. What is the best way to contribute this data back? One pull request overall, one pull request per state, ...? Most of the work was done in PostGIS, so I'm not 100% sure what the best way to give the data back will be.

[nvkelso]

Nice! Can you submit a small PR for just one or 2 states to demonstrate the changes? Then we can talk about the rest... For Postgres... is this a 1 time operation? Ideally it’s something that can be done each makefile build. Like with OGR or Mapshaper?

…

On Sep 9, 2018, at 14:33, Andy Boothe ***@***.***> wrote: I've done a little work on the data set and I believe I've got (a) all precincts assigned to a county; and (b) all counties standardized to FIPS and name. What is the best way to contribute this data back? One pull request overall, one pull request per state, ...? Most of the work was done in PostGIS, so I'm not 100% sure what the best way to give the data back will be. — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.

[sigpwned]

Thanks for the quick response!

Can you submit a small PR for just one or 2 states to demonstrate the changes? Then we can talk about the rest...

Absolutely. I think that's a great approach.

For Postgres... is this a 1 time operation? Ideally it’s something that can be done each makefile build. Like with OGR or Mapshaper?

A lot of this work was by-hand, so I'm afraid it's going to be a bit of a mixed bag, at least the way I've done it. However, I'm much more of a generalist than a GIS expert, so it's also totally possible that someone with more GIS experience could take the work I've done and figure out a way to stitch it into a more build-style process.

I'll try to open a couple of different PRs demonstrating different kind of work; link to those PRs here in a separate comment; and include in that comment the other work that I've done.

Also, THANK YOU for providing a great foundation here! I think making this data generally available is really important work. I hope I can help.

[sigpwned]

I was only able to open one ticket/PR this evening, but I did take the time to learn how to integrate the change into the Makefile. Hopefully that's at least a reasonable prototype for the other changes I have.

The new ticket is #149. The new PR is #150.

Here are the other changes I have. I did the work about a week ago, so it's possible that you've already fixed some of these. I think all this work can be considered part and parcel of this issue. I have organized them as a checklist to show the work I have yet to port over:

• Replace the county names in Oregon with FIPS codes. fix Oregon county labels #150
• Replace the county names in Hawaii with FIPS codes. Standardize Hawaii county and precinct IDs #152
• Replace the county names in Virginia with FIPS codes. Standardize Virginia county and precinct IDs #154
• Replace the county names in Pennsylvania with FIPS codes. Standardize Pennsylvania county IDs #156
• Standardize Kansas county FIPS codes. (Some were 3-digit without state; some were 5-digit, with state.) Standardize Kansas county IDs #158
• Replace the county names in Washington with FIPS codes. Standardize Washington county IDs #160
• Assign precincts in Iowa that were missing county assignments to the county which contains the most of their area. Perform IA and NM precinct county assignments #162
• Assign all precincts in Delaware to the county that contains the most of their area. (All precincts were previously assigned to county 000, which does not exist.) Perform Delaware precinct county assignments #164
• Assign precincts in New Mexico that were missing county assignments to the county which contains the most of their area. Perform IA and NM precinct county assignments #162
• Assign precincts in California that were missing county assignments to the county which contains the most of their area. California precincts have no county assignment #167

I also did some additional work that I think is probably beyond the scope of this issue that I will handle through another issue:

• I aggregated Washington precincts spread across multiple records into a single MultiPolygon record.
  • It looks like precincts in WA can consist of multiple non-contiguous regions
  • Each original record seemed to represent one contiguous region
  • This action puts multiple non-contiguous regions into one record

Hopefully those make sense! Are these changes valuable? If so, what do you think is the best way to get these changes into the data set? Ideally, via the Makefile, right?

[sigpwned]

Changes to OR have been made per issue #149 / PR #150.

[sigpwned]

Changes to HI have been made per issue #151 / PR #152.

[sigpwned]

Changes to VA have been made per issue #153 / PR #154.

[sigpwned]

Changes to PA have been made per issue #155 / PR #156.

Note that this change includes only county FIPS standardization and not precinct standardization. PA precincts do not always have names, and when names are present they are not unique within a county (e.g.: fid=274 and fid=298 both have county 107 and precinct Wd South).

How should precinct IDs be generated in this case? FIDs look like a good candidate, but without knowing more about how they were generated it's hard to recommend them.

[sigpwned]

Changes to KS have been made per issue #157 / PR #158.

Note that this change includes only county FIPS standardization and not precinct standardization. KS precincts have many different labeling schemes, several of which have no obvious mapping back to a unique 4-digit numerical code (e.g., "Overland Park 1-01").

How should precinct IDs be handled in this case?

[sigpwned]

Changes to WA have been made per issue #159 / PR #160.

Note that this change includes only county FIPS standardization and not precinct standardization. WA precincts have a very consistent naming scheme and it would be easy to transform the existing names into any one of several good, standard, cross-compatible alternatives. However, it must first be decided what format to adopt.

How should precinct IDs be handled in this case?

It's important to settle this question for WA and the other states above. But you folks are way ahead of me, per #144. 🙂