[postgis-users] Re: TIGER geocoder question

[Marvin]

Hi, Stephen,

It looks like that there are more problems than I expected. I tried to run
"geocde" and it returned a geom object. Then I created a tmp_geocode_result
table that has the same structure of the returned cursor and a function to
display the coordinates of the geom object. However, I keep getting the
message "ERROR:  value too long for type character varying(90)" when running
my function. It seems that the tmp table doesn't have the exact same
structure as the cursor and the geom object was filled into a wrong place. I
listed the table tmp_geocode_result and the function below. Could you give
me some advise? Thanks.

CREATE TABLE tmp_geocode_result (
   id      INTEGER,
   tlid    INTEGER,
   fedirp  VARCHAR(2),
   fename  VARCHAR(30),
   fetype  VARCHAR(4),
   fedirs  VARCHAR(2),
   zip     INTEGER,
   state   VARCHAR(2),
   county  VARCHAR(90),
   cousub  VARCHAR(90),
   place   VARCHAR(90),
   rating  INTEGER
);

SELECT
AddGeometryColumn('tmp_geocode_result','address_geom',4269,'POINT',3);


CREATE OR REPLACE FUNCTION geocode_test(add VARCHAR) RETURNS VARCHAR AS $$
DECLARE
    result tmp_geocode_result%ROWTYPE;
    cursor1 refcursor;
BEGIN
    cursor1 := geocode(cursor1,add);
    FETCH cursor1 into result;
    RETURN AsText(result.address_geom);
END;
$$ LANGUAGE plpgsql


On 6/28/07, Shuo Liu <delphet at gmail.com> wrote:
>
> Hi, All,
>
> I'm working on a GIS project and trying to use TIGER Geocoder from the
> refractions website on TIGER data. The two sql files in the Geocoder
> generated some errors when being loaded, complaining that some tables
> ("gazetteer_places", "tiger_geocode_roads", "place_lookup", "roads_local",
> and "countysub_lookup") are missing. Some messages from the mailing list say
> that gazetteer tables should be loaded from the Census Bureau gazetteer
> files. But that doesn't help find "tiger_geocode_roads" and "roads_local"
> which don't exist in the loaded TIGER database. I used ogr2ogr to load the
> TIGER data (
> http://docs.codehaus.org/display/GEOSDOC/Loading+TIGER+basedata) and it
> seems that some fields required by the Geocoder are in "completechain" but
> not all. Can anybody who have experience share some hint on this problem?
> Thank you very much.
>
> Marvin
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070629/3351632a/attachment.html>