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
