Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

i have a table of points with each point acquired on a specified date, by a specified source, and having a measured value. i want to consider points as duplicates if they have the same date and if they are within a certain distance of each other (say <10 m), even if they came from different sources. here's a brief example using 8 points:

create table points (gid serial primary key, date date, source varchar, value integer);

select addGeometryColumn('points', 'geom', 32633, 'point', 2);

insert into points (date, source, value, geom) values
  ('1985-06-19', 'B', 22, st_geomFromText('POINT(593835 3910494)', 32633)),
  ('1985-06-19', 'A', 48, st_geomFromText('POINT(602920 3910591)', 32633)),
  ('1987-04-17', 'B', 12, st_geomFromText('POINT(592317 3910479)', 32633)),
  ('1993-07-22', 'A', 16, st_geomFromText('POINT(592323 3910476)', 32633)),
  ('1993-07-22', 'B', 30, st_geomFromText('POINT(592317 3910479)', 32633)),
  ('2000-07-09', 'A', 20, st_geomFromText('POINT(592108 3909121)', 32633)),
  ('2006-07-14', 'A', 22, st_geomFromText('POINT(592933 3909842)', 32633)),
  ('2006-07-14', 'B', 18, st_geomFromText('POINT(592931 3909843)', 32633));

i'm trying to create a query that should result in 6 points, since points 4/5 and 7/8 would be considered duplicates because they have the same date and <10 m from each other. i also want the query to select the duplicate point that has the maximum value, so in my example the result set would be ('geom' field not shown):

 gid |    date    | source | value 
-----+------------+--------+-------
   1 | 1985-06-19 | B      |    22
   2 | 1985-06-19 | A      |    48
   3 | 1987-04-17 | B      |    12
   5 | 1993-07-22 | B      |    30
   6 | 2000-07-09 | A      |    20
   7 | 2006-07-14 | A      |    22
(6 rows)

been trying various queries using self joining and the st_dwithin and st_distance functions, but can't seem to nail it down. i'm using postgis 2.0 and postgresql 9.1. thanks!

update: based on the link whuber suggested, i explored using the st_snapToGrid function and this query seems to produce the result i'm looking for:

select distinct on (date, grid_location) grouped.*
from
(select *, st_snapToGrid(geom, 10, 10) as grid_location
from points order by date, grid_location, value desc) as grouped;

update 2: the above query eliminated most of the duplicates in my actual dataset, but i found several still making it through since sometimes points would be snapped to different grids if they happened to be bisected by a grid boundary. i've posted another solution as an answer to my question.

share|improve this question
2  
What would you do when, on the same date, points A and B are 9 m from each other (and therefore duplicates), B and C are 9 m from each other (and therefore duplicates), but A and C are more than 10 m from each other (and therefore not duplicates)? –  whuber May 5 '12 at 0:35
1  
You might be able to adapt the solution at gis.stackexchange.com/questions/14138. –  whuber May 5 '12 at 0:40
    
@whuber - yes, it's not clear what should be considered duplicates in the situation you proposed. i think such a case would be rare in my dataset. actual duplicates tend to be 10s to 100s of meters apart, while truly different points on the same date are likely 1000s of meters apart. thanks for the link to the other solution. i think the st_snapToGrid function might do what i need (query added to question above). –  pistachionut May 5 '12 at 4:48
    
You can answer and accept your own question so the topic is closed. –  underdark May 5 '12 at 9:22
add comment

1 Answer

up vote 3 down vote accepted

this seems to work, although not a compact, single query solution. it also only works if the duplicates are a result of 2 sources (it wouldn't select a single value if duplicate values came from sources 'A', 'B', and 'C'):

--table of just duplicates
create table dups as
select t1.*, st_centroid(st_union(t1.geom, t2.geom)) as cent_geom
from points as t1 join points as t2 on st_dwithin(t1.geom, t2.geom, 10)
where t1.date = t2.date and t1.gid != t2.gid
order by date, value desc;

--table of no duplicates
create table no_dups as select points.*
from points left join dups on points.gid = dups.gid
where dups.gid is null;

--insert distinct duplicates
insert into no_dups
select distinct on (date, cent_geom) gid, date, source, value, geom
from dups;

select * from no_dups
order by gid;
share|improve this answer
    
Also to note, an application of this is in the CrimeStat spatial program, as is call the "Fuzzy Mode" (see chapter 6 of the manual. One instance I have used it is when I was combining geocoded data from several years, and prior years were geocoded using different street center-line files, resulting in slightly different geocoded coordinates (and I didn't feel like going back and re-geocoding all of the previous data). –  Andy W May 6 '12 at 13:34
    
A slight difference in application though is that CrimeStat uses the centroid of the points within a pre-specified radius, whereas it appears here you use the coordinates of the point with the earliest time-stamp? –  Andy W May 6 '12 at 13:36
    
@Andy W - the dates are date types, not timestamp types, so orderring by date gives me duplicate events that happened on the same day. i did realize a deficiency in my solution though: if i have 4 points all on the same date, with one pair close together, the other pair close together, but the two pairs are far apart, my query would consider those all duplicates and only select 1 event rather than 2. adding a field with the pair centroid and using that in the distinct on clause would work (code above updated). my solution also only works when there are at most 2 potential sources. –  pistachionut May 7 '12 at 22:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.