<div dir="ltr">Hi,
<span style="color:rgb(80,0,80)">Steve W,</span><div><span style="color:rgb(80,0,80)"><br></span></div><div><span style="color:rgb(80,0,80)">Many thanks,</span></div><div><span style="color:rgb(80,0,80)"><br></span></div><div><span style="color:rgb(80,0,80)">How best to see where two addresses match.</span></div><div><span style="color:rgb(80,0,80)"><br></span></div><div><span style="color:rgb(80,0,80)">For instance, one would say: 101d--120a Some Great Street and 104d-110d Some Great Street match.</span></div><div><span style="color:rgb(80,0,80)"><br></span></div><div><span style="color:rgb(80,0,80)">How best to do it?</span></div><div><span style="color:rgb(80,0,80)"><br></span></div><div><span style="color:rgb(80,0,80)">Would it be possible to turn both ranges into arrays, and then intersect the range to check out?</span></div><div><span style="color:rgb(80,0,80)"><br></span></div><div><span style="color:rgb(80,0,80)">Please enlighten me.</span></div><div><span style="color:rgb(80,0,80)"><br></span></div><div><span style="color:rgb(80,0,80)">Regards,</span></div><div><span style="color:rgb(80,0,80)"><br></span></div><div><font color="#500050">David</font></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, 20 Dec 2020 at 00:30, 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">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 trying to <br>
> clean up addresses in general is much more complicated. 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>
><br>
> This will install as a postgresql extension and once you set it 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>
> <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>
> And there is a docs/ directory with more documentation. I also provide <br>
> sample starter files for parsing addresses of about 25 countries, so <br>
> you can pick the one(s) you need and then customize them for your needs.<br>
><br>
> If you just want to try working with simple regex you probably 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>
><br>
> |SELECT REGEXP_REPLACE( REGEXP_REPLACE||(string, |'[, .]+', ' <br>
> '), -- convert comma, space, period to a single space<br>
> '^ *|--| *$', '') -- remove leading and trailing <br>
> white space and '--'<br>
> );<br>
<br>
But this will not handle "101 - 103 -- a" without adding more clauses. <br>
And how do you plan to handle things like abbreviations? Ave, ave, <br>
avenue, st, street, saint, or lots of other issues. A lot depends on <br>
your use case, are you geocoding these are just trying to clean up 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 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>
>> <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>
</blockquote></div>