[postgis-users] line_interpolate_point complains on 2nd Arg

[Kevin Neufeld]

Are you sure you've verified the range is [0,1]?  Try your query with 
just selecting the case statements:
SELECT
  CASE WHEN ... END AS param1,
  CASE WHEN ... END AS param2,
...
FROM countrystreets ...

If so, can you isolate the geometry that is causing the error?  If it's 
not too large, post the WKT or hexstring representation here. 

-- Kevin

Sufficool, Stanley wrote:
> 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
> 		)
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>