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.*
(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.

  • 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, 2012 at 0:35
  • 1
    You might be able to adapt the solution at gis.stackexchange.com/questions/14138.
    – whuber
    May 5, 2012 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). May 5, 2012 at 4:48

1 Answer 1


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;
  • 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, 2012 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, 2012 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. May 7, 2012 at 22:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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