How to find records with a time difference less than a threshold The Next CEO of Stack...
What CSS properties can the br tag have?
What day is it again?
My ex-girlfriend uses my Apple ID to login to her iPad, do I have to give her my Apple ID password to reset it?
How to get the last not-null value in an ordered column of a huge table?
From jafe to El-Guest
What steps are necessary to read a Modern SSD in Medieval Europe?
Would a grinding machine be a simple and workable propulsion system for an interplanetary spacecraft?
Physiological effects of huge anime eyes
Is Nisuin Biblical or Rabbinic?
Is there a way to save my career from absolute disaster?
Is fine stranded wire ok for main supply line?
How to avoid supervisors with prejudiced views?
What is the difference between "hamstring tendon" and "common hamstring tendon"?
Small nick on power cord from an electric alarm clock, and copper wiring exposed but intact
Inductor and Capacitor in Parallel
Defamation due to breach of confidentiality
Cannot shrink btrfs filesystem although there is still data and metadata space left : ERROR: unable to resize '/home': No space left on device
"Eavesdropping" vs "Listen in on"
Expressing the idea of having a very busy time
Does Germany produce more waste than the US?
Man transported from Alternate World into ours by a Neutrino Detector
Is it professional to write unrelated content in an almost-empty email?
Ising model simulation
Strange use of "whether ... than ..." in official text
How to find records with a time difference less than a threshold
The Next CEO of Stack Overflow
I have a table with historical records 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 records whose time difference are less than 25 hours and spatial distances less than 5 km.
I tried the following code from a previous question in the Geographic Information Systems
community
SELECT DISTINCT ON (t1.cast_id) t1.cast_id, t1.date, 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 t1.date=t2.date AND t1.cast_id <> t2.cast_id
In this case the first nine rows look like:
cast_id | date | date_time | lat | lon | nlevels | instrument
----------+------------+---------------------+---------+-----------+---------+------------
10050246 | 1994-08-02 | 1994-08-02 21:45:00 | 23.1330 | -113.0830 | 14 | mbt
10050247 | 1994-08-02 | 1994-08-02 22:00:00 | 23.1330 | -113.0830 | 11 | mbt
10050248 | 1994-08-02 | 1994-08-02 22:15:00 | 23.1170 | -113.0670 | 17 | mbt
10052517 | 1994-10-13 | 1994-10-13 22:00:00 | 22.8000 | -112.7000 | 17 | mbt
10052519 | 1994-10-13 | 1994-10-13 22:15:00 | 22.8000 | -112.7000 | 16 | mbt
10052520 | 1994-10-13 | 1994-10-13 22:30:00 | 22.8170 | -112.7170 | 16 | mbt
10052521 | 1994-10-13 | 1994-10-13 22:45:00 | 22.8170 | -112.7170 | 17 | mbt
10052523 | 1994-10-13 | 1994-10-13 23:00:00 | 22.8330 | -112.7330 | 16 | mbt
10052524 | 1994-10-13 | 1994-10-13 23:15:00 | 22.8330 | -112.7330 | 17 | mbt
However, with that code I only got duplicates considering stations collected the same day and less than 5 km apart. In the previous question it was recommend me to use the postgresql function called AGE. I think it will be great because I coudl specify any time inteval in case I changed my definition for duplicates (timediff of 25 or 18 hours).
Could you help me to include the AGE function in the query, considering the above code?
postgresql postgis duplication
asked 1 min ago
AmaruAmaru
1
New contributor
Amaru is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I have a table with historical records 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 records whose time difference are less than 25 hours and spatial distances less than 5 km.
I tried the following code from a previous question in the Geographic Information Systems
community
SELECT DISTINCT ON (t1.cast_id) t1.cast_id, t1.date, 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 t1.date=t2.date AND t1.cast_id <> t2.cast_id
In this case the first nine rows look like:
cast_id | date | date_time | lat | lon | nlevels | instrument
----------+------------+---------------------+---------+-----------+---------+------------
10050246 | 1994-08-02 | 1994-08-02 21:45:00 | 23.1330 | -113.0830 | 14 | mbt
10050247 | 1994-08-02 | 1994-08-02 22:00:00 | 23.1330 | -113.0830 | 11 | mbt
10050248 | 1994-08-02 | 1994-08-02 22:15:00 | 23.1170 | -113.0670 | 17 | mbt
10052517 | 1994-10-13 | 1994-10-13 22:00:00 | 22.8000 | -112.7000 | 17 | mbt
10052519 | 1994-10-13 | 1994-10-13 22:15:00 | 22.8000 | -112.7000 | 16 | mbt
10052520 | 1994-10-13 | 1994-10-13 22:30:00 | 22.8170 | -112.7170 | 16 | mbt
10052521 | 1994-10-13 | 1994-10-13 22:45:00 | 22.8170 | -112.7170 | 17 | mbt
10052523 | 1994-10-13 | 1994-10-13 23:00:00 | 22.8330 | -112.7330 | 16 | mbt
10052524 | 1994-10-13 | 1994-10-13 23:15:00 | 22.8330 | -112.7330 | 17 | mbt
However, with that code I only got duplicates considering stations collected the same day and less than 5 km apart. In the previous question it was recommend me to use the postgresql function called AGE. I think it will be great because I coudl specify any time inteval in case I changed my definition for duplicates (timediff of 25 or 18 hours).
Could you help me to include the AGE function in the query, considering the above code?
postgresql postgis duplication
asked 1 min ago
AmaruAmaru
1
New contributor
Amaru is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I have a table with historical records 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 records whose time difference are less than 25 hours and spatial distances less than 5 km.
I tried the following code from a previous question in the Geographic Information Systems
community
SELECT DISTINCT ON (t1.cast_id) t1.cast_id, t1.date, 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 t1.date=t2.date AND t1.cast_id <> t2.cast_id
In this case the first nine rows look like:
cast_id | date | date_time | lat | lon | nlevels | instrument
----------+------------+---------------------+---------+-----------+---------+------------
10050246 | 1994-08-02 | 1994-08-02 21:45:00 | 23.1330 | -113.0830 | 14 | mbt
10050247 | 1994-08-02 | 1994-08-02 22:00:00 | 23.1330 | -113.0830 | 11 | mbt
10050248 | 1994-08-02 | 1994-08-02 22:15:00 | 23.1170 | -113.0670 | 17 | mbt
10052517 | 1994-10-13 | 1994-10-13 22:00:00 | 22.8000 | -112.7000 | 17 | mbt
10052519 | 1994-10-13 | 1994-10-13 22:15:00 | 22.8000 | -112.7000 | 16 | mbt
10052520 | 1994-10-13 | 1994-10-13 22:30:00 | 22.8170 | -112.7170 | 16 | mbt
10052521 | 1994-10-13 | 1994-10-13 22:45:00 | 22.8170 | -112.7170 | 17 | mbt
10052523 | 1994-10-13 | 1994-10-13 23:00:00 | 22.8330 | -112.7330 | 16 | mbt
10052524 | 1994-10-13 | 1994-10-13 23:15:00 | 22.8330 | -112.7330 | 17 | mbt
However, with that code I only got duplicates considering stations collected the same day and less than 5 km apart. In the previous question it was recommend me to use the postgresql function called AGE. I think it will be great because I coudl specify any time inteval in case I changed my definition for duplicates (timediff of 25 or 18 hours).
Could you help me to include the AGE function in the query, considering the above code?
postgresql postgis duplication
asked 1 min ago
AmaruAmaru
1
New contributor
Amaru is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have a table with historical records 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 records whose time difference are less than 25 hours and spatial distances less than 5 km.
I tried the following code from a previous question in the Geographic Information Systems
community
SELECT DISTINCT ON (t1.cast_id) t1.cast_id, t1.date, 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 t1.date=t2.date AND t1.cast_id <> t2.cast_id
In this case the first nine rows look like:
cast_id | date | date_time | lat | lon | nlevels | instrument
----------+------------+---------------------+---------+-----------+---------+------------
10050246 | 1994-08-02 | 1994-08-02 21:45:00 | 23.1330 | -113.0830 | 14 | mbt
10050247 | 1994-08-02 | 1994-08-02 22:00:00 | 23.1330 | -113.0830 | 11 | mbt
10050248 | 1994-08-02 | 1994-08-02 22:15:00 | 23.1170 | -113.0670 | 17 | mbt
10052517 | 1994-10-13 | 1994-10-13 22:00:00 | 22.8000 | -112.7000 | 17 | mbt
10052519 | 1994-10-13 | 1994-10-13 22:15:00 | 22.8000 | -112.7000 | 16 | mbt
10052520 | 1994-10-13 | 1994-10-13 22:30:00 | 22.8170 | -112.7170 | 16 | mbt
10052521 | 1994-10-13 | 1994-10-13 22:45:00 | 22.8170 | -112.7170 | 17 | mbt
10052523 | 1994-10-13 | 1994-10-13 23:00:00 | 22.8330 | -112.7330 | 16 | mbt
10052524 | 1994-10-13 | 1994-10-13 23:15:00 | 22.8330 | -112.7330 | 17 | mbt
However, with that code I only got duplicates considering stations collected the same day and less than 5 km apart. In the previous question it was recommend me to use the postgresql function called AGE. I think it will be great because I coudl specify any time inteval in case I changed my definition for duplicates (timediff of 25 or 18 hours).
Could you help me to include the AGE function in the query, considering the above code?
postgresql postgis duplication
postgresql postgis duplication
asked 1 min ago
AmaruAmaru
1
New contributor
Amaru is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 min ago
AmaruAmaru
1
New contributor
Amaru is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 min ago
AmaruAmaru
1
New contributor
Amaru is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 min ago
AmaruAmaru
1
asked 1 min ago
AmaruAmaru
1
1
New contributor
Amaru is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Amaru is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Amaru is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
0
active
oldest
votes
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Amaru is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233636%2fhow-to-find-records-with-a-time-difference-less-than-a-threshold%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Amaru is a new contributor. Be nice, and check out our Code of Conduct.
Amaru is a new contributor. Be nice, and check out our Code of Conduct.
Amaru is a new contributor. Be nice, and check out our Code of Conduct.
Amaru is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233636%2fhow-to-find-records-with-a-time-difference-less-than-a-threshold%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
