Proper identification of duplicates using spatial and temporal criteria in PostGISHow to properly set up indexes for PostGIS distance queries?Designing Spatial Database for temporal Data?postgis distance calculation between points with lat, long coordinatesPostGIS - Geometry or Geography find points within distance in metersDealing with custom projections (from prj file) in PostGIS (also GeoServer and OpenLayers)Nearest facility with pgRoutingSpatial+Temporal database request for 4D intersectionWhy is the Google Maps circle shape not matching to the distance calculated with PostgreSQL?am I setting up data from google geocoding correctly for ST_DWithin search?Finding list of points near current long/lat using PostGIS?Problem with st_clusterWithin and conditions
What are these boxed doors outside store fronts in New York?
Can you really stack all of this on an Opportunity Attack?
Which country benefited the most from UN Security Council vetoes?
Why do I get two different answers for this counting problem?
Pattern match does not work in bash script
What's the point of deactivating Num Lock on login screens?
How can I make a cone from a cube and view the cube with different angles?
What does "Puller Prush Person" mean?
Why "Having chlorophyll without photosynthesis is actually very dangerous" and "like living with a bomb"?
Can an x86 CPU running in real mode be considered to be basically an 8086 CPU?
How is it possible to have an ability score that is less than 3?
Assigning pointers to atomic type to pointers to non atomic type
What does it mean to describe someone as a butt steak?
How does strength of boric acid solution increase in presence of salicylic acid?
High voltage LED indicator 40-1000 VDC without additional power supply
I'm planning on buying a laser printer but concerned about the life cycle of toner in the machine
How can I prevent hyper evolved versions of regular creatures from wiping out their cousins?
Fencing style for blades that can attack from a distance
The use of multiple foreign keys on same column in SQL Server
Replacing matching entries in one column of a file by another column from a different file
Have astronauts in space suits ever taken selfies? If so, how?
Today is the Center
Why, historically, did Gödel think CH was false?
To string or not to string
Proper identification of duplicates using spatial and temporal criteria in PostGIS
How to properly set up indexes for PostGIS distance queries?Designing Spatial Database for temporal Data?postgis distance calculation between points with lat, long coordinatesPostGIS - Geometry or Geography find points within distance in metersDealing with custom projections (from prj file) in PostGIS (also GeoServer and OpenLayers)Nearest facility with pgRoutingSpatial+Temporal database request for 4D intersectionWhy is the Google Maps circle shape not matching to the distance calculated with PostgreSQL?am I setting up data from google geocoding correctly for ST_DWithin search?Finding list of points near current long/lat using PostGIS?Problem with st_clusterWithin and conditions
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I have a table with metadata about sampling locations in the ocean. It includes geographic coordinates, date-time in which sampling was done the number of vertical measurements in each location (nlevels), and the kid of instrument used to take samples (instrument column).
Due to this particular dataset came from historical records, it can include duplicates that I would like to identify using spatiotemporal criteria (allowing rounding errors in spatial coordinates). I would like to detect all stations separated by a distance less than 5km and a temporal difference less than 25 hours
My code for testing was:
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::geography, t2.geom::geography, 5000.0, true)
WHERE AGE(t1.date_time, t2.date_time) < interval '1 days 01:00:00' AND t1.cast_id <> t2.cast_id
The create table 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
944149 | 1950-03-09 00:10:12 | 25.4300 | -113.4700 | 13 | mbt
944153 | 1950-03-09 13:55:12 | 25.0000 | -112.8300 | 13 | mbt
The time difference looks ok, but spatial separation it is incorrect. For example, consider the cast ids 944149 and 944153 (the last two) from the above results.
944149 | 1950-03-09 00:10:12 | 25.4300 | -113.4700 | 13 | mbt
944153 | 1950-03-09 13:55:12 | 25.0000 | -112.8300 | 13 | mbt
The distance seems to be greater than 5 km.
Thus, is not fulfilling my duplicated definition (< 5km; < 25 hours)
Could you help me to identify my error?
postgis spatio-temporal-data duplication
edited Apr 3 at 8:12
John Powell
10.9k43050
asked Mar 31 at 2:33
AmaruAmaru
112
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.
|
show 6 more comments
I have a table with metadata about sampling locations in the ocean. It includes geographic coordinates, date-time in which sampling was done the number of vertical measurements in each location (nlevels), and the kid of instrument used to take samples (instrument column).
Due to this particular dataset came from historical records, it can include duplicates that I would like to identify using spatiotemporal criteria (allowing rounding errors in spatial coordinates). I would like to detect all stations separated by a distance less than 5km and a temporal difference less than 25 hours
My code for testing was:
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::geography, t2.geom::geography, 5000.0, true)
WHERE AGE(t1.date_time, t2.date_time) < interval '1 days 01:00:00' AND t1.cast_id <> t2.cast_id
The create table 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
944149 | 1950-03-09 00:10:12 | 25.4300 | -113.4700 | 13 | mbt
944153 | 1950-03-09 13:55:12 | 25.0000 | -112.8300 | 13 | mbt
The time difference looks ok, but spatial separation it is incorrect. For example, consider the cast ids 944149 and 944153 (the last two) from the above results.
944149 | 1950-03-09 00:10:12 | 25.4300 | -113.4700 | 13 | mbt
944153 | 1950-03-09 13:55:12 | 25.0000 | -112.8300 | 13 | mbt
The distance seems to be greater than 5 km.
Thus, is not fulfilling my duplicated definition (< 5km; < 25 hours)
Could you help me to identify my error?
postgis spatio-temporal-data duplication
edited Apr 3 at 8:12
John Powell
10.9k43050
asked Mar 31 at 2:33
AmaruAmaru
112
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.
You haven't attempted a temporal deconfliction constraint, so there's no indication what your time column might be. When doing a self join, you need a WHERE constraint to eliminate identity (t2.id <> t1.id) or previous clusters (t2.id > t1.id). It's not clear what your SRID is so the significance of your search distance is unclear. If you use or cast togeography, you can use a geodetic search in meters (though you need to index appropriately if casting)
– Vince
Mar 31 at 12:38
I followed your suggestion using WHERE to eliminate identity. Also, I used geography to get the distance in meters. The list of duplicates makes sense to me but I am struggling with the date. I am going to edit my question.
– Amaru
Mar 31 at 20:03
Asking for help with theageoperator is probably more in the realm of Database Administrators
– Vince
Mar 31 at 20:11
2
Where does nlevels come from? I think you need the rank function, but, I am unclear on the question. Ignore @Vince, there are plenty of us here who can cope with time as well as space :-)
– John Powell
Apr 1 at 14:17
1
The problem, I think, is that if you use the distance in meters in ST_DWithin, where the input data are in lat/lon, 4326, then you need to cast to geography not geometry. If you use the first form, then the geometries need to be in a projected coordinate system where the units are in meters. It isn't clear from the question what SRID you are using.
– John Powell
Apr 3 at 5:50
|
show 6 more comments
I have a table with metadata about sampling locations in the ocean. It includes geographic coordinates, date-time in which sampling was done the number of vertical measurements in each location (nlevels), and the kid of instrument used to take samples (instrument column).
Due to this particular dataset came from historical records, it can include duplicates that I would like to identify using spatiotemporal criteria (allowing rounding errors in spatial coordinates). I would like to detect all stations separated by a distance less than 5km and a temporal difference less than 25 hours
My code for testing was:
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::geography, t2.geom::geography, 5000.0, true)
WHERE AGE(t1.date_time, t2.date_time) < interval '1 days 01:00:00' AND t1.cast_id <> t2.cast_id
The create table 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
944149 | 1950-03-09 00:10:12 | 25.4300 | -113.4700 | 13 | mbt
944153 | 1950-03-09 13:55:12 | 25.0000 | -112.8300 | 13 | mbt
The time difference looks ok, but spatial separation it is incorrect. For example, consider the cast ids 944149 and 944153 (the last two) from the above results.
944149 | 1950-03-09 00:10:12 | 25.4300 | -113.4700 | 13 | mbt
944153 | 1950-03-09 13:55:12 | 25.0000 | -112.8300 | 13 | mbt
The distance seems to be greater than 5 km.
Thus, is not fulfilling my duplicated definition (< 5km; < 25 hours)
Could you help me to identify my error?
postgis spatio-temporal-data duplication
edited Apr 3 at 8:12
John Powell
10.9k43050
asked Mar 31 at 2:33
AmaruAmaru
112
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 metadata about sampling locations in the ocean. It includes geographic coordinates, date-time in which sampling was done the number of vertical measurements in each location (nlevels), and the kid of instrument used to take samples (instrument column).
Due to this particular dataset came from historical records, it can include duplicates that I would like to identify using spatiotemporal criteria (allowing rounding errors in spatial coordinates). I would like to detect all stations separated by a distance less than 5km and a temporal difference less than 25 hours
My code for testing was:
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::geography, t2.geom::geography, 5000.0, true)
WHERE AGE(t1.date_time, t2.date_time) < interval '1 days 01:00:00' AND t1.cast_id <> t2.cast_id
The create table 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
944149 | 1950-03-09 00:10:12 | 25.4300 | -113.4700 | 13 | mbt
944153 | 1950-03-09 13:55:12 | 25.0000 | -112.8300 | 13 | mbt
The time difference looks ok, but spatial separation it is incorrect. For example, consider the cast ids 944149 and 944153 (the last two) from the above results.
944149 | 1950-03-09 00:10:12 | 25.4300 | -113.4700 | 13 | mbt
944153 | 1950-03-09 13:55:12 | 25.0000 | -112.8300 | 13 | mbt
The distance seems to be greater than 5 km.
Thus, is not fulfilling my duplicated definition (< 5km; < 25 hours)
Could you help me to identify my error?
postgis spatio-temporal-data duplication
postgis spatio-temporal-data duplication
edited Apr 3 at 8:12
John Powell
10.9k43050
asked Mar 31 at 2:33
AmaruAmaru
112
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.
edited Apr 3 at 8:12
John Powell
10.9k43050
asked Mar 31 at 2:33
AmaruAmaru
112
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.
edited Apr 3 at 8:12
John Powell
10.9k43050
edited Apr 3 at 8:12
John Powell
10.9k43050
edited Apr 3 at 8:12
John Powell
10.9k43050
10.9k43050
asked Mar 31 at 2:33
AmaruAmaru
112
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 Mar 31 at 2:33
AmaruAmaru
112
asked Mar 31 at 2:33
AmaruAmaru
112
112
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.
You haven't attempted a temporal deconfliction constraint, so there's no indication what your time column might be. When doing a self join, you need a WHERE constraint to eliminate identity (t2.id <> t1.id) or previous clusters (t2.id > t1.id). It's not clear what your SRID is so the significance of your search distance is unclear. If you use or cast togeography, you can use a geodetic search in meters (though you need to index appropriately if casting)
– Vince
Mar 31 at 12:38
I followed your suggestion using WHERE to eliminate identity. Also, I used geography to get the distance in meters. The list of duplicates makes sense to me but I am struggling with the date. I am going to edit my question.
– Amaru
Mar 31 at 20:03
Asking for help with theageoperator is probably more in the realm of Database Administrators
– Vince
Mar 31 at 20:11
2
Where does nlevels come from? I think you need the rank function, but, I am unclear on the question. Ignore @Vince, there are plenty of us here who can cope with time as well as space :-)
– John Powell
Apr 1 at 14:17
1
The problem, I think, is that if you use the distance in meters in ST_DWithin, where the input data are in lat/lon, 4326, then you need to cast to geography not geometry. If you use the first form, then the geometries need to be in a projected coordinate system where the units are in meters. It isn't clear from the question what SRID you are using.
– John Powell
Apr 3 at 5:50
|
show 6 more comments
You haven't attempted a temporal deconfliction constraint, so there's no indication what your time column might be. When doing a self join, you need a WHERE constraint to eliminate identity (t2.id <> t1.id) or previous clusters (t2.id > t1.id). It's not clear what your SRID is so the significance of your search distance is unclear. If you use or cast togeography, you can use a geodetic search in meters (though you need to index appropriately if casting)
– Vince
Mar 31 at 12:38
I followed your suggestion using WHERE to eliminate identity. Also, I used geography to get the distance in meters. The list of duplicates makes sense to me but I am struggling with the date. I am going to edit my question.
– Amaru
Mar 31 at 20:03
Asking for help with theageoperator is probably more in the realm of Database Administrators
– Vince
Mar 31 at 20:11
2
Where does nlevels come from? I think you need the rank function, but, I am unclear on the question. Ignore @Vince, there are plenty of us here who can cope with time as well as space :-)
– John Powell
Apr 1 at 14:17
1
The problem, I think, is that if you use the distance in meters in ST_DWithin, where the input data are in lat/lon, 4326, then you need to cast to geography not geometry. If you use the first form, then the geometries need to be in a projected coordinate system where the units are in meters. It isn't clear from the question what SRID you are using.
– John Powell
Apr 3 at 5:50
You haven't attempted a temporal deconfliction constraint, so there's no indication what your time column might be. When doing a self join, you need a WHERE constraint to eliminate identity (
t2.id <> t1.id) or previous clusters (t2.id > t1.id). It's not clear what your SRID is so the significance of your search distance is unclear. If you use or cast to geography, you can use a geodetic search in meters (though you need to index appropriately if casting)– Vince
Mar 31 at 12:38
You haven't attempted a temporal deconfliction constraint, so there's no indication what your time column might be. When doing a self join, you need a WHERE constraint to eliminate identity (
t2.id <> t1.id) or previous clusters (t2.id > t1.id). It's not clear what your SRID is so the significance of your search distance is unclear. If you use or cast to geography, you can use a geodetic search in meters (though you need to index appropriately if casting)– Vince
Mar 31 at 12:38
I followed your suggestion using WHERE to eliminate identity. Also, I used geography to get the distance in meters. The list of duplicates makes sense to me but I am struggling with the date. I am going to edit my question.
– Amaru
Mar 31 at 20:03
I followed your suggestion using WHERE to eliminate identity. Also, I used geography to get the distance in meters. The list of duplicates makes sense to me but I am struggling with the date. I am going to edit my question.
– Amaru
Mar 31 at 20:03
Asking for help with the
age operator is probably more in the realm of Database Administrators– Vince
Mar 31 at 20:11
Asking for help with the
age operator is probably more in the realm of Database Administrators– Vince
Mar 31 at 20:11
2
2
Where does nlevels come from? I think you need the rank function, but, I am unclear on the question. Ignore @Vince, there are plenty of us here who can cope with time as well as space :-)
– John Powell
Apr 1 at 14:17
Where does nlevels come from? I think you need the rank function, but, I am unclear on the question. Ignore @Vince, there are plenty of us here who can cope with time as well as space :-)
– John Powell
Apr 1 at 14:17
1
1
The problem, I think, is that if you use the distance in meters in ST_DWithin, where the input data are in lat/lon, 4326, then you need to cast to geography not geometry. If you use the first form, then the geometries need to be in a projected coordinate system where the units are in meters. It isn't clear from the question what SRID you are using.
– John Powell
Apr 3 at 5:50
The problem, I think, is that if you use the distance in meters in ST_DWithin, where the input data are in lat/lon, 4326, then you need to cast to geography not geometry. If you use the first form, then the geometries need to be in a projected coordinate system where the units are in meters. It isn't clear from the question what SRID you are using.
– John Powell
Apr 3 at 5:50
|
show 6 more comments
0
active
oldest
votes
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "79"
;
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%2fgis.stackexchange.com%2fquestions%2f317259%2fproper-identification-of-duplicates-using-spatial-and-temporal-criteria-in-postg%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 Geographic Information Systems 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%2fgis.stackexchange.com%2fquestions%2f317259%2fproper-identification-of-duplicates-using-spatial-and-temporal-criteria-in-postg%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
You haven't attempted a temporal deconfliction constraint, so there's no indication what your time column might be. When doing a self join, you need a WHERE constraint to eliminate identity (
t2.id <> t1.id) or previous clusters (t2.id > t1.id). It's not clear what your SRID is so the significance of your search distance is unclear. If you use or cast togeography, you can use a geodetic search in meters (though you need to index appropriately if casting)– Vince
Mar 31 at 12:38
I followed your suggestion using WHERE to eliminate identity. Also, I used geography to get the distance in meters. The list of duplicates makes sense to me but I am struggling with the date. I am going to edit my question.
– Amaru
Mar 31 at 20:03
Asking for help with the
ageoperator is probably more in the realm of Database Administrators– Vince
Mar 31 at 20:11
2
Where does nlevels come from? I think you need the rank function, but, I am unclear on the question. Ignore @Vince, there are plenty of us here who can cope with time as well as space :-)
– John Powell
Apr 1 at 14:17
1
The problem, I think, is that if you use the distance in meters in ST_DWithin, where the input data are in lat/lon, 4326, then you need to cast to geography not geometry. If you use the first form, then the geometries need to be in a projected coordinate system where the units are in meters. It isn't clear from the question what SRID you are using.
– John Powell
Apr 3 at 5:50