[postgis-users] Various ways to handle addresses in postgresql

Stephen Woodbridge stephenwoodbridge37 at gmail.com
Sat Apr 25 13:20:28 PDT 2020


'^( *Building *[0-9]+)?[- 0-9]*'

or something like that should do it. But I think you will find that a 
more robust solution is to use parse_address() and/or 
standardize_address() as they will recognize a lot of other address 
constructs, like "apt 3a" for for example.

parse_address() that a text field and breaks it into "house number 
street name" and "city state zip", but only works well in North America.

standardize_address() that comes with postGIS, breaks the address down 
into its components and can separate out things like buildings, and 
apartment/unit specifiers so you can then take the fields you are 
interested in and recombine just them in a new string. Again, this works 
best in North America.

My github address-standardizer is built to recognize address for most 
counties, but it can also be configured to recognize address standards 
for any county without too much effort. It compiles and installs as 
postgresql extension.

Addresses are generally very messy and unless your addresses are vary 
simple you will be constantly fighting with this or that exception.


On 4/25/2020 2:55 PM, Shaozhong SHI wrote:
> Is there a way to left trim including the building and number?
> Building 3  21-1              Great Avenue, a city, a country, this 
> planet.
> How to take way those things which are too local to an address?
> Regards,
> Shao
> On Sat, 25 Apr 2020 at 01:48, Shaozhong SHI <shishaozhong at gmail.com 
> <mailto:shishaozhong at gmail.com>> wrote:
>     I find this is a simple, but important question.
>     How best to split numbers and the rest of address?
>     For instance, one tricky one is as follows:
>     21-1 Great Avenue, a city, a country, this planet
>     How to turn this into the following:
>     column 1,       column 2
>       21-1              Great Avenue, a city, a country, this planet
>     Note:  there is a hyphen in  21-1
>     Any clue?
>     Regards,
>     Shao
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

More information about the postgis-users mailing list