<html>
<head>
<style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 12pt;
font-family:Calibri
}
--></style></head>
<body class='hmmessage'><div dir='ltr'>Hello,<div><br></div><div>I have two tables, one containing polygons and one with points:</div><div><br></div><div># polygons</div><div><div>gid | integer | not null default nextval('cuadro_produce_region_gid_seq'::regclass)</div><div>id | character varying(10) | </div><div>zona | character varying(10) | </div><div>area | character varying(10) | </div><div>cod | double precision | </div><div>seccion | double precision | </div><div>ordxdistc | double precision | </div><div>geom | geometry(PolygonZM,4326) | </div></div><div><br></div><div># points</div><div><div> nombre_embarcacion | character varying(150) | </div><div> matricula_embarcacion | character varying(150) | </div><div> tipo_casco | character varying(150) | </div><div> capacidad_bodega | character varying(150) | </div><div> nombre_aparejo | character varying(150) | </div><div> descripcion_proveedor | character varying(150) | </div><div> fecha | character varying(150) | </div><div> longitud | numeric | </div><div> latitud | numeric | </div><div> velocidad | numeric | </div><div> rumbo | numeric | </div><div> ts | tsvector | </div><div> geom | geometry(Point,4326) | </div></div><div><br></div><div>Finding all points, first filtered by velocity ("velocidad" field), inside each region was quite easy:</div><div><br></div><div><span style="font-size: 12pt;">select cuadro_produce_region.gid,cuadro_produce_region.area,cuadro_produce_region.zona,cuadro_produce_region.cod,count(*) as Nro from temporada1erapesca2012_point_wgs84,cuadro_produce_region where (temporada1erapesca2012_point_wgs84.velocidad between '0.2' and '1.6') and st_intersects(cuadro_produce_region.geom,temporada1erapesca2012_point_wgs84.geom) group by cuadro_produce_region.gid order by cuadro_produce_region.gid,cuadro_produce_region.cod;</span></div><div><div><div><br></div></div></div><div>but now I need to select unique values from fields "fecha" and "matricula_embarcacion" of the table 'temporada1erapesca2012_point_wgs84' first and then apply the intersection with the 'cuadro_produce_region' table, I tried "distinct" in the where clause with no success so far:</div><div><br></div><div>select cuadro_produce_region.gid,cuadro_produce_region.area,cuadro_produce_region.zona,cuadro_produce_region.cod,count(*) as Nro from temporada1erapesca2012_point_wgs84,cuadro_produce_region where (temporada1erapesca2012_point_wgs84.velocidad between '0.2' and '1.6' and distinct(temporada1erapesca2012_point_wgs84.matricula_embarcacion) and distinct(temporada1erapesca2012_point_wgs84.fecha)) and st_intersects(cuadro_produce_region.geom,temporada1erapesca2012_point_wgs84.geom) group by cuadro_produce_region.gid order by cuadro_produce_region.gid,cuadro_produce_region.cod;</div><div><br></div><div>how could I do this query? "fecha" field has this structure: 16/06/2012 03:00</div><div><br></div><div>Any hints are welcomed, thanks in advance,</div> </div></body>
</html>