I am currently working on a Street Lighting Network. I have two layers in PostGIS ( PostGIS 2.1.3 and PostgreSQL 9.3.5):
- Manholes (56.000 Polygon)
- Pipes (Linestring)
The manholes are perfect rectangles composed by 4 nodes (vertices).
I have to digitize all the pipes from each manhole to the other manholes.
When digitizing the pipes, I have activated the snapping, but as the manholes have no middle nodes in each face I have to digitize from the vertices.
I would like to be able to draw a line between manholes but from the middle of a faces of the polygon, not from vertices.
To try to solve this problem, I thought that I could create a middle node between each of the 4 Linestrings of each Polygon. Each resulting polygon should have 8 nodes then.
My approach, which finally ended up working perfectly is:
Dump polygon into linestrings. I have found this query that does it and modify the query to construct a linestring with each startpoint, middlepoint(centroid) and endpoint. Then rebuild the polygon:
SELECT id, ST_MakePolygon(ST_LineMerge(ST_Union(the_geom))) as the_geom FROM( SELECT id, ST_MakeLine(ARRAY[sp,st_centroid(ST_MakeLine(sp,ep)),ep]) as the_geom FROM -- extract the endpoints for every 2-point line segment for each linestring (SELECT id, 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 id, (ST_Dump(ST_Boundary(the_geom))).geom FROM manholes ) AS linestrings ) AS segments ) as lines GROUP BY id