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

[Andy Colson]

On 6/11/2015 3:36 PM, Gery . wrote:
> 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,
>
>

Your first email had much better formatting.


 > 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:

Start with the first thing, select it out, then wrap it into a derived 
table.

so first:

select distinct fecha, matricula_embarcacion
from temporada1erapesca2012_point_wgs84

If that returns to you the right data, then expand it:

select whatever
from cuadro_produce_region
inner join (
     select distinct fecha, matricula_embarcacion
     from temporada1erapesca2012_point_wgs84
) as tmpPoints on (tmpPoints.fecha = cuadro_produce_region.fecha)


-Andy