Regina, Thanks so much for the reply. I ran the missing_indexes_generate_script(), actually it did not return anything, I am assuming all the indexes are in place. That may be because I ran install_missing_indexes() earlier. I changed the debug flag in geocode_address and it produced a very long query that it runs to geocode the address. I tried to cut and paste the query to run the plan, I am getting errors, I will figure that out.
My question is, do we use gecode or geocode_address for faster querying? I noticed that geocode_address takes the normalized address where as geocode takes address as string parameter. By adding additional normalize_address function when doing the geocode_address akes it run any faster? Thanks Ravi Ada On Wed, 14 Dec 2011 04:18:27 -0500, Paragon Corporation wrote > > 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 > > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users Thanks, Ravi Ada 918-630-7381 _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
