0

I'm trying to find the closest point in a line from an insertion point closest to it. To do it, in the middle of the function I use

select id from rede_viaria.t_rviaria_via vias 
where 
  st_intersects(
    st_closestpoint(
      vias.the_geom,
      (st_transform(
        ST_GeomFromEWKT(
          'SRID=3857;POINT(-922235.81565421 5053077.824914)'
        ), 3763)
      )
    ),
    vias.the_geom
  );

The problem is that I'm receiving 1200 rows instead 1 based on the intersection. Could anyone please help? What I'm doing wrong?

2

I take it that there are about 1200 roads in your vias table.

The problem is that you are assuming that ST_ClosestPoint is an aggregate function, but it is not. It is called on each row in your table. If that is not what you want, you need to get the closest road first.

There are other examples of that on this site, but it'll probably be a combination of ST_Distance, ordering results, and limiting to one row. It may not be particularly fast, but with only 1200 rows, that will probably not be a problem.

4
  • Meanwhile I've found a workaround that gave me the correct road but I don't know if this is a right procedure... Instead of use St_ClosestPoint, I used the St_Snap with a 15meters tolerance and limit 1 in order to get the closest road... 'select vias.id_via from rede_viaria.t_rviaria_via vias where st_intersects(st_snap((st_transform(ST_GeomFromEWKT($1), 3763)), vias.the_geom, 15), vias.the_geom) limit 1' Is this correct? Aug 4 '15 at 10:40
  • Its completely dependent on your data. What happens if you have a point 17 metres away? Or you are close to an intersection and you snap to one that is slightly further away? Without knowing what your need is, and your exact data, its a little difficult to provide advice. Maybe you can mark this one as answered and provide a more specific question?
    – BradHards
    Aug 4 '15 at 11:51
  • I want to get the house number based on the distance since the beginning of the road until the intersection of the point given by the user. Should I edit this question or mark it as answered and open a new one? Thanks in advanced for your attention Aug 4 '15 at 13:39
  • I think I've answered your "as asked" question, which was about the general principle. However editting it is also an option - depends on how big the change will be. If it will likely invalidate my answer and I don't have a solution to the next question, then I'll need to delete the answer, and all the comments here will disappear too.
    – BradHards
    Aug 4 '15 at 20:55

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.