I have a table that containing placename regions and another table of roads. I have another table that containing centroid of both placename region and roads.The table structure of third table is placename,Roadname,x,y. I want to update the placename field of the roads which are passing through the corresponding placename region. I used the following query:

Update haryana set placename = placename_region.name from placename_region where haryana.the_geom in (select st_centroid(ch_roads.the_geom) from ch_roads,placename_region where st_contains(placename_region.the_geom,ch_roads.the_geom))

But it went wrong. wrong placenames updated in placename field. Please help me to solve this


It doesn't work because that's not the proper way of making a UPDATE from a SELECT , You are doing something else...

Something like this should work, although I don't understand this part of the query:

haryana.the_geom in (select st_centroid(ch_roads.the_geom) ....)

or as I rephrased it:

st_centroid(ch_roads.the_geom) = haryana.the_geom

here is the full query:

Update haryana set placename = placename_region.name from placename_region,ch_roads where st_contains(placename_region.the_geom,ch_roads.the_geom) && st_centroid(ch_roads.the_geom) = haryana.the_geom;


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.