Baixe o app para aproveitar ainda mais
Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original
INTRODUCTION ProjectionandTransformationCalculations.xls v3.36 23-December-2013 This spreadsheet which will carry out common calculations with coordinates. All of the concepts and formulae given in "A guide to coordinate systems in Great Britain", which is the user manual for these calculations, are detailed in this spreadsheet. "A guide to coordinate systems in Great Britain" is available from the Ordnance Survey GPS web site (www.ordnancesurvey.co.uk/gps). The guide gives some important warnings about the use of its formulae, which you should be aware of. There are also other projection functions such as scale factors, convergence and t-T in this spreadsheet that are not in the guide. The Constants sheet is where ellipsoid, projection and Helmert transformation parameters are entered. The Enter coordinates here sheet is for user input of coordinates to the calculations. Use the buttons at the bottom left of the screen to scroll through the other sheets. Each sheet carries out a different calculation. The calculations are shown in "step by step" format where all the intermediate calculation results are given and they are also shown as results in single cells computed by user defined functions written in Visual Basic. The code for the user defined functions can be viewed and if required copied to other applications. The calculation sheets are as follows:- Lat & Long Format Conversions converts between DMS format, decimal degrees and degrees & decimal minutes. Helmert Transformation transforms the user entered cartesian coordinates to cartesian coordinates on a new datum using the transformation parameters from Constants. XYZ to lat,long,H converts the user entered cartesian coordinates to geodetic coordinates in DMS format using the ellipsoidal parameters from Constants. lat,long to E,N projects the user entered geodetic coordinates to easting and northings using a Transverse Mercator projection and the ellipsoid and projection parameters from Constants. E,N to lat,long is the reverse of lat,long to E,N. lat,long,H to XYZ is the reverse of XYZ to lat,long,H. UD Funcs Transformation Example shows how a complete Helmert transformation from WGS84 geodetic coordinates to OSGB36 Eastings and Northings and vice versa can be carried out using the user defined functions. convergence from lat & long computes the convergence at the geodetic coordinate entered by the user. convergence from E,N computes the convergence at the easting, northing entered by the user. local scale factor from lat,lon computes the local scale factor at the geodetic coordinate entered by the user. local & line sf from E,N computes the local scale factor at the easting, northing entered by the user and also the line scale factor between easting, northing and easting2, northing2. t-T correction from E,N computes the t-T (difference between true and projected directions) between easting, northing and easting2, northing2 entered by the user. true azimuth from E,N computes the true direction (after allowing for t-T and convergence) between easting, northing and easting2, northing2 entered by the user. Cells for user data entry are coloured Cells giving calculation results are coloured Cells showing intermediate calculation steps are coloured Cells which repeat the user inputted data are coloured Constants ELLIPSOID AND PROJECTION CONSTANTS ° ' " Dec Degs Rads Semi-major axis, a 6377563.396 True origin latitude, j0 N 49 0 0 49.00000000 0.855211333 Semi-minor axis, b 6356256.909 True origin longitude, l0 W 2 0 0 -2.00000000 -0.034906585 Central Meridan Scale, F0 0.999601271700 a for OSGB36 = 6377563.3960 True origin Easting, E0 400000.000 b for OSGB36 = 6356256.9090 True origin Northing, N0 -100000.000 a for GRS80 & WGS84 = 6378137.0000 aF0 6375020.48098897 b for GRS80 = 6356752.3141 Slight difference in b is due to different flattening values bF0 6353722.48948831 b for WGS84 = 6356752.3142 All other parameters same Projection Notes 1. The constants above are correct for working with the Ordnance Survey National Grid. If you want to work with OS National Grid, do not change anything in the above box. 2. It is also possible to use this spreadsheet to work with Universal Transverse Mercator (UTM) grids. The constants that must be entered in the yellow boxes if you want to work with UTM are given in Annex A of 'A Guide to coordinate systems in Great Britain', available from the OS GPS website www.gps.gov.uk. HELMERT TRANSFORMATION CONSTANTS ETRS89 (WGS84) to OSGB36 translation parallel to X -446.448 metres -446.448 translation parallel to Y 125.157 metres 125.157 translation parallel to Z -542.060 metres -542.060 scale change 20.4894 parts per million 20.4894 rotation about X -0.1502 seconds of arc -0.1502 rotation about Y -0.2470 seconds of arc -0.2470 rotation about Z -0.8421 seconds of arc -0.8421 reverse signs to go from OSGB36 to ETRS89 Transformation Notes 1. It is very important to understand the limitations of simple datum transformations. This method does not take account of the local distortions present in coordinate reference frames (TRFs) This is especially important when one or both of the coordinate systems involved is based on a traditional triangulation network For instance, transforming between WGS84 GPS coordinates and OSGB36 National Grid will incur transformation errors of up to five metres, depending on location. Also, this method cannot convert ellipsoid heights to heights above mean sea level. For full details on the limitations of the method coded here, see the OS publication 'A guide to coordinate systems in Great Britain', available free from our GPS Website (www.gps.gov.uk) or OS customer helpline. 2. The constants in the table above are suitable for transforming from WGS84 GPS coordinates to OSGB36 National Grid. By changing the constants above, this spreadsheet may be used to convert between any two geodetic datums Some ellipsoid constants are given in 'A guide to coordinate systems in Great Britain' Ordnance Survey cannot supply suitable transformation parameters for the geodetic systems of other countries - please make enquiries for these parameters to the national mapping agencies of the country concerned. Enter coordinates here Enter your coordinates in the yellow boxes ° ' " Decimal Deg Radians latitude N 52 39 27.253085 52.65757030139 0.9190479779 Enter either latitude and longitude longitude E 1 43 4.517692 1.7179215810 0.0299833879 Ellipsoidal height 24.700 metres Ellipsoidal height is optional (see notes) Easting 651409.903 metres or easting and northing Northing 313177.270 metres Easting 2 626238.249 metres 2nd pair of coordinates is optional (see notes) Northing 2 302646.415 metres Cartesian X 3909833.018 metres or Earth centred Cartesian XYZ coordinates Cartesian Y -147097.138 metres Cartesian Z 5020322.478 metres Notes 1. Type your input data in the yellow boxes only on this page. Enter either Latitude, Longitude and optional Ellipsoidal height, Easting and Northing or Earth centred Cartesian coordinates. To convert from decimal latitude and longitude or degrees lat / long and decimal minutes to the degrees, minutes and seconds format see the "Lat & Long Format Conversions " sheet. Ellipsoidal height is only required for the Cartesian XYZ coordiante conversions. The Earth centred Cartesian coordinates are used as input to the Helmert datum transformation calculations. Look at the following sheets to see the results of the calculations. 2. Check that the appropriate parameters are set on the "Constants" sheet. 3. For two of the calculations (t-T correction and true azimuth), the eastings and northings of two points are required. For these calculations only, you must enter Eastings 2 and Northings 2 in addition to Easting and Northing 4. For background information on the calculations contained in this spreadsheet, download 'A Guide to Coordinate Systems in Great Britain' from our GPS website www.gps.gov.uk, or see any textbook on map projections or surveying computations. Lat & Long Format Conversions Latitude & Longitude Format Conversions (Enter your coordinates in the yellow boxes) Degrees, Minutes & Seconds to Decimal Degrees and Degrees & Decimal Minutes Input Data Output Data Degrees (°) Minutes (') Seconds (") Decimal Degrees Degrees & Decimal Minutes latitude N 52 39 27.253100 52.657570305556 N 52 39.454218333 longitude E 1 43 4.517700 1.717921583333 E 1 43.075295000 Decimal Degrees to Degrees, Minutes & Seconds and Degrees & Decimal Minutes Output Data Input Data Output Data Degrees (°) Minutes (') Seconds (") Decimal Degrees Degrees & Decimal Minutes latitude N 52 39 27.253100 52.657570305556 N 52 39.454218333 longitude E 1 43 4.517700 1.717921583333 E 1 43.075295000 Degrees & Decimal Minutes to Degrees, Minutes & Seconds and Decimal Degrees Output Data Input Data Degrees (°) Minutes (') Seconds (") Decimal Degrees Degrees & Decimal Minutes latitude N 52 39 27.253100 52.657570305556 N 52 39.454218333 longitude E 1 43 4.517700 1.717921583333 E 1 43.075295000 Helmert Transformation Helmert Datum Transformation X 3909833.018 This is the input data Y -147097.1376 Z 5020322.4777 X 3909460.068 This is the result of the calculation Y -146987.301 Z 5019888.070 Rotation and Scale Matrix (H) These values are intermediate calculation steps for the 1.0000204894 4.08261601E-06 -1.19748979E-06 formulae given in 'A Guide to Coordinate Systems in Great Britain' -4.08261601E-06 1.0000204894 7.28190149E-07 1.19748979E-06 -7.28190149E-07 1.0000204894 See the Transformation Notes on the "Constants" page for information on the Translation Matrix (T) Parameters (see "Constants" sheet) limitations and accuracy of this transformation. -446.448 translation parallel to X -446.448 m 125.157 translation parallel to Y 125.157 m -542.06 translation parallel to Z -542.060 m scale change 20.4894 ppm H * Input Cartesian coords rotation about X -0.1502 secs 3909906.515806589 rotation about Y -0.2470 secs -147112.4581295882 rotation about Z -0.8421 secs 5020430.130195189 X 3909460.068 This is the result of the calculation using user defined functions. Y -146987.301 To see the Visual Basic code of the functions - Z 5019888.070 Menu = Tools, Macro, Visual Basic Editor, then view the code in "Module 1". XYZ to lat,long,H Cartesian XYZ to Latitude, longitude and Ellipsoidal height X 3909833.018 This is the input data Y -147097.1376 Z 5020322.4777 ° ' " Decimal Deg Radians latitude N 52 15 16.606464 52.25461290673 0.9120150446 This is the result of the calculation longitude W 2 9 16.510138 -2.154586149489 -0.0376046223 Ellipsoidal height 626.295 m p 3912599.110 These values are intermediate calculation steps for the e2 6.6705400741E-03 formulae given in 'A Guide to Coordinate Systems in Great Britain' Initial j N 52 15 16.6722 52.2546311564 0.9120153631 The radians value of latitude is computed using a user defined function n 6.3909051552E+06 To see the Visual Basic code of the function - Menu = Tools, Macro, Visual Basic Editor, then view the code in "Module 1". latitude N 52 15 16.606464 52.25461290673 This is the result of the calculation using user defined functions. longitude W 2 9 16.510138 -2.154586149489 To see the Visual Basic code of the functions - Ellipsoidal height 626.295 m Menu = Tools, Macro, Visual Basic Editor, then view the code in "Module 1". lat,long to E,N Latitude and longitude to easting and northing latitude N 52 39 27.253085 This is the input data longitude E 1 43 4.517692 Easting 651409.903 m This is the result of the calculation Northing 313177.270 m e2 6.67054007E-03 These values are intermediate calculation steps for the n 1.67322033E-03 formulae given in 'A Guide to Coordinate Systems in Great Britain' n 6.38850233E+06 r 6.37275644E+06 The value of M is computed using a user defined function h2 2.47081373E-03 To see the Visual Basic code of the function - P 6.48899729E-02 Menu = Tools, Macro, Visual Basic Editor, M 4.06688295E+05 then view the code in "Module 1". I 3.06688295E+05 II 1.54040791E+06 III 1.56068754E+05 IIIA -2.06711230E+04 IV 3.87512058E+06 V -1.70000782E+05 VI -1.01344704E+05 Easting 651409.903 m This is the result of the calculation using user defined functions. Northing 313177.270 m To see the Visual Basic code of the functions - Menu = Tools, Macro, Visual Basic Editor, then view the code in "Module 1". E,N to lat,long Easting and Northing to Latitude and Longitude Easting 651409.903 This is the input data Northing 313177.270 ° ' " Decimal Deg Radians latitude N 52 39 27.253087 52.65757030193 0.91904797787 This is the result of the calculation longitude E 1 43 4.517690 1.71792158065 0.02998338787 j' N 52 42 57.278445 52.7159106793 0.92006620954 These values are intermediate calculation steps for the e2 6.67054007E-03 formulae given in 'A Guide to Coordinate Systems in Great Britain' n 6.38852334E+06 r 6.37281931E+06 The radians value of j' and the value of M are computed using user defined functions h2 2.46422064E-03 To see the Visual Basic code of the functions - M 4.13177270E+05 Menu = Tools, Macro, Visual Basic Editor, Et 2.51409903E+05 then view the code in "Module 1". VII 1.61305625E-14 VIII 3.33955474E-28 IX 9.41985617E-42 X 2.58400625E-07 XI 4.69859700E-21 XII 1.61243166E-34 XIIA 6.65773163E-48 latitude N 52 39 27.253087 52.65757030193 This is the result of the calculation using user defined functions. longitude E 1 43 4.517690 1.71792158065 To see the Visual Basic code of the functions - Menu = Tools, Macro, Visual Basic Editor, then view the code in "Module 1". lat,long,H to XYZ Latitude, longitude and Ellipsoidal height to Cartesian XYZ latitude N 52 39 27.253085 This is the input data longitude E 1 43 4.517692 Ellipsoidal height 24.700 m X 3874938.850 m This is the result of the calculation Y 116218.624 m Z 5047168.207 m e2 6.6705400741E-03 These values are intermediate calculation steps for the n 6.3910506267E+06 formulae given in 'A Guide to Coordinate Systems in Great Britain' X 3874938.850 m This is the result of the calculation using user defined functions. Y 116218.624 m To see the Visual Basic code of the functions - Z 5047168.207 m Menu = Tools, Macro, Visual Basic Editor, then view the code in "Module 1". UD Funcs Tranformation Example This sheet gives an example of how the user defined functions stored in this workbook can be used. To see the Visual Basic code of the functions, menu = Tools, Macro, Visual Basic Editor, then view the code in "Module 1". A WGS84 lat, long and height (e.g. from a handheld GPS receiver) is converted and transformed in stages to an OSGB36 easting, northing and approx ODN height. The reverse transformation from OSGB36 to WGS84 is then performed. The results of each stage are all produced by user defined functions in single cells (i.e. there are no intermediate steps done in other cells). The outputs of the previous stage are passed as inputs to the next stage. Other parameters used are on the right of each stage. Note : be aware when changing the layout of this sheet that all cell references are ABSOLUTE. This is so that function inputs can be easily identified. WGS 84 to OSGB36 WGS84 Latitude, longitude and Ellipsoidal height to WGS84 Cartesian XYZ ° ' " Dec Degs User inputs DMS and Height. Decimal degrees and latitude N 52 39 28.723003 52.65797861194 Input height are the input to the user defined functions. longitude E 1 42 57.787253 1.71605201472 The DMS values are converted to decimal using Ellipsoidal height 69.391 m standard Excel functions X 3875311.465 m Output Ellipsoid parameters (WGS84) Y 116103.234 m a = 6378137.000 Z 5047602.291 m b = 6356752.314 Helmert Datum Transformation (WGS84 to OSGB36) Transformation (WGS84 to OSGB36) X 3875311.465 m Input translation parallel to X = -446.448 Y 116103.234 m See the Transformation Notes on the translation parallel to Y = 125.157 Z 5047602.291 m "Constants" page for information on the translation parallel to Z = -542.060 X 3874938.849 m Output limitations and accuracy of this scale change = 20.4894 Y 116218.624 m transformation. rotation about X = -0.1502 Z 5047168.210 m rotation about Y = -0.2470 rotation about Z = -0.8421 OSGB36 Cartesian XYZ to OSGB36 Latitude, longitude and approx ODN height The decimal degrees and height are the output of X 3874938.849 m Input the user defined functions. Y 116218.624 m The decimal values are converted to DMS using Z 5047168.210 m standard Excel functions ° ' " Dec Degs Ellipsoid parameters (OSGB36) latitude N 52 39 27.253135 52.657570315182 Output a = 6377563.396 longitude E 1 43 4.517710 1.717921586082 b = 6356256.910 ~ODN height 24.701 m OSGB36 Latitude and longitude to OSGB36 easting and northing ° ' " Dec Degs The decimal degrees are the input to the latitude N 52 39 27.253135 52.657570315182 Input user defined functions. longitude E 1 43 4.517710 1.717921586082 The decimal values are converted to DMS using Easting 651409.903 m Output standard Excel functions Northing 313177.271 m Ellipsoid and projection parameters (OSGB36) a = 6377563.396 b = 6356256.910 f0 = 0.9996012717 e0 = 400000 n0 = -100000 j0 = 49.00000 l0 = -2.00000 OSGB36 to WGS84 OSGB36 easting and northing to OSGB36 latitude and longitude The eastings, northings and approx ODN height are easting 651409.903 m Input input by the user. User defined functions convert northing 313177.270 m them to dec degrees. The decimal degrees are ° ' " Dec Degs converted to DMS using standard Excel functions latitude N 52 39 27.253095 52.657570304133 Output Ellipsoid and projection parameters (OSGB36) longitude E 1 43 4.517699 1.717921583102 a = 6377563.396 ~ODN height 24.700 m b = 6356256.910 f0 = 0.9996012717 e0 = 400000 n0 = -100000 j0 = 49.00000 l0 = -2.00000 OSGB36 latitude, longitude and approx ODN height to OSGB36 cartesian XYZ ° ' " Dec Degs Decimal degrees and height are the input to the latitude N 52 39 27.253095 52.65757030413 Input user defined functions. longitude E 1 43 4.517699 1.71792158310 The DMS values are converted to decimal using ~ODN height 24.700 m standard Excel functions X 3874938.849 m Output Ellipsoid parameters (OSGB36) Y 116218.624 m a = 6377563.396 Z 5047168.208 m b = 6356256.910 Helmert Datum Transformation (OSGB36 to WGS84) Transformation (OSGB36 to WGS84) X 3874938.849 m Input translation parallel to X = 446.448 Y 116218.624 m See the Transformation Notes on the translation parallel to Y = -125.157 Z 5047168.208 m "Constants" page for information on the translation parallel to Z = 542.060 X 3875311.472 m Output limitations and accuracy of this scale change = -20.4894 Y 116103.230 m transformation. rotation about X = 0.1502 Z 5047602.299 m rotation about Y = 0.2470 rotation about Z = 0.8421 WGS84 Cartesian XYZ to WGS84 Latitude, longitude and Ellipsoidal height The decimal degrees and height are the output of X 3875311.472 m Input the user defined functions. Y 116103.230 m The decimal values are converted to DMS using Z 5047602.299 m standard Excel functions ° ' " Dec Degs Ellipsoid parameters (WGS84) latitude N 52 39 28.722980 52.657978605604 Output a = 6378137.000 longitude E 1 42 57.787042 1.716051956076 b = 6356752.314 Ellipsoidal height 69.402 m convergence from lat & long Grid convergence from Latitude and Longitude latitude N 52 39 27.253085 This is the input data longitude E 1 43 4.517692 ° ' " Decimal Deg Radians convergence 2 57 26.556114 2.95737669840 0.0516159606 This is the result of the calculation n 6.38850233E+06 These values are intermediate calculation steps r 6.37275644E+06 h2 2.47081373E-03 XIII 7.95024505E-01 XIV 9.82300034E-02 XV 2.02438381E-03 convergence 2 57 26.556114 2.95737669840 This is the result of the calculation using a user defined function. To see the Visual Basic code of the function - Menu = Tools, Macro, Visual Basic Editor, then view the code in "Module 1". Notes Grid convergence is the angle FROM the meridian line through a point TO the North-South grid line at the same point. A positive angle is clockwise. convergence from E,N Grid convergence from Easting and Northing Easting 651409.903 This is the input data Northing 313177.270 ° ' " Decimal Deg Radians convergence 2 57 26.556147 2.9573767074 0.05161596077 This is the result of the calculation j' 52 42 57.278445 52.7159106793 0.92006620954 These values are intermediate calculation steps n 6.38852334E+06 r 6.37281931E+06 The radians value of j' is computed using a user defined function h2 2.46422064E-03 To see the Visual Basic code of the function - Et 2.51409903E+05 Menu = Tools, Macro, Visual Basic Editor, XVI 2.05594320E-07 then view the code in "Module 1". XVII 4.57174659E-21 XVIII 1.60898687E-34 convergence 2 57 26.556147 2.957376707 This is the result of the calculation using a user defined function. To see the Visual Basic code of the function - Menu = Tools, Macro, Visual Basic Editor, then view the code in "Module 1". Notes Grid convergence is the angle FROM the meridian line through a point TO the North-South grid line at the same point. A positive angle is clockwise. local scale factor from lat,lon Local scale factor from Latitude and Longitude latitude N 52 39 27.253085 This is the input data longitude E 1 43 4.517692 factor 1.00037733 This is the result of the calculation n 6.38850233E+06 These values are intermediate calculation steps r 6.37275644E+06 h2 2.47081373E-03 XIX 1.84422569E-01 XX -1.03620198E-02 factor 1.00037733 This is the result of the calculation using a user defined function. To see the Visual Basic code of the function - Menu = Tools, Macro, Visual Basic Editor, then view the code in "Module 1". Notes Local scale factor is the scale distortion inherent in the map projection at a point local & line sf from E,N Local scale factor from Easting and Northing Easting 651409.903 This is the input data Northing 313177.270 factor 1.00037732 This is the result of the calculation ° ' " Decimal Deg Radians These values are intermediate calculation steps j' 52 42 57.278445 52.715910679 0.9200662095 n 6.38852334E+06 The radians value of j' is computed using a user defined function r 6.37281931E+06 To see the Visual Basic code of the function - h2 2.46422064E-03 Menu = Tools, Macro, Visual Basic Editor, XXI 1.22811183E-14 then view the code in "Module 1". XXII 2.53854231E-29 factor 1.00037732 This is the result of the calculation using a user defined function. To see the Visual Basic code of the function - Menu = Tools, Macro, Visual Basic Editor, then view the code in "Module 1". Notes Local scale factor is the scale distortion inherent in the map projection at a point For a long line the scale factor at the mid point should be calculated. For lines up to 30km in length this will give a result with an error not exceeding 1 to 2 ppm. If still greater accuracy is required compute a line scale factor using Simpson's Rule as shown below. The line scale factor between Point 1 (East1, North1) and Point 2 (East2, North2) is the projected distance between the points divided by the true (geodesic) distance. Line scale factor = (1/6)(F1 + 4Fm + F2) Where F1 = scale factor at Point 1; F2 = scale factor at Point 2 and Fm = scale factor at mid point between Point 1 and Point 2. e.g.: - 1.00037732 = Scale factor at Point 1 (East1,North1) 1.00022969 = Scale factor at Point 2 (East2,North2) 1.0003015598 = Scale factor at mid point of Point 1 (East1,North1) and Point 2 (East2,North2) 1.00030221 = Line scale factor for line between Point 1 (East1,North1) and Point 2 (East2,North2) t-T correction from E,N t-T correction from Eastings and Northings Easting 651409.903 This is the input data Northing 313177.270 Easting 2 626238.249 Northing 2 302646.415 ° ' " Decimal Deg Radians (t1 - T1) 0 0 6.482943 0.00180081737 0.00003143019240 This is the result of the calculation (t2 - T2) -0 0 6.259111 -0.00173864187 -0.00003034502514 Nm 307911.8425 These values are intermediate calculation steps M 4.07911842E+05 j' 52 40 6.855172 52.668570881 0.9192399742 The radians value of j' and the value of M are computed using user defined functions n 6.38850630E+06 To see the Visual Basic code of the functions - r 6.37276830E+06 Menu = Tools, Macro, Visual Basic Editor, XXIII 4.09374978E-15 then view the code in "Module 1". (t1 - T1) 0 0 6.482943 0.00180081737 This is the result of the calculation using a user defined function. (t2 - T2) -0 0 6.259111 -0.00173864187 To see the Visual Basic code of the function - Menu = Tools, Macro, Visual Basic Editor, then view the code in "Module 1". Notes t-T correction is the difference between true direction and projected direction due to the curving of straight lines on the projection true azimuth from E,N True Azimuth from eastings and northings Easting 651409.903 This is the input data Northing 313177.270 Easting 2 626238.249 Northing 2 302646.415 ° ' " Decimal Deg Radians True Azimuth(1 to 2) 250 15 10.839515 250.253010976 4.367739005 This is the result of the calculation Grid Bearing(1 to 2) 247 17 50.766311 247.2974350863 4.316154474 These values are intermediate calculation steps C1 2 57 26.556147 2.9573767074 0.0516159608 (t1 - T1) 0 0 6.482943 0.001800817 0.000031430 True Azimuth(1 to 2) 250 15 10.839515 250.253010976 This is the result of the calculation using a user defined function. To see the Visual Basic code of the function - Menu = Tools, Macro, Visual Basic Editor, then view the code in "Module 1". Notes The true azimuth from one point (easting, northing) to a second point (easting 2, northing 2) is computed by applying the convergence and t-T corrections to the grid bearing.
Compartilhar