map_blind (10) [Avatar] Offline
#1
Tiger Geocoding -> Proper Query for enrolling results into geometry_column
Regina & Leo,
Congratulations on the new draft!

I downloaded State of New Jersey Composite of Parcel Data (NJ State Plane NAD83 FIPS 2900 Feet), sales transaction records, and owner records. These all appeared to have pin numbers that would allow relatively simple joining. As it turned out, the pin numbers were't unique and so only 53% of the parcel and transaction records joined.

I batch geocoded the 52,000 records (for my county of interest) that weren't pin unique thinking to do:
1) ST_Transform the geocode points to the NJ State Plane NAD83 that the polygons are in,
2) St_within (some short distance of my parcel polygons to help connect the
polys and geocoded points and thence the transaction records)

Then of course I got a little stumped because in my one trick pony way I only know how to manually update the geometry_column using the following:

NSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'public', 'HudsonCountyParcels', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM public."HudsonCountyParcels"
LIMIT 1;

Whereas the geocoder returns (name your table here) a table containing:
tableoid, cmax, xmax, cmin, xmin, ctid, addid, rating, full_address, norm_address, pt, owners_id. The owners_id field was my p_key field from the full list of transaction.

Am I trying to misapply the returned information or is there another query I should use to inform the geometry_column and thence allow me to see how these points plot against the polys in uDig?

Thanks for your guidance.

Chris
regina.leo (265) [Avatar] Offline
#2
Re: Tiger Geocoding -> Proper Query for enrolling results into geometry_column
Chris,

Have you tried using populate_geometry_columns?

http://www.postgis.org/documentation/manual-svn/Populate_Geometry_Columns.html
map_blind (10) [Avatar] Offline
#3
Re: Tiger Geocoding -> Proper Query for enrolling results into geometry_column
Thank you. Worked like a charm.