# clustering similar linestring with direction in PostgreSQL(PostGIS)

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?

• 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) 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 Commented Jan 5, 2023 at 10:36

## 1 Answer

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.

• @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. Commented Jan 5, 2023 at 13:43