4

I've searched around but haven't find anything about this topic. I have several lines that represent the border between adjacent areas, so each area is delimited by several lines. How can I merge the lines to create the polygons representing the areas? Each line has two columns representing the areas that it separates.

Added:

The problem can be divided in two steps: - Merge lines in a single unified line (ST_LineMerge, ST_Collect, ST_Union) - Create a polygon from the unified line

0

In CartoDB you can do this as two steps, first the SQL and then the creation of a new table from the result.

The SQL

SELECT 
  ST_MakePolygon(ST_LineMerge(ST_Collect(the_geom))) 
  the_geom, some_id_of_polygon 
FROM 
  tablename 
GROUP BY 
  some_id_of_polygon

That should give you a two column result, you wont be able to map it yet. You may also run into a problem if you don't close your polygon. Meaning, you need the polygon to end on the same point it started (in the list of vertices). If that is the case, you could fix it with,

SELECT 
  ST_MakePolygon(
    ST_AddPoint(
      ST_LineMerge(ST_Collect(the_geom)), 
      (SELECT ST_StartPoint(the_geom) FROM tablename 
       WHERE some_id_of_polygon = t.some_id_of_polygon LIMIT 1
      )
  ) 
  the_geom, some_id_of_polygon 
FROM 
  tablename 
GROUP BY 
  some_id_of_polygon

Create a new table

Now, you should be able to create a new table by clicking the link that appears in your table view Create new table from Query. If you don't see it right away, click the dropdown Options in the upper right and you should see it in there.

Your new table should be a set of polygons and your some_id_of_polygon

|improve this answer|||||
  • Hi! Thanks for the information. I get "Shell is not a line" message after running the sql query. Any hint? Thanks in advance. – user39301 Nov 15 '14 at 11:21

Your Answer

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