Speedup function to calculate distance from centroid of building to river segment in PostgreSQL

Question

I am trying to measure the distance from the centroid of a building to a river segment using the following function.

CREATE OR REPLACE FUNCTION river_foo_mapper () RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
new.river_distance := subquery.distance

FROM (with center as (
                        select st_transform(st_centroid(new.geometry),3857) as geom from
                        foo )
                        select st_distance(a.geom,st_transform(b.geometry,3857)) as distance
                        from center as a, rivers as b
                            order by st_transform(b.geometry,3857) <-> a.geom
                            limit 1) AS subquery ;
  RETURN NEW;
  END
 $$;

If I run the SQL as a select statement it takes less than 2 seconds but if I run it as a function it takes forever to finish. How best can I optimize this and is my function correct.

geozelot · Answer

Things to consider:

(K)NN searches are costly, and the key for performance is the use of the spatial index; any form of manipulation of the geometries will render that index useless.
Don't transform the geometries of the running table on-the-fly.
Spherical/Pseudo/Web Mercator is among the most uselss 'projections' there are when it comes to any form of measurement.Don't bother transforming to EPSG:3857 at all.
Function calls are expensive, and this is a trigger function, so it will need to be called per row; needless to say, keep the function overhead minimal, avoid CTEs if not necessary.
Also, it's cleaner to clearly isolate SQL and PL syntax.

My suggestion:

CREATE OR REPLACE FUNCTION river_foo_mapper ()
  RETURNS trigger
  LANGUAGE plpgsql AS
   $$
   BEGIN
     SELECT ST_Distance(ST_Centroid(NEW.geom)::GEOGRAPHY, rt.geom::GEOGRAPHY)
     INTO   NEW.river_distance
     FROM   rivers AS rt
     ORDER BY
            NEW.geom <-> rt.geom
     LIMIT  1;

RETURN NEW;
   END
   $$
;

I use the GEOGRAPHY type to get a highly precise distance measure in meter. This assumes your initial geometries are in a geographic reference system, and EPSG:4326 by default. Use ST_DistanceSpheroid on projected geometries to get a similar precise result based on the projections reference geoid.

It strikes me unnecessary to find the closest river segment to the centroid of a building, except maybe the buildings are partially on the river; if you need that, use ST_Centroid(NEW.geom). Note though that the planner might need to choose a slower seq scan then since it cannot determine that geometry ahead of execution (you might have aimed at this by using a CTE? Doesn't help, unfortunately...)

Speedup function to calculate distance from centroid of building to river segment in PostgreSQL

One Answer

Add your own answers!

Ask a Question