<div dir="ltr">Hi, Steve,<div><br><div>Many thanks.</div><div><br></div><div>We are strongly interested in dealing with UK addresses.</div></div><div><br></div><div>Last time, I passed on messages for testing. I was told not working for UK addresses.</div><div><br></div><div>I will try it again. Anything else do we have to watch out for?</div><div><br></div><div>Regards,</div><div><br></div><div>David</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sat, 9 Jan 2021 at 15:53, Stephen Woodbridge <<a href="mailto:stephenwoodbridge37@gmail.com">stephenwoodbridge37@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">David,<br>
<br>
This is the link to the address standardizer:<br>
<a href="https://github.com/woodbri/address-standardizer" rel="noreferrer" target="_blank">https://github.com/woodbri/address-standardizer</a><br>
<br>
This is a link to all my code that I developed consulting. It includes a <br>
few SQL geocoders based on the code above. And has some README files <br>
discussing how to build a geocoder which is the basis for how the <br>
geocoders work.<br>
<br>
<a href="https://github.com/woodbri/imaptools.com" rel="noreferrer" target="_blank">https://github.com/woodbri/imaptools.com</a><br>
<br>
this is the geocoder for Tiger data, but the code is essentially the <br>
same for every country because the when you load country specific data <br>
into the database it goes into its own table and then you standardize <br>
that data into stdstreets table and all queries are done against the <br>
stdstreets table and you only have to tweak the address range <br>
interpolation function which needs to access the source streets table <br>
for the geometry and house number ranges.<br>
<br>
<a href="https://github.com/woodbri/imaptools.com/blob/master/sql-scripts/geocoder/prep-tiger-geo-new.sql" rel="noreferrer" target="_blank">https://github.com/woodbri/imaptools.com/blob/master/sql-scripts/geocoder/prep-tiger-geo-new.sql</a><br>
<br>
I would approach this by:<br>
<br>
1. get the address standardizer compiled and installed. I can help if <br>
you run into problems or have questions.<br>
2. load your UK street data into rawdata schema, ideally it would be <br>
best if we can create a table/view that presents this data as a single <br>
table where each record represents one side of the street and one <br>
jurisdiction this may mean that a single record in your source data will <br>
generate multiple records in this table/view (this greatly simplifies <br>
the coding and performance later)<br>
3. look at the prep-tiger-geo-new.sql file<br>
4. create a stdstreets table and standardize your table/view data into it<br>
5. look at standardization failures and adjust lexicon and grammar as needed<br>
6. loop back to 4 until good enough<br>
7. load functions from prep-tiger-geo-new.sql file and adjust any for <br>
your data<br>
8. try it out!<br>
<br>
-Steve<br>
<br>
<br>
On 1/9/2021 10:22 AM, Shaozhong SHI wrote:<br>
> Hi, Stephen,<br>
><br>
> Many thanks. We are interested in it is working with the UK addresses.<br>
><br>
> Please send me the link to this.<br>
><br>
> Regards,<br>
><br>
> David<br>
><br>
> On Sat, 9 Jan 2021 at 15:00, Stephen Woodbridge <br>
> <<a href="mailto:stephenwoodbridge37@gmail.com" target="_blank">stephenwoodbridge37@gmail.com</a> <mailto:<a href="mailto:stephenwoodbridge37@gmail.com" target="_blank">stephenwoodbridge37@gmail.com</a>>> <br>
> wrote:<br>
><br>
> David,<br>
><br>
> Yup and this is just one a dozens of cases that you have to deal<br>
> with. You are dealing with a natural language processing problem.<br>
> And you have to deal with human input that has typos and<br>
> abbreviations.<br>
><br>
> These issues are what the address standardizer fixes. It tokenized<br>
> the address and uses the gazette to standardize the terms and then<br>
> classifies each term and assigns it to part of the address based<br>
> on a grammar.<br>
><br>
> So there is a simple solution, use my address standardizer, it is<br>
> free, MIT license, it has a sample lexicon/ gazette and grammar<br>
> for the UK, it is easy to modify these to fit your needs, and it<br>
> just works. Oh if you want to do another county it also has sample<br>
> files for 25 countries.<br>
><br>
> Sent from my iPhone<br>
><br>
>> On Jan 9, 2021, at 4:42 AM, Darafei Komяpa Praliaskouski<br>
>> <<a href="mailto:me@komzpa.net" target="_blank">me@komzpa.net</a> <mailto:<a href="mailto:me@komzpa.net" target="_blank">me@komzpa.net</a>>> wrote:<br>
>><br>
>> <br>
>> Hello,<br>
>><br>
>> People make neural networks for this kind of task:<br>
>><br>
>> <a href="https://github.com/openvenues/libpostal" rel="noreferrer" target="_blank">https://github.com/openvenues/libpostal</a><br>
>> <<a href="https://github.com/openvenues/libpostal" rel="noreferrer" target="_blank">https://github.com/openvenues/libpostal</a>><br>
>><br>
>> сб, 9 сту 2021, 12:40 карыстальнік Shaozhong SHI<br>
>> <<a href="mailto:shishaozhong@gmail.com" target="_blank">shishaozhong@gmail.com</a> <mailto:<a href="mailto:shishaozhong@gmail.com" target="_blank">shishaozhong@gmail.com</a>>> напісаў:<br>
>><br>
>> Hi, Steve W,<br>
>><br>
>> it is easy to parse addresses as tokens. But it is difficult<br>
>> to put tokens in right columns, due to that the same address<br>
>> could be expressed with partial address or full address.<br>
>><br>
>> The same address can be written like, Flat 1 122 Great Avenue<br>
>> London UK, or Flat 1 122 Greet Avenue Central London London<br>
>> United Kingdom.<br>
>><br>
>> When this happens, each address has different number of<br>
>> tokens, so different numbers of tokens. Is there a way to<br>
>> deal with this issue so that each token can get into right<br>
>> column?<br>
>><br>
>> Please enlighten me.<br>
>><br>
>> Regards,<br>
>><br>
>> David<br>
>><br>
>> On Sat, 25 Apr 2020 at 05:09, Stephen Woodbridge<br>
>> <<a href="mailto:stephenwoodbridge37@gmail.com" target="_blank">stephenwoodbridge37@gmail.com</a><br>
>> <mailto:<a href="mailto:stephenwoodbridge37@gmail.com" target="_blank">stephenwoodbridge37@gmail.com</a>>> wrote:<br>
>><br>
>> And I have create an address-standardizer project here<br>
>> <a href="https://github.com/woodbri/address-standardizer" rel="noreferrer" target="_blank">https://github.com/woodbri/address-standardizer</a><br>
>> <<a href="https://github.com/woodbri/address-standardizer" rel="noreferrer" target="_blank">https://github.com/woodbri/address-standardizer</a>> which<br>
>> is user<br>
>> configurable. I might be over kill is you just want to<br>
>> strip off the<br>
>> number, in which case you might just use a SQL regexp<br>
>> replace to remove it.<br>
>><br>
>> -Steve W<br>
>><br>
>> On 4/25/2020 12:04 AM, Stephen Woodbridge wrote:<br>
>> > PostGIS has address_standardizer extension that includes<br>
>> > parse_address() and standardize_address() functions.<br>
>> ><br>
>> > -Steve W<br>
>> ><br>
>> > On 4/24/2020 9:54 PM, Imre Samu wrote:<br>
>> >> > handle addresses in postgresql<br>
>> >><br>
>> >> maybe you can use the<br>
>> <a href="https://github.com/openvenues/libpostal" rel="noreferrer" target="_blank">https://github.com/openvenues/libpostal</a><br>
>> <<a href="https://github.com/openvenues/libpostal" rel="noreferrer" target="_blank">https://github.com/openvenues/libpostal</a>> library<br>
>> >> with your favorite language bindings ( Python / Ruby /<br>
>> Go / PHP /<br>
>> >> Node / R / Java ...)<br>
>> >><br>
>> >> or as a Postgres database extension:<br>
>> >><br>
>> <a href="https://info.crunchydata.com/blog/quick-and-dirty-address-matching-with-libpostal" rel="noreferrer" target="_blank">https://info.crunchydata.com/blog/quick-and-dirty-address-matching-with-libpostal</a><br>
>> <<a href="https://info.crunchydata.com/blog/quick-and-dirty-address-matching-with-libpostal" rel="noreferrer" target="_blank">https://info.crunchydata.com/blog/quick-and-dirty-address-matching-with-libpostal</a>><br>
>><br>
>> >><br>
>> >> <a href="https://github.com/pramsey/pgsql-postal" rel="noreferrer" target="_blank">https://github.com/pramsey/pgsql-postal</a><br>
>> <<a href="https://github.com/pramsey/pgsql-postal" rel="noreferrer" target="_blank">https://github.com/pramsey/pgsql-postal</a>><br>
>> >><br>
>> >> Regards,<br>
>> >> Imre<br>
>> >><br>
>> >><br>
>> >><br>
>> >><br>
>> >> Shaozhong SHI <<a href="mailto:shishaozhong@gmail.com" target="_blank">shishaozhong@gmail.com</a><br>
>> <mailto:<a href="mailto:shishaozhong@gmail.com" target="_blank">shishaozhong@gmail.com</a>><br>
>> >> <mailto:<a href="mailto:shishaozhong@gmail.com" target="_blank">shishaozhong@gmail.com</a><br>
>> <mailto:<a href="mailto:shishaozhong@gmail.com" target="_blank">shishaozhong@gmail.com</a>>>> ezt írta (időpont:<br>
>> 2020. ápr. 25.,<br>
>> >> Szo, 2:49):<br>
>> >><br>
>> >> I find this is a simple, but important question.<br>
>> >><br>
>> >> How best to split numbers and the rest of address?<br>
>> >><br>
>> >> For instance, one tricky one is as follows:<br>
>> >><br>
>> >> 21-1 Great Avenue, a city, a country, this planet<br>
>> >><br>
>> >> How to turn this into the following:<br>
>> >><br>
>> >> column 1, column 2<br>
>> >><br>
>> >> 21-1 Great Avenue, a city, a<br>
>> country, this planet<br>
>> >><br>
>> >> Note: there is a hyphen in 21-1<br>
>> >><br>
>> >> Any clue?<br>
>> >><br>
>> >> Regards,<br>
>> >><br>
>> >> Shao<br>
>> >> _______________________________________________<br>
>> >> postgis-users mailing list<br>
>> >> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
>> <mailto:<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
>> <mailto:<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
>> <mailto:<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>>><br>
>> >> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
>> <<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>><br>
>> >><br>
>> >><br>
>> >> _______________________________________________<br>
>> >> postgis-users mailing list<br>
>> >> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
>> <mailto:<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
>> >> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
>> <<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>><br>
>> ><br>
>><br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
>> <mailto:<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
>> <<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>><br>
>><br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
>> <mailto:<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
>> <<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>><br>
>><br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a> <mailto:<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
>> <<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a> <mailto:<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
> <<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>><br>
><br>
><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote></div>