[postgis-users] Tigerdata for AZ, AS and VI

[Paragon Corporation]

> I just don't understand why the geocode function takes so 
> long to return the coordinates. I am sure some of you on this 
> list might have done the batch geocoding millions of 
> addresses. I may be missing just a simple configuration which 
> might make a whole lot of difference in the speed. I don't 
> know what it is. I am following the examples exactly from this link
> (http://postgis.refractions.net/documentation/manual-svn/Geocode.html)
> 
> If someone is familiar with the problem willing to help me 
> using GoTo Meeting connection to my machine, I can arrange 
> that too. I just have to move along with my project and meet 
> the deadlines. I am already delayed, everybody in my team 
> asking me for this everyday.
> 
> 
> Thanks,
> Ravi Ada
> 
> 
Ravi,

Sorry been busy with raster stuff so haven't been tuned into this
discussion.

1) The indexes the loader generates are not the only ones needed.  Initially
I was constantly changing the loader script,
but since we were changing decisions as we changed code and optimal indexes
needed with aeach change required changing indexes, which indexes would be
best, I created a function that would put them in
rather than bothering with the loader (since a lot of people would already
have their data loaded)

Have you tried running that.  I suspect you are just missing indexes as the
timings you are getting are what I used to get earlier on.

If you haven't run the update script (which runs this routine anyway) or run
this to get generated script for indexes you are missing
you should.

http://www.postgis.org/documentation/manual-svn/Missing_Indexes_Generate_Scr
ipt.html

2) There are a couple of other things to note: First address you do around
an area can take a lot more time because of the data caching effects
in postgresql.  So for the example in the docs you describe.

I can do a geocode of 75 State Street,Boston, MA  -- and if I haven't done
any geocoding in a while that takes like 1-3 seconds

Then if I do 80 State Street, Boston, MA -- that subsequent takes anywhere
from 60 ms - 150 ms.
I also don't have all the states loaded since I only needed it for about 6
states.  thought that should just increase the planner time rather than
later times.

3) For debugging performance there is a variable in the geocode_address
function called var_debug.  Its false by default, change it to true.
That spits out the sql being run and is a better sql to pass to the planner
to check.

We were hoping to make these debugging features more publically exposed e.g
via a config table, but haven't had the time to do that.

Hope this all helps,
Regina
http://www.postgis.us