PostGIS – Locate Ways (MultiLineString) That Intersect Points

Question

I have two tables: crimes (points/ST_MakePoint) and ways (multilinestring). Initially what I did was reposition the points to the point closest to the track using the following:

UPDATE renzagoras.delitos as d 
SET   geom = ( 
    SELECT ST_ClosestPoint(r.geom, d.geom) 
    FROM   renzagoras.gis_osm_roads_free_1 r 
    WHERE  ST_DWithin(d.geom, r.geom, 10) 
    ORDER BY r.geom <-> d.geom LIMIT 1 ) 
WHERE _id = :id

Now I am interested in knowing which are the ways where these points are located and for this I tried to use ST_Intersect but it did not return all the ways (only some). Has anyone already experienced this type of case?

Answer 1

This is a common issue with derived geometries and the capacity of the numerical data types used to calculate the intersection between a positional and a direction vector - i.e. floating point inaccuracies.

---

I'd suggest to include the way id in the UPDATE statement; assuming you have another column way_id in your renzagoras.delitos table, run:

UPDATE
  renzagoras.delitos as d 
  SET
    (way_id, geom) = (
      SELECT
        <id>,
        ST_ClosestPoint(r.geom, d.geom)
      FROM
        renzagoras.gis_osm_roads_free_1 r 
      ORDER BY
        r.geom <-> d.geom
      LIMIT 1
    )
;

Alternatively, you can run a (K)NN query to identify the way ids:

SELECT
  d.*,
  w.<id>
FROM
  renzagoras.delitos as d 
CROSS JOIN LATERAL (
  SELECT
    <id>
  FROM
    renzagoras.gis_osm_roads_free_1 r 
  ORDER BY
    r.geom <-> d.geom
  LIMIT 1
) AS w
;