I have verified the CASE statements for the line_interpolate_point (returns .10101010101). My problem evidently was with line_substring where I was specifying the distance along the line instead of a float percentage. Problem solved.
It would be great if PostGIS had a line_interpolate_point_offset(line, percent, offset, angle) for doing this and offsetting the point to the left (angle = -90)/right (angle = 90) of the line segment to which it was matched. > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On > Behalf Of Kevin Neufeld > Sent: Tuesday, March 11, 2008 7:16 PM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] line_interpolate_point complains > on 2nd Arg > > > 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 [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > _______________________________________________ > postgis-users mailing list [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
