Excel-based coordinate conversion method

Abstract

The invention relates to the technical field of coordinate conversion, in particular to an Excel-based coordinate conversion method, which comprises the following steps of: converting geographic coordinates into plane coordinates by adopting a Gaussian projection normal calculation method; the plane coordinates obtained through conversion are converted into geographic coordinates again through a Gaussian projection back calculation method; optimizing the Gaussian projection coordinate conversion precision by using a compensation arbitrary band Gaussian coordinate conversion method; and evaluating Gaussian projection conversion reliability based on precision verification. According to the invention, the requirement of high-precision coordinate conversion in engineering construction and measurement can be met.

Description

Technical Field

[0001] The present application relates to the technical field of coordinate transformation, and in particular to a coordinate transformation method based on Excel. Background Art

[0002] With the continuous development of geographic information technology, coordinate transformation technology plays an increasingly important role in surveying and mapping, map making and geographic information system (GIS). As a commonly used map projection method, Gaussian projection is widely used to accurately describe the earth's surface and geographic data. Especially in the field of engineering surveying and geographic data processing, Gaussian projection provides a scheme of conformal cross-section elliptical cylindrical projection, which converts the longitude and latitude in the geodetic coordinate system into plane coordinates, making the data easier to analyze and apply. Since it was proposed by German mathematician Gauss in the 1820s, this projection method has undergone many developments and improvements, especially Krüger's supplement to the projection formula, making Gaussian projection one of the indispensable technologies in the field of surveying and mapping today.

[0003] In the prior art, there are various coordinate conversion methods, and many professional software tools have been developed for high-precision coordinate conversion. For example, coordinate conversion software based on VB programming such as COORD and GeoTrans can convert between different coordinate systems, and are widely used in the conversion of coordinate systems such as Beijing 54, WGS84 and Xi'an 80. In addition, software such as AutoCAD and ArcGIS also provide coordinate conversion functions, which can realize the conversion from one coordinate system to another, and are widely used in engineering measurement and map making. ArcGIS software can help users perform map projection and coordinate system conversion through the projection transformation function in its toolbox, support a variety of common ellipsoid parameters and coordinate system settings, and facilitate users to perform spatial data analysis and processing.

[0004] Although existing technologies have been applied in many fields, these methods have certain limitations. For example, existing software tools have certain errors in high-precision projection conversion, especially in coordinate conversion in high-altitude areas, and lack support for current mainstream coordinate systems (such as CGCS2000). In addition, existing coordinate conversion tools are complex to operate when processing large-scale data, have a high error rate, and cannot flexibly respond to customized requirements for different projection parameters. Therefore, existing technologies cannot meet the needs of high-precision and high-efficiency coordinate conversion, especially in engineering projects and high-precision measurements, and cannot provide a simple and accurate coordinate conversion solution. Summary of the invention

[0005] This application provides a coordinate conversion method based on Excel, which can meet the needs of high-precision coordinate conversion in engineering construction and measurement. This application provides the following technical solutions:

[0006] In a first aspect, the present application provides a coordinate conversion method based on Excel, the method comprising:

[0007] The Gauss projection method is used to convert geographic coordinates into plane coordinates;

[0008] Use the Gauss projection inverse calculation method to convert the converted plane coordinates back into geographic coordinates;

[0009] Use the compensation arbitrary band Gaussian coordinate transformation method to optimize the accuracy of Gaussian projection coordinate transformation;

[0010] Evaluate the reliability of Gaussian projection transformation based on accuracy verification.

[0011] In a specific implementation scheme, the use of Gauss projection calculation method to convert geographic coordinates into plane coordinates includes:

[0012] Convert the Gauss projection coordinate forward calculation formula into a format suitable for programming;

[0013] Convert the conversion formula into Excel-usable code, and directly calculate the Gaussian projection coordinates in Excel.

[0014] In a specific implementation scheme, the use of Gauss projection calculation method to convert geographic coordinates into plane coordinates includes:

[0015] In the header parameter setting area, enter the following parameters: central meridian, ellipsoid parameters, north offset, and scale factor;

[0016] Enter the point name and the latitude and longitude of the point in the manual input area. The data format is degrees, minutes and seconds.

[0017] The Excel formula implements all the calculations required for Gaussian projection calculation. The implementation of the formula is directly mapped to the cell, and Excel automatically calculates the X and Y coordinates.

