Hot answers tagged

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