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

[Gery .]

> Sorry, I didn't mean to confuse. I'm not familiar with your tables, I
> had no idea how to join the tables, it was example of derived tables,
> not example of what fields to join.
>
> What I mean is, start in the middle and keep wrapping things around it.
> Each derived table is like a temp table.
>
> -Andy
>

Thanks Andy, I solved this with two queries, first to filter and then to count points per region:

create temporary table tmp1era2012 as select distinct on (to_date(fecha,'DD/MM/YY'),matricula_embarcacion) nombre_embarcacion,matricula_embarcacion,tipo_casco,capacidad_bodega,nombre_aparejo,descripcion_proveedor,fecha,longitud,latitud,velocidad,rumbo,geom from temporada1erapesca2012_point_wgs84 where velocidad between '0.2' and '1.6' order by matricula_embarcacion;

select cuadro_produce_region.gid,cuadro_produce_region.area,cuadro_produce_region.zona,cuadro_produce_region.cod,count(*) as frecuenciausoxregion from tmp1era2012,cuadro_produce_region where st_intersects(cuadro_produce_region.geom,tmp1era2012.geom) group by cuadro_produce_region.gid order by cuadro_produce_region.area,cuadro_produce_region.cod;

it works well, but I think it can be done in only one query, that would be nice. Thanks anyway!