3

I write a request that allows to snap a point to another. Here is the request:

 SELECT
        f.gid as gid,
        ST_Snap (f.Geom, g.Geom, min, 5) as geom
 FROM
        boiten as f,
        (SELECT ST_Collect (Geom) as Geom
        FROM ft_chambre) as g

It works but the snapped points missed the closest position

Update:

For the CRS it's 2154 ,I want to snap the point to the red ones enter image description here I modified my code to this:

SELECT
   f.gid as gid,
   ST_Snap(f.Geom, g.Geom, st_distance(f.Geom, g.Geom)*1.2) as geom
FROM
   boiten as f,
   (SELECT ST_Collect(Geom) as Geom
   FROM ft_chambre) as g

enter image description here But like you see there are points witch snapped to the wrong place or has snapped to more than one.

For exemple This point has snapped to the wrong place where the other point has already snapped to enter image description here

geozelot
  • 30,050
  • 4
  • 32
  • 56
Rania ben othmen
  • 83
  • 5
  • How do the geometries look like? What are their coordinates? What's the CRS you're using for storing the data and for displaying the results? – IvanSanchez Jan 24 '18 at 15:30
  • What do you mean "snapped to the wrong place"? Can you give us an example of an expected behaviour, an what is actually happening instead? – Roberto Ribeiro Jan 24 '18 at 15:49
  • so you want each greenish point (f.Geom, I guess?) to snap to the nearest red point (g.Geom ?) only if that one is not already occupied? – geozelot Jan 24 '18 at 16:04
  • Yes and no i want to each point to snap to the nearest point even if ther is two points that are near each other they can snap to the same red one but in this case i have the green point wich snapped to the red wich is far – Rania ben othmen Jan 24 '18 at 16:08
  • 1
    in this case, is there another red point closer? if not, your query works just fine. the st_distance you use as tolerance does alway find a point to snap to, but having a multipoint as reference geometry does make that harder to control. – geozelot Jan 24 '18 at 17:12

1 Answers1

6

A quick hack to guarantee that you always snap to the closest point:

SELECT f.gid AS gid,
       ST_Snap(
           f.Geom,
           ST_ClosestPoint(g.Geom, f.Geom),
           ST_Distance(
               f.Geom,
               ST_ClosestPoint(g.Geom, f.Geom)
            ) * 1.01
        ) AS geom
FROM (
    SELECT ST_Collect(Geom) as Geom
    FROM ft_chambre
) AS g,
boiten AS f

Let me add: if this gives you the same (apoarently wrong) result, it's the tolerance that you need to define properly. If there are points that you don't want to be snapped due to them being too far, you need to specify what distance is too far.

geozelot
  • 30,050
  • 4
  • 32
  • 56
  • Thank you for that answer! I've tried ST_ClosestPoint and this works well and I guess it's best to spare a bit of quickness for robustness – Rania ben othmen Jan 25 '18 at 07:47
  • @Rania ben othmen as I said: quick hack...the query does have noticeable overhead due to ST_ClosestPoint in the distance calculation. if you want to improve on speed, try this query structure I gave to another question, using LATERAL JOIN to find the closest point for each input geometry. given proper indexing etc. that should boost speed a little. if I could help you here, consider an upvote or accepting my answer maybe? – geozelot Jan 25 '18 at 08:39
  • Sure it's the less i can do , and sorry i'm still new at this and thank you – Rania ben othmen Jan 25 '18 at 09:50