1 Reply Latest reply on Jun 8, 2011 4:20 AM by guest contributor

    TCRL: Rectangular bins for latitude and longitude

    Richard Leeke

      Robert Morton provided these calculations for deriving rectangular bins on a map in a workbook in this thread.

       

      I added a couple of extra levels of finer-grained bins, so thought I might as well put them on the TCRL to-do list.

       

      Here's Robert's brief explanation from that thread: "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."

       

      The original thread also includes a link to an example on Public and a packaged workbook with lots of data showing the calculations in use.

       

      I've flagged the ones I added in case I got the sums wrong (though they seem to work OK).

       

      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) (Richard)

       

      ROUND(DEGREES(

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

      - PI()/2

      ),

      3)

       

      Latitude (bin-6) (Richard)

       

      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) (Richard)

       

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

       

      Longitude (bin-6) (Richard)

       

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