Inserting st_length function doesn't match row numbers

Question

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.

Tom Boyd · Accepted Answer · 2023-02-09 11:37:10Z

0

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

answered Feb 9, 2023 at 11:37

Tom Boyd

4383 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

evan Over a year ago

So the following was what I was looking for and you helped me get there. I wanted to add the distances for the tables of road ways/linestrings from osm data. "update planet_osm_roads set cost = st_length(st_transform(way, 4326)::geography);"

Collectives™ on Stack Overflow

Inserting st_length function doesn't match row numbers

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related