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 STABLE; 37 38CREATE OR REPLACE FUNCTION geocode( 39 IN_ADDY NORM_ADDY, 40 max_results integer DEFAULT 10, 41 restrict_geom geometry DEFAULT null, 42 OUT ADDY NORM_ADDY, 43 OUT GEOMOUT GEOMETRY, 44 OUT RATING INTEGER 45) RETURNS SETOF RECORD 46AS $_$ 47DECLARE 48 rec RECORD; 49BEGIN 50 51 IF NOT IN_ADDY.parsed THEN 52 RETURN; 53 END IF; 54 55 -- Go for the full monty if we've got enough info 56 IF IN_ADDY.streetName IS NOT NULL AND 57 (IN_ADDY.zip IS NOT NULL OR IN_ADDY.stateAbbrev IS NOT NULL) THEN 58 59 FOR rec IN 60 SELECT * 61 FROM 62 (SELECT 63 DISTINCT ON ( 64 (a.addy).address, 65 (a.addy).predirabbrev, 66 (a.addy).streetname, 67 (a.addy).streettypeabbrev, 68 (a.addy).postdirabbrev, 69 (a.addy).internal, 70 (a.addy).location, 71 (a.addy).stateabbrev, 72 (a.addy).zip 73 ) 74 * 75 FROM 76 tiger.geocode_address(IN_ADDY, max_results, restrict_geom) a 77 ORDER BY 78 (a.addy).address, 79 (a.addy).predirabbrev, 80 (a.addy).streetname, 81 (a.addy).streettypeabbrev, 82 (a.addy).postdirabbrev, 83 (a.addy).internal, 84 (a.addy).location, 85 (a.addy).stateabbrev, 86 (a.addy).zip, 87 a.rating 88 ) as b 89 ORDER BY b.rating LIMIT max_results 90 LOOP 91 92 ADDY := rec.addy; 93 GEOMOUT := rec.geomout; 94 RATING := rec.rating; 95 96 RETURN NEXT; 97 98 IF RATING = 0 THEN 99 RETURN; 100 END IF; 101 102 END LOOP; 103 104 IF RATING IS NOT NULL THEN 105 RETURN; 106 END IF; 107 END IF; 108 109 -- No zip code, try state/location, need both or we'll get too much stuffs. 110 IF IN_ADDY.zip IS NOT NULL OR (IN_ADDY.stateAbbrev IS NOT NULL AND IN_ADDY.location IS NOT NULL) THEN 111 FOR rec in SELECT * FROM tiger.geocode_location(IN_ADDY, restrict_geom) As b ORDER BY b.rating LIMIT max_results 112 LOOP 113 ADDY := rec.addy; 114 GEOMOUT := rec.geomout; 115 RATING := rec.rating; 116 117 RETURN NEXT; 118 IF RATING = 100 THEN 119 RETURN; 120 END IF; 121 END LOOP; 122 123 END IF; 124 125 RETURN; 126 127END; 128$_$ LANGUAGE plpgsql STABLE 129 COST 1000; 130