Street names with apostrophes get capitalized WeIrD

[Nate-Wessel]

Queen'S Park

Problem is here:

bdit_tt_request_app/backend/app/links/here.py

Line 54 in 9729809

InitCap(attr.st_name) AS st_name,

[Nate-Wessel]

Not all are possessives

SELECT DISTINCT st_name
FROM here_gis.streets_att_24_4
WHERE st_name ~ E'\''

[AdK0101]

This should work for the most part.

SELECT DISTINCT
    st_name AS original_name,
    (
        SELECT string_agg(
            (
                -- split each whitespace‐separated token (w) on hyphens,
                -- fix each sub‐token, then re‐join with '-'
                SELECT string_agg(
                    CASE
                        -- Case 1: if sub‐token is exactly 'digits + ordinal suffix' (e.g. '10th', '2nd', '3rd'):
                        WHEN subw ~ E'^[0-9]+(st|nd|rd|th)$' THEN
                            subw

                        -- Case 2: if sub‐token begins with 'one lowercase letter + apostrophe + letter...' (e.g. "d'arcy"):
                        WHEN subw ~ E'^[a-z]''[a-z]' THEN
                            upper(substring(subw FROM 1 FOR 1)) -- first letter becomes uppercase
                            || ''''                                -- literal apostrophe
                            || upper(substring(subw FROM 3 FOR 1)) -- letter after apostrophe becomes uppercase
                            || lower(substring(subw FROM 4))      -- the rest becomes lowercase

                        -- Case 3: if sub‐token begins with 'one or more digits + a letter...' (but not a pure ordinal):
                        WHEN subw ~ E'^[0-9]+[a-z]' THEN
                            -- extract the leading digits, uppercase the very first letter after them,
                            -- then lowercase the remainder.
                            substring(subw FROM '^[0-9]+')                     -- e.g. '46'
                            || upper(substring(subw FROM '^[0-9]+([a-z])'))    -- e.g. 'T' from '46th'
                            || lower(
                                -- whatever remains after those first-digit‐plus‐letter characters
                                COALESCE(substring(subw FROM '^[0-9]+[a-z](.*)$'), '')
                            )

                        ELSE
                            -- Case 4: in all other cases: uppercase just the very first character of subw, lowercase the rest
                            upper(substring(subw FROM 1 FOR 1))
                            || lower(substring(subw FROM 2))
                    END
                    , '-' -- re‐join hyphen‐split pieces with a hyphen
                )
                FROM unnest(
                    regexp_split_to_array(w, E'-')
                ) AS parts(subw)
            )
        , ' ' ) -- re‐join all whitespace‐split tokens with a space
        FROM unnest(
            regexp_split_to_array(lower(st_name), E'\\s+')
        ) AS t(w)
    ) AS fixed_name
FROM here_gis.streets_att_24_4;

The reason I say for the most part, is that places like McDonald are Mcdonald, in this query. Of course, this was an issue originally as well. And furthermore, I am sure that there are cases that InitCap handles, but this doesn't (e.g. period inside a token), but those cases are also not in the street names present in streets_att_24_4. So before I go ahead and make a pull request, are these things we want to care about? If so, this query will need some more modification.

[gabrielwol]

This will be useful in other cases too! Can the sql live in another repo, perhaps here with similar functions?

5 years ago (and lightly updated by me last year) we worked on similar problems for gis.text_to_centreline:
https://github.com/CityofToronto/bdit_data-sources/blob/master/gis/text_to_centreline/sql/helper_functions/function-abbr_street.sql
https://github.com/CityofToronto/bdit_data-sources/blob/master/gis/text_to_centreline/sql/helper_functions/function-custom_case.sql

[Nate-Wessel]

Good idea @gabrielwol. It would be nice to encapsulate this in a function to keep the TT_app code simpler too.

[Nate-Wessel]

@AdK0101 it looks like you've gone beyond just handling the possessive / apostrophe problem. What are some of the other issues you discovered with InitCap?

[AdK0101]

@AdK0101 it looks like you've gone beyond just handling the possessive / apostrophe problem. What are some of the other issues you discovered with InitCap?

There's really only one other notable difference. In cases like "12A" in the street attributes table, InitCap turns it to 12a, but it remains 12A in my query, as an example. This is case 3. Just think it makes more sense to write Hwy-2A rather than Hwy-2a. As a point of comparison 49TH is 49th both after InitCap and my query.