[SCM] PostGIS branch master updated. 3.6.0rc2-380-g20fcd89ae
git at osgeo.org
git at osgeo.org
Mon Mar 9 02:09:48 PDT 2026
This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "PostGIS".
The branch, master has been updated
via 20fcd89ae646c2557cf892251242bc16a76a927c (commit)
from a586d720eb01112b22052865feca01a6d4b6eb9e (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit 20fcd89ae646c2557cf892251242bc16a76a927c
Author: Darafei Praliaskouski <me at komzpa.net>
Date: Mon Mar 9 13:00:44 2026 +0400
#1599 Improve country-aware address normalization
Canonicalize explicit country tokens in the address parser, carry country through norm_addy, and add regressions for country-aware tiger normalization while keeping tiger usable without address_standardizer.
Closes #1599
diff --git a/NEWS b/NEWS
index 4bbdc0e1d..d9bf639d8 100644
--- a/NEWS
+++ b/NEWS
@@ -41,6 +41,10 @@ This version requires GEOS 3.10 or higher
- #4828, geometry_columns handles NOT VALID SRID checks without errors (Darafei Praliaskouski)
- #6048, [raster] ST_Clip no longer crashes when clipping sparse band
selections (Darafei Praliaskouski)
+ - #1599, [address_standardizer] parse_address() and normalize_address() now
+ canonicalize trailing country tokens to ISO 3166-1 alpha-2 codes and
+ expose country on normalized addresses
+ (Darafei Praliaskouski)
- #5645, Docs: keep code operators ("=>") intact in translated manuals
by enforcing verbatim CSS (Darafei Praliaskouski)
- #5593, Docs: render manual images with GraphicsMagick without temporary files,
diff --git a/doc/extras_address_standardizer.xml b/doc/extras_address_standardizer.xml
index 287151ca4..d5735096d 100644
--- a/doc/extras_address_standardizer.xml
+++ b/doc/extras_address_standardizer.xml
@@ -14,8 +14,9 @@
<para>The parser works from right to left looking first at the macro elements
for postcode, state/province, city, and then looks micro elements to determine
if we are dealing with a house number street or intersection or landmark.
-It currently does not look for a country code or name, but that could be
-introduced in the future.</para>
+It conservatively recognizes trailing country tokens and canonicalizes them to
+ISO 3166-1 alpha-2 codes. Explicit country aliases update the parsed country,
+and otherwise the default US or CA inference still applies.</para>
<variablelist>
<varlistentry>
<term>Country code</term>
diff --git a/doc/installation.xml b/doc/installation.xml
index 35b05227f..e6805f8c0 100644
--- a/doc/installation.xml
+++ b/doc/installation.xml
@@ -925,7 +925,7 @@ test test-standardize_address_2 ... ok
<programlisting>cd extensions/postgis_tiger_geocoder
make install
make installcheck
- </programlisting></para>
+ </programlisting></para>
<para>output should look like:
<screen>============== dropping database "contrib_regression" ==============
DROP DATABASE
diff --git a/extensions/address_standardizer/address_parser.c b/extensions/address_standardizer/address_parser.c
index 3e1c642bf..cbe318942 100644
--- a/extensions/address_standardizer/address_parser.c
+++ b/extensions/address_standardizer/address_parser.c
@@ -21,8 +21,10 @@
#endif
Datum parse_address(PG_FUNCTION_ARGS);
+Datum strip_explicit_country(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(parse_address);
+PG_FUNCTION_INFO_V1(strip_explicit_country);
Datum parse_address(PG_FUNCTION_ARGS)
{
@@ -110,3 +112,18 @@ Datum parse_address(PG_FUNCTION_ARGS)
return result;
}
+Datum
+strip_explicit_country(PG_FUNCTION_ARGS)
+{
+ char *input;
+ char *working;
+ char country_code[3] = {0};
+
+ input = text_to_cstring(PG_GETARG_TEXT_P(0));
+ working = pstrdup(input);
+
+ if (strip_explicit_country_token(working, country_code))
+ PG_RETURN_TEXT_P(cstring_to_text(working));
+
+ PG_RETURN_TEXT_P(cstring_to_text(input));
+}
diff --git a/extensions/address_standardizer/address_standardizer.c b/extensions/address_standardizer/address_standardizer.c
index 363185529..5adb43666 100644
--- a/extensions/address_standardizer/address_standardizer.c
+++ b/extensions/address_standardizer/address_standardizer.c
@@ -386,11 +386,19 @@ static const char *
stdaddr_field_value(const STDADDR *stdaddr, size_t offset)
{
const char *const *field_value;
+ const char *canonical_country;
if (!stdaddr)
return NULL;
field_value = (const char *const *)((const char *)stdaddr + offset);
+ if (offset == offsetof(STDADDR, country) && *field_value)
+ {
+ canonical_country = country_code_from_name(*field_value);
+ if (canonical_country)
+ return canonical_country;
+ }
+
return *field_value;
}
diff --git a/extensions/address_standardizer/address_standardizer_functions.sql.in b/extensions/address_standardizer/address_standardizer_functions.sql.in
index 65c3e919b..535a28de3 100644
--- a/extensions/address_standardizer/address_standardizer_functions.sql.in
+++ b/extensions/address_standardizer/address_standardizer_functions.sql.in
@@ -47,3 +47,7 @@ CREATE OR REPLACE FUNCTION parse_address(IN text,
AS 'MODULE_PATHNAME', 'parse_address'
LANGUAGE 'c' IMMUTABLE STRICT;
+CREATE OR REPLACE FUNCTION strip_explicit_country(IN text)
+ RETURNS text
+ AS 'MODULE_PATHNAME', 'strip_explicit_country'
+ LANGUAGE 'c' IMMUTABLE STRICT;
diff --git a/extensions/address_standardizer/expected/test-standardize_address_1.out b/extensions/address_standardizer/expected/test-standardize_address_1.out
index 73604c0b6..ff6dbd83d 100644
--- a/extensions/address_standardizer/expected/test-standardize_address_1.out
+++ b/extensions/address_standardizer/expected/test-standardize_address_1.out
@@ -13,31 +13,31 @@ select * from standardize_address('us_lex'::text, 'us_gaz'::text, 'us_rules'::te
SELECT '#2981' As ticket, * FROM standardize_address('us_lex','us_gaz','us_rules', '1566 NEW STATE HWY, RAYNHAM, MA');
ticket | building | house_num | predir | qual | pretype | name | suftype | sufdir | ruralroute | extra | city | state | country | postcode | box | unit
--------+----------+-----------+--------+------+---------+-------------------+---------+--------+------------+-------+---------+---------------+---------+----------+-----+------
- #2981 | | 1566 | | | | NEW STATE HIGHWAY | | | | | RAYNHAM | MASSACHUSETTS | USA | | |
+ #2981 | | 1566 | | | | NEW STATE HIGHWAY | | | | | RAYNHAM | MASSACHUSETTS | US | | |
(1 row)
SELECT '#2978a' As ticket, * FROM standardize_address('us_lex','us_gaz','us_rules', '10-20 DORRANCE ST PROVIDENCE RI' );
ticket | building | house_num | predir | qual | pretype | name | suftype | sufdir | ruralroute | extra | city | state | country | postcode | box | unit
--------+----------+-----------+--------+------+---------+----------+---------+--------+------------+-------+------------+--------------+---------+----------+-----+------
- #2978a | | 10 20 | | | | DORRANCE | STREET | | | | PROVIDENCE | RHODE ISLAND | USA | | |
+ #2978a | | 10 20 | | | | DORRANCE | STREET | | | | PROVIDENCE | RHODE ISLAND | US | | |
(1 row)
SELECT '#2978b' As ticket, * FROM standardize_address('us_lex','us_gaz','us_rules', '10 20 DORRANCE ST PROVIDENCE RI' );
ticket | building | house_num | predir | qual | pretype | name | suftype | sufdir | ruralroute | extra | city | state | country | postcode | box | unit
--------+----------+-----------+--------+------+---------+----------+---------+--------+------------+-------+------------+--------------+---------+----------+-----+------
- #2978b | | 10 20 | | | | DORRANCE | STREET | | | | PROVIDENCE | RHODE ISLAND | USA | | |
+ #2978b | | 10 20 | | | | DORRANCE | STREET | | | | PROVIDENCE | RHODE ISLAND | US | | |
(1 row)
SELECT '#2978c' As ticket, * FROM standardize_address('us_lex','us_gaz','us_rules', '10-20 DORRANCE ST, PROVIDENCE, RI');
ticket | building | house_num | predir | qual | pretype | name | suftype | sufdir | ruralroute | extra | city | state | country | postcode | box | unit
--------+----------+-----------+--------+------+---------+----------+---------+--------+------------+-------+------------+--------------+---------+----------+-----+------
- #2978c | | 10 20 | | | | DORRANCE | STREET | | | | PROVIDENCE | RHODE ISLAND | USA | | |
+ #2978c | | 10 20 | | | | DORRANCE | STREET | | | | PROVIDENCE | RHODE ISLAND | US | | |
(1 row)
SELECT '#state_only_macro' AS ticket, * FROM standardize_address('us_lex','us_gaz','us_rules', '25 Prince Street, NC 09985');
ticket | building | house_num | predir | qual | pretype | name | suftype | sufdir | ruralroute | extra | city | state | country | postcode | box | unit
-------------------+----------+-----------+--------+------+---------+--------+---------+--------+------------+-------+------+----------------+---------+----------+-----+------
- #state_only_macro | | 25 | | | | PRINCE | STREET | | | | | NORTH CAROLINA | USA | 09985 | |
+ #state_only_macro | | 25 | | | | PRINCE | STREET | | | | | NORTH CAROLINA | US | 09985 | |
(1 row)
SELECT '#5299a' AS ticket, * FROM standardize_address('us_lex', 'us_gaz', 'us_rules','1 Timepiece Point','Boston, MA, 02220');
@@ -55,7 +55,7 @@ SELECT '#5299b' AS ticket, * FROM standardize_address('us_lex', 'us_gaz', 'us_r
SELECT '#5695a' AS ticket, * FROM standardize_address('us_lex', 'us_gaz', 'us_rules', 'ONE E PIMA ST STE 999, TUCSON, AZ');
ticket | building | house_num | predir | qual | pretype | name | suftype | sufdir | ruralroute | extra | city | state | country | postcode | box | unit
--------+----------+-----------+--------+------+---------+------+---------+--------+------------+-------+--------+---------+---------+----------+-----+-----------
- #5695a | | 1 | EAST | | | PIMA | STREET | | | | TUCSON | ARIZONA | USA | | | SUITE 999
+ #5695a | | 1 | EAST | | | PIMA | STREET | | | | TUCSON | ARIZONA | US | | | SUITE 999
(1 row)
DO $$
diff --git a/extensions/address_standardizer/parseaddress-api.c b/extensions/address_standardizer/parseaddress-api.c
index 8abf1d1b2..408a592f3 100644
--- a/extensions/address_standardizer/parseaddress-api.c
+++ b/extensions/address_standardizer/parseaddress-api.c
@@ -2,6 +2,7 @@
* parseaddres.c - utility to crack a string into address, city st zip
*
* Copyright 2006 Stephen Woodbridge
+ * Copyright 2026 Darafei Praliaskouski <me at komzpa.net>
*
* This code is released under and MIT-X style license,
*
@@ -9,13 +10,10 @@
* woodbri at swoodbridge.com
* woodbr at imaptools.com
*
- *
- * TODO:
- * * add recognition of country before or after postalcode
- *
*/
#include <string.h>
+#include <stdlib.h>
#include <ctype.h>
#include <stdio.h>
@@ -48,6 +46,23 @@ const char *parseaddress_cvsid();
char *clean_leading_punct(char *s);
static int is_parse_separator(unsigned char c);
static size_t normalize_address_input(char *s);
+static size_t normalize_country_token(const char *src, char *dst, size_t dst_size);
+static bool ends_with_supported_macro(const char *s);
+static bool extract_trailing_country(char *s, char *country_code);
+
+static const char *const us_zip_regex = "\\b(\\d{5})[-\\s]{0,1}?(\\d{0,4})?$";
+static const char *const canada_zip_regex = "\\b([a-z]\\d[a-z]\\s?\\d[a-z]\\d)$";
+static const char *const canada_province_regex = "^(?-xism:(?i:(?=[abmnopqsy])(?:n[ltsu]|[am]b|[bq]c|on|pe|sk|yt)))$";
+static const char *const us_ca_state_regex =
+ "\\b(?-xism:(?i:(?=[abcdfghiklmnopqrstuvwy])(?:a(?:l(?:a(?:bam|sk)a|berta)?|mer(?:ican)?\\ samoa|r(?:k(?:ansas)?|izona)?|[kszb])|s(?:a(?:moa|skatchewan)|outh\\ (?:carolin|dakot)a|\\ (?:carolin|dakot)a|[cdk])|c(?:a(?:lif(?:ornia)?)?|o(?:nn(?:ecticut)?|lorado)?|t)|d(?:e(?:la(?:ware)?)?|istrict\\ of\\ columbia|c)|f(?:l(?:(?:orid)?a)?|ederal\\ states\\ of\\ micronesia|m)|m(?:i(?:c(?:h(?:igan)?|ronesia)|nn(?:esota)?|ss(?:(?:issipp|our)i)?)?|a(?:r(?:shall(?:\\ is(?:l(?:and)?)?)?|yland)|ss(?:achusetts)?|ine|nitoba)?|o(?:nt(?:ana)?)?|[ehdnstpb])|g(?:u(?:am)?|(?:eorgi)?a)|h(?:awai)?i|i(?:d(?:aho)?|l(?:l(?:inois)?)?|n(?:d(?:iana)?)?|(?:ow)?a)|k(?:(?:ansa)?s|(?:entuck)?y)|l(?:a(?:bordor)?|ouisiana)|n(?:e(?:w(?:\\ (?:foundland(?:\\ and\\ labordor)?|hampshire|jersey|mexico|(?:yor|brunswic)k)|foundland)|(?:brask|vad)a)?|o(?:rth(?:\\ (?:mariana(?:\\ is(?:l(?:and)?)?)?|(?:carolin|dakot)a)|west\\ territor(?:ies|y))|va\\ scotia)|\\ (?:carolin|dakot)a|u(?:navut)?|[vhjmycdblsf]|w?t)|o(?:h(?:io)?|k(
?:lahoma)?|r(?:egon)?|n(?:t(?:ario)?)?)|p(?:a(?:lau)?|e(?:nn(?:sylvania)?|i)?|r(?:ince\\ edward\\ island)?|w|uerto\\ rico)|r(?:hode\\ island|i)|t(?:e(?:nn(?:essee)?|xas)|[nx])|ut(?:ah)?|v(?:i(?:rgin(?:\\ islands|ia))?|(?:ermon)?t|a)|w(?:a(?:sh(?:ington)?)?|i(?:sc(?:onsin)?)?|y(?:oming)?|(?:est)?\\ virginia|v)|b(?:ritish\\ columbia|c)|q(?:uebe)?c|y(?:ukon|t))))$";
+typedef struct country_alias {
+ const char *alias;
+ const char *alpha2;
+} CountryAlias;
+
+static const CountryAlias country_aliases[] = {
+#include "parseaddress-countries.h"
+};
static int
is_parse_separator(unsigned char c)
@@ -97,6 +112,171 @@ normalize_address_input(char *s)
return write_pos;
}
+static size_t
+normalize_country_token(const char *src, char *dst, size_t dst_size)
+{
+ size_t write_pos = 0;
+ bool previous_was_space = false;
+
+ if (!src || !dst || !dst_size)
+ return 0;
+
+ for (; *src != '\0' && write_pos + 1 < dst_size; src++)
+ {
+ unsigned char current = (unsigned char)*src;
+
+ if (isalpha(current))
+ {
+ dst[write_pos++] = (char)toupper(current);
+ previous_was_space = false;
+ continue;
+ }
+
+ if (isspace(current) || ispunct(current))
+ {
+ if (!write_pos || previous_was_space)
+ continue;
+
+ dst[write_pos++] = ' ';
+ previous_was_space = true;
+ }
+ }
+
+ if (write_pos && dst[write_pos - 1] == ' ')
+ write_pos--;
+
+ dst[write_pos] = '\0';
+ return write_pos;
+}
+
+static int
+compare_country_alias(const void *lhs, const void *rhs)
+{
+ const CountryAlias *left = (const CountryAlias *)lhs;
+ const CountryAlias *right = (const CountryAlias *)rhs;
+
+ return strcmp(left->alias, right->alias);
+}
+
+const char *
+country_code_from_name(const char *country_name)
+{
+ CountryAlias key;
+ char normalized_country[128];
+ const CountryAlias *match;
+
+ if (!normalize_country_token(country_name, normalized_country, sizeof(normalized_country)))
+ return NULL;
+
+ key.alias = normalized_country;
+ key.alpha2 = NULL;
+ match = bsearch(
+ &key, country_aliases, lengthof(country_aliases), sizeof(country_aliases[0]), compare_country_alias);
+
+ return match ? match->alpha2 : NULL;
+}
+
+/*
+ * Recognize the right-most macro shape that this parser already knows how to
+ * consume. That lets us conservatively strip trailing countries without
+ * erasing ordinary trailing qualifiers, and still supports short ZIP fragments
+ * once they are paired with a valid trailing state/province.
+ */
+static bool
+ends_with_supported_macro(const char *s)
+{
+ int macro_ovect[OVECCOUNT];
+ int rc;
+ char *state_probe;
+
+ if (!s || *s == '\0')
+ return false;
+
+ rc = match((char *)us_zip_regex, (char *)s, macro_ovect, 0);
+ if (rc >= 2)
+ return true;
+
+ rc = match((char *)canada_zip_regex, (char *)s, macro_ovect, PARSE_CASELESS);
+ if (rc >= 1)
+ return true;
+
+ rc = match((char *)us_ca_state_regex, (char *)s, macro_ovect, PARSE_CASELESS);
+ if (rc > 0)
+ return true;
+
+ rc = match("\\b(\\d{2,4})$", (char *)s, macro_ovect, 0);
+ if (rc < 2)
+ return false;
+
+ state_probe = pstrdup(s);
+ *(state_probe + macro_ovect[0]) = '\0';
+ (void)clean_trailing_punct(state_probe);
+
+ rc = match((char *)us_ca_state_regex, state_probe, macro_ovect, PARSE_CASELESS);
+ pfree(state_probe);
+
+ return rc > 0;
+}
+
+/*
+ * Strip a trailing country token before macro parsing so postcode and state
+ * extraction still see the expected right-most fields. Known US/CA country
+ * tokens update the country code; other recognized country names are stripped
+ * without changing the default country inference.
+ */
+static bool
+extract_trailing_country(char *s, char *country_code)
+{
+ int split_ovect[OVECCOUNT];
+ int rc;
+ const char *matched_country_code;
+ char saved;
+
+ if (!s || !country_code)
+ return false;
+
+ rc = match("([\\s,]+)([A-Za-z][A-Za-z\\s]{1,})$", s, split_ovect, 0);
+ if (rc < 3)
+ return false;
+
+ saved = s[split_ovect[0]];
+ s[split_ovect[0]] = '\0';
+
+ matched_country_code = country_code_from_name(s + split_ovect[4]);
+ if (!matched_country_code)
+ {
+ s[split_ovect[0]] = saved;
+ return false;
+ }
+
+ if (!ends_with_supported_macro(s))
+ {
+ s[split_ovect[0]] = saved;
+ return false;
+ }
+
+ strcpy(country_code, matched_country_code);
+
+ (void)clean_trailing_punct(s);
+ return true;
+}
+
+bool
+strip_explicit_country_token(char *s, char *country_code)
+{
+ size_t normalized_length;
+
+ if (!s || !country_code)
+ return false;
+
+ normalized_length = normalize_address_input(s);
+ if (!normalized_length)
+ return false;
+
+ (void)clean_trailing_punct(s);
+ return extract_trailing_country(s, country_code);
+}
+
const char *
get_state_regex(const char *st)
{
@@ -253,11 +433,10 @@ parseaddress(HHash *stH, char *s, int *reterr)
#include "parseaddress-regex.h"
int ovect[OVECCOUNT];
- char *stregx;
- char *caregx;
char *state = NULL;
char *regx;
size_t normalized_length;
+ bool explicit_country = false;
int rc;
ADDRESS *ret;
#ifdef USE_HSEARCH
@@ -299,14 +478,16 @@ parseaddress(HHash *stH, char *s, int *reterr)
ret->cc = (char *)palloc0(3 * sizeof(char));
strcpy(ret->cc, "US");
+ explicit_country = extract_trailing_country(s, ret->cc);
+
/* get US zipcode components */
- rc = match("\\b(\\d{5})[-\\s]{0,1}?(\\d{0,4})?$", s, ovect, 0);
+ rc = match((char *)us_zip_regex, s, ovect, 0);
if (rc >= 2)
{
ret->zip = (char *)palloc0((ovect[3] - ovect[2] + 1) * sizeof(char));
strncpy(ret->zip, s + ovect[2], ovect[3] - ovect[2]);
- if (rc >= 3)
+ if (rc >= 3 && ovect[5] > ovect[4])
{
ret->zipplus = (char *)palloc0((ovect[5] - ovect[4] + 1) * sizeof(char));
strncpy(ret->zipplus, s + ovect[4], ovect[5] - ovect[4]);
@@ -317,27 +498,54 @@ parseaddress(HHash *stH, char *s, int *reterr)
/* get canada zipcode components */
else
{
- rc = match("\\b([a-z]\\d[a-z]\\s?\\d[a-z]\\d)$", s, ovect, PARSE_CASELESS);
+ rc = match((char *)canada_zip_regex, s, ovect, PARSE_CASELESS);
if (rc >= 1)
{
ret->zip = (char *)palloc0((ovect[1] - ovect[0] + 1) * sizeof(char));
strncpy(ret->zip, s + ovect[0], ovect[1] - ovect[0]);
- strcpy(ret->cc, "CA");
+ if (!explicit_country)
+ strcpy(ret->cc, "CA");
/* truncate the postalcode off the string */
*(s + ovect[0]) = '\0';
}
+ else
+ {
+ int state_ovect[OVECCOUNT];
+ char *state_probe;
+
+ /*
+ * Accept a short trailing ZIP fragment only when removing it exposes
+ * a valid trailing state/province. That keeps "MA 021" working
+ * without turning arbitrary suffixes like "Suite 201" into ZIPs.
+ */
+ rc = match("\\b(\\d{2,4})$", s, ovect, 0);
+ if (rc >= 2)
+ {
+ state_probe = pstrdup(s);
+ *(state_probe + ovect[0]) = '\0';
+ (void)clean_trailing_punct(state_probe);
+
+ rc = match((char *)us_ca_state_regex, state_probe, state_ovect, PARSE_CASELESS);
+ pfree(state_probe);
+
+ if (rc > 0)
+ {
+ ret->zip = (char *)palloc0((ovect[3] - ovect[2] + 1) * sizeof(char));
+ strncpy(ret->zip, s + ovect[2], ovect[3] - ovect[2]);
+ *(s + ovect[0]) = '\0';
+ }
+ }
+ }
}
/* clean trailing punctuation */
(void)clean_trailing_punct(s);
+ explicit_country = extract_trailing_country(s, ret->cc) || explicit_country;
+ (void)clean_trailing_punct(s);
/* get state components */
- caregx = "^(?-xism:(?i:(?=[abmnopqsy])(?:n[ltsu]|[am]b|[bq]c|on|pe|sk|yt)))$";
- stregx =
- "\\b(?-xism:(?i:(?=[abcdfghiklmnopqrstuvwy])(?:a(?:l(?:a(?:bam|sk)a|berta)?|mer(?:ican)?\\ samoa|r(?:k(?:ansas)?|izona)?|[kszb])|s(?:a(?:moa|skatchewan)|outh\\ (?:carolin|dakot)a|\\ (?:carolin|dakot)a|[cdk])|c(?:a(?:lif(?:ornia)?)?|o(?:nn(?:ecticut)?|lorado)?|t)|d(?:e(?:la(?:ware)?)?|istrict\\ of\\ columbia|c)|f(?:l(?:(?:orid)?a)?|ederal\\ states\\ of\\ micronesia|m)|m(?:i(?:c(?:h(?:igan)?|ronesia)|nn(?:esota)?|ss(?:(?:issipp|our)i)?)?|a(?:r(?:shall(?:\\ is(?:l(?:and)?)?)?|yland)|ss(?:achusetts)?|ine|nitoba)?|o(?:nt(?:ana)?)?|[ehdnstpb])|g(?:u(?:am)?|(?:eorgi)?a)|h(?:awai)?i|i(?:d(?:aho)?|l(?:l(?:inois)?)?|n(?:d(?:iana)?)?|(?:ow)?a)|k(?:(?:ansa)?s|(?:entuck)?y)|l(?:a(?:bordor)?|ouisiana)|n(?:e(?:w(?:\\ (?:foundland(?:\\ and\\ labordor)?|hampshire|jersey|mexico|(?:yor|brunswic)k)|foundland)|(?:brask|vad)a)?|o(?:rth(?:\\ (?:mariana(?:\\ is(?:l(?:and)?)?)?|(?:carolin|dakot)a)|west\\ territor(?:ies|y))|va\\ scotia)|\\ (?:carolin|dakot)a|u(?:navut)?|[vhjmycdblsf]|w?t)|o(?:h(?:io)?|k
(?:lahoma)?|r(?:egon)?|n(?:t(?:ario)?)?)|p(?:a(?:lau)?|e(?:nn(?:sylvania)?|i)?|r(?:ince\\ edward\\ island)?|w|uerto\\ rico)|r(?:hode\\ island|i)|t(?:e(?:nn(?:essee)?|xas)|[nx])|ut(?:ah)?|v(?:i(?:rgin(?:\\ islands|ia))?|(?:ermon)?t|a)|w(?:a(?:sh(?:ington)?)?|i(?:sc(?:onsin)?)?|y(?:oming)?|(?:est)?\\ virginia|v)|b(?:ritish\\ columbia|c)|q(?:uebe)?c|y(?:ukon|t))))$";
-
- rc = match(stregx, s, ovect, PARSE_CASELESS);
+ rc = match((char *)us_ca_state_regex, s, ovect, PARSE_CASELESS);
if (rc > 0)
{
state = (char *)palloc0((ovect[1] - ovect[0] + 1) * sizeof(char));
@@ -371,8 +579,8 @@ parseaddress(HHash *stH, char *s, int *reterr)
}
/* check if it a Canadian Province */
- rc = match(caregx, ret->st, ovect, PARSE_CASELESS);
- if (rc > 0)
+ rc = match((char *)canada_province_regex, ret->st, ovect, PARSE_CASELESS);
+ if (rc > 0 && !explicit_country)
{
strcpy(ret->cc, "CA");
// if (ret->cc) printf(" CC: %s\n", ret->cc);
diff --git a/extensions/address_standardizer/parseaddress-api.h b/extensions/address_standardizer/parseaddress-api.h
index 4dffb5ab8..bb47558e7 100644
--- a/extensions/address_standardizer/parseaddress-api.h
+++ b/extensions/address_standardizer/parseaddress-api.h
@@ -57,6 +57,8 @@ typedef struct address_struct {
int clean_trailing_punct(char *s);
void strtoupper(char *s);
int match(char *pattern, char *s, int *ovect, int options);
+const char *country_code_from_name(const char *country_name);
+bool strip_explicit_country_token(char *s, char *country_code);
ADDRESS *parseaddress(HHash *stH, char *s, int *err);
int load_state_hash(HHash *stH);
void free_state_hash(HHash *stH);
diff --git a/extensions/address_standardizer/parseaddress-countries.h b/extensions/address_standardizer/parseaddress-countries.h
new file mode 100644
index 000000000..3b94c4932
--- /dev/null
+++ b/extensions/address_standardizer/parseaddress-countries.h
@@ -0,0 +1,942 @@
+/*
+ * Country aliases for parse_address(), generated from iso-codes and
+ * a small compatibility alias set. Keep this list sorted by alias.
+ */
+{"ABW", "AW"},
+{"AD", "AD"},
+{"AE", "AE"},
+{"AF", "AF"},
+{"AFG", "AF"},
+{"AFGHANISTAN", "AF"},
+{"AG", "AG"},
+{"AGO", "AO"},
+{"AI", "AI"},
+{"AIA", "AI"},
+{"AL", "AL"},
+{"ALA", "AX"},
+{"ALAND ISLANDS", "AX"},
+{"ALB", "AL"},
+{"ALBANIA", "AL"},
+{"ALGERIA", "DZ"},
+{"AM", "AM"},
+{"AMERICAN SAMOA", "AS"},
+{"AND", "AD"},
+{"ANDORRA", "AD"},
+{"ANGOLA", "AO"},
+{"ANGUILLA", "AI"},
+{"ANTARCTICA", "AQ"},
+{"ANTIGUA AND BARBUDA", "AG"},
+{"AO", "AO"},
+{"AQ", "AQ"},
+{"AR", "AR"},
+{"ARAB REPUBLIC OF EGYPT", "EG"},
+{"ARE", "AE"},
+{"ARG", "AR"},
+{"ARGENTINA", "AR"},
+{"ARGENTINE REPUBLIC", "AR"},
+{"ARM", "AM"},
+{"ARMENIA", "AM"},
+{"ARUBA", "AW"},
+{"AS", "AS"},
+{"ASM", "AS"},
+{"AT", "AT"},
+{"ATA", "AQ"},
+{"ATF", "TF"},
+{"ATG", "AG"},
+{"AU", "AU"},
+{"AUS", "AU"},
+{"AUSTRALIA", "AU"},
+{"AUSTRIA", "AT"},
+{"AUT", "AT"},
+{"AW", "AW"},
+{"AX", "AX"},
+{"AZ", "AZ"},
+{"AZE", "AZ"},
+{"AZERBAIJAN", "AZ"},
+{"BA", "BA"},
+{"BAHAMAS", "BS"},
+{"BAHRAIN", "BH"},
+{"BANGLADESH", "BD"},
+{"BARBADOS", "BB"},
+{"BB", "BB"},
+{"BD", "BD"},
+{"BDI", "BI"},
+{"BE", "BE"},
+{"BEL", "BE"},
+{"BELARUS", "BY"},
+{"BELGIUM", "BE"},
+{"BELIZE", "BZ"},
+{"BEN", "BJ"},
+{"BENIN", "BJ"},
+{"BERMUDA", "BM"},
+{"BES", "BQ"},
+{"BF", "BF"},
+{"BFA", "BF"},
+{"BG", "BG"},
+{"BGD", "BD"},
+{"BGR", "BG"},
+{"BH", "BH"},
+{"BHR", "BH"},
+{"BHS", "BS"},
+{"BHUTAN", "BT"},
+{"BI", "BI"},
+{"BIH", "BA"},
+{"BJ", "BJ"},
+{"BL", "BL"},
+{"BLM", "BL"},
+{"BLR", "BY"},
+{"BLZ", "BZ"},
+{"BM", "BM"},
+{"BMU", "BM"},
+{"BN", "BN"},
+{"BO", "BO"},
+{"BOL", "BO"},
+{"BOLIVARIAN REPUBLIC OF VENEZUELA", "VE"},
+{"BOLIVIA", "BO"},
+{"BOLIVIA PLURINATIONAL STATE OF", "BO"},
+{"BONAIRE SINT EUSTATIUS AND SABA", "BQ"},
+{"BOSNIA AND HERZEGOVINA", "BA"},
+{"BOSNIA HERZEGOVINA", "BA"},
+{"BOTSWANA", "BW"},
+{"BOUVET ISLAND", "BV"},
+{"BQ", "BQ"},
+{"BR", "BR"},
+{"BRA", "BR"},
+{"BRAZIL", "BR"},
+{"BRB", "BB"},
+{"BRITAIN", "GB"},
+{"BRITISH INDIAN OCEAN TERRITORY", "IO"},
+{"BRITISH VIRGIN ISLANDS", "VG"},
+{"BRN", "BN"},
+{"BRUNEI DARUSSALAM", "BN"},
+{"BS", "BS"},
+{"BT", "BT"},
+{"BTN", "BT"},
+{"BULGARIA", "BG"},
+{"BURKINA FASO", "BF"},
+{"BURMA", "MM"},
+{"BURUNDI", "BI"},
+{"BV", "BV"},
+{"BVT", "BV"},
+{"BW", "BW"},
+{"BWA", "BW"},
+{"BY", "BY"},
+{"BZ", "BZ"},
+{"CA", "CA"},
+{"CABO VERDE", "CV"},
+{"CAF", "CF"},
+{"CAMBODIA", "KH"},
+{"CAMEROON", "CM"},
+{"CAN", "CA"},
+{"CANADA", "CA"},
+{"CAYMAN ISLANDS", "KY"},
+{"CC", "CC"},
+{"CCK", "CC"},
+{"CD", "CD"},
+{"CENTRAL AFRICAN REPUBLIC", "CF"},
+{"CF", "CF"},
+{"CG", "CG"},
+{"CH", "CH"},
+{"CHAD", "TD"},
+{"CHE", "CH"},
+{"CHILE", "CL"},
+{"CHINA", "CN"},
+{"CHL", "CL"},
+{"CHN", "CN"},
+{"CHRISTMAS ISLAND", "CX"},
+{"CI", "CI"},
+{"CIV", "CI"},
+{"CK", "CK"},
+{"CL", "CL"},
+{"CM", "CM"},
+{"CMR", "CM"},
+{"CN", "CN"},
+{"CO", "CO"},
+{"COCOS KEELING ISLANDS", "CC"},
+{"COD", "CD"},
+{"COG", "CG"},
+{"COK", "CK"},
+{"COL", "CO"},
+{"COLOMBIA", "CO"},
+{"COM", "KM"},
+{"COMMONWEALTH OF DOMINICA", "DM"},
+{"COMMONWEALTH OF THE BAHAMAS", "BS"},
+{"COMMONWEALTH OF THE NORTHERN MARIANA ISLANDS", "MP"},
+{"COMOROS", "KM"},
+{"CONGO", "CG"},
+{"CONGO THE DEMOCRATIC REPUBLIC OF THE", "CD"},
+{"COOK ISLANDS", "CK"},
+{"COSTA RICA", "CR"},
+{"COTE D IVOIRE", "CI"},
+{"CPV", "CV"},
+{"CR", "CR"},
+{"CRI", "CR"},
+{"CROATIA", "HR"},
+{"CU", "CU"},
+{"CUB", "CU"},
+{"CUBA", "CU"},
+{"CURACAO", "CW"},
+{"CUW", "CW"},
+{"CV", "CV"},
+{"CW", "CW"},
+{"CX", "CX"},
+{"CXR", "CX"},
+{"CY", "CY"},
+{"CYM", "KY"},
+{"CYP", "CY"},
+{"CYPRUS", "CY"},
+{"CZ", "CZ"},
+{"CZE", "CZ"},
+{"CZECH REPUBLIC", "CZ"},
+{"CZECHIA", "CZ"},
+{"DE", "DE"},
+{"DEMOCRATIC PEOPLE S REPUBLIC OF KOREA", "KP"},
+{"DEMOCRATIC REPUBLIC OF SAO TOME AND PRINCIPE", "ST"},
+{"DEMOCRATIC REPUBLIC OF TIMOR LESTE", "TL"},
+{"DEMOCRATIC SOCIALIST REPUBLIC OF SRI LANKA", "LK"},
+{"DENMARK", "DK"},
+{"DEU", "DE"},
+{"DJ", "DJ"},
+{"DJI", "DJ"},
+{"DJIBOUTI", "DJ"},
+{"DK", "DK"},
+{"DM", "DM"},
+{"DMA", "DM"},
+{"DNK", "DK"},
+{"DO", "DO"},
+{"DOM", "DO"},
+{"DOMINICA", "DM"},
+{"DOMINICAN REPUBLIC", "DO"},
+{"DZ", "DZ"},
+{"DZA", "DZ"},
+{"EAST TIMOR", "TL"},
+{"EASTERN REPUBLIC OF URUGUAY", "UY"},
+{"EC", "EC"},
+{"ECU", "EC"},
+{"ECUADOR", "EC"},
+{"EE", "EE"},
+{"EG", "EG"},
+{"EGY", "EG"},
+{"EGYPT", "EG"},
+{"EH", "EH"},
+{"EL SALVADOR", "SV"},
+{"EQUATORIAL GUINEA", "GQ"},
+{"ER", "ER"},
+{"ERI", "ER"},
+{"ERITREA", "ER"},
+{"ES", "ES"},
+{"ESH", "EH"},
+{"ESP", "ES"},
+{"EST", "EE"},
+{"ESTONIA", "EE"},
+{"ESWATINI", "SZ"},
+{"ET", "ET"},
+{"ETH", "ET"},
+{"ETHIOPIA", "ET"},
+{"FALKLAND ISLANDS MALVINAS", "FK"},
+{"FAROE ISLANDS", "FO"},
+{"FEDERAL DEMOCRATIC REPUBLIC OF ETHIOPIA", "ET"},
+{"FEDERAL DEMOCRATIC REPUBLIC OF NEPAL", "NP"},
+{"FEDERAL REPUBLIC OF GERMANY", "DE"},
+{"FEDERAL REPUBLIC OF NIGERIA", "NG"},
+{"FEDERAL REPUBLIC OF SOMALIA", "SO"},
+{"FEDERATED STATES OF MICRONESIA", "FM"},
+{"FEDERATIVE REPUBLIC OF BRAZIL", "BR"},
+{"FI", "FI"},
+{"FIJI", "FJ"},
+{"FIN", "FI"},
+{"FINLAND", "FI"},
+{"FJ", "FJ"},
+{"FJI", "FJ"},
+{"FK", "FK"},
+{"FLK", "FK"},
+{"FM", "FM"},
+{"FO", "FO"},
+{"FR", "FR"},
+{"FRA", "FR"},
+{"FRANCE", "FR"},
+{"FRENCH GUIANA", "GF"},
+{"FRENCH POLYNESIA", "PF"},
+{"FRENCH REPUBLIC", "FR"},
+{"FRENCH SOUTHERN TERRITORIES", "TF"},
+{"FRO", "FO"},
+{"FSM", "FM"},
+{"GA", "GA"},
+{"GAB", "GA"},
+{"GABON", "GA"},
+{"GABONESE REPUBLIC", "GA"},
+{"GAMBIA", "GM"},
+{"GB", "GB"},
+{"GBR", "GB"},
+{"GD", "GD"},
+{"GE", "GE"},
+{"GEO", "GE"},
+{"GEORGIA", "GE"},
+{"GERMANY", "DE"},
+{"GF", "GF"},
+{"GG", "GG"},
+{"GGY", "GG"},
+{"GH", "GH"},
+{"GHA", "GH"},
+{"GHANA", "GH"},
+{"GI", "GI"},
+{"GIB", "GI"},
+{"GIBRALTAR", "GI"},
+{"GIN", "GN"},
+{"GL", "GL"},
+{"GLP", "GP"},
+{"GM", "GM"},
+{"GMB", "GM"},
+{"GN", "GN"},
+{"GNB", "GW"},
+{"GNQ", "GQ"},
+{"GP", "GP"},
+{"GQ", "GQ"},
+{"GR", "GR"},
+{"GRAND DUCHY OF LUXEMBOURG", "LU"},
+{"GRC", "GR"},
+{"GRD", "GD"},
+{"GREAT BRITAIN", "GB"},
+{"GREECE", "GR"},
+{"GREENLAND", "GL"},
+{"GRENADA", "GD"},
+{"GRL", "GL"},
+{"GS", "GS"},
+{"GT", "GT"},
+{"GTM", "GT"},
+{"GU", "GU"},
+{"GUADELOUPE", "GP"},
+{"GUAM", "GU"},
+{"GUATEMALA", "GT"},
+{"GUERNSEY", "GG"},
+{"GUF", "GF"},
+{"GUINEA", "GN"},
+{"GUINEA BISSAU", "GW"},
+{"GUM", "GU"},
+{"GUY", "GY"},
+{"GUYANA", "GY"},
+{"GW", "GW"},
+{"GY", "GY"},
+{"HAITI", "HT"},
+{"HASHEMITE KINGDOM OF JORDAN", "JO"},
+{"HEARD ISLAND AND MCDONALD ISLANDS", "HM"},
+{"HELLENIC REPUBLIC", "GR"},
+{"HK", "HK"},
+{"HKG", "HK"},
+{"HM", "HM"},
+{"HMD", "HM"},
+{"HN", "HN"},
+{"HND", "HN"},
+{"HOLY SEE VATICAN CITY STATE", "VA"},
+{"HONDURAS", "HN"},
+{"HONG KONG", "HK"},
+{"HONG KONG SPECIAL ADMINISTRATIVE REGION OF CHINA", "HK"},
+{"HR", "HR"},
+{"HRV", "HR"},
+{"HT", "HT"},
+{"HTI", "HT"},
+{"HU", "HU"},
+{"HUN", "HU"},
+{"HUNGARY", "HU"},
+{"ICELAND", "IS"},
+{"ID", "ID"},
+{"IDN", "ID"},
+{"IE", "IE"},
+{"IL", "IL"},
+{"IM", "IM"},
+{"IMN", "IM"},
+{"IN", "IN"},
+{"IND", "IN"},
+{"INDEPENDENT STATE OF PAPUA NEW GUINEA", "PG"},
+{"INDEPENDENT STATE OF SAMOA", "WS"},
+{"INDIA", "IN"},
+{"INDONESIA", "ID"},
+{"IO", "IO"},
+{"IOT", "IO"},
+{"IQ", "IQ"},
+{"IR", "IR"},
+{"IRAN", "IR"},
+{"IRAN ISLAMIC REPUBLIC OF", "IR"},
+{"IRAQ", "IQ"},
+{"IRELAND", "IE"},
+{"IRL", "IE"},
+{"IRN", "IR"},
+{"IRQ", "IQ"},
+{"IS", "IS"},
+{"ISL", "IS"},
+{"ISLAMIC REPUBLIC OF AFGHANISTAN", "AF"},
+{"ISLAMIC REPUBLIC OF IRAN", "IR"},
+{"ISLAMIC REPUBLIC OF MAURITANIA", "MR"},
+{"ISLAMIC REPUBLIC OF PAKISTAN", "PK"},
+{"ISLE OF MAN", "IM"},
+{"ISR", "IL"},
+{"ISRAEL", "IL"},
+{"IT", "IT"},
+{"ITA", "IT"},
+{"ITALIAN REPUBLIC", "IT"},
+{"ITALY", "IT"},
+{"IVORY COAST", "CI"},
+{"JAM", "JM"},
+{"JAMAICA", "JM"},
+{"JAPAN", "JP"},
+{"JE", "JE"},
+{"JERSEY", "JE"},
+{"JEY", "JE"},
+{"JM", "JM"},
+{"JO", "JO"},
+{"JOR", "JO"},
+{"JORDAN", "JO"},
+{"JP", "JP"},
+{"JPN", "JP"},
+{"KAZ", "KZ"},
+{"KAZAKHSTAN", "KZ"},
+{"KE", "KE"},
+{"KEN", "KE"},
+{"KENYA", "KE"},
+{"KG", "KG"},
+{"KGZ", "KG"},
+{"KH", "KH"},
+{"KHM", "KH"},
+{"KI", "KI"},
+{"KINGDOM OF BAHRAIN", "BH"},
+{"KINGDOM OF BELGIUM", "BE"},
+{"KINGDOM OF BHUTAN", "BT"},
+{"KINGDOM OF CAMBODIA", "KH"},
+{"KINGDOM OF DENMARK", "DK"},
+{"KINGDOM OF ESWATINI", "SZ"},
+{"KINGDOM OF LESOTHO", "LS"},
+{"KINGDOM OF MOROCCO", "MA"},
+{"KINGDOM OF NORWAY", "NO"},
+{"KINGDOM OF SAUDI ARABIA", "SA"},
+{"KINGDOM OF SPAIN", "ES"},
+{"KINGDOM OF SWEDEN", "SE"},
+{"KINGDOM OF THAILAND", "TH"},
+{"KINGDOM OF THE NETHERLANDS", "NL"},
+{"KINGDOM OF TONGA", "TO"},
+{"KIR", "KI"},
+{"KIRIBATI", "KI"},
+{"KM", "KM"},
+{"KN", "KN"},
+{"KNA", "KN"},
+{"KOR", "KR"},
+{"KOREA DEMOCRATIC PEOPLE S REPUBLIC OF", "KP"},
+{"KOREA REPUBLIC OF", "KR"},
+{"KP", "KP"},
+{"KR", "KR"},
+{"KUWAIT", "KW"},
+{"KW", "KW"},
+{"KWT", "KW"},
+{"KY", "KY"},
+{"KYRGYZ REPUBLIC", "KG"},
+{"KYRGYZSTAN", "KG"},
+{"KZ", "KZ"},
+{"LA", "LA"},
+{"LAO", "LA"},
+{"LAO PEOPLE S DEMOCRATIC REPUBLIC", "LA"},
+{"LAOS", "LA"},
+{"LATVIA", "LV"},
+{"LB", "LB"},
+{"LBN", "LB"},
+{"LBR", "LR"},
+{"LBY", "LY"},
+{"LC", "LC"},
+{"LCA", "LC"},
+{"LEBANESE REPUBLIC", "LB"},
+{"LEBANON", "LB"},
+{"LESOTHO", "LS"},
+{"LI", "LI"},
+{"LIBERIA", "LR"},
+{"LIBYA", "LY"},
+{"LIE", "LI"},
+{"LIECHTENSTEIN", "LI"},
+{"LITHUANIA", "LT"},
+{"LK", "LK"},
+{"LKA", "LK"},
+{"LR", "LR"},
+{"LS", "LS"},
+{"LSO", "LS"},
+{"LT", "LT"},
+{"LTU", "LT"},
+{"LU", "LU"},
+{"LUX", "LU"},
+{"LUXEMBOURG", "LU"},
+{"LV", "LV"},
+{"LVA", "LV"},
+{"LY", "LY"},
+{"MA", "MA"},
+{"MAC", "MO"},
+{"MACAO", "MO"},
+{"MACAO SPECIAL ADMINISTRATIVE REGION OF CHINA", "MO"},
+{"MADAGASCAR", "MG"},
+{"MAF", "MF"},
+{"MALAWI", "MW"},
+{"MALAYSIA", "MY"},
+{"MALDIVES", "MV"},
+{"MALI", "ML"},
+{"MALTA", "MT"},
+{"MAR", "MA"},
+{"MARSHALL ISLANDS", "MH"},
+{"MARTINIQUE", "MQ"},
+{"MAURITANIA", "MR"},
+{"MAURITIUS", "MU"},
+{"MAYOTTE", "YT"},
+{"MC", "MC"},
+{"MCO", "MC"},
+{"MD", "MD"},
+{"MDA", "MD"},
+{"MDG", "MG"},
+{"MDV", "MV"},
+{"ME", "ME"},
+{"MEX", "MX"},
+{"MEXICO", "MX"},
+{"MF", "MF"},
+{"MG", "MG"},
+{"MH", "MH"},
+{"MHL", "MH"},
+{"MICRONESIA", "FM"},
+{"MICRONESIA FEDERATED STATES OF", "FM"},
+{"MK", "MK"},
+{"MKD", "MK"},
+{"ML", "ML"},
+{"MLI", "ML"},
+{"MLT", "MT"},
+{"MM", "MM"},
+{"MMR", "MM"},
+{"MN", "MN"},
+{"MNE", "ME"},
+{"MNG", "MN"},
+{"MNP", "MP"},
+{"MO", "MO"},
+{"MOLDOVA", "MD"},
+{"MOLDOVA REPUBLIC OF", "MD"},
+{"MONACO", "MC"},
+{"MONGOLIA", "MN"},
+{"MONTENEGRO", "ME"},
+{"MONTSERRAT", "MS"},
+{"MOROCCO", "MA"},
+{"MOZ", "MZ"},
+{"MOZAMBIQUE", "MZ"},
+{"MP", "MP"},
+{"MQ", "MQ"},
+{"MR", "MR"},
+{"MRT", "MR"},
+{"MS", "MS"},
+{"MSR", "MS"},
+{"MT", "MT"},
+{"MTQ", "MQ"},
+{"MU", "MU"},
+{"MUS", "MU"},
+{"MV", "MV"},
+{"MW", "MW"},
+{"MWI", "MW"},
+{"MX", "MX"},
+{"MY", "MY"},
+{"MYANMAR", "MM"},
+{"MYS", "MY"},
+{"MYT", "YT"},
+{"MZ", "MZ"},
+{"NA", "NA"},
+{"NAM", "NA"},
+{"NAMIBIA", "NA"},
+{"NAURU", "NR"},
+{"NC", "NC"},
+{"NCL", "NC"},
+{"NE", "NE"},
+{"NEPAL", "NP"},
+{"NER", "NE"},
+{"NETHERLANDS", "NL"},
+{"NEW CALEDONIA", "NC"},
+{"NEW ZEALAND", "NZ"},
+{"NF", "NF"},
+{"NFK", "NF"},
+{"NG", "NG"},
+{"NGA", "NG"},
+{"NI", "NI"},
+{"NIC", "NI"},
+{"NICARAGUA", "NI"},
+{"NIGER", "NE"},
+{"NIGERIA", "NG"},
+{"NIU", "NU"},
+{"NIUE", "NU"},
+{"NL", "NL"},
+{"NLD", "NL"},
+{"NO", "NO"},
+{"NOR", "NO"},
+{"NORFOLK ISLAND", "NF"},
+{"NORTH KOREA", "KP"},
+{"NORTH MACEDONIA", "MK"},
+{"NORTHERN MARIANA ISLANDS", "MP"},
+{"NORWAY", "NO"},
+{"NP", "NP"},
+{"NPL", "NP"},
+{"NR", "NR"},
+{"NRU", "NR"},
+{"NU", "NU"},
+{"NZ", "NZ"},
+{"NZL", "NZ"},
+{"OM", "OM"},
+{"OMAN", "OM"},
+{"OMN", "OM"},
+{"PA", "PA"},
+{"PAK", "PK"},
+{"PAKISTAN", "PK"},
+{"PALAU", "PW"},
+{"PALESTINE", "PS"},
+{"PALESTINE STATE OF", "PS"},
+{"PAN", "PA"},
+{"PANAMA", "PA"},
+{"PAPUA NEW GUINEA", "PG"},
+{"PARAGUAY", "PY"},
+{"PCN", "PN"},
+{"PE", "PE"},
+{"PEOPLE S DEMOCRATIC REPUBLIC OF ALGERIA", "DZ"},
+{"PEOPLE S REPUBLIC OF BANGLADESH", "BD"},
+{"PEOPLE S REPUBLIC OF CHINA", "CN"},
+{"PER", "PE"},
+{"PERU", "PE"},
+{"PF", "PF"},
+{"PG", "PG"},
+{"PH", "PH"},
+{"PHILIPPINES", "PH"},
+{"PHL", "PH"},
+{"PITCAIRN", "PN"},
+{"PK", "PK"},
+{"PL", "PL"},
+{"PLURINATIONAL STATE OF BOLIVIA", "BO"},
+{"PLW", "PW"},
+{"PM", "PM"},
+{"PN", "PN"},
+{"PNG", "PG"},
+{"POL", "PL"},
+{"POLAND", "PL"},
+{"PORTUGAL", "PT"},
+{"PORTUGUESE REPUBLIC", "PT"},
+{"PR", "PR"},
+{"PRI", "PR"},
+{"PRINCIPALITY OF ANDORRA", "AD"},
+{"PRINCIPALITY OF LIECHTENSTEIN", "LI"},
+{"PRINCIPALITY OF MONACO", "MC"},
+{"PRK", "KP"},
+{"PRT", "PT"},
+{"PRY", "PY"},
+{"PS", "PS"},
+{"PSE", "PS"},
+{"PT", "PT"},
+{"PUERTO RICO", "PR"},
+{"PW", "PW"},
+{"PY", "PY"},
+{"PYF", "PF"},
+{"QA", "QA"},
+{"QAT", "QA"},
+{"QATAR", "QA"},
+{"RE", "RE"},
+{"REPUBLIC OF ALBANIA", "AL"},
+{"REPUBLIC OF ANGOLA", "AO"},
+{"REPUBLIC OF ARMENIA", "AM"},
+{"REPUBLIC OF AUSTRIA", "AT"},
+{"REPUBLIC OF AZERBAIJAN", "AZ"},
+{"REPUBLIC OF BELARUS", "BY"},
+{"REPUBLIC OF BENIN", "BJ"},
+{"REPUBLIC OF BOSNIA AND HERZEGOVINA", "BA"},
+{"REPUBLIC OF BOTSWANA", "BW"},
+{"REPUBLIC OF BULGARIA", "BG"},
+{"REPUBLIC OF BURUNDI", "BI"},
+{"REPUBLIC OF CABO VERDE", "CV"},
+{"REPUBLIC OF CAMEROON", "CM"},
+{"REPUBLIC OF CHAD", "TD"},
+{"REPUBLIC OF CHILE", "CL"},
+{"REPUBLIC OF COLOMBIA", "CO"},
+{"REPUBLIC OF COSTA RICA", "CR"},
+{"REPUBLIC OF COTE D IVOIRE", "CI"},
+{"REPUBLIC OF CROATIA", "HR"},
+{"REPUBLIC OF CUBA", "CU"},
+{"REPUBLIC OF CYPRUS", "CY"},
+{"REPUBLIC OF DJIBOUTI", "DJ"},
+{"REPUBLIC OF ECUADOR", "EC"},
+{"REPUBLIC OF EL SALVADOR", "SV"},
+{"REPUBLIC OF EQUATORIAL GUINEA", "GQ"},
+{"REPUBLIC OF ESTONIA", "EE"},
+{"REPUBLIC OF FIJI", "FJ"},
+{"REPUBLIC OF FINLAND", "FI"},
+{"REPUBLIC OF GHANA", "GH"},
+{"REPUBLIC OF GUATEMALA", "GT"},
+{"REPUBLIC OF GUINEA", "GN"},
+{"REPUBLIC OF GUINEA BISSAU", "GW"},
+{"REPUBLIC OF GUYANA", "GY"},
+{"REPUBLIC OF HAITI", "HT"},
+{"REPUBLIC OF HONDURAS", "HN"},
+{"REPUBLIC OF ICELAND", "IS"},
+{"REPUBLIC OF INDIA", "IN"},
+{"REPUBLIC OF INDONESIA", "ID"},
+{"REPUBLIC OF IRAQ", "IQ"},
+{"REPUBLIC OF KAZAKHSTAN", "KZ"},
+{"REPUBLIC OF KENYA", "KE"},
+{"REPUBLIC OF KIRIBATI", "KI"},
+{"REPUBLIC OF LATVIA", "LV"},
+{"REPUBLIC OF LIBERIA", "LR"},
+{"REPUBLIC OF LITHUANIA", "LT"},
+{"REPUBLIC OF MADAGASCAR", "MG"},
+{"REPUBLIC OF MALAWI", "MW"},
+{"REPUBLIC OF MALDIVES", "MV"},
+{"REPUBLIC OF MALI", "ML"},
+{"REPUBLIC OF MALTA", "MT"},
+{"REPUBLIC OF MAURITIUS", "MU"},
+{"REPUBLIC OF MOLDOVA", "MD"},
+{"REPUBLIC OF MOZAMBIQUE", "MZ"},
+{"REPUBLIC OF MYANMAR", "MM"},
+{"REPUBLIC OF NAMIBIA", "NA"},
+{"REPUBLIC OF NAURU", "NR"},
+{"REPUBLIC OF NICARAGUA", "NI"},
+{"REPUBLIC OF NORTH MACEDONIA", "MK"},
+{"REPUBLIC OF PALAU", "PW"},
+{"REPUBLIC OF PANAMA", "PA"},
+{"REPUBLIC OF PARAGUAY", "PY"},
+{"REPUBLIC OF PERU", "PE"},
+{"REPUBLIC OF POLAND", "PL"},
+{"REPUBLIC OF SAN MARINO", "SM"},
+{"REPUBLIC OF SENEGAL", "SN"},
+{"REPUBLIC OF SERBIA", "RS"},
+{"REPUBLIC OF SEYCHELLES", "SC"},
+{"REPUBLIC OF SIERRA LEONE", "SL"},
+{"REPUBLIC OF SINGAPORE", "SG"},
+{"REPUBLIC OF SLOVENIA", "SI"},
+{"REPUBLIC OF SOUTH AFRICA", "ZA"},
+{"REPUBLIC OF SOUTH SUDAN", "SS"},
+{"REPUBLIC OF SURINAME", "SR"},
+{"REPUBLIC OF TAJIKISTAN", "TJ"},
+{"REPUBLIC OF THE CONGO", "CG"},
+{"REPUBLIC OF THE GAMBIA", "GM"},
+{"REPUBLIC OF THE MARSHALL ISLANDS", "MH"},
+{"REPUBLIC OF THE NIGER", "NE"},
+{"REPUBLIC OF THE PHILIPPINES", "PH"},
+{"REPUBLIC OF THE SUDAN", "SD"},
+{"REPUBLIC OF TRINIDAD AND TOBAGO", "TT"},
+{"REPUBLIC OF TUNISIA", "TN"},
+{"REPUBLIC OF TURKIYE", "TR"},
+{"REPUBLIC OF UGANDA", "UG"},
+{"REPUBLIC OF UZBEKISTAN", "UZ"},
+{"REPUBLIC OF VANUATU", "VU"},
+{"REPUBLIC OF YEMEN", "YE"},
+{"REPUBLIC OF ZAMBIA", "ZM"},
+{"REPUBLIC OF ZIMBABWE", "ZW"},
+{"REU", "RE"},
+{"REUNION", "RE"},
+{"RO", "RO"},
+{"ROMANIA", "RO"},
+{"ROU", "RO"},
+{"RS", "RS"},
+{"RU", "RU"},
+{"RUS", "RU"},
+{"RUSSIA", "RU"},
+{"RUSSIAN FEDERATION", "RU"},
+{"RW", "RW"},
+{"RWA", "RW"},
+{"RWANDA", "RW"},
+{"RWANDESE REPUBLIC", "RW"},
+{"SA", "SA"},
+{"SAINT BARTHELEMY", "BL"},
+{"SAINT HELENA ASCENSION AND TRISTAN DA CUNHA", "SH"},
+{"SAINT KITTS AND NEVIS", "KN"},
+{"SAINT LUCIA", "LC"},
+{"SAINT MARTIN FRENCH PART", "MF"},
+{"SAINT PIERRE AND MIQUELON", "PM"},
+{"SAINT VINCENT AND THE GRENADINES", "VC"},
+{"SAMOA", "WS"},
+{"SAN MARINO", "SM"},
+{"SAO TOME AND PRINCIPE", "ST"},
+{"SAU", "SA"},
+{"SAUDI ARABIA", "SA"},
+{"SB", "SB"},
+{"SC", "SC"},
+{"SD", "SD"},
+{"SDN", "SD"},
+{"SE", "SE"},
+{"SEN", "SN"},
+{"SENEGAL", "SN"},
+{"SERBIA", "RS"},
+{"SEYCHELLES", "SC"},
+{"SG", "SG"},
+{"SGP", "SG"},
+{"SGS", "GS"},
+{"SH", "SH"},
+{"SHN", "SH"},
+{"SI", "SI"},
+{"SIERRA LEONE", "SL"},
+{"SINGAPORE", "SG"},
+{"SINT MAARTEN DUTCH PART", "SX"},
+{"SJ", "SJ"},
+{"SJM", "SJ"},
+{"SK", "SK"},
+{"SL", "SL"},
+{"SLB", "SB"},
+{"SLE", "SL"},
+{"SLOVAK REPUBLIC", "SK"},
+{"SLOVAKIA", "SK"},
+{"SLOVENIA", "SI"},
+{"SLV", "SV"},
+{"SM", "SM"},
+{"SMR", "SM"},
+{"SN", "SN"},
+{"SO", "SO"},
+{"SOCIALIST REPUBLIC OF VIET NAM", "VN"},
+{"SOLOMON ISLANDS", "SB"},
+{"SOM", "SO"},
+{"SOMALIA", "SO"},
+{"SOUTH AFRICA", "ZA"},
+{"SOUTH GEORGIA AND THE SOUTH SANDWICH ISLANDS", "GS"},
+{"SOUTH KOREA", "KR"},
+{"SOUTH SUDAN", "SS"},
+{"SPAIN", "ES"},
+{"SPM", "PM"},
+{"SR", "SR"},
+{"SRB", "RS"},
+{"SRI LANKA", "LK"},
+{"SS", "SS"},
+{"SSD", "SS"},
+{"ST", "ST"},
+{"STATE OF ISRAEL", "IL"},
+{"STATE OF KUWAIT", "KW"},
+{"STATE OF QATAR", "QA"},
+{"STP", "ST"},
+{"SUDAN", "SD"},
+{"SULTANATE OF OMAN", "OM"},
+{"SUR", "SR"},
+{"SURINAME", "SR"},
+{"SV", "SV"},
+{"SVALBARD AND JAN MAYEN", "SJ"},
+{"SVK", "SK"},
+{"SVN", "SI"},
+{"SWE", "SE"},
+{"SWEDEN", "SE"},
+{"SWISS CONFEDERATION", "CH"},
+{"SWITZERLAND", "CH"},
+{"SWZ", "SZ"},
+{"SX", "SX"},
+{"SXM", "SX"},
+{"SY", "SY"},
+{"SYC", "SC"},
+{"SYR", "SY"},
+{"SYRIA", "SY"},
+{"SYRIAN ARAB REPUBLIC", "SY"},
+{"SZ", "SZ"},
+{"TAIWAN", "TW"},
+{"TAIWAN PROVINCE OF CHINA", "TW"},
+{"TAJIKISTAN", "TJ"},
+{"TANZANIA", "TZ"},
+{"TANZANIA UNITED REPUBLIC OF", "TZ"},
+{"TC", "TC"},
+{"TCA", "TC"},
+{"TCD", "TD"},
+{"TD", "TD"},
+{"TF", "TF"},
+{"TG", "TG"},
+{"TGO", "TG"},
+{"TH", "TH"},
+{"THA", "TH"},
+{"THAILAND", "TH"},
+{"THE STATE OF ERITREA", "ER"},
+{"THE STATE OF PALESTINE", "PS"},
+{"TIMOR LESTE", "TL"},
+{"TJ", "TJ"},
+{"TJK", "TJ"},
+{"TK", "TK"},
+{"TKL", "TK"},
+{"TKM", "TM"},
+{"TL", "TL"},
+{"TLS", "TL"},
+{"TM", "TM"},
+{"TN", "TN"},
+{"TO", "TO"},
+{"TOGO", "TG"},
+{"TOGOLESE REPUBLIC", "TG"},
+{"TOKELAU", "TK"},
+{"TON", "TO"},
+{"TONGA", "TO"},
+{"TR", "TR"},
+{"TRINIDAD AND TOBAGO", "TT"},
+{"TT", "TT"},
+{"TTO", "TT"},
+{"TUN", "TN"},
+{"TUNISIA", "TN"},
+{"TUR", "TR"},
+{"TURKEY", "TR"},
+{"TURKIYE", "TR"},
+{"TURKMENISTAN", "TM"},
+{"TURKS AND CAICOS ISLANDS", "TC"},
+{"TUV", "TV"},
+{"TUVALU", "TV"},
+{"TV", "TV"},
+{"TW", "TW"},
+{"TWN", "TW"},
+{"TZ", "TZ"},
+{"TZA", "TZ"},
+{"U K", "GB"},
+{"U S", "US"},
+{"U S A", "US"},
+{"UA", "UA"},
+{"UG", "UG"},
+{"UGA", "UG"},
+{"UGANDA", "UG"},
+{"UK", "GB"},
+{"UKR", "UA"},
+{"UKRAINE", "UA"},
+{"UM", "UM"},
+{"UMI", "UM"},
+{"UNION OF THE COMOROS", "KM"},
+{"UNITED ARAB EMIRATES", "AE"},
+{"UNITED KINGDOM", "GB"},
+{"UNITED KINGDOM OF GREAT BRITAIN AND NORTHERN IRELAND", "GB"},
+{"UNITED MEXICAN STATES", "MX"},
+{"UNITED REPUBLIC OF TANZANIA", "TZ"},
+{"UNITED STATES", "US"},
+{"UNITED STATES MINOR OUTLYING ISLANDS", "UM"},
+{"UNITED STATES OF AMERICA", "US"},
+{"URUGUAY", "UY"},
+{"URY", "UY"},
+{"US", "US"},
+{"USA", "US"},
+{"UY", "UY"},
+{"UZ", "UZ"},
+{"UZB", "UZ"},
+{"UZBEKISTAN", "UZ"},
+{"VA", "VA"},
+{"VANUATU", "VU"},
+{"VAT", "VA"},
+{"VATICAN CITY", "VA"},
+{"VC", "VC"},
+{"VCT", "VC"},
+{"VE", "VE"},
+{"VEN", "VE"},
+{"VENEZUELA", "VE"},
+{"VENEZUELA BOLIVARIAN REPUBLIC OF", "VE"},
+{"VG", "VG"},
+{"VGB", "VG"},
+{"VI", "VI"},
+{"VIET NAM", "VN"},
+{"VIETNAM", "VN"},
+{"VIR", "VI"},
+{"VIRGIN ISLANDS BRITISH", "VG"},
+{"VIRGIN ISLANDS OF THE UNITED STATES", "VI"},
+{"VIRGIN ISLANDS U S", "VI"},
+{"VN", "VN"},
+{"VNM", "VN"},
+{"VU", "VU"},
+{"VUT", "VU"},
+{"WALLIS AND FUTUNA", "WF"},
+{"WESTERN SAHARA", "EH"},
+{"WF", "WF"},
+{"WLF", "WF"},
+{"WS", "WS"},
+{"WSM", "WS"},
+{"YE", "YE"},
+{"YEM", "YE"},
+{"YEMEN", "YE"},
+{"YT", "YT"},
+{"ZA", "ZA"},
+{"ZAF", "ZA"},
+{"ZAMBIA", "ZM"},
+{"ZIMBABWE", "ZW"},
+{"ZM", "ZM"},
+{"ZMB", "ZM"},
+{"ZW", "ZW"},
+{"ZWE", "ZW"},
diff --git a/extensions/address_standardizer/test-parseaddress.sql.in b/extensions/address_standardizer/test-parseaddress.sql.in
index b2a1224c0..f327354f5 100644
--- a/extensions/address_standardizer/test-parseaddress.sql.in
+++ b/extensions/address_standardizer/test-parseaddress.sql.in
@@ -69,6 +69,15 @@ copy test_parse_address (instring, outstring) from stdin;
@@ sttype words$
123 oak ln, marie ny (123,"oak ln",,"123 oak ln",marie,NY,,,US)
123 oak ln, new marie ny (123,"oak ln",,"123 oak ln","new marie",NY,,,US)
+529 Main Street, Boston, MA 021 (529,"Main Street",,"529 Main Street",Boston,MA,021,,US)
+529 Main Street, Boston, MA 021, France (529,"Main Street",,"529 Main Street",Boston,MA,021,,FR)
+100 Peachtree St, Atlanta, Georgia (100,"Peachtree St",,"100 Peachtree St",Atlanta,GA,,,US)
+212 n 3rd ave, Minneapolis, mn 55401, USA (212,"n 3rd ave",,"212 n 3rd ave",Minneapolis,MN,55401,,US)
+26 Court Street, Boston, Massachusetts 02109, FR (26,"Court Street",,"26 Court Street",Boston,MA,02109,,FR)
+26 Court Street, Boston, MA 02109, Rear (26,"Court Street, Boston, MA 02109",,"26 Court Street, Boston, MA 02109",Rear,,,,US)
+212 N 3rd Ave, Minneapolis, MN 55401, Suite A (212,"N 3rd Ave, Minneapolis, MN 55401",,"212 N 3rd Ave, Minneapolis, MN 55401","Suite A",,,,US)
+26 Court Street, Boston, Massachusetts 02109, France (26,"Court Street",,"26 Court Street",Boston,MA,02109,,FR)
+10 Main Street, Toronto, ON M5V 2T6, France (10,"Main Street",,"10 Main Street",Toronto,ON,"M5V 2T6",,FR)
\.
select id, instring, outstring as expected, parse_address(instring) as got_result
diff --git a/extensions/postgis_tiger_geocoder/Makefile.in b/extensions/postgis_tiger_geocoder/Makefile.in
index e0faf1caf..d2b5029d4 100644
--- a/extensions/postgis_tiger_geocoder/Makefile.in
+++ b/extensions/postgis_tiger_geocoder/Makefile.in
@@ -30,7 +30,7 @@ DATA_built = \
$(NULL)
REGRESS = test-normalize_address test-upgrade
-REGRESS_OPTS = --load-extension=fuzzystrmatch --load-extension=postgis --load-extension=$(EXTENSION)
+REGRESS_OPTS = --load-extension=fuzzystrmatch --load-extension=postgis
EXTSCHEMA_SUPPORTED = yes
## PostgreSQL < 16 doesn't understand new @extschema:extname@ syntax, so strip it
@@ -46,6 +46,7 @@ ifeq (@ADDRESS_STANDARDIZER@,address_standardizer)
REGRESS += test-pagc_normalize_address
REGRESS_OPTS += --load-extension=address_standardizer
endif
+REGRESS_OPTS += --load-extension=$(EXTENSION)
all: sql/$(EXTENSION)_pre.sql sql/$(EXTENSION)--$(EXTVERSION).sql sql/$(EXTENSION)--unpackaged--$(EXTVERSION).sql sql/$(EXTENSION)--ANY--$(EXTVERSION).sql sql/test-normalize_address.sql sql/test-pagc_normalize_address.sql expected/test-normalize_address.out expected/test-pagc_normalize_address.out sql/test-upgrade.sql expected/test-upgrade.out
diff --git a/extensions/postgis_tiger_geocoder/postgis_tiger_geocoder.control.in b/extensions/postgis_tiger_geocoder/postgis_tiger_geocoder.control.in
index 647cba6b8..ed256b89d 100644
--- a/extensions/postgis_tiger_geocoder/postgis_tiger_geocoder.control.in
+++ b/extensions/postgis_tiger_geocoder/postgis_tiger_geocoder.control.in
@@ -5,4 +5,4 @@ relocatable = false
schema = tiger
requires = 'postgis,fuzzystrmatch'
superuser= false
-no_relocate = 'postgis,fuzzystrmatch'
\ No newline at end of file
+no_relocate = 'postgis,fuzzystrmatch'
diff --git a/extensions/postgis_tiger_geocoder/sql_bits/norm_addy_create.sql.in b/extensions/postgis_tiger_geocoder/sql_bits/norm_addy_create.sql.in
index 7b70bf876..121548f8b 100644
--- a/extensions/postgis_tiger_geocoder/sql_bits/norm_addy_create.sql.in
+++ b/extensions/postgis_tiger_geocoder/sql_bits/norm_addy_create.sql.in
@@ -9,5 +9,6 @@ CREATE TYPE norm_addy AS (
stateAbbrev VARCHAR,
zip VARCHAR,
parsed BOOLEAN,
- zip4 varchar,
- address_alphanumeric varchar);
+ zip4 varchar(4),
+ address_alphanumeric varchar,
+ country varchar(2));
diff --git a/extensions/postgis_tiger_geocoder/sql_bits/norm_addy_upgrade.sql.in b/extensions/postgis_tiger_geocoder/sql_bits/norm_addy_upgrade.sql.in
index 99bf4364c..77561fa14 100644
--- a/extensions/postgis_tiger_geocoder/sql_bits/norm_addy_upgrade.sql.in
+++ b/extensions/postgis_tiger_geocoder/sql_bits/norm_addy_upgrade.sql.in
@@ -1,2 +1,3 @@
ALTER TYPE tiger.norm_addy ADD ATTRIBUTE zip4 varchar(4);
-ALTER TYPE tiger.norm_addy ADD ATTRIBUTE address_alphanumeric varchar;
\ No newline at end of file
+ALTER TYPE tiger.norm_addy ADD ATTRIBUTE address_alphanumeric varchar;
+ALTER TYPE tiger.norm_addy ADD ATTRIBUTE country varchar(2);
diff --git a/extensions/postgis_tiger_geocoder/sql_bits/upgrade_before.sql.in b/extensions/postgis_tiger_geocoder/sql_bits/upgrade_before.sql.in
index 5bba06428..bcaba281d 100644
--- a/extensions/postgis_tiger_geocoder/sql_bits/upgrade_before.sql.in
+++ b/extensions/postgis_tiger_geocoder/sql_bits/upgrade_before.sql.in
@@ -1,10 +1,41 @@
-- these introduced in PostGIS 2.4
DO language plpgsql
$$
- BEGIN
- ALTER TYPE tiger.norm_addy ADD ATTRIBUTE zip4 varchar;
+DECLARE
+ norm_addy_relid oid := (SELECT typrelid FROM pg_type WHERE oid = 'tiger.norm_addy'::regtype);
+BEGIN
+ /*
+ * Check each attribute independently so upgrades from releases that already
+ * have zip4/address_alphanumeric still pick up newer additions like country.
+ */
+ IF NOT EXISTS (
+ SELECT 1
+ FROM pg_attribute
+ WHERE attrelid = norm_addy_relid
+ AND attname = 'zip4'
+ AND NOT attisdropped
+ ) THEN
+ ALTER TYPE tiger.norm_addy ADD ATTRIBUTE zip4 varchar(4);
+ END IF;
+
+ IF NOT EXISTS (
+ SELECT 1
+ FROM pg_attribute
+ WHERE attrelid = norm_addy_relid
+ AND attname = 'address_alphanumeric'
+ AND NOT attisdropped
+ ) THEN
ALTER TYPE tiger.norm_addy ADD ATTRIBUTE address_alphanumeric varchar;
- EXCEPTION
- WHEN others THEN -- ignore the error probably cause it already exists
- END;
-$$;
\ No newline at end of file
+ END IF;
+
+ IF NOT EXISTS (
+ SELECT 1
+ FROM pg_attribute
+ WHERE attrelid = norm_addy_relid
+ AND attname = 'country'
+ AND NOT attisdropped
+ ) THEN
+ ALTER TYPE tiger.norm_addy ADD ATTRIBUTE country varchar(2);
+ END IF;
+END;
+$$;
diff --git a/extras/tiger_geocoder/create_geocode.sql b/extras/tiger_geocoder/create_geocode.sql
index 58647c154..daaa98f33 100644
--- a/extras/tiger_geocoder/create_geocode.sql
+++ b/extras/tiger_geocoder/create_geocode.sql
@@ -34,7 +34,7 @@ CREATE TYPE norm_addy AS (
location VARCHAR,
stateAbbrev VARCHAR,
zip VARCHAR,
- parsed BOOLEAN, zip4 varchar, address_alphanumeric varchar);
+ parsed BOOLEAN, zip4 varchar(4), address_alphanumeric varchar, country varchar(2));
-- System/General helper functions
\i utility/utmzone.sql
@@ -82,4 +82,4 @@ COMMIT;
-- Tiger to PostGIS Topology
-- only usable if you have topology installed
-\i topology/tiger_topology_loader.sql
\ No newline at end of file
+\i topology/tiger_topology_loader.sql
diff --git a/extras/tiger_geocoder/normalize/normalize_address.sql b/extras/tiger_geocoder/normalize/normalize_address.sql
index 7af89b318..59bd9f148 100644
--- a/extras/tiger_geocoder/normalize/normalize_address.sql
+++ b/extras/tiger_geocoder/normalize/normalize_address.sql
@@ -71,6 +71,8 @@ DECLARE
rec RECORD;
ws VARCHAR;
rawInput VARCHAR;
+ parse_address_helper oid := to_regprocedure('parse_address(text)');
+ strip_country_helper oid := to_regprocedure('strip_explicit_country(text)');
-- is this a highway
-- (we treat these differently since the road name often comes after the streetType)
isHighway boolean := false;
@@ -87,6 +89,22 @@ BEGIN
RETURN result;
END IF;
+ /*
+ * Legacy tiger SQL can still be installed without address_standardizer.
+ * Probe for the newer country helpers before using them.
+ */
+ IF parse_address_helper IS NOT NULL THEN
+ EXECUTE 'SELECT NULLIF((parse_address($1)).country, '''')'
+ INTO result.country
+ USING rawInput;
+ END IF;
+
+ IF strip_country_helper IS NOT NULL THEN
+ EXECUTE 'SELECT strip_explicit_country($1)'
+ INTO rawInput
+ USING rawInput;
+ END IF;
+
ws := E'[ ,.\t\n\f\r]';
IF debug_flag THEN
diff --git a/extras/tiger_geocoder/normalize/pprint_addy.sql b/extras/tiger_geocoder/normalize/pprint_addy.sql
index 900ee952b..f9ad31347 100644
--- a/extras/tiger_geocoder/normalize/pprint_addy.sql
+++ b/extras/tiger_geocoder/normalize/pprint_addy.sql
@@ -37,7 +37,13 @@ BEGIN
|| COALESCE(input.stateAbbrev || ' ' , '')
|| cull_null(input.zip) || COALESCE('-' || input.zip4, '');
- RETURN trim(result);
+ result := trim(result);
+
+ IF result <> '' AND input.country IS NOT NULL THEN
+ result := result || ', ' || input.country;
+ END IF;
+
+ RETURN result;
END;
$_$ LANGUAGE plpgsql IMMUTABLE;
diff --git a/extras/tiger_geocoder/pagc_normalize/pagc_normalize_address.sql b/extras/tiger_geocoder/pagc_normalize/pagc_normalize_address.sql
index e1acdaddc..befce7643 100644
--- a/extras/tiger_geocoder/pagc_normalize/pagc_normalize_address.sql
+++ b/extras/tiger_geocoder/pagc_normalize/pagc_normalize_address.sql
@@ -20,10 +20,12 @@ BEGIN
rawInput := trim(in_rawinput);
var_parse_rec := parse_address(rawInput);
+
result.location := var_parse_rec.city;
result.stateAbbrev := trim(var_parse_rec.state);
result.zip := var_parse_rec.zip;
result.zip4 := NULLIF(var_parse_rec.zipplus,'');
+ result.country := NULLIF(var_parse_rec.country,'');
var_rec := standardize_address('pagc_lex'
, 'pagc_gaz'
@@ -39,6 +41,7 @@ BEGIN
result.streetName := trim(var_rec.name);
result.location := trim(var_rec.city);
result.stateAbbrev := trim(var_rec.state);
+ result.country := COALESCE(NULLIF(var_rec.country,''), result.country);
--this should be broken out separately like pagc, but normalizer doesn't have a slot for it
result.streettypeAbbrev := trim(COALESCE(var_rec.suftype, var_rec.pretype));
result.preDirAbbrev := trim(var_rec.predir);
@@ -49,4 +52,4 @@ BEGIN
END
$$
LANGUAGE plpgsql IMMUTABLE STRICT
- COST 100;
\ No newline at end of file
+ COST 100;
diff --git a/extras/tiger_geocoder/regress/normalize_address_regress b/extras/tiger_geocoder/regress/normalize_address_regress
index 6a417acaa..676bb7698 100644
--- a/extras/tiger_geocoder/regress/normalize_address_regress
+++ b/extras/tiger_geocoder/regress/normalize_address_regress
@@ -1,45 +1,51 @@
-#887|2450|N|COLORADO|St|||PHILADELPHIA|PA|19132|t||2450
-#1051a|212||3rd|Ave|N|Suite 560|Minneapolis|MN|55401|t||212
-#1051b|3937||43RD|Ave|S||MINNEAPOLIS|MN|55406|t||3937
-#1051c|212|N|3rd|Ave|||Minneapolis|MN|55401|t||212
-#1051d|212||3rd|Ave|N||Minneapolis|MN|55401|t||212
-529||Main|St|||Boston|MA|02129|t||529
-529||Main|St|||Boston|MA|02129|t||529
-529||Main|St|||Boston|MA||t||529
-529||Main|St||Apt 201|Boston|MA|02129|t||529
-529||Main|St||Apt 201|Boston|MA|02129|t||529
-529||Main|St||Apt 201|Boston|MA||t||529
-#1108a|529||Main|St||Suite 201|Boston|MA|02129|t||529
-#1073a|212||3rd|Ave|N||MINNEAPOLIS|MN|553404|t|4|212
-#1073b|212||3rd|Ave|N||MINNEAPOLIS|MN|55401|t||212
-#1073c|529||Main|St|||Boston|MA|021|t||529
-#1086a|949|N|3rd|St|||New Hyde Park|NY|11040|t||949
-#1086b|949|N|3rd|St|||New Hyde Park|NY|11040|t||949
-#1076a|16725||24|Co Rd|||Plymouth|MN|55447|t||16725
-#1076b|16725||24|Co Rd|||Plymouth|MN|55447|t||16725
-#1076c|13800||9|Co Hwy|||Andover|MN|55304|t||13800
-#1076d|13800||9||||Andover|MN|55304|t||13800
-#1076e|14||Forest|Rd|||Acton|MA||t||14
-#1076f|1940||C|Co Rd|W||Roseville|MN|55113|t||1940
-#1076g|3900||6|Rte|||Eastham|MA|02642|t||3900
-#1076h|4533||PARK|Ave|S||MINNEAPOLIS|MN|55407|t||4533
-#1076i|4533||33|Co Rd|||MINNEAPOLIS|MN|55407|t||4533
-#1109a|4373||LAKE|Dr|||ROBBINSDALE|MN|55422|t||4373
-#1109b|4373||LAKE|Dr|||ROBBINSDALE|MN|55422|t||4373
-#1074a|3420||RHODE ISLAND|Ave|S||ST. LOUIS PARK|MN|55426|t||3420
-#1074b|26||Court|St|||Boston|MA|02109|t||26
-#1112a|8401|W|35W|Svc Dr|NE||Blaine|MN|55449|t||8401
-#1112b|8401||35W||||Blaine|MN|55449|t||8401
-#1112c|8401||35W||W||Blaine|MN|55449|t||8401
-#1112d|8401|W|35W||||Blaine|MN|55449|t||8401
-#1112e|8401|W|35W||||Blaine|MN|55449|t||8401
-#1125a|19596 Co Rd 480, COLCORD, OK 74338
-#1125b|4345 353 Rte, SALAMANCA, NY 14779|4345||353|Rte|||SALAMANCA|NY|14779|t||4345
-#1125c|19799 State Rte O, COSBY, MO 64436|19799||O|State Rte|||COSBY|MO|64436|t||19799
-#1125d|Interstate Interstate 90, Boston, MA|||Interstate 90||||Boston|MA||t||Interstate
-#1125e|I-90 I-90,Boston, MA|||I-90,Boston|||||MA||t||I-90
-#1125f|I I 90, Boston, MA|||I 90||||Boston|MA||t||I
-#1310a|1110 W CAPITOL Ave, WEST SACRAMENTO, CA|1110|W|CAPITOL|Ave|||WEST SACRAMENTO|CA||t||1110
-#1614a|8435 Co Rd 20 SE, ROCHESTER, MN 55904|8435||20|Co Rd|SE||ROCHESTER|MN|55904|t||8435
-#1614b|3208 US Hwy 52, Rochester, MN 55901|3208||52|US Hwy|||Rochester|MN|55901|t||3208
-#1108a|529 Main St, Suite 201, Boston, MA 02129|529||Main|St||Suite 201|Boston|MA|02129|t||529
+#887|2450|N|COLORADO|St|||PHILADELPHIA|PA|19132|t||2450|US
+#1051a|212||3rd|Ave|N|Suite 560|Minneapolis|MN|55401|t||212|US
+#1051b|3937||43RD|Ave|S||MINNEAPOLIS|MN|55406|t||3937|US
+#1051c|212|N|3rd|Ave|||Minneapolis|MN|55401|t||212|US
+#1599|212|N|3rd|Ave|||Minneapolis|MN|55401|t||212|US
+#1599b|212|N|3rd|Ave|||Minneapolis|MN|55401|t||212|FR
+#1599c|26||Court|St|||Boston|MA|02109|t||26|FR
+#1599d|529||Main|St|||Boston|MA|021|t||529|FR
+#1599e|26||Court|St|||Boston|MA|02109|t||26|FR
+#1051d|212||3rd|Ave|N||Minneapolis|MN|55401|t||212|US
+529||Main|St|||Boston|MA|02129|t||529|US
+529||Main|St|||Boston|MA|02129|t||529|US
+529||Main|St|||Boston|MA||t||529|US
+529||Main|St||Apt 201|Boston|MA|02129|t||529|US
+529||Main|St||Apt 201|Boston|MA|02129|t||529|US
+529||Main|St||Apt 201|Boston|MA||t||529|US
+#1108a|529||Main|St||Suite 201|Boston|MA|02129|t||529|US
+#1073a|212||3rd|Ave|N||MINNEAPOLIS|MN|553404|t|4|212|US
+#1073b|212||3rd|Ave|N||MINNEAPOLIS|MN|55401|t||212|US
+#1073c|529||Main|St|||Boston|MA|021|t||529|US
+#1086a|949|N|3rd|St|||New Hyde Park|NY|11040|t||949|US
+#1086b|949|N|3rd|St|||New Hyde Park|NY|11040|t||949|US
+#1076a|16725||24|Co Rd|||Plymouth|MN|55447|t||16725|US
+#1076b|16725||24|Co Rd|||Plymouth|MN|55447|t||16725|US
+#1076c|13800||9|Co Hwy|||Andover|MN|55304|t||13800|US
+#1076d|13800||9||||Andover|MN|55304|t||13800|US
+#1076e|14||Forest|Rd|||Acton|MA||t||14|US
+#1076f|1940||C|Co Rd|W||Roseville|MN|55113|t||1940|US
+#1076g|3900||6|Rte|||Eastham|MA|02642|t||3900|US
+#1076h|4533||PARK|Ave|S||MINNEAPOLIS|MN|55407|t||4533|US
+#1076i|4533||33|Co Rd|||MINNEAPOLIS|MN|55407|t||4533|US
+#1109a|4373||LAKE|Dr|||ROBBINSDALE|MN|55422|t||4373|US
+#1109b|4373||LAKE|Dr|||ROBBINSDALE|MN|55422|t||4373|US
+#1074a|3420||RHODE ISLAND|Ave|S||ST. LOUIS PARK|MN|55426|t||3420|US
+#1074b|26||Court|St|||Boston|MA|02109|t||26|US
+#1112a|8401|W|35W|Svc Dr|NE||Blaine|MN|55449|t||8401|US
+#1112b|8401||35W||||Blaine|MN|55449|t||8401|US
+#1112c|8401||35W||W||Blaine|MN|55449|t||8401|US
+#1112d|8401|W|35W||||Blaine|MN|55449|t||8401|US
+#1112e|8401|W|35W||||Blaine|MN|55449|t||8401|US
+#1125a|19596 Co Rd 480, COLCORD, OK 74338, US
+#1125b|4345 353 Rte, SALAMANCA, NY 14779, US|4345||353|Rte|||SALAMANCA|NY|14779|t||4345|US
+#1125c|19799 State Rte O, COSBY, MO 64436, US|19799||O|State Rte|||COSBY|MO|64436|t||19799|US
+#1125d|Interstate Interstate 90, Boston, MA, US|||Interstate 90||||Boston|MA||t||Interstate|US
+#1125e|I-90 I-90,Boston, MA, US|||I-90,Boston|||||MA||t||I-90|US
+#1125f|I I 90, Boston, MA, US|||I 90||||Boston|MA||t||I|US
+#1599g|
+#1310a|1110 W CAPITOL Ave, WEST SACRAMENTO, CA, US|1110|W|CAPITOL|Ave|||WEST SACRAMENTO|CA||t||1110|US
+#1614a|8435 Co Rd 20 SE, ROCHESTER, MN 55904, US|8435||20|Co Rd|SE||ROCHESTER|MN|55904|t||8435|US
+#1614b|3208 US Hwy 52, Rochester, MN 55901, US|3208||52|US Hwy|||Rochester|MN|55901|t||3208|US
+#1108a|529 Main St, Suite 201, Boston, MA 02129, US|529||Main|St||Suite 201|Boston|MA|02129|t||529|US
diff --git a/extras/tiger_geocoder/regress/normalize_address_regress.sql b/extras/tiger_geocoder/regress/normalize_address_regress.sql
index 0447bdfdf..46749542e 100644
--- a/extras/tiger_geocoder/regress/normalize_address_regress.sql
+++ b/extras/tiger_geocoder/regress/normalize_address_regress.sql
@@ -3,6 +3,11 @@ SELECT '#887' As ticket, * FROM normalize_address('2450 N COLORADO ST, PHILADELP
SELECT '#1051a' As ticket, * FROM normalize_address('212 3rd Ave N Suite 560, Minneapolis, MN 55401');
SELECT '#1051b' As ticket, * FROM normalize_address('3937 43RD AVE S, MINNEAPOLIS, MN 55406');
SELECT '#1051c' As ticket, * FROM normalize_address('212 N 3rd Ave, Minneapolis, MN 55401');
+SELECT '#1599' As ticket, * FROM normalize_address('212 n 3rd ave, Minneapolis, mn 55401, USA');
+SELECT '#1599b' As ticket, * FROM normalize_address('212 n 3rd ave, Minneapolis, mn 55401, France');
+SELECT '#1599c' As ticket, * FROM normalize_address('26 Court Street, Boston, Massachusetts 02109, France');
+SELECT '#1599d' As ticket, * FROM normalize_address('529 Main Street, Boston, MA 021, France');
+SELECT '#1599e' As ticket, * FROM normalize_address('26 Court Street, Boston, Massachusetts 02109, FR');
-- City missing , -- NOTE this one won't normalize right if you don't have MN data loaded
SELECT '#1051d' As ticket, * FROM normalize_address('212 3rd Ave N Minneapolis, MN 55401');
-- comma in wrong spot
@@ -86,6 +91,7 @@ SELECT '#1125d' As ticket, pprint_addy(addy), addy.* FROM normalize_address('Int
SELECT '#1125e' As ticket, pprint_addy(addy), addy.* FROM normalize_address('I-90,Boston, MA') As addy;
--broke this one too
SELECT '#1125f' As ticket, pprint_addy(addy), addy.* FROM normalize_address('I 90,Boston, MA') As addy;
+SELECT '#1599g' As ticket, pprint_addy(ROW(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,true,NULL,NULL,'US')::norm_addy);
-- location with prefixes getting caught in post prefix
SELECT '#1310a' As ticket, pprint_addy(addy), addy.* FROM normalize_address('1110 W CAPITOL AVE, WEST SACRAMENTO, CA') As addy;
diff --git a/extras/tiger_geocoder/regress/pagc_normalize_address_regress b/extras/tiger_geocoder/regress/pagc_normalize_address_regress
index 95216890b..9ea5fefd6 100644
--- a/extras/tiger_geocoder/regress/pagc_normalize_address_regress
+++ b/extras/tiger_geocoder/regress/pagc_normalize_address_regress
@@ -1,53 +1,58 @@
true
-#887|2450|N|COLORADO|ST|||PHILADELPHIA|PA|19132|t||2450
-#1051a|212||3RD|AVE|N|STE 560|MINNEAPOLIS|MN|55401|t||212
-#1051b|3937||43RD|AVE|S||MINNEAPOLIS|MN|55406|t||3937
-#1051c|212|N|3RD|AVE|||MINNEAPOLIS|MN|55401|t||212
-#1051d|212||3RD|AVE|N||MINNEAPOLIS|MN|55401|t||212
-529||MAIN|ST|||BOSTON|MA|02129|t||529
-529||MAIN|ST|||BOSTON|MA|02129|t||529
-529||MAIN|ST|||BOSTON|MA||t||529
-529||MAIN|ST||APT 201|BOSTON|MA|02129|t||529
-529||MAIN|ST||APT 201|BOSTON|MA|02129|t||529
-529||MAIN|ST||APT 201|BOSTON|MA||t||529
-#1108a|529||MAIN|ST||STE 201|BOSTON|MA|02129|t||529
-#1073a|212||3RD|AVE|N||MINNEAPOLIS|MN|55340|t|4|212
-#1073b|212||3RD|AVE|N||MINNEAPOLIS|MN|55401|t||212
-#1073c|529||MAIN|ST||BOSTON|MASSACHUSETTS||021|t||529
-#1086a|949|N|3RD|ST|||NEW HYDE PARK|NY|11040|t||949
-#1086b|949|N|3RD|ST|||NEW HYDE PARK|NY|11040|t||949
-#1076a|16725||24|CO RD|||PLYMOUTH|MN|55447|t||16725
-#1076b|16725||24|CO RD|||PLYMOUTH|MN|55447|t||16725
-#1076c|13800||9|CO HWY|||ANDOVER|MN|55304|t||13800
-#1076d|13800||9||||ANDOVER|MN|55304|t||13800
-#1076e|14||FOREST|RD|||ACTON|MA||t||14
-#1076f|1940||C|CO RD|W||ROSEVILLE|MN|55113|t||1940
-#1076g|3900||6|RTE|||EASTHAM|MA|02642|t||3900
-#1076h|4533||PARK|AVE|S||MINNEAPOLIS|MN|55407|t||4533
-#1076i|4533||33|CO RD|||MINNEAPOLIS|MN|55407|t||4533
-#1109a|4373||LAKE|DR|||ROBBINSDALE|MN|55422|t||4373
-#1109b|4373||LAKE|DR|||ROBBINSDALE|MN|55422|t||4373
-#1074a|3420||RHODE ISLAND|AVE|S||SAINT LOUIS PARK|MN|55426|t||3420
-#1074b|26||COURT|ST|||BOSTON|MA|02109|t||26
-#1112a|8401|W|35 W|SVC DR|NE||BLAINE|MN|55449|t||8401
-#1112b|8401||35 W||||BLAINE|MN|55449|t||8401
-#1112c|8401||35 W||W||BLAINE|MN|55449|t||8401
-#1112d|8401|W|35||W||BLAINE|MN|55449|t||8401
-#1112e|8401|W|35||W||BLAINE|MN|55449|t||8401
-#1125a|19596 CO RD 480, COLCORD, OK 74338
-#1125b|4345 353 RTE, SALAMANCA, NY 14779|4345||353|RTE|||SALAMANCA|NY|14779|t||4345
-#1125c|19799 STATE RTE 0, COSBY, MO 64436|19799||0|STATE RTE|||COSBY|MO|64436|t||19799
-#1125d|||||||||||t||
-#1125e|||||||||||t||
-#1125f|||||||||||t||
-#1310a|1110 W CAPITOL AVE, WEST SACRAMENTO, CA|1110|W|CAPITOL|AVE|||WEST SACRAMENTO|CA||t||1110
-#1614a|8435 CO RD 20 SE, ROCHESTER, MN 55904|8435||20|CO RD|SE||ROCHESTER|MN|55904|t||8435
-#1614b|3208 US RTE 52, ROCHESTER, MN 55901|3208||52|US RTE|||ROCHESTER|MN|55901|t||3208
-#1108a|529 MAIN ST, STE 201, BOSTON, MA 02129|529||MAIN|ST||STE 201|BOSTON|MA|02129|t||529
-#3259a|212||3RD|AVE|N|STE 560|MINNEAPOLIS|MN|55401|t||212
-#3259b|3937||43RD|AVE|S||MINNEAPOLIS|MN|55406|t||3937
-#2981|1566||NEW STATE HWY||||RAYNHAM|MA||t||1566
-#2978a|10||DORRANCE|ST|||PROVIDENCE|RI||t||10 20
-#2978b|10||DORRANCE|ST|||PROVIDENCE|RI||t||10 20
-#2978c|10||DORRANCE|ST|||PROVIDENCE|RI||t||10 20
+#887|2450|N|COLORADO|ST|||PHILADELPHIA|PA|19132|t||2450|US
+#1051a|212||3RD|AVE|N|STE 560|MINNEAPOLIS|MN|55401|t||212|US
+#1051b|3937||43RD|AVE|S||MINNEAPOLIS|MN|55406|t||3937|US
+#1051c|212|N|3RD|AVE|||MINNEAPOLIS|MN|55401|t||212|US
+#1599|212|N|3RD|AVE|||MINNEAPOLIS|MN|55401|t||212|US
+#1599b|212|N|3RD|AVE|||MINNEAPOLIS|MN|55401|t||212|FR
+#1599c|26||COURT|ST|||BOSTON|MA|02109|t||26|FR
+#1599d|529||MAIN|ST|||BOSTON|MA|021|t||529|FR
+#1599e|26||COURT|ST|||BOSTON|MA|02109|t||26|FR
+#1051d|212||3RD|AVE|N||MINNEAPOLIS|MN|55401|t||212|US
+529||MAIN|ST|||BOSTON|MA|02129|t||529|US
+529||MAIN|ST|||BOSTON|MA|02129|t||529|US
+529||MAIN|ST|||BOSTON|MA||t||529|US
+529||MAIN|ST||APT 201|BOSTON|MA|02129|t||529|US
+529||MAIN|ST||APT 201|BOSTON|MA|02129|t||529|US
+529||MAIN|ST||APT 201|BOSTON|MA||t||529|US
+#1108a|529||MAIN|ST||STE 201|BOSTON|MA|02129|t||529|US
+#1073a|212||3RD|AVE|N||MINNEAPOLIS|MN|55340|t|4|212|US
+#1073b|212||3RD|AVE|N||MINNEAPOLIS|MN|55401|t||212|US
+#1073c|529||MAIN|ST|||BOSTON|MA|021|t||529|US
+#1086a|949|N|3RD|ST|||NEW HYDE PARK|NY|11040|t||949|US
+#1086b|949|N|3RD|ST|||NEW HYDE PARK|NY|11040|t||949|US
+#1076a|16725||24|CO RD|||PLYMOUTH|MN|55447|t||16725|US
+#1076b|16725||24|CO RD|||PLYMOUTH|MN|55447|t||16725|US
+#1076c|13800||9|CO HWY|||ANDOVER|MN|55304|t||13800|US
+#1076d|13800||9||||ANDOVER|MN|55304|t||13800|US
+#1076e|14||FOREST|RD|||ACTON|MA||t||14|US
+#1076f|1940||C|CO RD|W||ROSEVILLE|MN|55113|t||1940|US
+#1076g|3900||6|RTE|||EASTHAM|MA|02642|t||3900|US
+#1076h|4533||PARK|AVE|S||MINNEAPOLIS|MN|55407|t||4533|US
+#1076i|4533||33|CO RD|||MINNEAPOLIS|MN|55407|t||4533|US
+#1109a|4373||LAKE|DR|||ROBBINSDALE|MN|55422|t||4373|US
+#1109b|4373||LAKE|DR|||ROBBINSDALE|MN|55422|t||4373|US
+#1074a|3420||RHODE ISLAND|AVE|S||SAINT LOUIS PARK|MN|55426|t||3420|US
+#1074b|26||COURT|ST|||BOSTON|MA|02109|t||26|US
+#1112a|8401|W|35 W|SVC DR|NE||BLAINE|MN|55449|t||8401|US
+#1112b|8401||35 W||||BLAINE|MN|55449|t||8401|US
+#1112c|8401||35 W||W||BLAINE|MN|55449|t||8401|US
+#1112d|8401|W|35||W||BLAINE|MN|55449|t||8401|US
+#1112e|8401|W|35||W||BLAINE|MN|55449|t||8401|US
+#1125a|19596 CO RD 480, COLCORD, OK 74338, US
+#1125b|4345 353 RTE, SALAMANCA, NY 14779, US|4345||353|RTE|||SALAMANCA|NY|14779|t||4345|US
+#1125c|19799 STATE RTE 0, COSBY, MO 64436, US|19799||0|STATE RTE|||COSBY|MO|64436|t||19799|US
+#1125d|||||||||||t|||US
+#1125e|||||||||||t|||US
+#1125f|||||||||||t|||US
+#1310a|1110 W CAPITOL AVE, WEST SACRAMENTO, CA, US|1110|W|CAPITOL|AVE|||WEST SACRAMENTO|CA||t||1110|US
+#1614a|8435 CO RD 20 SE, ROCHESTER, MN 55904, US|8435||20|CO RD|SE||ROCHESTER|MN|55904|t||8435|US
+#1614b|3208 US RTE 52, ROCHESTER, MN 55901, US|3208||52|US RTE|||ROCHESTER|MN|55901|t||3208|US
+#1108a|529 MAIN ST, STE 201, BOSTON, MA 02129, US|529||MAIN|ST||STE 201|BOSTON|MA|02129|t||529|US
+#3259a|212||3RD|AVE|N|STE 560|MINNEAPOLIS|MN|55401|t||212|US
+#3259b|3937||43RD|AVE|S||MINNEAPOLIS|MN|55406|t||3937|US
+#2981|1566||NEW STATE HWY||||RAYNHAM|MA||t||1566|US
+#2978a|10||DORRANCE|ST|||PROVIDENCE|RI||t||10 20|US
+#2978b|10||DORRANCE|ST|||PROVIDENCE|RI||t||10 20|US
+#2978c|10||DORRANCE|ST|||PROVIDENCE|RI||t||10 20|US
false
diff --git a/extras/tiger_geocoder/regress/pagc_normalize_address_regress.sql b/extras/tiger_geocoder/regress/pagc_normalize_address_regress.sql
index abd6d094b..783732fce 100644
--- a/extras/tiger_geocoder/regress/pagc_normalize_address_regress.sql
+++ b/extras/tiger_geocoder/regress/pagc_normalize_address_regress.sql
@@ -7,6 +7,11 @@ SELECT '#1051a' As ticket, * FROM normalize_address('212 3rd Ave N Suite 560, Mi
SELECT '#1051b' As ticket, * FROM normalize_address('3937 43RD AVE S, MINNEAPOLIS, MN 55406');
-- this currently turns 3rd to 3, we want to fix to keep 3rd as is
SELECT '#1051c' As ticket, * FROM normalize_address('212 N 3rd Ave, Minneapolis, MN 55401');
+SELECT '#1599' As ticket, * FROM normalize_address('212 n 3rd ave, Minneapolis, mn 55401, USA');
+SELECT '#1599b' As ticket, * FROM normalize_address('212 n 3rd ave, Minneapolis, mn 55401, France');
+SELECT '#1599c' As ticket, * FROM normalize_address('26 Court Street, Boston, Massachusetts 02109, France');
+SELECT '#1599d' As ticket, * FROM normalize_address('529 Main Street, Boston, MA 021, France');
+SELECT '#1599e' As ticket, * FROM normalize_address('26 Court Street, Boston, Massachusetts 02109, FR');
-- this currently turns 3rd to 3, we want to fix to keep 3rd as is
SELECT '#1051d' As ticket, * FROM normalize_address('212 3rd Ave N Minneapolis, MN 55401');
-- comma in wrong spot
diff --git a/extras/tiger_geocoder/upgrade_geocode.sql b/extras/tiger_geocoder/upgrade_geocode.sql
index 1057a3a8b..c98decd9f 100644
--- a/extras/tiger_geocoder/upgrade_geocode.sql
+++ b/extras/tiger_geocoder/upgrade_geocode.sql
@@ -28,12 +28,43 @@ ALTER TABLE state_lookup ADD CONSTRAINT state_lookup_statefp_key UNIQUE(statefp)
-- these introduced in PostGIS 2.4
DO language plpgsql
$$
- BEGIN
- ALTER TYPE tiger.norm_addy ADD ATTRIBUTE zip4 varchar;
+DECLARE
+ norm_addy_relid oid := (SELECT typrelid FROM pg_type WHERE oid = 'tiger.norm_addy'::regtype);
+BEGIN
+ /*
+ * Check each attribute independently so upgrades from releases that already
+ * have zip4/address_alphanumeric still pick up newer additions like country.
+ */
+ IF NOT EXISTS (
+ SELECT 1
+ FROM pg_attribute
+ WHERE attrelid = norm_addy_relid
+ AND attname = 'zip4'
+ AND NOT attisdropped
+ ) THEN
+ ALTER TYPE tiger.norm_addy ADD ATTRIBUTE zip4 varchar(4);
+ END IF;
+
+ IF NOT EXISTS (
+ SELECT 1
+ FROM pg_attribute
+ WHERE attrelid = norm_addy_relid
+ AND attname = 'address_alphanumeric'
+ AND NOT attisdropped
+ ) THEN
ALTER TYPE tiger.norm_addy ADD ATTRIBUTE address_alphanumeric varchar;
- EXCEPTION
- WHEN others THEN -- ignore the error probably cause it already exists
- END;
+ END IF;
+
+ IF NOT EXISTS (
+ SELECT 1
+ FROM pg_attribute
+ WHERE attrelid = norm_addy_relid
+ AND attname = 'country'
+ AND NOT attisdropped
+ ) THEN
+ ALTER TYPE tiger.norm_addy ADD ATTRIBUTE country varchar(2);
+ END IF;
+END;
$$;
CREATE INDEX idx_tiger_edges_countyfp ON edges USING btree(countyfp);
-----------------------------------------------------------------------
Summary of changes:
NEWS | 4 +
doc/extras_address_standardizer.xml | 5 +-
doc/installation.xml | 2 +-
extensions/address_standardizer/address_parser.c | 17 +
.../address_standardizer/address_standardizer.c | 8 +
.../address_standardizer_functions.sql.in | 4 +
.../expected/test-standardize_address_1.out | 12 +-
extensions/address_standardizer/parseaddress-api.c | 242 +++++-
extensions/address_standardizer/parseaddress-api.h | 2 +
.../address_standardizer/parseaddress-countries.h | 942 +++++++++++++++++++++
.../address_standardizer/test-parseaddress.sql.in | 9 +
extensions/postgis_tiger_geocoder/Makefile.in | 3 +-
.../postgis_tiger_geocoder.control.in | 2 +-
.../sql_bits/norm_addy_create.sql.in | 5 +-
.../sql_bits/norm_addy_upgrade.sql.in | 3 +-
.../sql_bits/upgrade_before.sql.in | 43 +-
extras/tiger_geocoder/create_geocode.sql | 4 +-
.../tiger_geocoder/normalize/normalize_address.sql | 18 +
extras/tiger_geocoder/normalize/pprint_addy.sql | 8 +-
.../pagc_normalize/pagc_normalize_address.sql | 5 +-
.../regress/normalize_address_regress | 96 ++-
.../regress/normalize_address_regress.sql | 6 +
.../regress/pagc_normalize_address_regress | 107 +--
.../regress/pagc_normalize_address_regress.sql | 5 +
extras/tiger_geocoder/upgrade_geocode.sql | 41 +-
25 files changed, 1451 insertions(+), 142 deletions(-)
create mode 100644 extensions/address_standardizer/parseaddress-countries.h
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list