[postgis-users] Poor Geocoder Performance

Mon Dec 22 08:46:04 PST 2014

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20141222/05503bd1/attachment.html>