Trim spaces in the location fields and regenerate location ids

[davidgamez]

Description

Some location IDs in the location table contain a _ at the end. The municipality may have ending spaces. Example: US-Washington-Wenatchee_

Proposed solution

Trim spaces at the beginning and end of the municipality values. As part of this issue, we should also trim spaces in country_code and subdivision_name to avoid the same problem.

[emmambd]

Tasks:

• Identify all feeds with missing municipality and/or subdivision field (2 underscores in the middle, underscore at beginning or end)
• Modifying the concatenation code in populate script to trim
• Update database (manually or Liquibase)

Resources:

update database script locally

[davidgamez]

Blocked by #402

[jcpitre]

Because the dash is a legitimate character in subdivision-name. and municipality, it is suggested to change the separator to a character that will be disallowed when setting the data. For example |

Question: Why is it a problem with is ending with the separator (for |, example is US|New-York|
Or have 2 separators after the other. e.g. US||
That is as good an ID as any other, plus it conveys some info.

[jcpitre]

In the current DEV db, there are:

• 5 subdivision_name starting or ending with a space
• 6 municipalities starting or ending with a space

Also tested for two+ consecutive spaces in either field, but found none.

[jcpitre]

The trimming of spaces has already been done in #406
Tested by running the populate script without trimming then the same script with trimming. The results is that there are duplicated entries in the location table.

I suggest we manually remove these entries from the dev database once the #406 is released.

[jcpitre]

Blocked until after release.
We will then have to remove entries in the location table by hand

Or maybe we should automatically remove all unused entries in the location table once in a while?

[jcpitre]

Deleted 15 unused locations by hand in PROD.