Calculating cost values in pgRouting using ST_DWithin

Question

I have a table where there are two geometries: geom_way (for routing roads) and (osm_meta) which is a triangulation from polygons. I use ST_DWithin function (between both geometries) with a distance of 30 meters to divide the costs values for the optimal routing, however, no changes are applied to the resulting cost_greenery column: the ones which should be affected because are in a distance lower than 30m still have the same value as the normal cost. The code below:

UPDATE utrecht_osm_foot_2po_4pgr_duplicate SET cost_greenery =
CASE ST_DWithin(utrecht_osm_foot_2po_4pgr_duplicate.geom_way, utrecht_osm_foot_2po_4pgr_duplicate.osm_meta, 30)
WHEN TRUE THEN
    utrecht_osm_foot_2po_4pgr_duplicate.cost / 1000
ELSE
    utrecht_osm_foot_2po_4pgr_duplicate.cost
END;

Do you have any clue on how to solve this?

Answer 1

You should simply

UPDATE
  utrecht_osm_foot_2po_4pgr_duplicate
SET
  cost_greenery = cost / 1000
WHERE
  ST_DWithin(geom_way, osm_meta, 30)
;

---

If no update happened

• make sure your geometries have a suitable projection - the exact same for both - to be used with meter as unit, or use a CAST(geom AS GEOGRAPHY) if they are referenced in a geographical coordinate system
• test with

  SELECT
    ST_Distance(way_geom, osm_meta) AS dst
  FROM
    utrecht_osm_foot_2po_4pgr_duplicate
  ORDER BY
    dst
  ;
  

  if there are any within that distance...

Answer 2

Try maybe this:

UPDATE utrecht_osm_foot_2po_4pgr_duplicate
    SET cost_greenery = (
        CASE
            WHEN ST_DWithin(utrecht_osm_foot_2po_4pgr_duplicate.geom_way, utrecht_osm_foot_2po_4pgr_duplicate.osm_meta, 30)
                THEN utrecht_osm_foot_2po_4pgr_duplicate.cost / 1000
            ELSE utrecht_osm_foot_2po_4pgr_duplicate.cost
        END);