In my PostGIS database (PostGIS 1.5 on Postgres 8.4.1), I have two tables: roads (composed of linestrings) and crashes (composed of points). I've been trying to correlate each crash to a road, but I'm having problems getting the following to work:
SELECT ST_ClosestPoint(road.the_geom, crash.the_geom), ST_Intersects(ST_ClosestPoint(road.the_geom, crash.the_geom), road.the_geom) ST_Distance(ST_ClosestPoint(road.the_geom, crash.the_geom), crash.the_geom) AS distance FROM --Table crashes is already in SRID 4326 (SELECT the_geom FROM crashes WHERE gid = 360) as crash, (SELECT ST_SetSrid(the_geom, 4326) as the_geom from roads) as road ORDER BY distance;
This queury should return the closest point to the crash with gid 360 on each road, but the ST_Intersects function returns false for the first result (the true closest point over all roads). Am I doing something wrong? Is there another way to link the crash to the closest road?