How to convert polygon data into line segments using PostGIS

sql postgresql gis postgis
gouse shaik picture gouse shaik · Sep 29, 2011 · Viewed 11.2k times · Source

I have a polygon data table in PostgreSQL/PostGIS. Now I need to convert this Polygon data into its corresponding line segments. Can anybody tell me how to convert it using PostGIS queries.

Thanks in Advance

Answer

mloskot picture mloskot · Oct 4, 2011

Generally, converting polygon to line may be not straightforward because there is no one-to-one mapping and various elements of polygon map to different linestring (exterior ring, interior rings, etc.).

Considering that, you will need to split each of those separately following possible approach like this:

SELECT ST_AsText( ST_MakeLine(sp,ep) )
FROM
   -- extract the endpoints for every 2-point line segment for each linestring
   (SELECT
      ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) as sp,
      ST_PointN(geom, generate_series(2, ST_NPoints(geom)  )) as ep
    FROM
       -- extract the individual linestrings
      (SELECT (ST_Dump(ST_Boundary(geom))).geom
       FROM mypolygontable
       ) AS linestrings
    ) AS segments;

depending on what polygon data are stored in mypolygontable, you may want to dump not only the boundary (as above using ST_Boundary) but also other elements. The code above with more detailed overview is taken from the postgis-users list: Split a polygon to N linestrings

There is also a generic approach to the problem explained in Exploding a linestring or polygon into individual vectors in PostGIS

Related questions

SQL query for point-in-polygon using PostgreSQL

I have the following simple table: CREATE TABLE tbl_test ( id serial NOT NULL, poly polygon NOT NULL ) WITH (OIDS=FALSE); I then try to insert a row with a polygon: insert into tbl_test values(1, PolyFromText('POLYGON((0 0, 10 10, 10 0, 0 0))')) And …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More