<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7654.12">
<TITLE>Geocode function fails when calling normalize_address</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->
<P><FONT SIZE=2 FACE="Arial">Hi All:</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">My batch geocode of 100K records is failing with error messages that refer to an operand in the normalize_address function. I based my single-column UPDATE on the multi-column example in "PostGIS In Action" and have tweaked the syntax only to achieve slight variation in the error message.</FONT></P>
<P><FONT SIZE=2 FACE="Arial">The query is something like:</FONT>
</P>
<BR>
<P><FONT SIZE=2 FACE="Arial">UPDATE respondents_addr SET the_geom =</FONT>
<BR><FONT SIZE=2 FACE="Arial"> g.geomout</FONT>
<BR><FONT SIZE=2 FACE="Arial"> FROM (SELECT DISTINCT ON (respondent, s_year) respondent, s_year, (geocode(address)).*</FONT>
<BR><FONT SIZE=2 FACE="Arial"> FROM respondents_addr As ra</FONT>
<BR><FONT SIZE=2 FACE="Arial"> WHERE ra.address IS NOT NULL</FONT>
<BR><FONT SIZE=2 FACE="Arial"> ORDER BY respondent, s_year, rating) As g</FONT>
<BR><FONT SIZE=2 FACE="Arial"> WHERE g.respondent = respondents_addr.respondent</FONT>
<BR><FONT SIZE=2 FACE="Arial"> AND g.s_year = respondents_addr.s_year;</FONT>
</P>
<BR>
<P><FONT SIZE=2 FACE="Arial">The most frequent error text is:</FONT>
</P>
<BR>
<P><FONT SIZE=2 FACE="Arial">ERROR: invalid regular expression: quantifier operand invalid</FONT>
<BR><FONT SIZE=2 FACE="Arial">CONTEXT: PL/pgSQL function "normalize_address" line 386 at assignment</FONT>
<BR><FONT SIZE=2 FACE="Arial">PL/pgSQL function "geocode" line 10 at assignment</FONT>
<BR><FONT SIZE=2 FACE="Arial">********** Error **********</FONT>
<BR><FONT SIZE=2 FACE="Arial">ERROR: invalid regular expression: quantifier operand invalid</FONT>
<BR><FONT SIZE=2 FACE="Arial">SQL state: 2201B</FONT>
<BR><FONT SIZE=2 FACE="Arial">Context: PL/pgSQL function "normalize_address" line 386 at assignment</FONT>
<BR><FONT SIZE=2 FACE="Arial">PL/pgSQL function "geocode" line 10 at assignment</FONT>
</P>
<BR>
<P><FONT SIZE=2 FACE="Arial">Also the query runs for more than an hour, (xp box with 3.5 GB ram, PosgreSQL 9.0.6, PostGIS 2.0.0 (might be the problem?)) and no matter how the error message changes it always references the normalization function, so I tried:</FONT></P>
<BR>
<P><FONT SIZE=2 FACE="Arial">SELECT (normalize_address(address)).* FROM respondents_addr WHERE address IS NOT NULL LIMIT 1000;</FONT>
</P>
<BR>
<P><FONT SIZE=2 FACE="Arial">With the LIMIT it ran quickly and returned the expected result, so I tried:</FONT>
</P>
<BR>
<P><FONT SIZE=2 FACE="Arial">SELECT (normalize_address(address)).* FROM respondents_addr WHERE address IS NOT NULL LIMIT 5000;</FONT>
</P>
<BR>
<P><FONT SIZE=2 FACE="Arial">Bumping the LIMIT up reproduced the error from the geocode attempts. I'd say "aha" if only I knew why.</FONT>
<BR><FONT SIZE=2 FACE="Arial">I checked the list archives and googled several combinations of words from the error but alas, to no avail. I suspect that I've either repeated a simple syntax error or that my setup is somehow off -- any help will be appreciatedand I might as well close with results of PostGIS_full_version():</FONT></P>
<BR>
<P><FONT SIZE=2 FACE="Arial">POSTGIS="2.0.0 r9605" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER</FONT></P>
<BR>
<P><FONT SIZE=2 FACE="Arial">Thank you,</FONT>
<BR><FONT SIZE=2 FACE="Arial">Rob</FONT>
</P>
</BODY>
</HTML>