-- Function: fn_geocode(integer, character varying, character varying, character varying, character varying, integer, double precision)

-- DROP FUNCTION fn_geocode(integer, character varying, character varying, character varying, character varying, integer, double precision);

CREATE OR REPLACE FUNCTION fn_geocode (
	m_house_number integer, m_pre_dir character varying, m_street_name character varying, 
	m_street_type character varying, m_suf_dir character varying, m_zipcode integer, 
	m_offset double precision
)
  RETURNS geometry AS
$BODY$

	declare thePoint geometry;

BEGIN 
/*
	Match a point to a street segment and offset to the appropriate side by the given m_offset

	REVISION HISTORY
	2008-08-01	Stan S	Initial Coding
	2008-08-29	Stan S	Updated street matching to consider range and not just parity of street to determine side

*/

	BEGIN
		thePoint := (	
		SELECT fn_offsetpoint(the_geom, 
		CASE WHEN (m_house_number % 2 ) = (l_add_from % 2) AND (m_house_number BETWEEN l_add_from AND l_add_to ) THEN
			(m_house_number - l_add_from) / (l_add_to - l_add_from + .0001)	/* Avoid div by zero errors on single unit ranges */
		ELSE
			(m_house_number - r_add_from) / (r_add_to - r_add_from + .0001)
		END
		, m_offset,
		case when (m_house_number % 2 ) = (l_add_from % 2) AND (m_house_number BETWEEN l_add_from AND l_add_to ) THEN 'L' ELSE 'R' END)
		FROM countystreets 
		where replace(street_name,' ','') = replace(m_street_name,' ','')
		AND street_type = m_street_type
		AND pre_dir = m_pre_dir
		AND (
			(
				m_house_number BETWEEN l_add_from and l_add_to
				AND (m_house_number % 2 ) = (l_add_from % 2)
				AND m_zipcode = l_zipcode
			) OR (
				m_house_number BETWEEN r_add_from and r_add_to
				AND (m_house_number % 2 ) = (r_add_from % 2)
				AND m_zipcode = r_zipcode
			)
		)

		);
	EXCEPTION 
		/* More than one record returned from sub query / multiple line segment matches */
		WHEN CARDINALITY_VIOLATION THEN thePoint := NULL;
	END;

	RETURN thePoint;
END;
$BODY$
  LANGUAGE 'plpgsql' STABLE
  COST 100;
ALTER FUNCTION fn_geocode(integer, character varying, character varying, character varying, character varying, integer, double precision) OWNER TO postgres;