[0018] In a specific implementation scheme, the use of the Gaussian projection back calculation method to convert the converted plane coordinates back into geographic coordinates includes:

[0019] Convert the Gauss projection coordinate inverse calculation formula into a format suitable for programming;

[0020] Convert the conversion formula into Excel-usable code, and directly calculate the Gaussian projection coordinates in Excel.

[0021] In a specific implementation scheme, the use of the Gaussian projection back calculation method to convert the converted plane coordinates back into geographic coordinates includes:

[0022] Enter the necessary parameters in the header settings area: northing offset, easting offset, scale factor, central meridian longitude, and select ellipsoid parameters;

[0023] Enter the point name and plane coordinates X, Y values ​​in the manual input area;

[0024] The calculation area automatically applies the Gauss projection inverse calculation formula based on the input parameters and data, and gradually calculates the geographic coordinates.

[0025] In a specific implementation scheme, the use of the compensation arbitrary band Gaussian coordinate transformation method to optimize the Gaussian projection coordinate transformation accuracy includes:

[0026] Enter the necessary parameters, and enter the central meridian, ellipsoid parameters, projection height Hm, north offset, east offset, and scale factor in the header parameter setting area;

[0027] Enter the point name, latitude, longitude, and elevation in the manual area;

[0028] The X, Y values ​​and the projection deformation length value ΔS are calculated in the calculation area.

[0029] In a specific implementation manner, the calculating of the X, Y values ​​and the projected deformation length value ΔS in the calculation area includes:

[0030] The new ellipsoid parameters are calculated based on the ellipsoid expansion method approximate algorithm, and the geodetic height ΔH of the independent coordinate projection surface is used as the change of the major radius of the ellipsoid. The main calculation steps are as follows:

[0031] The major radius of the ellipsoid is adjusted as follows:

[0032] a 新 =a+Δa

[0033] Δa=ΔH

[0034] The new ellipsoid geographic coordinates are as follows:

[0035] B 独 =B+dB

[0036] L 独 =L-0

[0037]

[0038] In the formula, a is the major radius of the ellipsoid, e' is the second eccentricity of the ellipsoid, and Δa is the difference between the major semi-axes of the two ellipsoids;

[0039] Substitute the new ellipsoid parameters into the Gaussian direct calculation formula to calculate and obtain the plane coordinates;

[0040] Through discrete point sampling and error regression analysis, the error regression equation is used to correct the preliminary results, and the error compensation formula is established as follows:

[0041] ΔS=(-0.198+0.00173966Hm+0.00010328H) / 100.

[0042] In the second aspect, the present application provides a coordinate conversion system based on Excel, which adopts the following technical solution:

[0043] A coordinate conversion system based on Excel, comprising:

[0044] Gauss projection calculation module, used to convert geographic coordinates into plane coordinates using Gauss projection calculation method;

[0045] Gauss projection inversion module, used to convert the converted plane coordinates back into geographic coordinates using Gauss projection inversion method;

[0046] Compensated arbitrary band Gaussian coordinate conversion module, used to optimize the accuracy of Gaussian projection coordinate conversion by using the compensated arbitrary band Gaussian coordinate conversion method;

[0047] The accuracy verification module is used to evaluate the reliability of Gaussian projection transformation based on accuracy verification.

[0048] In a third aspect, the present application provides an electronic device, comprising a processor and a memory; a program is stored in the memory, and the program is loaded and executed by the processor to implement an Excel-based coordinate conversion method as described in the first aspect.

[0049] In a fourth aspect, the present application provides a computer-readable storage medium, wherein the storage medium stores a program, and when the program is executed by a processor, the program is used to implement a coordinate conversion method based on Excel as described in the first aspect.

[0050] In summary, the beneficial effects of this application include at least:

[0051] 1) By combining the ellipsoid expansion method approximation algorithm and error regression analysis, the present invention optimizes the accuracy of Gaussian projection coordinate conversion. In arbitrary band Gaussian coordinate conversion, especially for high altitude areas and special coordinate systems, the conversion error is accurately corrected by using elevation compensation and error regression formulas. The verification results show that the errors of Excel Gaussian projection forward and inverse calculation programs are both zero, and the accuracy can reach 0.1mm, which meets the requirements of high-precision coordinate conversion.

