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
