I have a Linestrings layer with all the horizontal signage (lines in the floor) in my town.

enter image description here

I need to calculate the area of the closed linestrings. The thing here is that every side of each closed square is a different linestring. I mean, in the image's sidewalk every rectangle/square is composed of 4 linetrings, instead of being a single linestring the whole rectangle, it is composed of 4 linestrings.

Due to this, when I convert the layer to polygon using ST_MakePolygon I am getting 0 when calculting the st_area of the sidewalk and other elements..

I guess I need to create first a single linestring for every square(rectangle but I don't know how to do it...

I am currently using this query to get the area of closed linestrings with very bad result:

SELECT sub.id, st_area(st_makepolygon(ST_ForceClosed(sub.the_geom))) as area
    sh_lineas sub
     where ST_NPoints(sub.the_geom)>3

My approach would be to create a subquery first and do ST_MakeLine with all the lines that are less than 1 cm from one to the other. Is it right?

  • Please post the query you are using. – John Powell Mar 8 '17 at 10:16
  • I'm not sure but as far as I know there is no simple one-liner for this. You have to write some procedure to collect all intersecting lines with recurrence, join them, create polygons and then cut a holes in them (e.g. sign of triangle with a hole inside) – Jendrusk Mar 8 '17 at 10:59
  • @JohnBarça edited with my tests – Egidi Mar 8 '17 at 11:26
  • I think you need to use ST_LineMerge or ST_Union on sub.the_geom before passing that the ST_MakePolygon. ST_MakePolygon expects a linestring, which would represent the polygon. So ST_MakePolygon(ST_LineMerge(sub.the_geom)) essentially, possibly with ST_ForceClosed. – John Powell Mar 8 '17 at 11:36
  • @JohnBarça I think there is no way to do this in one select cause you have to collect lines to create a polygon... sth like group by geometry but if you know such resolution I'll be happy to see it... – Jendrusk Mar 8 '17 at 16:23

You can do this in two steps using by first dumping the merged lines using ST_Dump, formed by unioning your linestrings together (ST_Union effectively works as a spatial group by) and then use ST_MakePolygon, using ST_IsClosed to convert only those dumped geometries that are closed linestrings.

WITH lines(geom) AS 
  (SELECT (ST_Dump(ST_Linemerge(ST_Union(geom)))).geom 
     FROM some_table)
    row_number() over() as id, 
 FROM lines 
 WHERE ST_Isclosed(geom);

You could also use things like ST_ForceClosed, as you had in your original version, ST_SnapToGrid to further refine things, if you think there might be tiny gaps between what should be intersecting lines. If you are certain that all the lines with make up a polygon, you can actually do this as a single select, dropping the ST_Dump (but this is probably an unsafe approach).

The row_number() over() is just to create a unique id for each polygon, which you may not need, and could also get from the path that is returned along with each geom by ST_Dump.

With the test data in @Jendrusk's post the above produces 8 polygons, the first few of which look like:

1 | POLYGON((-1.14366998577525 0.177809388335704,-1.13798008534851 0.783783783783784,-1.0697012802276 0.783783783783784,-1.06543385490754 0.180654338549075,-1.14366998577525 0.177809388335704))

2 | POLYGON((-0.967972083926032 0.614153627311522,-0.534828413940256 0.614864864864865,-0.736108641536273 0.210881934566145,-0.967972083926032 0.614153627311522))

3 | POLYGON((-0.965838371266003 0.685988620199146,-0.962993421052632 0.791251778093883,-0.860575213371266 0.791251778093883,-0.860575213371266 0.683143669985775,-0.965838371266003 0.685988620199146))

4 | POLYGON((-0.896137091038407 0.577169274537696,-0.600973506401138 0.575035561877667,-0.731129978662874 0.292674253200569,-0.896137091038407 0.577169274537696))

  • niccce... as I said - You're The Boss :) – Jendrusk Mar 9 '17 at 17:52
  • Of course there's cutting holes left, but this one select makes 3/4 of my procedure. I will never thinked out that you need to combine it into one big geometry to later break inot ready to use lines – Jendrusk Mar 9 '17 at 18:01
  • @Jendrusk. Great, I do loads of work in plpgsql and love it, but sometimes there is a less obvious way to do in straight SQL. – John Powell Mar 10 '17 at 10:07
  • Thanks! @JohnBarça your solution plus st_snaptogrid made it!! – Egidi Mar 10 '17 at 13:42
  • @Egidi, you are welcome. Out of interest, where did you put the ST_SnapToGrid, inside the ST_Union function? – John Powell Mar 10 '17 at 13:52

Since I had the same problem some time ago and it could be useful for me to I decided to write some procedure.

Feel free to use it and/or contribute.

I'm sure there is another, maybe simpler way to do this but this is but this is also working.

  • Thanks for the test data. That's a cool procedure, but I'm pretty sure you can do it as a two-step query. – John Powell Mar 9 '17 at 15:40

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.