5

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.

enter image description here

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:

enter image description here

4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.