[postgis-users] geocoding is very slow

[Paragon Corporation]

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
h <http://www.postgis.us> ttp://www.postgis.us
h <http://postgis.net> ttp://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




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130423/0272acbb/attachment.html>