Problems with PostGIS St_closestpoint function

Question

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?

Answer 1

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.