Hi, All,
I'm working on a GIS project using PostgreSQL and PostGIS. In the project I need to find locations of about 12K addresses (the process is referred to as geocoding). I wrote some script to perform this task by calling a procedure "tiger_geocoding" that is provided by PostGIS. My script seems to crash the server after a while with the following message:
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
The log shows the following message:
CurTransactionContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExecutorState: 122880 total in 4 blocks; 1912 free (9 chunks); 120968 used
ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
ExprContext: 8192 total in 1 blocks; 8000 free (1 chunks); 192 used
ExprContext: 8192 total in 1 blocks; 8000 free (1 chunks); 192 used
ExprContext: 8192 total in 1 blocks; 8096 free (1 chunks); 96 used
SPI Exec: 0 total in 0 blocks; 0 free (0 chunks); 0 used
SPI Proc: 8192 total in 1 blocks; 2616 free (0 chunks); 5576 used
ExecutorState: 57344 total in 3 blocks; 35776 free (7 chunks); 21568 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
2007-07-10 12:25:57 LOG: server process (PID 2004) exited with exit code -1073741819
2007-07-10 12:25:57 LOG: terminating any other active server processes
2007-07-10 12:25:59 WARNING: terminating connection because of crash of another server process
My script is listed below:
CREATE OR REPLACE FUNCTION geocode_fall2006 () RETURNS VOID AS $$
DECLARE
address VARCHAR(255);
geom GEOMETRY;
tmp_geom GEOMETRY;
counter INTEGER := 0;
app_id VARCHAR(50);
st VARCHAR(50);
f_processed BOOLEAN;
f_geocoded BOOLEAN;
BEGIN
FOR app_id, address, st, f_processed, f_geocoded IN SELECT APPLICATION_ID, add, state, geoprocessed, geocoded FROM fall2006 LOOP
RAISE NOTICE 'add=%, app_id=%, state=%', address, app_id, st;
IF upper(st)='OH' AND f_processed='f'
THEN
geom := geocode_test(address);
IF geom IS NOT NULL
THEN
counter := counter + 1;
-- TIGER SRID is 32767.
tmp_geom := PointFromText('POINT(' || X(geom) || ' ' || Y(geom) || ')',4269);
tmp_geom := transform(tmp_geom,4326);
-- id := SRID(tmp_geom);
-- RAISE NOTICE 'srid=%', id;
UPDATE fall2006 SET lat_lon = tmp_geom WHERE APPLICATION_ID = app_id;
UPDATE fall2006 SET geocoded = 't' WHERE APPLICATION_ID = app_id;
RAISE NOTICE 'UPDATE fall2006 SET lat_lon = % WHERE APPLICATION_ID = %;', AsText(tmp_geom), app_id;
END IF;
UPDATE fall2006 SET geoprocessed = 't' WHERE APPLICATION_ID = app_id;
END IF;
END LOOP;
RAISE NOTICE 'counter=%', counter;
END;
$$ LANGUAGE plpgsql
I googled and found a similar bug was reported for version 8.1 and was claimed to be fixed (http://archives.postgresql.org/pgsql-bugs/2006-12/msg00214.php). However, the PostgreSQL in my machine is 8.2.4, which is supposed to be free of the bug. Any suggestion will be greatly appreciated.
_______________________________________________
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
