[postgis-users] Geocoding on Windows very slow

[Paragon Corporation]

Upgrading the scripts should be sufficient.  We haven't added any columns
since. 

-----Original Message-----
From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Jonathan Haglund
Sent: Friday, September 27, 2013 12:44 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Geocoding on Windows very slow

Am I able to just upgrade the scripts in the tiger directory or do I need to
upgrade the whole install to 2.1.1?

Jonathan Haglund
The Go Travel Sites
801-783-5071
jonathan.haglund at gotravelsites.com
www.gotravelsites.com



On Thursday, September 26, 2013 9:58:40 PM, Paragon Corporation wrote:
> Jonathan,
>
> IT's hard to say since like I said unfortunately you have a bad build.  
> You should probably upgrade your scripts to 2.1.1 version (which is 
> now upgraded to use new tiger 2013 data). I made a lot of corrections 
> since the build you have.  One major one that comes to mind is that in 
> PostgreSQL 9.2 I noticed it was slower for some things than it was in 9.1.
>
> So to work around the problem I had this line which is in the newer 
> versions.  You might want to check that is set on your function or 
> play around with it.
>
> ALTER FUNCTION tiger.geocode_address(norm_addy, integer, geometry) SET 
> join_collapse_limit='2';
>
>
> To debug the plans, we have some settings stored in geocode_settings 
> table
>
> http://postgis.net/docs/manual-2.1/Set_Geocode_Setting.html
>
> Try SELECT set_geocode_setting('debug_geocode_address', 'true') As 
> result;
>
>
> This will output the generated SQL in NOTICE.  You can then run the 
> code threw EXPLAIN to see where the time is being spent.
>
>
> Hope that helps,
> Regina
> http://www.postgis.us
> http://postgis.net
>
>
> -----Original Message-----
> From: postgis-users-bounces at lists.osgeo.org
> [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Jonathan 
> Haglund
> Sent: Thursday, September 26, 2013 3:30 PM
> To: PostGIS Users Discussion
> Subject: [postgis-users] Geocoding on Windows very slow
>
> The following query takes around 28 seconds to complete:
>
> select g.rating, ST_X(geomout) as lon, ST_Y(geomout) as lat, (addy).* 
> from geocode( '601 15th Ave NW, Birmingham, Alabama, 35215', 1 ) as g
>
> I did select install_missing_indexes(), tried vacuum analyze on the 
> top-level tables, and thrown lots of resources and posgresql.  My 
> system does not want for CPU or ram, the databases are on a raid 10.  
> I have 17 states' data loaded, in case that matters.
>
> When I loaded the tiger data I ran into a problem with the windows 
> version of the scripts and had to alter the top-level tables before 
> being able to do the states.  As a result tlid moved to the last 
> position.  I made no other alterations.  I think this would affect 
> index performance on mysql but I don't have experience with this in 
> postgresql, so that's part of my question.
>
> What might I look for next in troubleshooting?  Does 2.1 promise to 
> fix all my woes as I have read in other performance questions?
>
> My versions:
>
> PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit
> POSTGIS="2.0.3 r11132" 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" LIBXML="2.7.8"
> LIBJSON="UNKNOWN" RASTER
> _______________________________________________
> 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
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users