[postgis-users] geocoding is very slow

[Shawn Peterson]

Hi,

Thanks for the advice. I applied "vacuum analyze" and it solved the problem!

Thanks a lot,
Shawn


On Mon, Apr 22, 2013 at 10:38 PM, Paragon Corporation <lr at pcorp.us> wrote:

> **
> You might want to vacuum analyze.  I got around to loading California data
> and out of the box it was slow as you said, but then I forgot I had't done
> a vacuum analyze and then your example went down to about 60-82ms.
>
> Most important tables to vacuum analyze
>
>
> vacuum analyze verbose edges;
> vacuum analyze verbose faces;
> vacuum analyze verbose addr;
>
> This took 78ms
>
> SELECT g.rating, ST_Y(g.geomout) As lat, ST_X(g.geomout) As lng,
> (addy).address As stno, (addy).streetname As street,
> (addy).streettypeabbrev As styp, (addy).location As city,
> (addy).stateabbrev As st,(addy).zip
> FROM geocode('675 Westwood Plaza, Los Angeles, CA 90024') As g;
>
> and output:
>
>  rating |       lat        |        lng        | stno |  street  | styp
> |    city     | st |  zip
>
> --------+------------------+-------------------+------+----------+------+-------------+----+-------
>       6 | 34.0710080002074 | -118.444804646536 |  700 | Westwood | Plz  |
> Los Angeles | CA | 90024
>      22 | 34.0592340476004 | -118.444228693569 | 1160 | Westwood | Blvd |
> Los Angeles | CA | 90024
>      24 | 34.0459149281485 | -118.433621998435 | 2001 | Westwood | Blvd |
> Los Angeles | CA | 90025
>
> If you only want one answer pass in optional number of records:
>
> SELECT g.rating, ST_Y(g.geomout) As lat, ST_X(g.geomout) As lng,
> (addy).address As stno, (addy).streetname As street,
> (addy).streettypeabbrev As styp, (addy).location As city,
> (addy).stateabbrev As st,(addy).zip
> FROM geocode('675 Westwood Plaza, Los Angeles, CA 90024',1) As g;
>
>  ------------------------------
> *From:* postgis-users-bounces at lists.osgeo.org [mailto:
> postgis-users-bounces at lists.osgeo.org] *On Behalf Of *Shawn Peterson
> *Sent:* Monday, April 22, 2013 10:57 PM
> *To:* PostGIS Users Discussion
> *Subject:* Re: [postgis-users] geocoding is very slow
>
>  Hi Regina,
>
> Thanks for the tips. I tried "SELECT install_missing_indexes();" but it
> does not help.
>
> My OS information:
>
>  PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (SUSE
> Linux) 4.3.4 [gcc-4_3-branch revision 152973], 64-bit POSTGIS="2.0.4SVN
> r11295" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL
> 1.9.2, released 2012/10/08 GDAL_DATA not found" LIBXML="2.9.0" RASTER
>
>
> I'll try to install PostGIS 2.1 to see if it helps.
>
> Thanks,
> Shawn
>
>
>
> On Mon, Apr 22, 2013 at 12:30 PM, Paragon Corporation <lr at pcorp.us> wrote:
>
>> **
>> Shawn,
>> Well it should be somewhere between 40-200ms.
>>
>> So yah something seems wrong there. Usual culprit is missing indexes.
>>
>> Try:
>>
>> SELECT install_missing_indexes();
>>
>>
>> I don't have California data loaded so can't compare and am also running
>> with PostGIS 2.1 tiger_geocoder which has some major improvements.
>> Which OS are you running on?
>>
>> SELECT version() || ' ' || postgis_full_version();
>>
>>
>> Hope that helps,
>> Regina
>> 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 *Shawn Peterson
>> *Sent:* Monday, April 22, 2013 2:05 PM
>> *To:* postgis-users at lists.osgeo.org
>> *Subject:* [postgis-users] geocoding is very slow
>>
>>   I have successfully installed postgresq and postgis. I tried the
>> examples (addresses in MA) from
>> http://postgis.refractions.net/docs/Geocode.html and the speed seems
>> fine. However, when I tested it on addresses from CA, it is extremely slow:
>>
>> (~ 2 min 20 sec)
>> SELECT g.rating, ST_Y(g.geomout) As lat, ST_X(g.geomout) As lng,
>> (addy).address As stno, (addy).streetname As street,
>> (addy).streettypeabbrev As styp, (addy).location As city,
>> (addy).stateabbrev As st,(addy).zip
>> FROM geocode('*7788 Regents Road, San Diego, CA 92122*') As g;
>>
>> (~ 2 min 40 sec)
>> SELECT g.rating, ST_Y(g.geomout) As lat, ST_X(g.geomout) As lng,
>> (addy).address As stno, (addy).streetname As street,
>> (addy).streettypeabbrev As styp, (addy).location As city,
>> (addy).stateabbrev As st,(addy).zip
>> FROM geocode('*675 Westwood Plaza, Los Angeles, CA 90024*') As g;
>>
>> My goal is to do bulk geocoding. With the current speed I would have to
>> give up postgis. Can anyone help? Any input is appreciated!
>>
>> P.S.: Here is some information for my system:
>> CPU: Intel Xeon 2.67GHZ
>> postgresql: 9.2.4
>> postgis: 2.0.4SVN
>>
>> I've tried to adjust some parameters in postgresql.conf, but it's still
>> as slow as with default parameters:
>> shared_buffers = 500MB
>> work_mem = 16MB
>> maintenance_work_mem = 16MB
>> wal_buffers = 1MB
>> checkpoint_segments = 6
>> seq_page_cost = 1.0
>> random_page_cost = 2.0
>> join_collapse_limit = 2
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130423/367780f6/attachment.html>