Hi Ravi,<div><br></div><div>There is a thread about the tiger geocoder performance from awhile back you might want to look into. I had to do this before with about 1M addresses and ended up creating an ec2 instance for each state and sending addresses with that state to that specific host (using a simple regex to match state in an address line). Somewhat expensive, but got the job done quickly.</div>
<div><br></div><div>Johnathan</div><div><br><div class="gmail_quote">On Tue, Nov 29, 2011 at 9:18 PM, Ravi ada <span dir="ltr"><<a href="mailto:raviada@dobeyond.com">raviada@dobeyond.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div lang="EN-US" link="blue" vlink="purple"><div><p class="MsoNormal">Hello All,<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">I have been fighting with PostGIS for the last week or two to geocode about 2 million addresses. Here are the challenges that I am facing.<u></u><u></u></p>
<p><u></u><span>1)<span style="font:7.0pt "Times New Roman""> </span></span><u></u>I sorted the addresses by zip, address, city to group the similar addresses together.<u></u><u></u></p><p><u></u><span>2)<span style="font:7.0pt "Times New Roman""> </span></span><u></u>So far I have seen only 50% of rating zero, that means perfect match.<u></u><u></u></p>
<p><u></u><span>3)<span style="font:7.0pt "Times New Roman""> </span></span><u></u>Some addresses have Suite# or Apt# in address line 1. Geocode function takes longer time for such addresses<u></u><u></u></p>
<p><u></u><span>4)<span style="font:7.0pt "Times New Roman""> </span></span><u></u>Addresses that are on the interstate, may be on the service road. These are taking longer to code.<u></u><u></u></p><p><u></u><span>5)<span style="font:7.0pt "Times New Roman""> </span></span><u></u>Getting syntax errors on line_segment function when processing some addresses that have additional data in parenthesis and a period. For example "5727 FM 3097(HORIZON RD.),ROCKWALL,TX,750327786" <u></u><u></u></p>
<p><u></u><span>6)<span style="font:7.0pt "Times New Roman""> </span></span><u></u>I am using batch updates selecting the addresses per state to reduce the size of the batch. I used straight SQL update as mentioned in the documentation, and wrote a function to call geo code for each address. In both cases the query ran for more than 6 hours and never been able finish successfully. If I kill the query, nothing gets updated to the address table because the whole transaction is killed and not committed.<u></u><u></u></p>
<p><u></u><span>7)<span style="font:7.0pt "Times New Roman""> </span></span><u></u>I am running this on Ubuntu 11.10 and postgresql 9.1 on a virtual machine on 8GB i5 machine. I know the resources are very less for such a task.<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">My questions are<u></u><u></u></p><p><u></u><span>1)<span style="font:7.0pt "Times New Roman""> </span></span><u></u>How other people are geocoding so many addresses, what is the best way to do it, knowing that we may not have fully normalized addresses.<u></u><u></u></p>
<p><u></u><span>2)<span style="font:7.0pt "Times New Roman""> </span></span><u></u>How long it should take to geocode about 2mil addresses.<u></u><u></u></p><p><u></u><span>3)<span style="font:7.0pt "Times New Roman""> </span></span><u></u>What does it mean if the rating is non-zero, and more than 50. Do we get the distance wring for such addresses?<u></u><u></u></p>
<p><u></u><span>4)<span style="font:7.0pt "Times New Roman""> </span></span><u></u>What is the recommended hardware configuration? We need to geocode these addresses only once, and periodically to the delta that we get every month. <u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal">Thank you so much for all the efforts that you guys are contributing to the project. Any help with my questions would greatly be appreciated.<u></u><u></u></p><p class="MsoNormal">
<u></u> <u></u></p><p class="MsoNormal">Thanks<u></u><u></u></p><p class="MsoNormal">Ravi Ada<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"><u></u> <u></u></p></div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br><br clear="all"><div><br></div>-- <br><i><font color="#CCCCCC">Johnathan</font></i><div><font color="#CCCCCC">Software Engineer</font></div><div><font color="#CCCCCC">San Francisco, </font><font color="#cccccc">California</font></div>
<div><i><font color="#CCCCCC"><a href="http://twitter.com/#!/iamleppert" target="_blank">Follow me on Twitter: @iamleppert</a></font></i></div><br>
</div>