As described earlier there is also some new distance related functions in new PostGIS release. Since I am in some way responsible for their existance I feel a need to give some examples of how they can be used.
Example 1 Snap GPS-points to roads
If collecting GPS-points along a road you will get them placed on the side of the roadline because of the inaccurancy of the GPS and the road on the map. One way to get the points on the road is to use ST_Closestpoint.
We have a table with our roads called ….. roads (why not) and another table called gpspoints with pour GPS points. The gepspoints are as expected placed, not on the road, but next to the road. There is also a GPS point that probably have been collected when walking in the forest since it is to far away from the road.
Now lets do a first attempt to move the points to the roads. We do:
Select ST_Closestpoint(a.the_geom, b.the_geom) as the_geom
from roads a inner join gpspoints b
on ST_Dwithin(a.the_geom, b.the_geom, 100);
We count on that no GPS points should be more than 100 meters away from the road if it is collected from the road. That much error should never be from the GPS, but it is possible if it is just started and so on. The result from our query will be the green dots below:
But there is a problem. Not the point out in the forest. That one we have abandoned since it is more than 100 meters from the road. But the point between two road parcels a little bitdown from the middle of the map. That GPS point did get two new points insted of just one. What has happened is that every road feature closer than 100 meters of a GPS point has had a point from that GPS point.
This can be solved by collecting all road features that is within those 100 meters from the GPS point into a geometry collection which we put into the ST_Closestpoint function. Since grouping by geometries often is a bad idea we group by the id of the road features but also group by the geometriy just to avoid the need of aggregating also the GPS points. The query will look something like:
Select ST_Closestpoint(ST_Collect(a.the_geom), b.the_geom) as the_geom
from roads a inner join gpspoints b
on ST_Dwithin(a.the_geom, b.the_geom, 100)
group by b.gid, b.the_geom;
and the result will be, again like the green dots:
That’s better. Now it looks like expected.