Hi Karsten,
try (I hope I made no mistake...):
update ca.r_model_cells a set quad_id =b.quad_id from
(select quad.quad_id, r_model_cells.record_id rid from ca.r_model_cells
inner join ca.quad
on quad.merc_geom && r_model_cells.merc_geom
where st_within(r_model_cells.merc_geom,quad.merc_geom)) b
where a.record_id=b.rid;
You have to relate the results of the subquery to some row identificator
in the target table, otherwise it tries to write all results of the
subquery into every row of the target table...
Regards,
Birgit.
On 16.02.2011 10:03, karsten vennemann wrote:
Ok I am fighting with this SQL task:
I have a polygon layer "quad" and a point location layer
"r_model_cells" in PostGIS. In each of the quads there will be one or
more of the pints located.
Now I would like to update a field 'quad_id' in he point layer
"r_model_cells" with the id of the quad it is located within. Thus I
can get all point ids and respective quad ids they are via this query
select quad.quad_id qid, r_model_cells.record_id rid from ca.quad,
ca.r_model_cells
where st_within(r_model_cells.merc_geom,quad.merc_geom);
but how can I use this query to update a column my field quad_id in he
point layer. I can'get my snytax together :(
I also have this so far
update ca.r_model_cells set quad_id =
(select quad.quad_id from ca.quad, ca.r_model_cells
where
quad.merc_geom && r_model_cells.merc_geom
and intersects(quad.merc_geom,r_model_cells.merc_geom));
But this query will rerieve more then one records in the subquery ...
Thus I get ERROR:
more than one row returned by a subquery used as an expression I want
to limit to one which correcponds to the point in question....
Any ideas what my syntax should be ?
Note: Right now I can do it via two queries below
---------------------------------------------------------------------------------------------------------------------
select quad.quad_id qid, r_model_cells.record_id AS rid
into temp_rid
from ca.quad, ca.r_model_cells
where st_within(r_model_cells.merc_geom,quad.merc_geom) ;
and then
update ca.r_model_cells set quad_id =
qid from (select temp_rid.qid qid, temp_rid.rid,
r_model_cells.record_id from temp_rid, ca.r_model_cells
where temp_rid.rid =r_model_cells.record_id ) as bubu ;
---------------------------------------------------------------------------------------------------------------------
Cheers
Karsten
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
