6 Replies Latest reply on Dec 29, 2016 1:45 PM by Kirk Wythers

    Custom GIS Polygon Optimization

    alexander.turner.0

      Hi all,

       

      Some years ago using a vbscript that Joe Mako had created which unfortunately for new users no longer exists I created custom postcode polygons for Greece which have been working fine.

       

      The polygon table is 6 columns by 887.144 rows:

      Column A: PolygonID

      Column B: SubPolygonID

      Column C: PointID (this is the polygon path)

      Column D: Longitude

      Column E: Latitude

      Column F: PostCode


      The kind of maps I've been creating are similar to the below:

      All LOB Full Color Cap 1.jpg


      The issue is that each visualization takes a bit more than a minute and a half and when multiplied by many clients and trials creates a bottleneck... (I'm running 9.1 Desktop in a fast workstation, i7 3770K, 32 gb ram, ssd). On top of this the table containing the data to be visualized on the polygons needs to be a very small table else the 1.5 minutes becomes easily more than 5.


      I tried to go through the exercise of re-creating the above table using this article: Step By Step Instructions – ShapeFile to Custom Polygons using Open Source GIS (QGIS)


      I got a much reduced table that now has 454163 rows but as Greece has many islands got shady results as you will note from the below picture (circled in red some of the issues):


      Test Pic.png

       

      So, the questions are:

       

      1. Is there a way do you think to optimize the initial table?

      2. Through Import Custom Geo-coding one can import points on the map. Is there a way to import / embed polygons ??

       

      Any other ideas / workarounds much appreciated in advance,

       

      regards,

      Alex

        • 1. Re: Custom GIS Polygon Optimization
          Simon Runc

          hi Alex,

           

          ...Announced during TC15 (Developers on Stage) was the ability (at some point in the future...they weren't specific) to load ShapeFiles directly into Tableau as a datasource...however that doesn't help you atm!

           

          So Islands are always an issue...if you think about the Path Values we create, if a 'geography' encompasses several islands and our path goes (1st Island)1,2,3...100 (second Island) 101,102... Tableau will plot 100 ->101 across the sea! This is what the SubPolygon_ID in your original solution was doing. Do you still have this in your new data? if so try doing the path's on SubPolygonID (which you'll also need to bring into your VizLoD). You can then colour/blend/Label on PolygonID, i just needs the Sub-Polygon to restart the path for each Island.

           

          There is also an attachment to my article, which shows the use of a tool in QGIS called simplify geometries. This has the effect of reducing the number of points, and if you get the level of simplification correct there is very little difference in the resolution/accuracy of the plotted-shapes.

           

          What is your source shapefile for this? If you let me know where you got it (eg. In the UK the ONS [Office Of National Statistics] provides free downloads for all UK geographies). I can take a look.


          ...nice computer spec btw!! I wish I had 32GB!!

          • 2. Re: Custom GIS Polygon Optimization
            alexander.turner.0

            Simon hi,

             

            Many thanks for your response.

             

            I'm not a GIS specialist (not even close). There is no SubPolygon_ID in the new data, hence as you say the problem.

             

            What I could do, please confirm if you think it would work, is the following:

             

            I can write a routine to deduce when one polygon finishes but the next Polygon_ID is not zero and then either introduce a SubPolygon_ID or restart numbering from 0 for the new polygon.

             

            basically i would check when (Lat + Lon) are the same in the same Polygon (post code) that would mean that the specific polygon is closed. If then the next Polygon_ID is not zero it means the path is "jumping" to somewhere else.

             

            What do you think ??

             

            Unfortunately the shapefiles were purchased and are not distributable... I will try the simplify geometries to see if it would make a difference.

             

            thanks again!

            • 3. Re: Custom GIS Polygon Optimization
              Simon Runc

              alexander.turner.0 wrote:

              I'm not a GIS specialist (not even close).

              ...me too! that's why I wrote the original QGIS Wiki article...So that it came from a 'layman' perspective...so a lot of 'press this', 'press that'...without thinking too much about what was happening!

               

              Yes your idea sounds like it would do the trick. I'm fortunate enough to have Alteryx now, where you load in a Shapefile and 20 seconds later a Polygon TDE (with Path) is spat out the other end! (if it's any consolation, I do feel guilty using it, knowing the pain of those without!!).This is actually a macro in Alteryx that someone (wonderful!) has built, and I can go into the macro and see what's it's doing...It's basically doing what you've suggested (it does create a Sub-Polygon ID (and path for each sub-polygon) and you need to bring both (Polygon and Sub-polygon) into the VizLoD in Tableau for it to plot the map correctly.

               

              On the simplification...If you look at the polygon file in Tableau as points, and zoom in you'll see lots of points on top of one another (i.e. loosing them wouldn't affect the polygon that is drawn)...and you end up with a far smaller file. The simplify geometries as a variable on how much simplification it's applying, so it's a bit of trial and error to get the fewest points, without loosing accuracy. The default setting seems to take around 1/4 of the points away. So your file would be around 300k.

               

              Hope this helps

              1 of 1 people found this helpful
              • 4. Re: Custom GIS Polygon Optimization
                alexander.turner.0

                Simon hi,

                 

                Got around to writing it and seems to be working in my case quite nicely

                 

                Managed to reduce the table to 110,000 rows...

                 

                The code is written in VBA and assumes one has followed your steps in producing the text file and has dumped that in an xls file.

                 

                here it is below for anyone else who faces the same problem. Just copy it and save it as anything.bas and import as a module in excel:

                 

                 

                Attribute VB_Name = "ModFixPolygons"

                Option Explicit

                 

                 

                Private Sub Make_Sub_Polygon_ID()

                Dim data(), SubPolygon() As Integer

                Dim x As Long, j As Long, xRowCount As Long

                Dim xCounter As Long

                Dim xFlag As Boolean

                Dim SubPolygonCounter As Integer

                Dim xLatLon As String

                 

                 

                xRowCount = ActiveSheet.Range("A1").CurrentRegion.Rows.Count - 1

                ReDim data(1 To 4, 1 To xRowCount) '5 columns, Lat, Lon, PolygonID, SubPolygonID, Cresta_ID

                ReDim SubPolygon(1 To xRowCount, 1 To 1)

                 

                 

                xCounter = 0

                'Assuming that:

                'Column 1/A is Longitude or X

                'Column 2/B is Latitde or Y

                'Column 3/C is PostCode

                'Column 4/D is the PolygonID

                'Column 8/H is the SubPolygonID which this Sub calculates

                 

                 

                For x = 1 To xRowCount

                    data(1, x) = ActiveSheet.Cells(x + 1, 1) 'Latitude X

                    data(2, x) = ActiveSheet.Cells(x + 1, 2) 'Longitude Y

                    data(3, x) = ActiveSheet.Cells(x + 1, 3) 'PostCode

                    data(4, x) = ActiveSheet.Cells(x + 1, 4) 'PolygonID

                Next

                 

                 

                SubPolygon(1, 1) = 1

                 

                 

                SubPolygonCounter = 1

                xLatLon = data(1, 1) & data(2, 1) 'Load initial LatLon combination

                 

                 

                For x = 2 To xRowCount - 1

                    If xLatLon <> data(1, x) & data(2, x) Or xFlag Then 'we are in the same polygon

                        SubPolygon(x, 1) = SubPolygonCounter

                        xFlag = False

                    Else 'i.e. we are not on the same polygon

                        xLatLon = data(1, x + 1) & data(2, x + 1) 'Load Start of New Polygon

                        xFlag = True

                      

                        If data(4, x) = data(4, x + 1) Then

                            'This Polygon is finished but in the same Postcode.

                            'i.e. this is a sub-polygon

                            SubPolygon(x, 1) = SubPolygonCounter

                            SubPolygonCounter = SubPolygonCounter + 1

                        Else

                            'This Polygon is finished and we are in a new Postcode

                            SubPolygonCounter = 1

                            SubPolygon(x, 1) = SubPolygonCounter

                        End If

                      

                    End If

                Next

                 

                 

                'Dump SubPolygons to Column 8, row 2 Onwards

                ActiveSheet.Range(Cells(2, 8), Cells(xRowCount, 8)) = SubPolygon

                 

                 

                End Sub

                • 5. Re: Custom GIS Polygon Optimization
                  Simon Runc

                  hi Alex,

                   

                  Excellent work!...and thanks for sharing the VBA. I'll also add this to my Custom Polygon's document, as this will be very handy for lots of people...Thanks again!

                  • 6. Re: Custom GIS Polygon Optimization
                    Kirk Wythers

                    Hi Alex,

                     

                    Thank you for posting this. I am in the same boat as many others and would like to give this a go... However, I have never run a line of VBA code in my life (done lots of other coding, but no VBA). I did get the code imported into my Excel a a module, but I'm suspisious that I need to modify your code somewhat to match the column positions of my spreadsheet. Can you give me the crash course on what to do next?

                     

                    BTW... sorry to bother you with this.

                     

                    My exported shape file is attached. Thanks!.