3

Linestrings with directions are stored in the form of geometry in PostgreSQL, as shown in the figure below.

I would like to cluster similar(location, length, and direction) shapes through functions such as ST_ClusterDBSCAN of PostGIS, but there is no option in ST_ClusterDBSCAN considering the direction of the straight line. (direction of the line is determined by the start and end points of the stored node. )

Is there any good way to solve the problem?

enter image description here

enter image description here

4
  • What are your measures of similarity? Direction? Location? Length? Are all your linestrings straight or do some have additional points? Please add this information to the question to make it more clear. :) Commented Jan 5, 2023 at 8:42
  • Thank you for the good feedback. the elements of similarity are three conditions: location, length, and direction. and all linestrings are straight (have not additional points)
    – myskbj
    Commented Jan 5, 2023 at 8:56
  • Two horizontal lines, one from left to right and the other from right to left should be considered to have the same direction? One will get azimuth value of 0 and the other 270
    – Bera
    Commented Jan 5, 2023 at 10:13
  • The two straight lines must be grouped into separate clusters
    – myskbj
    Commented Jan 5, 2023 at 10:36

1 Answer 1

2

A common way is to collapse multi-dimensional observations into low-dimensional realms that can be handled with simpler algorithms - in this case could try to find a coefficient for your 4 dimensions (x, y, length, azimuth) to project onto the 2 dimensions (x, y) needed for the cluster functionality in PostGIS.

The general idea is to create a surrogate table with Point geoms having their coordinates augmented by a given coefficient derived off the 4 line predicates (x, y, length, azimuth); a simple starting point could be to use the ST_Centroid of your segments as positional indicator and work in weighted factors of the predicates:

SELECT
  line_id,
  ST_MakePoint(
    1 / ( <positional_weight> * x + <length_weight> * len + <directional_weight> * azm ),
    1 / ( <positional_weight> * y + <length_weight> * len + <directional_weight> * azm )
  ) AS geom
FROM
  <lines>,
  LATERAL ST_Length(geom) AS len,
  LATERAL ST_Centroid(geom) AS ctr,
  LATERAL ST_X(ctr) AS x,
  LATERAL ST_Y(ctr) AS y,
  LATERAL ST_StartPoint(geom) AS sp,
  LATERAL ST_EndPoint(geom) AS ep,
  LATERAL DEGREES(ST_Azimuth(sp, ep)) AS azm
;

This is a simplistic weighting: you can and should normalize/enhance the factorization to suit your needs; the idea is to be able to weight all predicates individually based on their importance for the actual clustering.

On a table like the one above you can apply ST_ClusterDBSCAN (or ST_ClusterKMEANS) with the eps parameter referencing the 'observation distance' of the surrogate Point coordinates. You can link back to your original lines by their line_id.

1
  • 1
    @BERA yes and no: the LATERAL expressions are executed/evaluated only once per row - and only PL/pgSQL functions get cached - so you can refer to the same expression output of a single invocation multiple times in the SELECT block, as well as subsequent LATERAL expressions.
    – geozelot
    Commented Jan 5, 2023 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.