4
votes
Accepted
using st_collect vs st_union to calculate centroids
ST_Collect aggregates input sets into the minimally possible higher dimensional representation (either a MULTI geometry or a GEOMETRYCOLLECTION) without changing the structure of the input geometries. ...
- 25.1k
4
votes
Accepted
Is "order by" clause in DBSCAN necessary?
It's not necessary to obtain a correct result. But because DBSCAN may have multiple valid solutions for a given set of inputs, the ordering may be necessary to obtain a stable result (i.e., the same ...
- 12.5k
3
votes
Accepted
ClusterDBSCAN eps unit is meter or degrees if SRID is 4326
No. You are looking at an eps distance of 100 degrees!
ST_ClusterDBSCAN currently has no signature accepting a GEOGRAPHY, so your only option is to ST_Transform your input GEOMETRYs to a suitable ...
- 25.1k
3
votes
Best approximation of meters to degrees - North America
I would do it this way, by projecting your geometries which are given in WGS84 (EPSG:4326) into your preferred local Cartesian coordinate reference frame (in order to have metric distances):
SELECT
...
- 7,063
3
votes
Accepted
Total length of intersecting lines - comparing multiple lines to each other
You need to buffer the tracks, get the intersection, get the total intersection length per intersecting imei. Then you can compare the lengths. More length = more intersection.
with tracks AS
(...
- 6,584
3
votes
using st_collect vs st_union to calculate centroids
st_union will merge coincident points, st_collect will preserve them. So a centroid computed with st_collect will give more weight to coincident points, with st_union any location with coincident ...
- 54k
2
votes
Accepted
Calculating speed at various points along a route for each device
Arithmetic on two TIMESTAMP types return an INTERVAL type - use their EPOCH second representations instead:
( EXTRACT(EPOCH FROM time_created) - EXTRACT(EPOCH FROM LAG(time_created) OVER(PARTITION BY ...
- 25.1k
2
votes
Best approximation of meters to degrees - North America
You want to use the GEOGRAPHY data type to measure geoidal distances globally.
Functions providing a signature for this type accept any threshold parameters, and return their measurements, in meter (...
- 25.1k
2
votes
Total length of intersecting lines - comparing multiple lines to each other
ST_Within() returns a boolean. It won't help you much in your task, I guess.
What you should do is comparing how much shared area exist between buffers for each pairs of lines.
Functions to play with ...
- 7,063
2
votes
Accepted
Total number of intersections of multiple linestrings - not working as expected
For the dwithin search, you're trying to do too many things. You just need to find how many other tracks are within 5m of each track. This could work:
select imei, count(intersectedimei) from
(
...
- 6,584
2
votes
Accepted
Total number of intersections within a given radius
If you want buffered intersection, one simple way is to buffer your tracks before the intersection finding. I would go with something like this (buffer of 2.5m to have intersection with lines at 5m):
...
- 2,022
1
vote
Accepted
Copy/Paste of features into existing PostGIS table
One way to do it, from the postgis side, is to add a new serial column and set it as the primary key:
alter table your_table add column pk serial primary key;
This will provide QGIS the next sequence ...
- 6,584
1
vote
Accepted
Clustering based on diverging and converging coordinates
This sounds like a timeseries+geospatial kind of question rather than just a pure spatial question. I'm guessing that this is GPS data and you're trying to locate the start/end points of repeated ...
- 1,276
1
vote
Accepted
PostGIS, create a view with a spatial join
recognition to the good folks in the comments.
I finalized it on a MATERIALIZED VIEW with a automated REFRESH.
SELECT aton_float.*,
depth_area.drval1 AS depth_at_pos_min,
depth_area.drval2 AS ...
- 874
1
vote
Accepted
ST_GEOMFROMTEXT generated by ora2pg fails
Ora2pg seems to generate non-standard WKT. POLYGON is a geometry that is made from straight segments. Same kind of geometry with curved segments is a CURVEPOLYGON.
Edit POLYGON into CURVEPOLYGON and ...
- 54.1k
Only top scored, non community-wiki answers of a minimum length are eligible