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: [email protected] > To: [email protected] > 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 > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