[0052] 2) The Gaussian projection coordinate conversion method is converted into an easy-to-operate Excel formula. Users only need to enter relevant parameters (such as ellipsoid parameters, coordinate values, etc.) in Excel to automatically convert coordinates without installing additional plug-ins (such as VBA, etc.). This design simplifies the operation process and improves work efficiency, and is particularly suitable for engineering sites and fast data processing applications.

[0053] 3) Supports a variety of commonly used ellipsoids (such as CGCS2000, WGS84, Beijing 54, Xi'an 80), and can be flexibly switched according to different coordinate system requirements. A new calculation column for projection deformation length ΔS is added to the program to intuitively display the projection deformation and help users clearly analyze the deformation effect in coordinate transformation. In addition, combined with discrete point sampling and regression analysis, the present invention can provide accurate coordinate transformation results under different projection bands and elevation conditions, overcoming the limitations of existing technologies in high-precision projection transformation and customization requirements.

[0054] By converting complex mathematical formulas into calculation processes suitable for Excel, users can quickly complete high-precision conversions between geographic coordinates and plane coordinates without installing additional plug-ins. The invention adopts an ellipsoid expansion approximation algorithm, and combines error regression analysis to correct the projection elevation and conversion errors, effectively solving the error problem caused by high-altitude areas and changes in projection parameters in arbitrary Gaussian coordinate conversion. At the same time, a new calculation column for the projection deformation length is added to the program to intuitively display the impact of projection deformation; angle data are input and output in degrees, minutes and seconds format to reduce the error rate of data entry. The verification results show that the conversion accuracy of this method reaches the range of 0.1mm to 1mm, which meets the needs of high-precision coordinate conversion in engineering construction and measurement, and effectively overcomes the shortcomings of the existing technology in high-altitude areas, customized parameter support and efficient batch processing, and significantly improves the conversion efficiency and engineering application effect.

[0055] The above description is only an overview of the technical solution of the present application. In order to more clearly understand the technical means of the present application and implement it according to the contents of the specification, the following is a detailed description of the preferred embodiments of the present application in conjunction with the accompanying drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0056] Figure 1 It is a flowchart of the coordinate conversion method based on Excel in the embodiment of the present application.

[0057] Figure 2 This is a diagram of the interface of the Gaussian projection forward calculation program in the embodiment of the present application.

[0058] Figure 3 This is a diagram of the interface of the Gaussian projection backcalculation program in the embodiment of the present application.

[0059] Figure 4This is a diagram of the interface of the program for compensating arbitrary band Gaussian coordinate conversion according to an embodiment of the present application.

[0060] Figure 5 It is a structural block diagram of the coordinate conversion system based on Excel in the embodiment of the present application.

[0061] Figure 6 It is a block diagram of an electronic device for coordinate conversion based on Excel in an embodiment of the present application. DETAILED DESCRIPTION

[0062] The specific implementation methods of the present application are further described in detail below in conjunction with the accompanying drawings and examples. The following examples are used to illustrate the present application but are not intended to limit the scope of the present application.

[0063] Optionally, the present application uses the Excel-based coordinate conversion method provided in each embodiment as an example for use in an electronic device, where the electronic device is a terminal or a server. The terminal may be a mobile phone, a computer, a tablet computer, etc. This embodiment does not limit the type of electronic device.

[0064] Reference Figure 1 , is a flow chart of a coordinate conversion method based on Excel provided by an embodiment of the present application, the method comprising at least the following steps:

[0065] Step S101: Use Gauss projection calculation method to convert geographic coordinates into plane coordinates.

[0066] In step S101, Gauss projection calculation is based on a series of mathematical formulas, and the corresponding plane coordinates are calculated through the specified ellipsoid parameters and projection coordinate system. Specifically, the Gauss projection coordinate calculation formula is converted into a format suitable for programming as follows:

[0067]

[0068] in:

[0069] FE-easting deviation, generally FE=500000m;

[0070] FN-North Shift, which is generally 0 in the Northern Hemisphere and FN=10000000m in the Southern Hemisphere;

[0071] k0-projection scale factor, Gaussian projection k0=1, UTM projection k0=0.9996;

[0072] a- major semi-axis of the reference ellipsoid (m);

[0073] f-reference ellipsoid flattening;

[0074] T=tan 2 B;

[0075] C=e' 2 *cos 2 B;

[0076] A = (L-L0)*cosB;

[0077]

[0078] M=a*[k1*B+k2*sin(2B)+k3*sin(4B)+k4*sin(6B)];

