Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer 1

up vote 7 down vote accepted

This is a problem coming from that ST_Intersects has no tolerance. Even if double precision coordinates holds a lot of decimals they form a grid where the only places for the points is in the crossings. Often the line doesn't intersect with any of those crosses and there is no way any point will intersect the line exactly. The workaround is to use st_dwithin instead with a small tolerance.

/Nicklas

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.