Okay, so I'm new at SQL, so bear with me. I'm trying to use the tiger extensions on postGIS (2.1.7) to normalize my addresses and then geocode them. The normalize query runs, but never returns an answer. Here's my table:

CREATE TABLE public.clients
 usb_number numeric,
 affiliation text,
 type_company text,
 status text,
 region text,
 company_name text,
 o_address text,
 street_num numeric,
 street_name text,
 street_abr text,
 city text,
 state_s text,
 zip character varying,
 rating numeric,
 lon double precision,
 lat double precision,
 addid integer NOT NULL DEFAULT nextval('clients_addid_seq'::regclass),
 new_address text

Here's my query:

UPDATE clients SET 
   new_address = subquery.address,
   street_num = subquery.address,
   street_name = subquery.streetname,
   street_abr = subquery.streettypeabbrev,
   city = subquery.location,
   state_s = subquery.stateAbbrev,
   zip = subquery.zip
FROM (SELECT o_address As orig, (g.na).address, (g.na).streetname, (g.na).streettypeabbrev, (g.na).location, (g.na).stateAbbrev, (g.na).zip
   FROM (SELECT address, normalize_address(address) As na
       FROM clients) As g) AS subquery;

What am I doing wrong here?


Got it!!

UPDATE clients SET 
   new_address = (subquery).address,
   street_num = (subquery).address,
   street_name = (subquery).streetname,
   street_abr = (subquery).streettypeabbrev,
   city = (subquery).location,
   state_s = (subquery).stateAbbrev,
   zip = (subquery).zip
FROM (SELECT c_address As orig, (g.na).address, (g.na).streetname, (g.na).streettypeabbrev, (g.na).location, (g.na).stateAbbrev, (g.na).zip
   FROM (SELECT c_address, normalize_address(c_address) As na
       FROM clients) As g) AS subquery
           WHERE c_address = orig;
  • you should mark your answer. – LR1234567 Nov 11 '15 at 18:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.