[0079] M0=a*[k1*B0+k2*sin(2B0)+k3*sin(4B0)+k4*sin(6B0)];

[0080] e-the first eccentricity of the reference ellipsoid;

[0081] e'-the first eccentricity of the reference ellipsoid;

[0082] B-calculate the latitude of the point (radians);

[0083] L calculates the longitude of the point (radians);

[0084] B0 - latitude of origin (radians);

[0085] L0 - central meridian longitude (radians);

[0086]

[0087] The core of this step is to convert the above formula into Excel usable code, so that users can directly calculate Gaussian projection coordinates in Excel. The design process is as follows:

[0088] Header parameter setting area: In this area, users need to enter the following parameters:

[0089] Central Meridian: Used to set the central meridian of the projection.

[0090] Ellipsoid parameter selection: Select the ellipsoid parameters through the drop-down menu, such as CGCS2000, WGS84, Beijing 54 or Xi'an 80 coordinate system.

[0091] False Northing (FN) and False Easting (FE): Usually, FE = 500000m, FN = 0 (Northern Hemisphere).

[0092] The proportionality factor k is usually 1.

[0093] Manual Input Area: Users need to enter the point name and the latitude (B) and longitude (L) of the point in this area. The data format is degrees, minutes, and seconds (usually accurate to 0.00001″).

[0094] Calculation area: This area is automatically calculated by Excel for X and Y coordinates. The Excel formula implements all the calculations required for Gaussian projection calculation, as shown in Table 1. The specific steps and formula implementation are directly mapped to the cells, so that the user can automatically get the converted coordinate values ​​after entering the necessary parameters.

[0095] Input and Output: Input items: central meridian, ellipsoid parameters, northing, easting, projection scale factor, point name, latitude (B) and longitude (L). Output items: calculated X and Y coordinate values.

[0096] In the implementation, the Gauss projection calculation program interface is as follows Figure 2 As shown, the Gaussian projection forward calculation program cell code is shown in Table 1:

[0097] Table 1

[0098]

[0099]

[0100]

[0101] Step S102: Use the Gauss projection back calculation method to reconvert the converted plane coordinates into geographic coordinates.

[0102] Specifically, the Gauss projection coordinate forward calculation formula is converted into a format suitable for programming as follows:

[0103]

[0104] in:

[0105]

[0106] T f =tan 2 B f ;

[0107] C f =e' 2 *cos 2 B f ;

[0108]

[0109] In step S102, these plane coordinates and related ellipsoid parameters are used to convert the plane coordinates back to geographic coordinates using the Gauss projection inverse formula. The input parameters are the plane coordinates obtained from step S101. First, enter the necessary parameters in the header setting area: northing FN, easting FE (usually 500000), scale factor k (usually 1), central meridian longitude, and select ellipsoid parameters, such as CGCS2000, WGS84, Beijing 54, or Xi'an 80. Then enter the point name and plane coordinate X, Y values ​​in the manual input area, and automatically apply the Gauss projection inverse formula in the calculation area according to the input parameters and data to gradually calculate the geographic coordinates.

[0110] In practice, the interface of the Gaussian projection backcalculation program is shown in the figure below. Figure 3 As shown, the Gaussian projection back-calculation program cell code is shown in Table 2:

[0111] Table 2

[0112]

[0113]

[0114]

[0115] Step S103, using the compensation arbitrary band Gaussian coordinate transformation method to optimize the Gaussian projection coordinate transformation accuracy;

[0116] In step S103, an ellipsoid expansion approximation algorithm is used to solve the conversion error problem caused by the change of projection band parameters in arbitrary band Gaussian projection by introducing elevation compensation and error regression optimization.

