4

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?

points and ways

2
  • 3
    It's extremely unlikely for points to intersect lines due to floating-point precision issues. Use a ST_DWithin with a small tolerance instead.
    – Vince
    Jul 22 at 16:21
  • And if intersection is a must, then you can add vertices to the lines at the proper locations, see this post and its st_snap solution
    – JGH
    Jul 22 at 16:32

1 Answer 1

5

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
;
1
  • 1
    It really worked, thank you very much and I hope this answer will serve more people. Jul 22 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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