[postgis-users] line_interpolate_point complains on 2nd Arg

Sufficool, Stanley ssufficool at rov.sbcounty.gov
Tue Mar 11 16:35:29 PDT 2008


I have been attempting to find a SQL only geocode query for our specific
dataset and arrived at the following. line_interpolate_point complains
that the 2nd param is out of range [0,1]. I verified that the value
returned is 0.101010101010101 (float8). I have replaced the "CASE" with
this value and it still complains.

The query interpolates the point, grabs the line segment and offsets the
point to the left/right of the street segment by 50 units.

PostgreSQL 8.0.15, Gentoo Linux x86, PostGIS 1.3.1

SELECT 
	translate(
		line_interpolate_point (
			the_geom, 
			CASE WHEN ( (320 % 2) = (l_add_from % 2) )
				THEN ( (320 - l_add_from) / (l_add_to -
l_add_from) )
				ELSE ( (320 - r_add_from) / (r_add_to -
r_add_from) )
			END::float
		),
		CASE WHEN (320 % 2) = (l_add_from % 2) THEN 
				(x(line_substring(the_geom, 
				length(the_geom) * ( (320 - l_add_from)
/ (l_add_to - l_add_from) ),
				length(the_geom) * ( (320 - l_add_from)
/ (l_add_to - l_add_from) ) + 50)))
			ELSE
				-(x(line_substring(the_geom, 
				length(the_geom) * ( (320 - r_add_from)
/ (r_add_to - r_add_from) ),
				length(the_geom) * ( (320 - r_add_from)
/ (r_add_to - r_add_from) ) + 50)))
		END,
		CASE WHEN (320 % 2) = (l_add_from % 2) THEN 
				-(y(line_substring(the_geom, 
				length(the_geom) * ( (320 - l_add_from)
/ (l_add_to - l_add_from) ),
				length(the_geom) * ( (320 - l_add_from)
/ (l_add_to - l_add_from) ) + 50)))
			ELSE
				(y(line_substring(the_geom, 
				length(the_geom) * ( (320 - r_add_from)
/ (r_add_to - r_add_from) ),
				length(the_geom) * ( (320 - r_add_from)
/ (r_add_to - r_add_from) ) + 50)))
		END
	) as address_point,
	CASE WHEN (320 % 2) = (l_add_from % 2) THEN 'LEFT' else 'RIGHT'
END as street_side
	FROM countystreets s 
	WHERE CASE WHEN (320 % 2) = (l_add_from % 2) THEN (l_add_to -
l_add_from) ELSE (r_add_to - r_add_from) END > 0
	AND s.pre_dir = 'E'
	AND replace(s.street_name,' ','') = 'PHILLIPS' 
	AND s.street_type = 'ST' 
	AND s.suf_dir = '' 
	AND s.l_zipcode = substr('91761-4230',1, 5) 
	AND (
		320 BETWEEN l_add_from and l_add_to
		OR 320 BETWEEN r_add_from and r_add_to
		)



More information about the postgis-users mailing list