12 Replies Latest reply on Aug 29, 2015 7:53 AM by Hannes Bos

    Lightning density maps

    Bogdan Antonescu

      Hi,,

       

      I am trying to do a lightning density map, and I want to ask if this is possible using Tableau.

       

      The input data for this map are the location of the cloud-to-ground lightning data, basically points

      for which I have latitude and longitude. The lightning data are stored in a Excel file with two columns

      (lat and lon).

       

      In order to calculate the lightning density maps, I need to define a grid (e.g., 20 x 20 km) that will cover

      a region (e.g., UK). For the next step I need to count the number of lightning points in each cell, and the

      to divide this number by the are of the cell (e.g., 400 km^2). The end result is a filed map based in the

      lightning density data.

       

      My question is: can I do this in Tableau?

       

      Also, it is possible to obtain an filled map with counties colored based in the number of point (defined by lat and lon)

      in each county?

       

      Thank you very much.

       

      Cheers,

      Bogdan

        • 1. Re: Lightning density maps
          Shawn Wallwork

          Bogdan, the answer to your first question about lightening density is: probably, but it will be a bit tricky. Your second question is much easier to do especially if you predetermine which county the lightening strike occurred in and include a County column in your raw data, then it should be fairly straightforward to color the counties by the count of strikes.

           

          It sounds like an interesting project. If you post some sample data I'd be willing to mess with it a bit. --Shawn

          • 2. Re: Lightning density maps
            Richard Leeke

            For your first question, you need to calculate the locations of the centres of each cell of your grid and then display marks at those locations, coloured by lightening strike density.  If you use square marks, then by tweaking the size of the marks you should be able to get an approximation to a filled map - though if the area is too large I think you would start to see the effects of distortion due to the map projection.

             

            I did something a bit similar to show earthquake density in New Zealand in this viz, using some formulae for calculating the lat/lon of the grid which I got from Robert Morton of Tableau.  Take a look at the "Depth" tab, which shows maximum earthquake depth at each point on a grid of variable resolution.  That grid is defined in terms of some fairly arbitrary division of lat/lon, as I recall, you would need different maths to work out your 20 x 20 km grid.

             

            I think what you really want is the ability to define custom shapes for filled maps (in your case a grid of squares), but this isn't supported in version 7.  There was plenty of discussion of the wish to be able to do that on the version 7 beta forum.

             

            For your second question, displaying that data is easy as long as you know which counties the strikes occurred in, as Shawn says.  Tableau can do the visualisation, but it's not a GIS so it doesn't do any analysis of which locations fall within the boundaries of each county.

            • 3. Re: Lightning density maps
              Richard Leeke

              Edit: Whoops, double post.  That often seems to happen when a Captcha is required to post.

              • 4. Re: Lightning density maps
                Shawn Wallwork

                Richard, I had the same idea of using filled squares on top of a Background Image of a map. It's 'do-able', (see attached) but completely impractical because the squares are dynamically re-sized depending on the zoom level. One interesting note: the squares don't actually turn out to be squares, they're rectangles. I suspect this is because monitors don't have the same number of pixels in each direction.

                 

                So I guess this topic should get moved to Wouldn't it be nice if...

                 

                --Shawn

                • 5. Re: Lightning density maps
                  Richard Leeke

                  Thought about this some more while I was stuck in a traffic jam (good place to think).

                   

                  I think it may be possible to generate a filled grid dynamically using polygons by dynamically calculating the coordinates of the corners of the grid.  I'll have a go next time I want a brain-teaser.

                   

                  If that works as I'm thinking it would be much better than the custom geocoding approach (which Tableau doesn't support anyway), since the grid size and area would all be dynamically controlled - you could have a grid-size parameter, for example.

                  • 6. Re: Lightning density maps
                    Richard Leeke

                    I had a quick go at this and it's really easy to do a filled grid with the grid boundaries defined in terms of degrees of latitude and longitude.  I've attached an example with a grid size parameter, so you can adjust the (fractional degree) grid size, using that NZ earthquake data I had from the viz I linked to above.

                     

                    The trouble with this is that the cells of the grid aren't squares and the area on the surface of the earth isn't uniform across the map, so colouring the cells by count of lightening strikes or earthquakes is very misleading if used over a significant geographic area (i.e. if the surface distance corresponding to a degree of latitude varies significantly over the map)..

                     

                    To do it properly and arrive at a grid with uniform surface area cells needs an extension of the calculations I got from Robert Morton to do the grid in that previous earthquake viz.  I had a quick think about how to do that and I think it's doable but non-trivial.

                     

                    The trick to getting the filled polygon grid is just to create a second table with 5 rows and create a data source with a cross product between that and the original data.  That returns 5 rows per source data row, representing the 5 points needed to draw a square (the 4 corners, with the first corner repeated as the last corner to form a closed loop.  The table looks like this:

                     

                    Path Order,Lat Offset,Long Offset

                    1,0,0

                    2,1,0

                    3,1,1

                    4,0,1

                    5,0,0

                     

                    Then just calculate the corners of the grid by adding the offsets, multiplied by a grid size parameter, like this:

                     

                    ROUND([LAT]/[Grid Size (degrees)])*[Grid Size (degrees)] + [Lat Offset] * [Grid Size (degrees)]

                     

                    and

                     

                    ROUND([LONG]/[Grid Size (degrees)])*[Grid Size (degrees)] + [Long Offset] * [Grid Size (degrees)]

                    • 7. Re: Lightning density maps
                      Richard Leeke

                      Thought of a way to use table calculations to make use of Robert's trig functions without having to mess with the trigonometry.

                       

                      This has a choice of 6 sizes for the square grids (roughly 250, 100, 50, 20, 10 and 5 kms).

                       

                      The cells of the grid aren't exactly square, and the size of the grid varies a bit over the extent of the map (from 24.8 km to 25.3 km over a couple of  hundred kms - if I got the sums right).  Hover over the grid and displays what I think the dimensions are (X kms, Y kms).

                       

                      As the area of the cells varies a bit, the best way to colour them is probably with a density measure.  I've used earthquakes per km^2 here (again, if I got the sums right).

                       

                      The cell sizes go wonky in areas where there isn't any data, because I'm using table calculation functions to look up the coordinates of the next cell, rather than doing the maths to work out where the edges are, so if there are cells with no data you can end up with rectangles.

                       

                      That's about the best I can come up with.

                      • 8. Re: Lightning density maps
                        Shawn Wallwork

                        Great stuff Richard!

                        • 9. Re: Lightning density maps
                          Shawn Wallwork

                          Bogdan, I was watching some of the videos from the last Tableau Conference this morning and came across this session:

                           

                          http://www.tableausoftware.com/tcc11-sessions

                          -- Using New Chart Types in Tableau - Alan Eldridge (it's the 5 one down).

                           

                          It's not exactly what you're looking for, but you might find it useful, especially his heat map workbook (see attached).

                          • 10. Re: Lightning density maps
                            Philip Hoyos

                            I know this is quite some time ago this post was created, but it helped me out. So thank you for that. I do have one question though.

                            When I create the polygons it will obviously only create around my specified coordinates - in my case that means that there will be a lot of empty space around some coordinates where the square polygons will be missing (like in the example from above: http://community.tableau.com/servlet/JiveServlet/download/145119-121869/earthquake_grid.png). Thus my question is:

                            1. Are there any easy way to either fill out the empty spaces with polygons? or

                            2. Would it be a better idea to create a grid from the top-left corner of the map and this way create a map grid?

                            If option two - how would you go about linking the information to the square polygon grid.

                             

                            Thank you for your time and help, its much appreciated!

                            /Phil

                            • 11. Re: Lightning density maps
                              Allan Walker

                              Philip Hoyos wrote:

                               

                              1. Are there any easy way to either fill out the empty spaces with polygons? or

                              2. Would it be a better idea to create a grid from the top-left corner of the map and this way create a map grid?

                              If option two - how would you go about linking the information to the square polygon grid.

                               

                              Thank you for your time and help, its much appreciated!

                              /Phil

                               

                              Phil, this might be appropriate: Re: Geographical Map

                              • 12. Re: Lightning density maps
                                Hannes Bos

                                Hi Richard,

                                 

                                Many thanks for sharing your knowledge and solutions. I have tried to replicate the density grid map (NZ Earthquake), abut struggle with the dimension "Path Order". Can you please help? Is "Path Order a coding (1 to 5) I have to add in my source database to all rows, or?

                                 

                                Hope you can help,

                                 

                                Many thanks

                                 

                                Hannes