[postgis-users] Poor Geocoder Performance

Mon Dec 22 08:52:54 PST 2014

Correction - I meant ~1,000 addresses, not 10,000!

On Mon, Dec 22, 2014 at 10:46 AM, Brandon Abear <babear at carthage.edu> wrote:

> Hi Regina,
>
> I increased the shared_buffer to 4gb and the performance is MUCH better. I
> was able to geocode ~10,000 addresses in an hour. I ran a few of the
> addresses that were taking close to a minute to geocode, and they now
> return a result in less than a second.
>
> This performance should be sufficient since I am staging my data to be
> passed to the geocoder when a) there is a new address in the source table
> or b) the address has changed. This is done monthly, so I am comfortable
> with this.
>
> I will upgrade to the latest version of PostGIS as well.
>
> Thank you for your help on this. I also wanted to thank you for
> contributions to "PostGIS In Action," as your writing (I assume you are the
> same Regina!) and code examples made for a very painless jump into the
> world of spatial databases.
>  On Mon, Dec 22, 2014 at 5:32 AM, Paragon Corporation <lr at pcorp.us> wrote:
>
>>  Brandon,
>>
>> Hmm weird is this a new street?  I should load up tiger 2014 data to see
>> if a difference.  (PostGIS 2.1 has tiger 2013 load-- 2014 loader is in
>> PostGIS 2.2)
>>
>> I was using 2013 data  and it gives me a bad answer, but inspecting the
>> edges data, I see no EADS listed in Chicago area.
>>
>> select pprint_addy(addy), rating from geocode('7 EADS ST, CHICAGO, IL
>> 60632',3);
>>
>> I get these answers in about 150ms (but like I said I only have a few
>> states loaded and my shared buffers is set to 2GB)
>>
>> Eads St, Benton, IL 62812;20
>> 7 Edds St, Pekin, IL 61554;21
>> W Eads St, Urbana, IL 61801;22
>>
>> I looked up the same search on Google maps which seemed also wrong
>>
>>  Kopka Pinkus Dolin & Eads
>> 200 North LaSalle Street #2850
>> Chicago, IL 60601
>>
>>
>> Have another address I can experiment with.
>> Thanks,
>> Regina
>>  ------------------------------
>> *From:* postgis-users-bounces at lists.osgeo.org [mailto:
>> postgis-users-bounces at lists.osgeo.org] *On Behalf Of *Brandon Abear
>> *Sent:* Monday, December 22, 2014 12:13 AM
>>
>> *To:* PostGIS Users Discussion
>> *Subject:* Re: [postgis-users] Poor Geocoder Performance
>>
>>   Here is a sample address. This one currently takes 30567ms to return.
>> 7 EADS ST, CHICAGO, IL 60632
>>
>> My desktop has 16GB of ram and an i7 processor.
>>
>>
>> On Sun, Dec 21, 2014 at 10:52 PM, Paragon Corporation <lr at pcorp.us>
>> wrote:
>>
>>>  Brandon,
>>>
>>> For 64-bit 256mb is a little low.  I would up that to at least a
>>> gigabyte if you can spare it.  What processor are you running? and how much
>>> motherboard ram do you have?
>>>
>>> Most of my work (particularly on windows) is regional so never had to
>>> load more than 5 or so states. So it's possible that is an issue.  I think
>>> Darkblue_b (Brian Hamlin, wave if you are reading) has done much larger
>>> datasets so he might have a better idea.  Then again I think he runs on
>>> beefed up hardware with 16 cores and lots of RAM.
>>>
>>> If you can send me a sample address that would help.  Can send me off
>>> list if sensitive information.
>>>
>>> Thanks,
>>> Regina
>>>
>>>
>>>
>>>  ------------------------------
>>> *From:* postgis-users-bounces at lists.osgeo.org [mailto:
>>> postgis-users-bounces at lists.osgeo.org] *On Behalf Of *Brandon Abear
>>> *Sent:* Sunday, December 21, 2014 11:30 PM
>>> *To:* PostGIS Users Discussion
>>> *Subject:* Re: [postgis-users] Poor Geocoder Performance
>>>
>>>   Hi Regina,
>>>
>>> I will check out the update that you have uploaded.
>>>
>>> To answer your questions:
>>> 1) Yep, I ran the nation script before running the subsequent state
>>> scripts.
>>> 2) The test batch includes addresses in all of the continental states.
>>> If I am able to get the geocoder to run quickly enough, I will be geocoding
>>> millions of addresses.
>>> 3) I have all states loaded.
>>> 4) Windows 7 64-bit
>>> 5) PostgreSQL shared_buffer is 256mb
>>> 6) I am running the same version as you.
>>>
>>> On Sun, Dec 21, 2014 at 10:16 PM, Paragon Corporation <lr at pcorp.us>
>>> wrote:
>>>
>>>>  Brandon,
>>>>
>>>>
>>>> BTW: I recently uploaded PostGIS 2.1.5 on stackbuilder for 9.3 and 9.4
>>>> (still need to do for 9.2)  and I have made some fixes between 2.1.3 and
>>>> 2.1.5 of the geocoder so might be worthwhile upgrading.
>>>>
>>>> To upgrade doing a :
>>>>
>>>> ALTER EXTENSION postgis_tiger_geocoder UPDATE TO "2.1.5";
>>>> Couple of questions
>>>>
>>>> 1) Did you run the nation script? That is often the cause of this kind
>>>> of issue if that was not done before loading states
>>>> 2) Which area are you running?
>>>> 3) How many states do yo have loaded?  I can try to test out myself to
>>>> see if I can replicate the issue you are having.
>>>> 4) Are you running on 32-bit windows 7 or 64-bit
>>>> 5) What is your  PostgreSQL shared_buffers set to in postgresql.conf?
>>>> 6) What is the exact version of PostgreSQL 9.3 you are running:  SELECT
>>>> version();
>>>>
>>>> mine returns: PostgreSQL 9.3.5, compiled by Visual C++ build 1600,
>>>> 64-bit
>>>>
>>>> Thanks,
>>>> Regina
>>>> PostGIS PSC member and Windows PostGIS package maintainer
>>>> http://www.postgis.us
>>>> http://postgis.net
>>>>
>>>>
>>>>  ------------------------------
>>>> *From:* postgis-users-bounces at lists.osgeo.org [mailto:
>>>> postgis-users-bounces at lists.osgeo.org] *On Behalf Of *Brandon Abear
>>>> *Sent:* Sunday, December 21, 2014 10:21 PM
>>>> *To:* postgis-users at lists.osgeo.org
>>>> *Subject:* [postgis-users] Poor Geocoder Performance
>>>>
>>>>   I recently installed PostGIS 2.1.3 on a local PostgreSQL instance
>>>> (9.3). I imported the countrywide TIGER data set, installed the missing
>>>> indexes, and ran a vacuum analyze on everything.
>>>>
>>>> The geocoder is significantly slower than what is reported in the
>>>> documentation (http://postgis.net/docs/Geocode.html). The example
>>>> takes roughly 4 seconds to return a result while the documentation shows
>>>> ~61ms. There are other addresses which take nearly a minute to geocode. I
>>>> ran through a batch of 500 addresses to test, and only a handful returned a
>>>> rating under 20. I am running on Windows 7.
>>>>
>>>> I have looked through as many similar issues online as I could find. I
>>>> also changed some of the config settings such as shared_buffer, but the
>>>> performance increase was negligible. I am out of ideas. Has anyone run into
>>>> a similar issue and found a solution?
>>>>
>>>> Thank you for your time!
>>>>
>>>> --
>>>> *Brandon M. Abear*
>>>> *Carthage College, 2013*
>>>> Cell: (847) 848-3907
>>>> babear at carthage.edu
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at lists.osgeo.org
>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>
>>>
>>>
>>>
>>> --
>>> *Brandon M. Abear*
>>> *Carthage College, 2013*
>>> Cell: (847) 848-3907
>>> babear at carthage.edu
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>
>>
>>
>> --
>> *Brandon M. Abear*
>> *Carthage College, 2013*
>> Cell: (847) 848-3907
>> babear at carthage.edu
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>
>
> --
> *Brandon M. Abear*
> *Carthage College, 2013*
> Cell: (847) 848-3907
> babear at carthage.edu
>

-- 
*Brandon M. Abear*
*Carthage College, 2013*
Cell: (847) 848-3907
babear at carthage.edu
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20141222/371a8fca/attachment.html>