5

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?

1

2 Answers 2

5

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...
1
  • your approach is much smarter!
    – Taras
    Jun 14 at 5:11
4

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

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.