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
