Intersections data: pretty the names

[gregallensworth]

Relevant to #73, some data thoughts:

The intersections in the nyc_intersections table, their names are visually overbearing: WASHINGTON STREET & OAK AVENUE

To what degree could these be made easier on the eyes?

• Converting from "UPPER CASE" to "Title Case"
• contracting AVENUE, STREET, PARKWAY, BOULEVARD etc.

As always, it's never quite so easy to Title Case things, as there are intentional outliers: "MLK" and "USA" should be all caps, while "McColough" and "O'Brian" are intentionally mixed-case. This could be a significantly larger project to vet names and correct them individually...

[danrademacher]

Client says just make the boroughs uppercase so everything matches. Don't try for sentence case. Maybe just do this with CSS?

[danrademacher]

Client requested abbreviations without knowing it was on our list of potential needs. Her words:

Ranking : is it possible to abbreviate avenue, place, street and boulevard and not capitalize the abbreviation? Also East and West should be E and W

I'm a little thrown by "not capitalize the abbreviation" -- seems like we should go all caps or sentence case. So let's stick with all caps for now

[gregallensworth]

This is coming along slowly but surely.

First set of queries: define street1 and street2 fields, and populate them.

ALTER TABLE nyc_intersections ADD COLUMN street1 VARCHAR(40)
ALTER TABLE nyc_intersections ADD COLUMN street1 VARCHAR(40) 

UPDATE nyc_intersections SET street1=split_part(name, ' & ', 1)
UPDATE nyc_intersections SET street2=split_part(name, ' & ', 2)

The queries to actually do the renaming, are in the file attached. They cover the W S E N SE SW NE NW suffixes, and the street names so far detected in the data. They also cover a few common abbreviations. sql_queries_for_street_names.txt

Sadly, the way to apply these SQL queries is via the CARTo web UI, where you copy, alt-tab, paste, alt-tab, copy, ...

Notes:

• The above file contains queries for the street1 field, and you would need to copy-edit to form the queries for street2 as well.
• A vacuum operation is included in the queries from time to time, and prevents the table from bloating as you do the updates (a known PostgreSQL phenomenon) since you don't end up with the table quadrup.ling in size.

[gregallensworth]

Then of course, the final touch after correcting street name & suffix, and then street prefix, ... is to concatenate them back together:

UPDATE nyc_intersections SET name = CONCAT(street1, ' & ', street2)

And then the cleanup:

VACUUM FULL nyc_intersections

[gregallensworth]

All done!

[danrademacher]

Hmm, testing locally, I still see "EAST" a lot

and "WEST"

Shouldn't those have become "E" and "W"?

[gregallensworth]

Finally all redone.

I went back to the dataset on P and reworked the name correction stuff. Version up in the DB at CARTO (and visible on the site, live and development) has the best names I have come up with so far.