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
