<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
<META NAME="GENERATOR" CONTENT="GtkHTML/3.14.3">
</HEAD>
<BODY>
Hey Pere,<BR>
<BR>
Perhaps you're looking for a INNER JOIN clause? ;-)<BR>
Regards,<BR>
<BR>
On Mon, 2007-12-17 at 09:19 -0800, pere roca wrote:
<BLOCKQUOTE TYPE=CITE>
<PRE>
<FONT COLOR="#000000">hi!</FONT>
<FONT COLOR="#000000">please, I need some help with a query to extract points from polygons and</FONT>
<FONT COLOR="#000000">update the value (count points) in a field of the polygon table. This counts</FONT>
<FONT COLOR="#000000">gives me the name of polygon and counts points-in-polygon. Nice. </FONT>
<FONT COLOR="#000000">select polygon.name, count(points.gid) from points, polygon where</FONT>
<FONT COLOR="#000000">points.the_geom && polygon.the_geom and Contains</FONT>
<FONT COLOR="#000000">(polygon.the_geom,points.the_geom) group by polygon.name</FONT>
<FONT COLOR="#000000">Now I need to update the number_of_points field of polygon table.</FONT>
<FONT COLOR="#000000">update polygon set number_points=</FONT>
<FONT COLOR="#000000">(</FONT>
<FONT COLOR="#000000">select count(point.gid) from</FONT>
<FONT COLOR="#000000"> point, polygon </FONT>
<FONT COLOR="#000000"> where point.the_geom && polygon.the_geom and </FONT>
<FONT COLOR="#000000"> Contains (polygon.the_geom,point.the_geom) </FONT>
<FONT COLOR="#000000"> group by polygon.name</FONT>
<FONT COLOR="#000000">) </FONT>
<FONT COLOR="#000000">from point where point.the_geom && polygon.the_geom and</FONT>
<FONT COLOR="#000000">Contains(polygon.the_geom, point.the_geom) </FONT>
<FONT COLOR="#000000">It just fills the "number_points" field of the polygons that intersect with</FONT>
<FONT COLOR="#000000">points, BUT the value assigned is the same for all (that's the total COUNT</FONT>
<FONT COLOR="#000000">of all points, because all of these points are inside a polygon). </FONT>
<FONT COLOR="#000000">I've checked many more sentences with no success.</FONT>
<FONT COLOR="#000000">Of course if at the end of the sentence I add "where polygon.name="x", will</FONT>
<FONT COLOR="#000000">update ALL the values with the number of points that are only inside this</FONT>
<FONT COLOR="#000000">polygon. </FONT>
<FONT COLOR="#000000">If I don't have a "link" between the two tables that says like: "where</FONT>
<FONT COLOR="#000000">polygon.name=point.poly_id"...do I HAVE TO construct a pgsql function to</FONT>
<FONT COLOR="#000000">loop over all the polygons?</FONT>
<FONT COLOR="#000000">Is Contains the fastest way to work with these kind of problems?</FONT>
<FONT COLOR="#000000">Thanks a lot,</FONT>
<FONT COLOR="#000000">Pere</FONT>
</PRE>
</BLOCKQUOTE>
<TABLE CELLSPACING="0" CELLPADDING="0" WIDTH="100%">
<TR>
<TD>
--<BR>
<B><FONT SIZE="1">Pedro Doria Meunier</FONT></B> <BR>
<FONT SIZE="1">Ips da Olaria</FONT><BR>
<FONT SIZE="1">Edf. Jardins do Garajau, 4 r/c Y</FONT><BR>
<FONT SIZE="1">9125-163 Caniço</FONT><BR>
<FONT SIZE="1">Madeira</FONT><BR>
<FONT SIZE="1">Portugal</FONT><BR>
<FONT SIZE="1">GSM: +351 96 17 20 188 Skype: pdoriam</FONT><BR>
<FONT SIZE="1"><A HREF="http://www.madeiragps.com">http://www.madeiragps.com</A></FONT>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>