Hi All:
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.
The query is something like:
UPDATE respondents_addr SET the_geom =
g.geomout
FROM (SELECT DISTINCT ON (respondent, s_year) respondent, s_year,
(geocode(address)).*
FROM respondents_addr As ra
WHERE ra.address IS NOT NULL
ORDER BY respondent, s_year, rating) As g
WHERE g.respondent = respondents_addr.respondent
AND g.s_year = respondents_addr.s_year;
The most frequent error text is:
ERROR: invalid regular expression: quantifier operand invalid
CONTEXT: PL/pgSQL function "normalize_address" line 386 at assignment
PL/pgSQL function "geocode" line 10 at assignment
********** Error **********
ERROR: invalid regular expression: quantifier operand invalid
SQL state: 2201B
Context: PL/pgSQL function "normalize_address" line 386 at assignment
PL/pgSQL function "geocode" line 10 at assignment
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:
SELECT (normalize_address(address)).* FROM respondents_addr WHERE
address IS NOT NULL LIMIT 1000;
With the LIMIT it ran quickly and returned the expected result, so I
tried:
SELECT (normalize_address(address)).* FROM respondents_addr WHERE
address IS NOT NULL LIMIT 5000;
Bumping the LIMIT up reproduced the error from the geocode attempts. I'd
say "aha" if only I knew why.
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():
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
Thank you,
Rob
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
