6

I have a bunch of GPS tracks in a Postgres+PostGIS database and want to extract a single metric as a measure of turns shown in the data. Some of the GPS targets travel in almost straight lines while others do some curves. The curves are sometimes all right/left hand turns but most of the time it's just squiggles. I've tried to use the length of the line vs the distance between start/end, or area covered by the convex hull but these aren't perfect.

Can you think of a function that would be able to determine the "curviness" of a linestring and could, possibly, differentiate between straight lines, regular turns, or random paths?

This paper makes mention of taking the sums of the delta in bearing for each vertex but I was wondering whether PostGIS has something built in that would make it easier.

There's no guarantee that any GPS track would be complete, cover a set time period, have the same length etcetc

Here are some examples of the GPS tracks:

enter image description here

enter image description here

3

2 Answers 2

6

One measure is Sinuosity which is the line length divided by the shortest distance from the line start and end points:

select id, 
       st_length(geom)/st_distance(st_startpoint(geom), st_endpoint(geom)) as sinuosity, 
       geom
from test.testlines

The values will range from 1 to infinity, 1 for straight lines:

enter image description here

1
  • 1
    Good to have a name for the ratio. It does seem like I would then need 2 metrics to meet my needs. Sinuosity for curvy/not curvy and something else for regular vs not regular turns. Any ideas on the second?
    – RedM
    Nov 8, 2023 at 15:13
2

I'm not sure if this will be of great use to you, it might be too specific, but I have a function that tests for what I call cutbacks in lines. This is where a line bends more than a certain amount of degrees.

First create a new type:

create type cutback_flag as (hascutbacks bool, cutbackgeom geometry);

Then create the cutback function - you will need to adjust the degree ranges to use the threshold you want. Currently it flags bends greater than 90 degrees (more than a right angle bend, an acute angle rather than obtuse). The function returns true if there is a cutback in the source geometry and also returns a point at the cutback:

create or replace function flag_cutbacks(g geometry)
returns cutback_flag language plpgsql as $$
declare
    numpoints int;
    cutbackgeom geometry;
    hascutbacks bool;
    r cutback_flag;
begin
    select st_numpoints(g) into numpoints;
    for i in 1..numpoints loop
        if  degrees(st_angle(st_pointn(g,i+1),st_pointn(g,i),st_pointn(g,i-1),st_pointn(g,i))) between 0 and 90
            or degrees(st_angle(st_pointn(g,i+1),st_pointn(g,i),st_pointn(g,i-1),st_pointn(g,i))) between 270 and 360 then
            hascutbacks := true;
            cutbackgeom := st_collect(cutbackgeom, st_pointn(g,i));
        end if;
    end loop;
    select hascutbacks, st_union(cutbackgeom) into r;
    return r;
end; $$;

Use the function:

select (flag_cutbacks(geom)).*, id, geom from super_awesome_geometry_table;

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.