Splitting Geometries from Vertex Points with PostGIS

Article Number : 10

In this post, I will explain how you can get segments by LineString or Polygon geometry’s vertex points.

You can use it if you have your own spatial table in your existing database. If you do not have a spatial table for testing, you can download the zip file I have shared below and use the patika.sql file in it.

If you open the patika.sql file with a Text Editor, you can create a spatial table by running the SQL codes in it in the Query Tool in the PgAdmin application.

After this step, all you have to do is run the SQL code below.

WITH segments AS (
SELECT id as patika_id, (
  ST_MakeLine(lag((pt).geom, 1, NULL) OVER (PARTITION BY id ORDER BY id, (pt).path), (pt).geom)) AS geom
  FROM (SELECT id, ST_DumpPoints(geom) AS pt FROM patika) as dumps
)
SELECT * FROM segments WHERE geom IS NOT NULL;

If you want to save this query as a different table, you can add “CREATE TABLE segments AS” to the beginning.

CREATE TABLE segments AS WITH segments AS (
SELECT id as patika_id, (
  ST_MakeLine(lag((pt).geom, 1, NULL) OVER (PARTITION BY id ORDER BY id, (pt).path), (pt).geom)) AS geom
  FROM (SELECT id, ST_DumpPoints(geom) AS pt FROM patika) as dumps
)
SELECT * FROM segments WHERE geom IS NOT NULL;

In case of a possible problem, you can write a comment below. I wish you success in your work.