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

[Stephen Woodbridge]

Ravi,

The process for geocoding follows this:

Load the data:
1. get a reference set of streets (ie: the Tiger data)
2. normalize the names as you load the data
3. build the indexes you need for the queries

Query for an address:
1. normalize the address on input
2. query the normalized reference

Ok, so you know most of this because you have already done it, but the 
important part here is the you normalize BOTH the reference data and the 
input to a query. This resolves things like:

main street != main st

because the normalize parses the addresses and converts them into a 
normalized standard form so that you can match. Yes it takes time to 
normalize the request, but if you don't normalize it, then there is a 
good change that you will not match an appropriate street in the 
reference set.

-Steve

On 12/14/2011 9:06 AM, Ravi Ada wrote:
> 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 at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> Thanks,
> Ravi Ada
> 918-630-7381
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users