9 Replies Latest reply on Oct 4, 2013 2:47 PM by Richard Leeke

    Polygons with a hole in the middle (a.k.a. donut shaped)

    Siebe Bosch

      Dear forum,


      When converting ESRI Shapefiles into polygons for Tableau I run into the problem that Tableau does not understand polygons that have a holes in the middle. In shapefiles this principle is dealt with by defining the "inner circle" as a polygon in counterclockwise direction whereas the "outer circle" is defined in clockwise direction.


      Has any of you successfully imported donut-shaped polygons into Tableau?




        • 1. Re: Polygons with a hole in the middle (a.k.a. donut shaped)
          Richard Leeke

          Have you had a look at my Viz Talk thread about Filled Maps?


          That discusses two approaches to converting shape files for use in Tableau: one is an unsupported hack to insert the shapes into Tableau's internal geocoding database. That handles inner rings automatically. The other approach is a utility for turning a shape file into a set of points to allow you to draw polygons. That has the advantage of not doing anything unsupported with Tableau, but the disadvantage of being less functional. One of the things it can't do directly is support inner rings because Tableau polygons don't have the concept of inner rings. So the best you can do is create separate polygons for each hole in the shape and arrange the drawing order so that the holes are drawn over the top of the outer polygon.


          You can see an example of both of those approaches on the third tab of the embedded viz at the top of the article (which shows Tsunami warning zones near my house) - also embedded below. The article talks more about it.


          1 of 1 people found this helpful
          • 2. Re: Polygons with a hole in the middle (a.k.a. donut shaped)
            Siebe Bosch

            Thanks Richard! That is already quite helpful. I didn't know about the hack, although I'd prefer sticking to my own conversion routine. Using the various rings in the right order seems like a nice solution for now. I just thought of another solution: defining the outer ring simply as one polygon. The only downside would be that there will be a line between the inner and outer ring.

            • 3. Re: Polygons with a hole in the middle (a.k.a. donut shaped)
              Richard Leeke

              Yeah, I experimented with that, but it is decidedly non-trivial (and looks quite ugly with all the lines) if there are multiple inner rings. The hard part is deciding where to draw the lines from the outer ring to each inner ring without intersecting part of the outer boundary or one of the other inner rings. If you have a simple donot-shaped parcel it's easy, but if you have a complex labyrinth of lakes and waterways it can be hard even to do it by eye.

              1 of 1 people found this helpful
              • 4. Re: Polygons with a hole in the middle (a.k.a. donut shaped)
                Siebe Bosch

                Hmm, as you might have guessed my job too is working with lakes and waterways... I might have a look into that hack after all. Thanks for the warning on the complexity of drawing lines between inner and outer ring. Saves me a lot of headache

                • 5. Re: Polygons with a hole in the middle (a.k.a. donut shaped)
                  Allan Walker

                  Hi Siebe,


                  Here's Scotland water (from the cloudmade Scotland OSM data)




                  Upshot: Path order is very important.


                  P.S. Let me know if you need Holland/Belgium etc.

                  1 of 1 people found this helpful
                  • 6. Re: Polygons with a hole in the middle (a.k.a. donut shaped)
                    Richard Leeke

                    As a matter of interest, I just happened to come across an old workbook where I drew a very donut-shaped donut as a polygon with a hole in the middle using the approach you were thinking about. With a filled polygon you actually don't get a hard line between the outer and inner rings, but if you look carefully you can just see a faint vertical line from the centre of the circle (which was only there because of how I'd drawn this particular example).


                    Filled Donut.PNG.png


                    Line Donut.PNG.png

                    • 7. Re: Polygons with a hole in the middle (a.k.a. donut shaped)
                      Siebe Bosch

                      Thanks, Allan. It's not that I need The Netherlands; I need to plot results of my hydraulic computations in a part of the country, but I've got the issue solved now. Will post my solution in response to Richard's answers.

                      • 8. Re: Polygons with a hole in the middle (a.k.a. donut shaped)
                        Siebe Bosch

                        Looks excellent, Richard. Actually I solved the problem by drawing the various rings in the right order (your first suggestion if I recall correctly). Works for now, but for more complicated stuff in the future I'll certainly look into your other things.

                        Schermafbeelding 2013-10-04 om 23.15.32 1.png


                        For those who are interested: I wrote a VBA-script that uses the Mapwindow OCX. Using the OCX I can read shapefiles and write all their content to Excel. For each part inside a shape I create a new shape. Then I write the points one by one to the Excel-workbook, numbering each point in ascending order. I included the VBA-code below.


                        VBA (Visual Basic for Applications) code to read an ESRI shapefile by using the OCX from Mapwindow (www.mapwindow.org). So I recommend you first install MapWindow version 4.8. Then in Excel you can create a reference to the mapwingis.ocx library. The code below will help you reading a shapefile and writing the points + path oder to excel.





                        Option Explicit


                        Private Sub CommandButton1_Click()


                          Dim myMW As clsMapWindow

                          Set myMW = New clsMapWindow

                          Dim Utils As MapWinGIS.Utils

                          Set Utils = New MapWinGIS.Utils

                          Dim myShape As MapWinGIS.Shape, partShape As MapWinGIS.Shape

                          Dim myPoint As MapWinGIS.Point

                          Dim PolyNum As Long


                          Dim X As Double, Y As Double, Lat As Double, Lon As Double

                          Dim i As Long, j As Long, r As Long, k As Long, c As Long

                          Dim asfFieldIdx As Long, Field1Idx As Long, Field2Idx As Long


                          Dim asf As clsShapeFile

                          Set asf = New clsShapeFile


                          'read the path to the shapefile and subdivide multipart polygons into separate polygons

                          Call asf.Read(ActiveSheet.Cells(2, 2))

                          Call asf.SF.ExplodeShapes(False)


                          'clear the worksheet



                          'find the field index numbers for required fields

                          For i = 0 To asf.SF.NumFields - 1

                            If UCase(Trim(asf.SF.Field(i).Name)) = UCase(Trim(ActiveSheet.Cells(2, 3))) Then

                              asfFieldIdx = i

                            ElseIf UCase(Trim(asf.SF.Field(i).Name)) = UCase(Trim(ActiveSheet.Cells(2, 4))) Then

                              Field1Idx = i

                            ElseIf UCase(Trim(asf.SF.Field(i).Name)) = UCase(Trim(ActiveSheet.Cells(2, 5))) Then

                              Field2Idx = i

                            End If



                          'write the column headers to the worksheet

                          r = 5 'row number of worksheet

                          c = 1 'column number of worksheet

                          ActiveSheet.Cells(r, c) = "Polygon number"

                          ActiveSheet.Cells(r, c + 1) = ActiveSheet.Cells(2, 3)

                          ActiveSheet.Cells(r, c + 2) = ActiveSheet.Cells(2, 4)

                          ActiveSheet.Cells(r, c + 3) = ActiveSheet.Cells(2, 5)

                          ActiveSheet.Cells(r, c + 4) = "Part"

                          ActiveSheet.Cells(r, c + 5) = "Vertex"

                          ActiveSheet.Cells(r, c + 6) = "X"

                          ActiveSheet.Cells(r, c + 7) = "Y"

                          ActiveSheet.Cells(r, c + 8) = "Lat"

                          ActiveSheet.Cells(r, c + 9) = "Lon"


                          'walk through all shapes in the shapefile

                          For i = 0 To asf.SF.NumShapes - 1

                            Set myShape = asf.SF.Shape(i)

                              'walk through all parts that this shape has

                              For j = 0 To myShape.NumParts - 1

                                'convert the part to a new shape

                                Set partShape = myShape.PartAsShape(j)

                                PolyNum = PolyNum + 1 'make sure each polygon gets a unique number (for DETAIL in Tableau)


                                'walk through all parts in the shape

                                For k = 0 To partShape.numPoints - 1


                                  'update the progress indicator

                                  ActiveSheet.Cells(3, 2) = (k + 1) / myShape.numPoints



                                  'go down one row on the worksheet and write all data for this point

                                  r = r + 1

                                  X = myShape.Point(k).X  'X Dutch RD-coordinate system

                                  Y = myShape.Point(k).Y 'Y Dutch RD-coordinate system

                                  Call RD2LATLONG(X, Y, Lat, Lon) 'a conversioin routine to convert XY to WGS85 projection

                                  ActiveSheet.Cells(r, c) = PolyNum 'number of the polygon (Use on DETAIL in Tableau!)

                                  ActiveSheet.Cells(r, c + 1) = asf.SF.CellValue(asfFieldIdx, i) 'cell value from a field in the shapefile

                                  ActiveSheet.Cells(r, c + 2) = asf.SF.CellValue(Field1Idx, i)   'cell value from a field in the shapefile

                                  ActiveSheet.Cells(r, c + 3) = asf.SF.CellValue(Field2Idx, i)   'cell value from a field in the shapefile

                                  ActiveSheet.Cells(r, c + 4) = j + 1 'shape index number

                                  ActiveSheet.Cells(r, c + 5) = k + 1 'point index number (Use on PATH in Tableau!)

                                  ActiveSheet.Cells(r, c + 6) = X 'X-coordinate in Dutch RD-system

                                  ActiveSheet.Cells(r, c + 7) = Y 'Y-coordinate in Dutch RD-system

                                  ActiveSheet.Cells(r, c + 8) = Lat 'Latitude in WGS84

                                  ActiveSheet.Cells(r, c + 9) = Lon 'Longitude in WGS84




                        End Sub

                        • 9. Re: Polygons with a hole in the middle (a.k.a. donut shaped)
                          Richard Leeke

                          Glad you got it going.


                          I can't remember the details, but I know I did hit examples doing the same thing with shspetotab where it just wasn't possible to predict the correct order in all cases. That may have been with multi-polygons (ie multiple outer rings as well as inner rings in a single shape).


                          One tricky case I do remember is where there are outer rings inside inner rings - even with inner rings inside them. Think of a country with a lake with an island in the lake and a smaller lake on the island.



                          Sent from Telecom’s Smartphone network.