<div dir="ltr">Hi, Steve,<div><br></div><div>Alternatively, one could try to do something like the following to generate values to mark each row with a group number, by using something like the following:</div><div><br></div><div>SELECT t.value<br>from regexp_matches('1234567 - 7654321 - some - more - text', '\d+', 'g') with ordinality as t(value,idx)<br>where t.idx = 2;<br></div><div><br></div><div>Then, the strategy is to do address-matching for each group, in order to copy with different cases.</div><div><br></div><div>The cases, here, of interest is to, for the first step, determine how many elements of regexp_matches are present.</div><div><br></div><div>What do you think?</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 Mon, 21 Dec 2020 at 21:32, Shaozhong SHI <<a href="mailto:shishaozhong@gmail.com">shishaozhong@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"><div dir="ltr">Hi, Steve,<div><br></div><div>I do respect your work.</div><div><br></div><div>I am exploring a much simpler way, due to current circumstance, and curiosity to research.</div><div><br></div><div>I have been struggling to find a way to locate and access regex elements. For instance, how to refer to and access the one before the last. The idea is precisely about your mentioning of matched groups.</div><div><br></div><div>Is there a way to reverse this index?</div><div><br></div><div><a href="https://stackoverflow.com/questions/52178844/get-second-match-from-regexp-matches-results" target="_blank">https://stackoverflow.com/questions/52178844/get-second-match-from-regexp-matches-results</a><br></div><div><br></div><div>Regards,</div><div><br></div><div>David</div><div><br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, 21 Dec 2020 at 20:52, Stephen Woodbridge <<a href="mailto:stephenwoodbridge37@gmail.com" target="_blank">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>
in terms of geocoding, which is how I think about these issues, a street <br>
can have a range of house numbers distributed over it. When you talk <br>
about "101d--120a Some Great Street" as a street you have to decided:<br>
<br>
1. is this one location or many?<br>
2. how is it distributed over the street segment?<br>
<br>
On input what does this address mean?<br>
<br>
1. is it one location? therefore only an exact match should geocode to it.<br>
2. if it is an address range, then what are you looking for in the <br>
reference data set. If multiple ranges intersect this input range, then <br>
what is the correct response coordinate?<br>
3. what is the location that the user is looking for? What is the <br>
physical location of "101d--120a Some Great Street" that someone is <br>
trying to drive to, or deliver a package to? And how is that encoded in <br>
the reference dataset? Because that is what you need to match it to.<br>
<br>
Geocoding is a human language parsing problem: a human has entered an <br>
address and is expecting a response that will get them to the desired <br>
location. This is a hard problem and I have never seen a "simple"" <br>
solution that works for a wide range of inputs. I always start with the <br>
80-20 rule, try to solve 80% of your cases with 20% of the rules or <br>
effort, and then look at the cases that are failing and try to abstract <br>
them so you can add a few more rules to solve 80% of the failures and so <br>
on till you get close enough.<br>
<br>
My address standardizer is only the front-end parser for the address <br>
text to separate it into classified components so you can then make <br>
sense out of the text and do what you want with the components.<br>
<br>
Read some of the links I sent you to understand the problems. This code <br>
has been used with multiple datasets and multiple countries. I <br>
personally have used it to build geocoders based on US Tiger data, <br>
Canada Census data, Australia's master address list, UK data, <br>
Navteq/HERE data for US, Canada, UK, and Germany.<br>
<br>
It requires some work but the results have been really good. You have to <br>
try stuff and see if it works for you or not. Maybe as you suggest there <br>
is a simple solution for the case you present, but is this case 80% of <br>
your addresses fit into or is it the case that 1/2% of your addresses <br>
fit into. Whichever it is, focus on the 80% of the cases and solve that <br>
first, then tackle the harder outliers.<br>
<br>
By the way postgis, has an address standardizer included with it. I <br>
ported that into postgis from another project, my <br>
<a href="http://github.com/woodbri/address_standardizer" rel="noreferrer" target="_blank">github.com/woodbri/address_standardizer</a> repository is not the same, I <br>
wrote this to be C++ based, supporting utf8, multiple languages and <br>
multiple countries and to be easier for humans to configure and modify, <br>
none of which exists in the current postgis code. I had hoped to get my <br>
new code integrated into postgis, but I'm retired now and energies are <br>
getting directed elsewhere.<br>
<br>
Hope this helps you to get started.<br>
<br>
-Steve<br>
<br>
On 12/21/2020 3:00 PM, Shaozhong SHI wrote:<br>
> Hi, Steve,<br>
><br>
> Thanks.<br>
><br>
> I think that the key matter lies to one thing:<br>
><br>
> Get SAO and PAO numbers and text separate, so that we can intersect <br>
> SAO and PAO number arrays. By doing so, we can detect addresses for <br>
> matching addresses to known address points.<br>
><br>
> This could offer a much simpler alternative.<br>
><br>
> What do you think?<br>
><br>
> Regards,<br>
><br>
> David<br>
><br>
> On Mon, 21 Dec 2020 at 17:06, 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>
> On 12/21/2020 11:40 AM, Shaozhong SHI wrote:<br>
> > Hi, Steve W,<br>
> ><br>
> > Many thanks,<br>
> ><br>
> > How best to see where two addresses match.<br>
> ><br>
> > For instance, one would say: 101d--120a Some Great Street and<br>
> > 104d-110d Some Great Street match.<br>
> ><br>
> > How best to do it?<br>
> ><br>
> > Would it be possible to turn both ranges into arrays, and then<br>
> > intersect the range to check out?<br>
> ><br>
> > Please enlighten me.<br>
> So what I've done with ranges is to put start and end in the database<br>
> for the reference dataset, then you have to decide how you want to<br>
> handle ranges on input. Ranges on input typically imply generating<br>
> multiple queries to the database. When geocoding you first want to<br>
> match<br>
> the country, city, street which will give you a small set of records,<br>
> then evaluate each record but ranking them with some rules, or<br>
> evaluating each, etc to get the best case. It also depends on<br>
> where you<br>
> are doing fuzzy matching or not.<br>
><br>
> You might want to checkout:<br>
> <a href="https://github.com/woodbri/imaptools.com" rel="noreferrer" target="_blank">https://github.com/woodbri/imaptools.com</a><br>
> <<a href="https://github.com/woodbri/imaptools.com" rel="noreferrer" target="_blank">https://github.com/woodbri/imaptools.com</a>> - this is all my public<br>
> code<br>
> that I developed consulting<br>
> <a href="https://github.com/woodbri/imaptools.com/blob/master/README-geocoder-design.md" rel="noreferrer" target="_blank">https://github.com/woodbri/imaptools.com/blob/master/README-geocoder-design.md</a><br>
> <<a href="https://github.com/woodbri/imaptools.com/blob/master/README-geocoder-design.md" rel="noreferrer" target="_blank">https://github.com/woodbri/imaptools.com/blob/master/README-geocoder-design.md</a>><br>
> <a href="https://github.com/woodbri/imaptools.com/blob/master/README-geocoder.md" rel="noreferrer" target="_blank">https://github.com/woodbri/imaptools.com/blob/master/README-geocoder.md</a><br>
> <<a href="https://github.com/woodbri/imaptools.com/blob/master/README-geocoder.md" rel="noreferrer" target="_blank">https://github.com/woodbri/imaptools.com/blob/master/README-geocoder.md</a>><br>
><br>
> I'm retired now and released all this code under MIT-X license,<br>
> and you<br>
> will find my SQL Geocoder buried in there and hopefully, I have left<br>
> enough comments for you to figure out how to reuse it. Start by<br>
> reading<br>
> the README-geocoder*.md links above.<br>
><br>
> -Steve<br>
><br>
> ><br>
> > Regards,<br>
> ><br>
> > David<br>
> ><br>
> > On Sun, 20 Dec 2020 at 00:30, 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>><br>
> <mailto:<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>>>><br>
> > wrote:<br>
> ><br>
> > On 12/19/2020 7:24 PM, Stephen Woodbridge wrote:<br>
> > > Hi David,<br>
> > ><br>
> > > Yes and No! you can do that for some simple addresses, but<br>
> > trying to<br>
> > > clean up addresses in general is much more complicated.<br>
> I've been<br>
> > > doing this for 20+ years writing geocoders. Check out:<br>
> > ><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>><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>>><br>
> > ><br>
> > > This will install as a postgresql extension and once you<br>
> set it<br>
> > up it<br>
> > > has an easy function call that can be used to clean things up.<br>
> > > Read this to get a better idea:<br>
> > ><br>
> > ><br>
> ><br>
> <a href="https://github.com/woodbri/address-standardizer/blob/develop/DOCUMENTATION.md" rel="noreferrer" target="_blank">https://github.com/woodbri/address-standardizer/blob/develop/DOCUMENTATION.md</a><br>
> <<a href="https://github.com/woodbri/address-standardizer/blob/develop/DOCUMENTATION.md" rel="noreferrer" target="_blank">https://github.com/woodbri/address-standardizer/blob/develop/DOCUMENTATION.md</a>><br>
> > <br>
> <<a href="https://github.com/woodbri/address-standardizer/blob/develop/DOCUMENTATION.md" rel="noreferrer" target="_blank">https://github.com/woodbri/address-standardizer/blob/develop/DOCUMENTATION.md</a><br>
> <<a href="https://github.com/woodbri/address-standardizer/blob/develop/DOCUMENTATION.md" rel="noreferrer" target="_blank">https://github.com/woodbri/address-standardizer/blob/develop/DOCUMENTATION.md</a>>><br>
> ><br>
> > ><br>
> > ><br>
> > > And there is a docs/ directory with more documentation. I also<br>
> > provide<br>
> > > sample starter files for parsing addresses of about 25<br>
> > countries, so<br>
> > > you can pick the one(s) you need and then customize them for<br>
> > your needs.<br>
> > ><br>
> > > If you just want to try working with simple regex you probably<br>
> > want to<br>
> > > read:<br>
> > ><br>
> > > <a href="https://www.postgresqltutorial.com/regexp_replace/" rel="noreferrer" target="_blank">https://www.postgresqltutorial.com/regexp_replace/</a><br>
> <<a href="https://www.postgresqltutorial.com/regexp_replace/" rel="noreferrer" target="_blank">https://www.postgresqltutorial.com/regexp_replace/</a>><br>
> > <<a href="https://www.postgresqltutorial.com/regexp_replace/" rel="noreferrer" target="_blank">https://www.postgresqltutorial.com/regexp_replace/</a><br>
> <<a href="https://www.postgresqltutorial.com/regexp_replace/" rel="noreferrer" target="_blank">https://www.postgresqltutorial.com/regexp_replace/</a>>><br>
> > ><br>
> > > |SELECT REGEXP_REPLACE( REGEXP_REPLACE||(string, |'[, .]+', '<br>
> > > '), -- convert comma, space, period to a single space<br>
> > > '^ *|--| *$', '') -- remove leading and<br>
> > trailing<br>
> > > white space and '--'<br>
> > > );<br>
> ><br>
> > But this will not handle "101 - 103 -- a" without adding more<br>
> > clauses.<br>
> > And how do you plan to handle things like abbreviations?<br>
> Ave, ave,<br>
> > avenue, st, street, saint, or lots of other issues. A lot<br>
> depends on<br>
> > your use case, are you geocoding these are just trying to<br>
> clean up<br>
> > some<br>
> > text.<br>
> > ><br>
> > ><br>
> > > -Steve W<br>
> > ><br>
> > > On 12/19/2020 5:40 PM, Shaozhong SHI wrote:<br>
> > >> For instance,<br>
> > >><br>
> > >> We may encounter,<br>
> > >><br>
> > >> My Great Place. 101-103--a, Great Avenue, London, , SW3 5EE<br>
> > >><br>
> > >> Can we do some thing like<br>
> > >><br>
> > >> SELECT<br>
> > >> REGEXP_MATCHES(' My Great' Place. 101-103--a, Great<br>
> Avenue,<br>
> > >> London, , SW3 5EE ',<br>
> > >> a_regular_expression,<br>
> > >> 'g');<br>
> > >><br>
> > >> so that we end up a clean address line with one line of code?<br>
> > >><br>
> > >> My Great Place 101-103a Great Avenue London SW3 5EE<br>
> > >><br>
> > >> What regex is that?<br>
> > >><br>
> > >> Can we guarantee a single space between words?<br>
> > >><br>
> > >> Regards,<br>
> > >><br>
> > >> David<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>
> > <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>
> > <<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>
> <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>
> > <<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> <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>
><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>
</blockquote></div>