0

I have a table with historical rows indicating the positions in which ocean samples were taken. From this table, I would like to find duplicates based on spatial and temporal criteria. Thus, the duplicates will refer to those rows whose time difference are less than 25 hours and spatial distances less than 5 km.

I tried the following code

CREATE TABLE dups_v2 AS SELECT DISTINCT ON (t1.cast_id) t1.cast_id, t1.date, t1.date_time ,t1.lat, t1.lon, t1.nlevels, t1.instrument 
FROM good_dates t1 JOIN good_dates t2
ON ST_DWithin(t1.geom::geometry, t2.geom::geometry, 5000.0, true)
WHERE AGE(t1.date_time, t2.date_time) < interval '1 days 01:00:00' AND t1.cast_id <> t2.cast_id

In this case the result looks like:

cast_id   |      date_time      |   lat   |    lon    | nlevels | instrument 
----------+---------------------+---------+-----------+---------+------------
 943046   | 1950-02-03 05:00:00 | 25.9200 | -114.5500 |      25 | mbt
 177584   | 1950-02-03 08:00:00 | 25.8200 | -114.8080 |      14 | osd
 943053   | 1950-02-03 08:06:00 | 25.8200 | -114.7700 |      24 | mbt
 943059   | 1950-02-03 10:00:00 | 25.7000 | -114.9700 |      22 | mbt
 4369553  | 1950-03-05 00:00:00 | 25.7720 | -114.8570 |      40 | mbt
 4369511  | 1950-03-05 04:00:00 | 25.9500 | -114.5000 |      38 | mbt
 4369514  | 1950-03-05 05:00:00 | 25.8700 | -114.6500 |      39 | mbt
 178401   | 1950-03-05 07:54:00 | 25.8580 | -114.7500 |       8 | osd
 4369529  | 1950-03-05 07:58:12 | 25.8200 | -114.7500 |      42 | mbt

However, the distance does not seem to fit my duplicate definition (< 25h and < 5km apart).

For example, the following two points seem to be farther than 5 km:

944388   | 1950-03-19 19:00:00 | 21.1700 | -108.3300 |      13 | mbt
944390   | 1950-03-19 22:00:00 | 20.7800 | -107.9000 |      13 | mbt

Could you help me to identify my error?

  • Why don't you display the values from t2? It will show the matching record and explain your question. You found all the pairs of matching points, but you display only the left side of the match. – filiprem Apr 2 at 20:51
  • I did not understand. If I changed the SELECT statement in the above code to: SELECT DISTINCT ON (t1.cast_id) t1.cast_id, t2.cast_id I get the error: ERROR: column "cast_id" specified more than once Thank you for your comment @fillipem – Amaru Apr 3 at 7:59
  • you cannot CREATE TABLE with two columns of the same name. Use an alias for the t2.cast_id. – filiprem Apr 3 at 12:19
  • Thank you. You are rigth and I misunderstood how the query works. I would like to generate a list of all records which are less than 5 km apart and with a time difference less than 25 hours. I think a better approach could be clustering the data based on my criteria. The function ST_ClusterDBSCAN can be used to generate spatial clusters and then use AGE in some way to select based on temporal criteria. – Amaru Apr 5 at 7:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.