[GIS] Simplify the straight sections of (multi)Linestring in PostgreSQL/PostGIS leaving the curved sections untouched

linestringpostgispostgresqlsimplify

I have (multi)linestrings containing upwards of 5000 points and I want to perform two simplifications possibly using Ramer Douglas Peucker or alternative methods in PostGIS. The (multi)linestrings consist of straight lines, 90 degree curves, 180 degree curves and loops (circles).

I understand that ST_simplify may remove the loops and possibly some of the bends depending on the tolerance set. I need to have a zoomed out version which can probably be done satisfactorily done with ST_simplify. However, on the zoomed in version, it is important to keep the loops, and curve points.

I would like to remove all the points from the straight segments (within a tolerance) which constitute 90% of the (multi)linestring and keep the points on the curve section.

How can I achieve this in PostGIS?

Best Answer

Use ST_SetEffectiveArea in conjunction with ST_FilterByM on the result of ST_LineMerge, e.g.:

SELECT  ST_AsText(
            ST_FilterByM(
                ST_SetEffectiveArea(
                    ST_LineMerge('MULTILINESTRING((0 0, 0 1), (0 1, 0 2), (0 2, 0 3), (0 3, 0 4), (0 4, 1 4), (1 4, 2 4), (2 4, 3 4))'::GEOMETRY)
                ),
                0.0001
            )
        )
;

SELECT  ST_AsText(
            ST_FilterByM(
                ST_SetEffectiveArea(
                    ST_Buffer('POINT(0 0)'::GEOMETRY, 0.1)
                ),
                0.0001
            )
        )
;

Related Solutions

[GIS] Find the closest line vertex from the linestring using PostGIS

ST_ClosestPoint will find the closest point on a linestring. For example, locating a point on a road that is closest to a point-of-interest off to one side:

SELECT ST_AsText(ST_ClosestPoint(road, poi))
FROM (SELECT
  'LINESTRING (10 10, 20 20)'::geometry AS road,
  'POINT (14 10)'::geometry AS poi
) AS f;
  st_astext
--------------
 POINT(12 12)
(1 row)

There are theoretically an infinite number of points that are on a line, and this location is exactly on the road linestring:

SELECT ST_Intersects('LINESTRING (10 10, 20 20)'::geometry, 'POINT (12 12)'::geometry);
 st_intersects
---------------
 t
(1 row)

If the objective is to get the closest vertex or coordinate from the linestring, the road needs to be converted to a MultiPoint geometry. Here's a function that does that:

CREATE OR REPLACE FUNCTION ST_AsMultiPoint(geometry) RETURNS geometry AS
'SELECT ST_Union((d).geom) FROM ST_DumpPoints($1) AS d;'
LANGUAGE sql IMMUTABLE STRICT COST 10;

Then use it similar to before:

SELECT ST_AsText(ST_ClosestPoint(ST_AsMultiPoint(road), poi))
FROM (SELECT
  'LINESTRING (10 10, 20 20)'::geometry AS road,
  'POINT (14 10)'::geometry AS poi
) AS f;
  st_astext
--------------
 POINT(10 10)
(1 row)

[GIS] LineString VS Multi-LineString Performance PostGIS

I wouldn't expect any significant difference in performance unless you have very unusual data. There are a few places in the code where optimizations are put in for edge cases like two-point LineStrings, and you will miss out on some of these if you're storing your LineStrings as MultiLineStrings. There may be some special code paths in GEOS that you miss out on with MultiLineStrings as well.

There's a storage overhead to Multi* geometries, which will be small as long as the number of points in your geometry is small (really, it's only significant for single-point MultiPoints).

That said, if your geometries all have only a single component, there is no advantage to storing them as MultiLineStrings, so you may as well convert to LineString. If nothing else, this will make your data model more self-documenting.

Best Answer

Related Solutions

[GIS] Find the closest line vertex from the linestring using PostGIS

[GIS] LineString VS Multi-LineString Performance PostGIS

Related Question