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

Andy Colson andy at squeakycode.net
Fri Jun 12 11:41:57 PDT 2015


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


On 6/12/2015 9:42 AM, Gery . wrote:> 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;
 >
 >
 > ----------------------------------------



Sorry, I didn't mean to confuse.  I'm not familiar with your tables, I 
had no idea how to join the tables, it was example of derived tables, 
not example of what fields to join.

What I mean is, start in the middle and keep wrapping things around it. 
  Each derived table is like a temp table.

-Andy



More information about the postgis-users mailing list