<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=utf-8" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.19019"></HEAD>
<BODY>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=784354608-16022011>Ok I
am fighting with this SQL task:</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN
class=784354608-16022011></SPAN></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=784354608-16022011>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.
</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=784354608-16022011>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</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN
class=784354608-16022011></SPAN></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=784354608-16022011>select
quad.quad_id qid, r_model_cells.record_id rid from ca.quad, ca.r_model_cells
<BR>where st_within(r_model_cells.merc_geom,quad.merc_geom);</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN
class=784354608-16022011></SPAN></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=784354608-16022011>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 :(</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN
class=784354608-16022011></SPAN></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=784354608-16022011>I also
have this so far</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN
class=784354608-16022011></SPAN></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=784354608-16022011>update
ca.r_model_cells set quad_id = <BR>(select quad.quad_id from ca.quad,
ca.r_model_cells <BR>where<BR>quad.merc_geom && r_model_cells.merc_geom
<BR>and intersects(quad.merc_geom,r_model_cells.merc_geom));</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN
class=784354608-16022011></SPAN></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=784354608-16022011>But
this query will rerieve more then one records in the subquery
...</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=784354608-16022011>Thus I
get ERROR: </SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=784354608-16022011>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....</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=784354608-16022011><FONT
color=#000080 size=2 face=Arial><SPAN class=784354608-16022011>Any ideas what my
syntax should be ?</SPAN></FONT></DIV>
<DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN
class=784354608-16022011></SPAN></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN
class=784354608-16022011></SPAN></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=784354608-16022011>Note:
</SPAN></FONT></SPAN></FONT><FONT color=#000080 size=2 face=Arial><SPAN
class=784354608-16022011>Right now I can do it via two queries below
</SPAN></FONT></DIV></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN
class=784354608-16022011></SPAN></FONT><FONT color=#000080 size=2
face=Arial><SPAN class=784354608-16022011><SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---</SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=784354608-16022011>select
quad.quad_id qid, r_model_cells.record_id AS rid <BR>into temp_rid<BR>from
ca.quad, ca.r_model_cells <BR>where
st_within(r_model_cells.merc_geom,quad.merc_geom) ;</SPAN></FONT></DIV>
<DIV><SPAN class=784354608-16022011></SPAN><FONT face=Arial><FONT
color=#000080><FONT size=2></FONT></FONT></FONT> </DIV>
<DIV><FONT face=Arial><FONT color=#000080><FONT size=2>a<SPAN
class=784354608-16022011>nd then </SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial><FONT color=#000080><FONT size=2><SPAN
class=784354608-16022011></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=784354608-16022011>update
ca.r_model_cells set quad_id = <BR>qid from (select temp_rid.qid qid,
temp_rid.rid, r_model_cells.record_id from temp_rid, ca.r_model_cells <BR>where
temp_rid.rid =r_model_cells.record_id ) as bubu ;</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN class=784354608-16022011><SPAN
class=784354608-16022011><SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN class=784354608-16022011>---<SPAN
class=784354608-16022011>---<SPAN
class=784354608-16022011>---</SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN
class=784354608-16022011></SPAN></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN
class=784354608-16022011></SPAN></FONT> </DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN
class=784354608-16022011>Cheers</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial><SPAN
class=784354608-16022011>Karsten</SPAN></FONT></DIV>
<DIV><FONT color=#000080 size=2 face=Arial></FONT> </DIV></BODY></HTML>