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

Wed Dec 14 11:30:22 PST 2011

On 12/14/2011 10:12 AM, Ravi ada wrote:
> Thanks Steve.
> I did not get when you said '2. normalize the names as you load the data',
> is this the step we need to do manually or load scripts automatically do
> this step?

the load scripts do this automatically.

> I am normalizing my addresses before querying the reference data (tiger
> data).  Please clarify.

You should use geocode() not geocode_address(), because geocode() will 
normalize the address in the same way the the reference addresses are 
normalized. If you split the address into fields and call 
geocode_address() this is NOT the same as normalizing the address. If 
you want to be able to get good matches, you have to use the same 
normalize function for both the reference and the input addresses.

These functions are not about performance they are about using the tool 
the correct way.

-Steve

> Thanks
> Ravi Ada
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Stephen
> Woodbridge
> Sent: Wednesday, December 14, 2011 8:14 AM
> To: postgis-users at postgis.refractions.net
> Subject: Re: [postgis-users] Tigerdata for AZ, AS and VI
>
> 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.htm
>>>> l)
>>>>
>>>> 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_Gener
>>> ate_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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users