nicklas (2) [Avatar] Offline
#1
Hallo

Fantastic book. I really enjoy reading it, but it will take a while to digest it all smilie

Just for the record, I think the new possibilities of snapping with st_closestpoint might be worth mentioning because they give the possibility to use the same approach with polygons, multipoints and collections.

the Listing 8.14 Query to snap points to linestring without subquery – version 2
would then look something like:

SELECT DISTINCT ON (pt.gid) st_closestpoint(ln.the_geom, pt.the_geom) as the_geom, pt.gid AS pt_id
FROM
point_table AS pt
INNER JOIN
line_table AS ln
ON ST_DWithin(pt.the_geom, ln.the_geom, 10.0)
ORDER BY pt.gid,ST_Distance(ln.the_geom, pt.the_geom);

and an even more wild approach would be to let the closestline-function do the finding of the closest line too. that would be possible by collecting all lines of interest into a collection and feed st_closestpoint with that collection. Then it would look something like:

SELECT st_closestpoint(st_collect(ln.the_geom), pt.the_geom) as the_geom, pt.gid
FROM
point_table AS pt
INNER JOIN
line_table AS ln
ON ST_DWithin(pt.the_geom, ln.the_geom, 10.0)
group by pt.the_geom, pt.gid;


Regards
Nicklas
regina.leo (265) [Avatar] Offline
#2
Re: alternative approach to Listing 8.14
Thanks Nicklas,

We had finised that chapter mostly before you put in those neat new functions. We were hoping ot go back and add them in to compare speeds. This is a great idea to replacee with ST_ClosestPoint. Will have to give that a try.

Thanks,
Regina