[postgis-users] Find unique date and code values before applying st_intersects

[Gery .]

Hello,
I have two tables, one containing polygons and one with points:
# polygonsgid       | integer                  | not null default nextval('cuadro_produce_region_gid_seq'::regclass)id         | character varying(10)    | zona    | character varying(10)    | area     | character varying(10)    | cod      | double precision         | seccion   | double precision         | ordxdistc | double precision         | geom       | geometry(PolygonZM,4326) | 
# points nombre_embarcacion    | character varying(150) |  matricula_embarcacion | character varying(150) |  tipo_casco            | character varying(150) |  capacidad_bodega      | character varying(150) |  nombre_aparejo        | character varying(150) |  descripcion_proveedor | character varying(150) |  fecha                 | character varying(150) |  longitud              | numeric                |  latitud               | numeric                |  velocidad             | numeric                |  rumbo                 | numeric                |  ts                    | tsvector               |  geom                  | geometry(Point,4326)   | 
Finding all points, first filtered by velocity ("velocidad" field), inside each region was quite easy:
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;
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:
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;
how could I do this query? "fecha" field has this structure:  16/06/2012 03:00
Any hints are welcomed, thanks in advance, 		 	   		  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150611/ba980487/attachment.html>