Adding new nodes in the middle of each líne of a Polygon

Question

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

Result:

Answer 1

I would create a middle point layer for snap purpose.
Using this nice script of Paul Ramsey and updating for your use case:

CREATE TABLE polygons ( 
  gid integer primary key, 
  geom geometry(Polygon, 4326)
);

INSERT INTO polygons VALUES (1, 'SRID=4326;POLYGON((0 0,1 0,1 1,0 1,0 0))');
INSERT INTO polygons VALUES (2, 'SRID=4326;POLYGON((10 10,11 10,11 11,10 11,10 10))');

WITH 
dumps AS ( 
  SELECT gid, ST_DumpPoints(geom) AS pt FROM polygons
), 
pts AS (
  SELECT gid, (pt).geom, (pt).path[2] AS vert FROM dumps
) 
, segments as (
SELECT a.gid, ST_AsText(ST_MakeLine(ARRAY[a.geom, b.geom])) AS geom, 
       a.vert, b.vert 
FROM pts a, pts b 
WHERE a.gid = b.gid AND a.vert = b.vert-1 AND b.vert > 1)
select ST_Line_Interpolate_Point(geom, 0.5) middle_points from segments;

Result (as_text):

"POINT(0.5 0)"
"POINT(1 0.5)"
"POINT(0.5 1)"
"POINT(0 0.5)"
"POINT(10.5 10)"
"POINT(11 10.5)"
"POINT(10.5 11)"
"POINT(10 10.5)"

You can create a new table or view with this and set the snap to it.