I am new to sql/postgres and am trying to run st_length on a database geometry and insert the length as a cost column but the result from running the following commands inserts the data seemingly randomly and not associated with the id column value like needed.
Command:
alter table planet_osm_roads add cost float;
insert into planet_osm_roads (cost)
select st_length(st_transform(way, 4326)::geography) from planet_osm_roads;
example result:
| source | target | cost |
|---|---|---|
| 30,749 | 30,750 | |
| 30,751 | 30,752 | |
| 7,552 | 30,385 | |
| 7.6144929361 | ||
| 41.7331770846 | ||
| 85.3575622508 | ||
| 50.0921684238 | ||
| 3 | 4 | |
| 111.5246694513 | ||
| 43.8658606368 |
I've ignored the other columns as they aren't needed. The columns with 'source' and 'target' values are associated with a specific 'osm_id' value and the cost column is null for those but the commands don't associated the cost value with the linestring row value.
I would expect the cost value to be inserted in the same row as to which the linestring data comes from. That is not what happens.
Insert adds new rows. You want to Update the table and Set cost where some row matches.
ALTER TABLE planet_osm_roads ADD cost FLOAT;
UPDATE planet_osm_roads
SET cost = 7.6144929361
WHERE source = 30,749
or target = something
or both
It's possible to do this from a select but how depends on your schema and data. Depending on your version of PostGres, you may even be able to do it with a generated column - Add generated column to an existing table Postgres