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 $$
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 ;

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.

  • any progress here?
    – geozelot
    Oct 8 '19 at 11:43
  • Haven't got around testing the solution Oct 8 '19 at 17:00

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
     SELECT ST_Distance(ST_Centroid(NEW.geom)::GEOGRAPHY, rt.geom::GEOGRAPHY)
     INTO   NEW.river_distance
     FROM   rivers AS rt
            NEW.geom <-> rt.geom
     LIMIT  1;


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...)

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.