[0117] In the implementation, first enter the necessary parameters, and enter in the header parameter setting area: central meridian: select the central meridian of the projection zone, ellipsoid parameters: pull down to select the ellipsoid type (CGCS2000 coordinate system ellipsoid, WGS84 coordinate system ellipsoid, Beijing 54 coordinate system ellipsoid or Xi'an 80 coordinate system ellipsoid), projection height Hm: used for error analysis and regression calculation, north offset FN, east offset FE (usually 500000), scale factor k (usually 1); then enter the point name, latitude B, longitude L (input in degrees, minutes, seconds format), and elevation Z in the manual area. Finally, the X, Y values ​​and projection deformation length value ΔS are calculated in the calculation area.

[0118] Specifically, the calculation process is as follows: First, the new ellipsoid parameters are calculated based on the ellipsoid expansion method approximation algorithm. The geodetic height ΔH of the independent coordinate projection surface is used as the change in the major radius of the ellipsoid. The main calculation steps are as follows:

[0119] The major radius of the ellipsoid is adjusted as follows:

[0120] a 新 =a+Δa

[0121] Δa=ΔH

[0122] The new ellipsoid geographic coordinates are as follows:

[0123] B 独 =B+dB

[0124] L 独 =L-0

[0125]

[0126] In the formula, a is the major radius of the ellipsoid, e' is the second eccentricity of the ellipsoid, Δa is the difference between the major semi-axes of the two ellipsoids; the meanings of other variables are consistent with the Gauss projection forward calculation formula in step S101.

[0127] Then the new ellipsoid parameters are brought into the Gaussian direct calculation formula to calculate and obtain the plane coordinates.

[0128] In addition, in order to further improve the accuracy, the error regression equation is used to correct the preliminary results through discrete point sampling and error regression analysis, and the error compensation formula is established as follows:

[0129] ΔS=(-0.198+0.00173966Hm+0.00010328H) / 100;

[0130] This formula combines the projection height Hm and elevation H to perform error compensation, making the conversion result more accurate.

[0131] In practice, the interface of the program for compensating arbitrary band Gaussian coordinate conversion is shown in the figure below. Figure 4 As shown, the program unit code for compensating arbitrary band Gaussian coordinate conversion is shown in Table 3:

[0132]

[0133]

[0134]

[0135] Step S104: Evaluate the reliability of Gaussian projection transformation based on accuracy verification.

[0136] In step S104, the accuracy of the Excel Gaussian projection conversion program is verified by actual data to evaluate the reliability of different conversion methods in different scenarios and ensure that the conversion results meet high-precision requirements.

[0137] Specifically, the coordinates of known points are first extracted from the existing 24 known control point data, including geographic coordinates (latitude B and longitude L) and plane coordinates (X, Y). Then the Gaussian projection forward calculation program, Gaussian projection inverse calculation program and arbitrary band Gaussian coordinate conversion program are verified respectively. The verification results show that the errors of the Excel Gaussian projection forward calculation program and the Excel Gaussian projection inverse calculation program are both 0, and the accuracy can reach 0.1mm and 0.000001″ respectively; the Excel Gaussian projection arbitrary band coordinate conversion program is used to convert the CGCS2000 coordinate system (central meridian longitude 90°; projection height 0m) into the Lhasa independent coordinate system (central meridian longitude 91°; projection height 3650m), and the maximum error is controlled within 1mm. The average projection deformation length after conversion is 1.1mm, which can meet the requirements of high-precision coordinate conversion. The errors of the forward calculation program and the inverse calculation program are both zero, which can meet the requirements of high-precision conversion; the maximum error and average projection deformation length of the arbitrary band coordinate conversion program are within the design range, meeting the actual application needs of the project.

[0138] The following is a parameter table of the technical solution of this application, as shown in Table 4, which is a commonly used ellipsoid parameter table.

[0139] Table 4

[0140]

[0141] As shown in Table 5, it is a statistical table of the error regression equation of the ellipsoid expansion method approximate algorithm.

[0142] Table 5

[0143]

[0144]

[0145]

[0146] Table 6 shows the normalized correction ΔS1 per kilometer of length.

[0147] Table 6

[0148] Hm / m 0 50 100 150 160 200 300 500 1000 2000 3000 <![CDATA[ΔS1 / cm]]> 0 -0.8 -1.6 -2.4 -2.5 -3.1 -4.7 -7.8 -15.7 -31.4 -47.1

[0149] Table 7 shows the projection correction ΔS2 per kilometer of length.

[0150] Table 7

[0151] ym / km 10 20 30 40 45 60 70 80 90 100 120 <![CDATA[ΔS2 / cm]]> 0.1 0.5 1.1 2.0 2.5 4.4 6.0 7.9 10.0 12.3 17.7

[0152] As shown in Table 8, it is a table of comprehensive deformation ΔS per kilometer length.

[0153] Table 8

[0154]

[0155] In summary, a high-precision method for Gaussian projection coordinate conversion based on Excel is proposed, which covers three modules: Gaussian forward calculation, inverse calculation and arbitrary zone coordinate conversion, and supports a variety of commonly used ellipsoid parameters (such as CGCS2000, WGS84, Beijing 54, Xi'an 80). By converting complex mathematical formulas into calculation processes suitable for Excel, users can quickly complete high-precision conversion between geographic coordinates and plane coordinates without installing additional plug-ins. The invention adopts the ellipsoid expansion method approximation algorithm, and combines error regression analysis to correct the projection elevation and conversion errors, effectively solving the error problem caused by high altitude areas and changes in projection parameters in arbitrary zone Gaussian coordinate conversion. At the same time, a new calculation column for projection deformation length is added to the program to intuitively display the impact of projection deformation; angle data are input and output in degree, minute, and second format to reduce the error rate of data entry. The verification results show that the conversion accuracy of this method reaches the range of 0.1mm to 1mm, which meets the needs of high-precision coordinate conversion in engineering construction and measurement, effectively overcomes the shortcomings of the existing technology in high altitude areas, customized parameter support and efficient batch processing, and significantly improves the conversion efficiency and engineering application effect.

[0156] Figure 5 This is a structural block diagram of an Excel-based coordinate conversion system provided by an embodiment of the present application. The system includes at least the following modules:

[0157] Gauss projection calculation module, used to convert geographic coordinates into plane coordinates using Gauss projection calculation method;

[0158] Gauss projection inversion module, used to convert the converted plane coordinates back into geographic coordinates using Gauss projection inversion method;

[0159] Compensated arbitrary band Gaussian coordinate conversion module, used to optimize the accuracy of Gaussian projection coordinate conversion by using the compensated arbitrary band Gaussian coordinate conversion method;

[0160] The accuracy verification module is used to evaluate the reliability of Gaussian projection transformation based on accuracy verification.

[0161] For relevant details, refer to the above method embodiment.

[0162] Figure 6 4 is a block diagram of an electronic device provided by an embodiment of the present application. The device at least includes a processor 401 and a memory 402.

[0163] The processor 401 may include one or more processing cores, such as a 4-core processor, an 8-core processor, etc. The processor 401 may be implemented in at least one hardware form of DSP (Digital Signal Processing), FPGA (Field-Programmable Gate Array), and PLA (Programmable Logic Array). The processor 401 may also include a main processor and a coprocessor. The main processor is a processor for processing data in the awake state, also known as a CPU (Central Processing Unit); the coprocessor is a low-power processor for processing data in the standby state. In some embodiments, the processor 401 may be integrated with a GPU (Graphics Processing Unit), which is responsible for rendering and drawing the content to be displayed on the display screen. In some embodiments, the processor 401 may also include an AI (Artificial Intelligence) processor, which is used to process computing operations related to machine learning.

[0164] The memory 402 may include one or more computer-readable storage media, which may be non-transitory. The memory 402 may also include a high-speed random access memory, and a non-volatile memory, such as one or more disk storage devices, flash memory storage devices. In some embodiments, the non-transitory computer-readable storage medium in the memory 402 is used to store at least one instruction, which is used to be executed by the processor 401 to implement the Excel-based coordinate conversion method provided in the method embodiment of the present application.

[0165] In some embodiments, the electronic device may further optionally include: a peripheral device interface and at least one peripheral device. The processor 401, the memory 402 and the peripheral device interface may be connected via a bus or a signal line. Each peripheral device may be connected to the peripheral device interface via a bus, a signal line or a circuit board. Schematically, the peripheral devices include but are not limited to: a radio frequency circuit, a touch display screen, an audio circuit, and a power supply.

[0166] Of course, the electronic device may also include fewer or more components, which is not limited in this embodiment.

[0167] Optionally, the present application also provides a computer-readable storage medium, in which a program is stored, and the program is loaded and executed by a processor to implement the Excel-based coordinate conversion method of the above method embodiment.

[0168] Optionally, the present application also provides a computer product, which includes a computer-readable storage medium, in which a program is stored, and the program is loaded and executed by a processor to implement the Excel-based coordinate conversion method of the above method embodiment.

[0169] The technical features of the above embodiments may be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0170] The above embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the invention patent. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the patent of the present application shall be subject to the attached claims.

Claims

1. A coordinate conversion method based on Excel, characterized in that: The method comprises: The Gauss projection method is used to convert geographic coordinates into plane coordinates; Use the Gauss projection inverse calculation method to convert the converted plane coordinates back into geographic coordinates; Use the compensation arbitrary band Gaussian coordinate transformation method to optimize the accuracy of Gaussian projection coordinate transformation; Evaluate the reliability of Gaussian projection transformation based on accuracy verification.

2. The coordinate conversion method based on Excel according to claim 1, characterized in that: The Gauss projection method is used to convert geographic coordinates into plane coordinates, including: Convert the Gauss projection coordinate forward calculation formula into a format suitable for programming; Convert the conversion formula into Excel-usable code, and directly calculate the Gaussian projection coordinates in Excel.

3. The coordinate conversion method based on Excel according to claim 2, characterized in that: The Gauss projection method is used to convert geographic coordinates into plane coordinates, including: In the header parameter setting area, enter the following parameters: central meridian, ellipsoid parameters, north offset, and scale factor; Enter the point name and the latitude and longitude of the point in the manual input area. The data format is degrees, minutes and seconds. The Excel formula implements all the calculations required for Gaussian projection calculation. The implementation of the formula is directly mapped to the cell, and Excel automatically calculates the X and Y coordinates.

4. The coordinate conversion method based on Excel according to claim 1, characterized in that: The method of using the Gauss projection back calculation method to convert the converted plane coordinates back into geographic coordinates includes: Convert the Gauss projection coordinate inverse calculation formula into a format suitable for programming; Convert the conversion formula into Excel-usable code, and directly calculate the Gaussian projection coordinates in Excel.

5. The coordinate conversion method based on Excel according to claim 4, characterized in that: The method of using the Gauss projection back calculation method to convert the converted plane coordinates back into geographic coordinates includes: Enter the necessary parameters in the header settings area: northing offset, easting offset, scale factor, central meridian longitude, and select ellipsoid parameters; Enter the point name and plane coordinates X, Y values ​​in the manual input area; The calculation area automatically applies the Gauss projection inverse calculation formula based on the input parameters and data, and gradually calculates the geographic coordinates.

6. The coordinate conversion method based on Excel according to claim 1, characterized in that: The method of using the compensation arbitrary band Gaussian coordinate transformation method to optimize the Gaussian projection coordinate transformation accuracy includes: Enter the necessary parameters, and enter the central meridian, ellipsoid parameters, projection height Hm, north offset, east offset, and scale factor in the header parameter setting area; Enter the point name, latitude, longitude, and elevation in the manual area; The X, Y values ​​and the projection deformation length value ΔS are calculated in the calculation area.

7. The coordinate conversion method based on Excel according to claim 6, characterized in that: The X, Y values ​​and the projection deformation length value ΔS calculated in the calculation area include: The new ellipsoid parameters are calculated based on the ellipsoid expansion method approximate algorithm, and the geodetic height ΔH of the independent coordinate projection surface is used as the change of the major radius of the ellipsoid. The main calculation steps are as follows: The major radius of the ellipsoid is adjusted as follows: in 新 =a+Δa Δa=ΔH The new ellipsoid geographic coordinates are as follows: B 独 =B+dB L 独 =L-0 In the formula, a is the major radius of the ellipsoid, e' is the second eccentricity of the ellipsoid, and Δa is the difference between the major semi-axes of the two ellipsoids; Substitute the new ellipsoid parameters into the Gaussian direct calculation formula to calculate and obtain the plane coordinates; Through discrete point sampling and error regression analysis, the error regression equation is used to correct the preliminary results, and the error compensation formula is established as follows: ΔS=(-0.198+0.00173966Hm+0.00010328H) / 100.

8. A coordinate transformation system based on Excel, characterized in that: include: Gauss projection calculation module, used to convert geographic coordinates into plane coordinates using Gauss projection calculation method; Gauss projection inversion module, used to convert the converted plane coordinates back into geographic coordinates using Gauss projection inversion method; Compensated arbitrary band Gaussian coordinate conversion module, used to optimize the accuracy of Gaussian projection coordinate conversion by using the compensated arbitrary band Gaussian coordinate conversion method; The accuracy verification module is used to evaluate the reliability of Gaussian projection transformation based on accuracy verification.

9. An electronic device, characterized in that: The device comprises a processor and a memory; a program is stored in the memory, and the program is loaded and executed by the processor to implement the Excel-based coordinate conversion method as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that: The storage medium stores a program, and when the program is executed by the processor, it is used to implement the coordinate conversion method based on Excel as described in any one of claims 1 to 7.

Images