pgAdmin pgScript     

Here is an example somewhat adapted from our upcoming Chapter 10: PostGIS in Action book. You would run a pgScript from a PgAdmin Query window by clicking the icon.

This code batch geocodes 500 records at a time committing every 500 records and repeats 20000 times.

DECLARE @I;
SET @I = 0;

WHILE @I < 20000
BEGIN

    UPDATE addr_to_geocode
    SET (rating, norm_address, pt)
    = (g.rating,
        COALESCE ((g.addy).address::text, '')
        || COALESCE(' ' || (g.addy).predirabbrev, '')
        || COALESCE(' ' || (g.addy).streetname,'')
        || ' ' || COALESCE(' ' || (g.addy).streettypeabbrev, '')
        || COALESCE(' ' || (g.addy).location || ', ', '')
        || COALESCE(' ' || (g.addy).stateabbrev, '')
        || COALESCE(' ' || (g.addy).zip, '')
    ,
    g.geomout
    )
        FROM (SELECT DISTINCT ON (addid) addid, (g1.geo).*
        FROM (SELECT addid, (geocode(address)) As geo
        FROM (SELECT * FROM addr_to_geocode WHERE ag.rating IS NULL ORDER BY addid LIMIT 500) As ag
    ) As g1
    -- 5 pick lowest rating
    ORDER BY addid, rating) As g
    WHERE g.addid = addr_to_geocode.addid;


    SET @I = @I + 1;
    PRINT @I;
END