1CREATE OR REPLACE FUNCTION geocode( 2 input VARCHAR, max_results integer DEFAULT 10, 3 restrict_geom geometry DEFAULT NULL, 4 OUT ADDY NORM_ADDY, 5 OUT GEOMOUT GEOMETRY, 6 OUT RATING INTEGER 7) RETURNS SETOF RECORD 8AS $_$ 9DECLARE 10 rec RECORD; 11BEGIN 12 13 IF input IS NULL THEN 14 RETURN; 15 END IF; 16 17 -- Pass the input string into the address normalizer 18 ADDY := normalize_address(input); 19 IF NOT ADDY.parsed THEN 20 RETURN; 21 END IF; 22 23/* FOR rec IN SELECT * FROM geocode(ADDY) 24 LOOP 25 26 ADDY := rec.addy; 27 GEOMOUT := rec.geomout; 28 RATING := rec.rating; 29 30 RETURN NEXT; 31 END LOOP;*/ 32 33 RETURN QUERY SELECT g.addy, g.geomout, g.rating FROM geocode(ADDY, max_results, restrict_geom) As g ORDER BY g.rating; 34 35END; 36$_$ LANGUAGE plpgsql COST 1000 37STABLE PARALLEL SAFE 38ROWS 1; 39 40CREATE OR REPLACE FUNCTION geocode( 41 IN_ADDY NORM_ADDY, 42 max_results integer DEFAULT 10, 43 restrict_geom geometry DEFAULT null, 44 OUT ADDY NORM_ADDY, 45 OUT GEOMOUT GEOMETRY, 46 OUT RATING INTEGER 47) RETURNS SETOF RECORD 48AS $_$ 49DECLARE 50 rec RECORD; 51BEGIN 52 53 IF NOT IN_ADDY.parsed THEN 54 RETURN; 55 END IF; 56 57 -- Go for the full monty if we've got enough info 58 IF IN_ADDY.streetName IS NOT NULL AND 59 (IN_ADDY.zip IS NOT NULL OR IN_ADDY.stateAbbrev IS NOT NULL) THEN 60 61 FOR rec IN 62 SELECT * 63 FROM 64 (SELECT 65 DISTINCT ON ( 66 (a.addy).address, 67 (a.addy).predirabbrev, 68 (a.addy).streetname, 69 (a.addy).streettypeabbrev, 70 (a.addy).postdirabbrev, 71 (a.addy).internal, 72 (a.addy).location, 73 (a.addy).stateabbrev, 74 (a.addy).zip 75 ) 76 * 77 FROM 78 tiger.geocode_address(IN_ADDY, max_results, restrict_geom) a 79 ORDER BY 80 (a.addy).address, 81 (a.addy).predirabbrev, 82 (a.addy).streetname, 83 (a.addy).streettypeabbrev, 84 (a.addy).postdirabbrev, 85 (a.addy).internal, 86 (a.addy).location, 87 (a.addy).stateabbrev, 88 (a.addy).zip, 89 a.rating 90 ) as b 91 ORDER BY b.rating LIMIT max_results 92 LOOP 93 94 ADDY := rec.addy; 95 GEOMOUT := rec.geomout; 96 RATING := rec.rating; 97 98 RETURN NEXT; 99 100 IF RATING = 0 THEN 101 RETURN; 102 END IF; 103 104 END LOOP; 105 106 IF RATING IS NOT NULL THEN 107 RETURN; 108 END IF; 109 END IF; 110 111 -- No zip code, try state/location, need both or we'll get too much stuffs. 112 IF IN_ADDY.zip IS NOT NULL OR (IN_ADDY.stateAbbrev IS NOT NULL AND IN_ADDY.location IS NOT NULL) THEN 113 FOR rec in SELECT * FROM tiger.geocode_location(IN_ADDY, restrict_geom) As b ORDER BY b.rating LIMIT max_results 114 LOOP 115 ADDY := rec.addy; 116 GEOMOUT := rec.geomout; 117 RATING := rec.rating; 118 119 RETURN NEXT; 120 IF RATING = 100 THEN 121 RETURN; 122 END IF; 123 END LOOP; 124 125 END IF; 126 127 RETURN; 128 129END; 130$_$ LANGUAGE plpgsql 131COST 1000 132STABLE PARALLEL SAFE 133ROWS 1; 134