Rectangular Bins for Latitude and Longitude

Version 2

    Description:

    It is sometimes useful to show high volume geographic data summarised into rectangular bins. This is tricky, though, because of the distortion caused by the map projection.

     

    Robert Morton provided an example of how to do this in an old forum thread (which is no longer available). I have extended these slightly to allow for finer grained bins.

     

    Robert's brief explanation from the original thread said: "This requires a bit of math to undo the Mercator projection prior to binning, because otherwise the bins won't be adjacent and will be disproportionately dense near the equator."

     

    Example Calculation:

    The calculations for six pairs of bins, with bin-1 the largest and bin-6 the smallest are shown below.

     

    Latitude (bin-1)

    ROUND(DEGREES(2*ATAN(EXP(ROUND(2*LN(TAN(RADIANS([Latitude]))+1/COS(RADIANS([Latitude]))),1)/2))-PI()/2),2)

     

    Latitude (bin-2)

    ROUND(DEGREES(2*ATAN(EXP(ROUND(5*LN(TAN(RADIANS([Latitude]))+1/COS(RADIANS([Latitude]))),1)/5))-PI()/2),2)

     

    Latitude (bin-3)

    ROUND(DEGREES(2*ATAN(EXP(ROUND(LN(TAN(RADIANS([Latitude]))+1/COS(RADIANS([Latitude]))),2)))-PI()/2),3)

     

    Latitude (bin-4)

    ROUND(DEGREES(2*ATAN(EXP(ROUND(2*LN(TAN(RADIANS([Latitude]))+1/COS(RADIANS([Latitude]))),2)/2))-PI()/2),3)

     

    Latitude (bin-5)

    ROUND(DEGREES(2*ATAN(EXP(ROUND(5*LN(TAN(RADIANS([Latitude]))+1/COS(RADIANS([Latitude]))),2)/5))-PI()/2),3)

     

    Latitude (bin-6)

    ROUND(DEGREES(2*ATAN(EXP(ROUND(LN(TAN(RADIANS([Latitude]))+1/COS(RADIANS([Latitude]))),3)))-PI()/2),4)

     

    Longitude (bin-1)

    ROUND(DEGREES(ROUND(2*RADIANS([Longitude]),1)/2),2)

     

    Longitude (bin-2)

    ROUND(DEGREES(ROUND(5*RADIANS([Longitude]),1)/5),2)

     

    Longitude (bin-3)

    ROUND(DEGREES(ROUND(RADIANS([Longitude]),2)),3)

     

    Longitude (bin-4)

    ROUND(DEGREES(ROUND(2*RADIANS([Longitude]),2)/2),3)

     

    Longitude (bin-5)

    ROUND(DEGREES(ROUND(5*RADIANS([Longitude]),2)/5),3)

     

    Longitude (bin-6)

    ROUND(DEGREES(ROUND(RADIANS([Longitude]),3)),4)

     

    Comments:

    An example of these calculations can be see in the view below showing the maximum magnitude of earthquakes recorded across New Zealand (download the workbook from Tableau Public to examine the detail). Select different values from the [Resolution] drop-down to display different sized bins.

     

     

    Further Reading/Examples:

    Another example of the use of bins, this time showing maximum earthquake depth, together with other views of the data can be seen in the blog posting this view is taken from.