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

[Gery .]

Sorry, my last email had the hotmail format, hope this one would have a much better format.

--

Hello,

I have two tables, one containing polygons and one with points:

& polygons

gid       | 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/73c29810/attachment.html>