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

[Gery .]

Thanks a lot Andy, I just got right the first query:

select distinct to_date(fecha,'DD/MM/YY') as fecha,matricula_embarcacion from temporada1erapesca2012_point_wgs84 order by matricula_embarcacion;

but the second one is not working:

select cuadro_produce_region.gid,cuadro_produce_region.area,cuadro_produce_region.zona,cuadro_produce_region.cod,count(*) from cuadro_produce_region inner join (select distinct to_date(fecha,'DD/MM/YY'),matricula_embarcacion from temporada1erapesca2012_point_wgs84 order by matricula_embarcacion) as tmpPtos on (tmpPtos.fecha = cuadro_produce_region.fecha);ERROR:  column tmpptos.fecha does not exist
LINE 1: ...84 order by matricula_embarcacion) as tmpPtos on (tmpPtos.fe...

and this is because there is no "fecha" in "cuadro_produce_region". INNER JOIN would work better than ST_intersects in this case? I mean, I need to find all points inside each region (10 regions) of the polygon table (ie. cuadro_produce_region), and st_intersects works well in the query below:

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;


----------------------------------------
> Date: Thu, 11 Jun 2015 16:53:46 -0500
> From: andy at squeakycode.net
> To: postgis-users at lists.osgeo.org
> Subject: Re: [postgis-users] Find unique date and code values before applying st_intersects
>
> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users