# Accurate Algorithms for Spatial Operations on the Spheroid in a Spatial Database Management System

^{1}

^{2}

^{*}

## Abstract

**:**

## 1. Introduction

- Operation A: Distance and azimuth (bearing) between two points.
- Operation B: Calculation of a second point, from a starting point, an azimuth and a distance.
- Operation C: Area calculation.
- Operation D: Line intersection.
- Operation E: Minimum distance from a point to a line.

- a limited, non-universal, area of use;
- scale distortions, leading to different scale factors for different points in the area;
- angle distortions: even for the case of conformal projections, that is those preserving angles, the angle preservation at every point A is fulfilled between the tangents to the original geodetic lines on the ellipsoid at A and the tangents to the projected lines at A, which are not straight lines on the map but curved lines. If the straight segments, or chords, joining line ends are considered, the original angle is not preserved;
- area distortions: even for the case of equal-area projections, the area of figures enclosed by geodetic lines is not preserved as the area in the map enclosed by the straight segments connecting the extreme points.

- To test the current geospatial solutions and check that they give approximate results when talking about ellipsoid calculations.
- To present two new algorithms to solve operations D and E accurately in a spatial database (PL/SQL) and with Java as a library, so that anyone can easily use it.
- To verify the results of the proposed algorithms (within the desired accuracy of 100 nm).
- To encourage the spatial analysis software vendors to implement the algorithms on the ellipsoid so that accurate results can be achieved.

## 2. Experiments

_{A},λ

_{A}), B(φ

_{B},λ

_{B}), C(φ

_{C},λ

_{C}) and D(φ

_{D},λ

_{D}): Case 1 A(54.0°,14.5°), B(54.2°,14.6°), C(54.1°,14.4°), D(54.0°,14.7°), distances AX and CX are 6.6 and 9.6 km; Case 2 A(52°,5°), B(51.4°,6°), C(51.5°,4.5°), D(52°,5.5°), distances AX and CX are 21.6 and 64.7 km; Case 3 A(42°,29°), B(39°,−77°), C(6°,0°), D(64°,−22°), distances AX and CX are 345 and 556 km; Case 4 A(35°,−92°), B(40°,52°), C(−8°,20°), D(49°,−95°), distances AX and CX are 2003 and 11,347 km. In the first example, the distance (average of AX and CX) is 8 km. For the second, third and fourth examples, the distances are 43, 450 and 6675 km, respectively. For each of the three geospatial software groups, namely geographic information systems (GIS), spatial database management systems (SDBMS) and geospatial libraries (APIs), we chose one of the most powerful solutions. Representing the first group, we will test ArcGIS by ESRI [19], which is a very well-known and powerful GIS Desktop solution, spread all over the world and used by many public governments. It is worth noting that none of the open-source GIS desktops (QGIS, GRASS, gvSIG, uDIG, etc.) implement operations D or E on the ellipsoid. The best known and most advanced open-source Spatial DBMS is, definitely, PostGIS [20] but it is discarded because it does not implement the D or E operations on the ellipsoid. We will use instead one of the most powerful spatial DBMS commercial solutions: the Oracle Spatial extension [21], which allows us to solve operations D and E on the ellipsoid. Regarding geospatial libraries using the ellipsoid in their algorithms we can find Google Earth Engine [22], which is one of the most powerful Geospatial API solutions, with many new features in recent years.

#### 2.1. Oracle Spatial

#### 2.2. Google Earth Engine

^{−12}degrees, which represent around 0.3 µm, attributable to machine precision.

#### 2.3. ArcGIS

#### 2.4. ArcGIS Plus Local Projections

#### 2.5. PostGIS Plus Local Projections

## 3. Validation of Results

#### 3.1. Tests

#### 3.1.1. Test A

_{AX}= α

_{AB}and α

_{CX}= α

_{CD}. The column deviation of Table 2 shows the value in arcseconds of Test A, Maximum (|α

_{AX}− α

_{AB}|, |α

_{CX}− α

_{CD}|), therefore, any value greater than zero means that point X does not lie on the geodesics exactly.

#### 3.1.2. Test B

_{AB}(Figure 3), between X′ and X″, greater than zero, where X″ is the projection of X′ on the geodesic AB.

_{AB}is the distance from the calculated point X′ to the geodesic AB. The same distance can be calculated from the point X′ the other geodesic CD and thus obtain a second deviation distance d

_{CD}. If both distances are zero, the X′ point is the exact intersection of the geodesics AB and CD.

_{AB}, d

_{CD}). The calculation is approximate (although quite accurate as it will be demonstrated in next section) because, obviously, we still do not know where the true point of the intersection is.

## 4. Exact Solution and Validation

_{AB}= α

_{AX}, and α

_{CD}= α

_{CX}. ST_Azimuth is the PostGIS solution to the inverse geodesic problem and it is using the Karney approach as well.

#### 4.1. Checking the Algorithm with Extended Precision

#### 4.2. Special Geodesic Intersection Cases

#### 4.3. Minimum Distance from Point to Geodesic

- Compute the distance s
_{AP}and azimuths α_{AP}and α_{AB}by means of the implementation of the inverse problem of geodesy. Obtain angle A as the difference of these azimuths. - Obtain an approximate value for distances s
_{PX}and s_{AX}by means of Equations (8) and (10) and compute the direct problem of geodesy from A with the distance s_{AX}and azimuth α_{AX}(which is the same as α_{AB}) in order to obtain a point X which will act as point A in the next iteration. - For subsequent iterations, go back to steps (1) and (2) to obtain first s
_{PX}but replace the formula to obtain s_{AX}by the following one, which has been obtained from the Napier pentagon for right-angle spherical triangles and unlike the one used in step (2) has no instabilities near the exact solution but a sharp convergence to it.

_{AP}/R equals π/2 (i.e., s

_{AP}of some 10,000 km), which is the reason we preferred to obtain first an approximate s

_{AP}by means of Equation (10), preventing this singularity to happen in subsequent iterations (for which s

_{AP}has small values).

_{XP}must differ in 90 degrees with respect to the azimuths α

_{XA}and α

_{XB,}and, in addition, α

_{AX}= α

_{AB}. Test A formula is: Maximum (|α

_{AX}− α

_{AB}|, |α

_{XP}− α

_{XA}± 90°|, |α

_{XP}− α

_{XB}± 90°|).

_{X′A}± 90° is applied from the point X′ plus a distance X′P (the distance X′X″ is close to zero), we will obtain a point P’’ that must coincide with the point P. If the distance PP″ is zero, it is guaranteed that the perpendicular to the geodesic through the point X crosses point P. Test B formula is: Maximum (X′X″, PP″), Figure 4. Table 5 shows the coordinates of the three examples used to test the algorithm whereas the column deviation of Table 6 shows the result of both tests.

## 5. Conclusions

- The accuracy obtained is higher than using local projections even considering very short distances.
- It allows a highly accurate spatial analysis even in large extensions of territory (national, continental or global).
- Worrying about choosing the best-fit projection for analyzing the data becomes unnecessary, since we do not need to use any projection.
- A good performance (considering the spatial analysis is on the ellipsoid) is achieved (20,000 geodesic intersections per second) due to a fast convergence process. The worst scenario took six iterations, if the final accuracy goal is µm instead of nm we can reduce the number of iterations by one or two. The proposed algorithm is fast enough to allow the migration of flat computational geometry libraries (JTS, GDAL, etc.) to computational geometries libraries on the ellipsoid. This way, a full spatial analysis software solution on the ellipsoid could be offered for the first time.

- Some of the most renowned spatial analysis software solutions rely on the use of auxiliary map projections to solve problems on the surface of the ellipsoid which may produce manifestly incorrect results. These auxiliary map projections must be abandoned altogether in favor of reliable algorithms for direct computation on the ellipsoid surface that produce accurate results irrespectively of the extension of the area of interest.
- In recent times: algorithms that yield results of an accuracy close to machine precision have been developed. This is the case of Karney’s implementation of the direct and inverse problems of geodesy [14] and Transverse Mercator formulas with an accuracy of a few nanometers [36], which are included in GeographicLib [13]. This is also the case of the algorithms for intersection of geodesics and minimum point-to-geodesic distance presented in this paper. Spatial analysis software solutions should incorporate these solutions in order to provide the user with the highest possible accuracy, that is, an accuracy close to machine precision.
- When performing geometric calculations on the ellipsoid surface, such as determination of the intersection of geodesics or minimum point-to-geodesic distance, suitable tests for validating the solution (such as the ones used in this paper) should be applied to ensure the degree of accuracy of the solution obtained.

## Author Contributions

## Funding

## Data Availability Statement

## Conflicts of Interest

## References

- Steiniger, S.; Hunter, A.J.S. The 2012 free and open source GIS software map-A guide to facilitate research, development, and adoption. Comput. Environ. Urban Syst.
**2013**, 39, 136–150. [Google Scholar] [CrossRef] - Meyer, T.H. Introduction to Geometrical and Physical Geodesy: Foundations of Geomatics; ESRI Press: Redlands, CA, USA, 2010. [Google Scholar]
- Torge, W. Geodesy; Walter de Gruyter: Berlin, Germany; New York, NY, USA, 2001. [Google Scholar]
- DMA Geodesy for the Layman; Defense Mapping Agency: Washington, DC, USA, 1983.
- NIMA Department of Defense World Geodetic System 1984; National Imagery and Mapping Agency Technical Report TR 8350.2, 3. ed.; National Imagery and Mapping Agency: Washington, DC, USA, 2000.
- Petit, G.; Luzum, B. IERS Conventions (2010). IERS Technical Note 36; Verlag des Bundesamts für Kartographie und Geodäsie: Frankfurt am Main, Germany, 2010. [Google Scholar]
- Altamimi, Z.; Rebischung, P.; Métivier, L.; Collilieux, X. ITRF2014: A new release of the International Terrestrial Reference Frame modeling non-linear station motions. J. Geophys. Res. Solid Earth
**2016**, 121, 6109–6131. [Google Scholar] [CrossRef] [Green Version] - ISO. Geographic Information-Geodetic References-Part 1: International Terrestrial Reference System (ITRS); 19161-1; ISO: Geneva, Switzerland, 2020. [Google Scholar]
- Burch, T. Data Collection of WGS 84 Information–Or Is It? GPS World. 2016. Available online: https://www.gpsworld.com/data-collection-of-wgs-84-information-or-is-it/ (accessed on 27 May 2021).
- Malys, S.; Wong, R.; True, S. The WGS 84 Terrestrial Reference Frame in 2016. In Proceedings of the Eleventh Meeting of the International Committee on GNSS, ICG-11, UNOOSA, Sochi, Russia, 6–11 November 2016. [Google Scholar]
- Quality Positioning Services, B.V. International Terrestrial Reference Frame 2014 (ITRF2014). 2020. Available online: https://confluence.qps.nl/qinsy/latest/en/international-terrestrial-reference-frame-2014-itrf2014-182618383.html (accessed on 27 May 2021).
- Jenny, B.; Šavrič, B.; Arnold, N.D.; Marston, B.E.; Preppernau, C.A. A Guide to Selecting Map Projections for World and Hemisphere Maps. In Choosing a Map Projection. Lecture Notes in Geoinformation and Cartography; Lapaine, M., Usery, E., Eds.; Springer: Cham, Switzerland, 2017; pp. 213–228. [Google Scholar] [CrossRef]
- Karney, C.F.F. GeographicLib 1.51. 2021. Available online: https://geographiclib.sourceforge.io/html/ (accessed on 16 January 2021).
- Karney, C.F.F. Algorithms for geodesics. J. Geod.
**2013**, 87, 43–55. [Google Scholar] [CrossRef] [Green Version] - PostGIS Geography Support Functions. 2021. Available online: https://postgis.net/docs/manual-3.1/PostGIS_Special_Functions_Index.html#PostGIS_GeographyFunctions (accessed on 28 February 2021).
- Kothuri, R.; Godfrind, A.; Beinat, E. Pro Oracle Spatial for Oracle Database 11 g; Apress: Berkeley, CA, USA, 2007. [Google Scholar]
- PostGIS ST_Area with GeographicLib. 2021. Available online: https://postgis.net/docs/ST_Area.html (accessed on 27 February 2021).
- Baselga, S.; Martínez-Llario, J.C. Intersection and point-to-line solutions for geodesics on the ellipsoid. Stud. Geophys. Geod.
**2018**, 62, 353–363. [Google Scholar] [CrossRef] [Green Version] - ESRI. ArcGIS Desktop. 2021. Available online: https://www.esri.com/ (accessed on 27 February 2021).
- PostGIS. 2021. Available online: https://postgis.net/ (accessed on 27 February 2021).
- Oracle. Oracle Spatial. 2021. Available online: https://docs.oracle.com/en/database/oracle/oracle-database/21/spatl (accessed on 27 February 2021).
- Google. Google Earth Engine. 2021. Available online: https://earthengine.google.com/ (accessed on 27 February 2021).
- Van Brummelen, G. Heavenly Mathematics: The Forgotten art of Spherical Trigonometry; Princeton University Press: Princeton, NJ, USA, 2013. [Google Scholar]
- Jayapalan, J. Coordinate System. Oracle Spatial Spatial Developer’s Guide, 21c. 2021. Available online: https://docs.oracle.com/en/database/oracle/oracle-database/21/spatl/spatial-concepts.html (accessed on 11 March 2021).
- Martinez-Llario, J.C.; Baselga, S. Geodesy Spatial Geometry Operators. 2021. Available online: https://figshare.com/s/58f3bf16ac8523a378e9 (accessed on 11 March 2021). it will be uploaded into GitHub for public use after approval of the manuscript.
- Google. Geodesic vs. Planar Geometries, Google Earth Engine. 2021. Available online: https://developers.google.com/earth-engine/geometries_planar_geodesic (accessed on 28 February 2021).
- Google. Geometric Operations, Google Earth Engine. 2021. Available online: https://developers.google.com/earth-engine/geometric_operations (accessed on 28 February 2021).
- ArcGIS Desktop. Creating Geodetic Features. 2020. Available online: https://desktop.arcgis.com/en/arcmap/latest/manage-data/creating-new-features/creating-geodetic-features.htm (accessed on 28 February 2021).
- ArcGIS Desktop. Spatial Reference and Geoprocessing. 2021. Available online: https://desktop.arcgis.com/en/arcmap/latest/tools/supplement/spatial-reference-and-geoprocessing.htm (accessed on 28 February 2021).
- PostGIS. ST_Intersection Best Fit Projection. 2021. Available online: https://postgis.net/docs/ST_Intersection.html/ (accessed on 28 February 2021).
- Sjöberg, L.E. New Solutions to Classical Geodetic Problems on the Ellipsoid. In Observing our Changing Earth; Sideris, M.G., Ed.; Springer: Berlin/Heidelberg, Germany, 2009; pp. 781–784. [Google Scholar]
- Sjöberg, L.E. Geodetic intersection on the ellipsoid. J. Geod.
**2008**, 82, 565–567. [Google Scholar] [CrossRef] - Sjöberg, L.E. Intersections on the sphere and ellipsoid. J. Geod.
**2002**. [Google Scholar] [CrossRef] - Goldberg, D. What Every Computer Scientist Should Know About Floating-Point Arithmetic. ACM Comput. Surv.
**1991**, 23, 5–48. [Google Scholar] [CrossRef] - Passmark Software. CPU Benchmarks. 2021. Available online: https://www.cpubenchmark.net/ (accessed on 17 March 2021).
- Karney, C.F.F. Transverse Mercator with an accuracy of a few nanometers. J. Geod.
**2011**, 85, 475–485. [Google Scholar] [CrossRef] [Green Version]

**Figure 3.**Distance deviation (d

_{AB}) from the geodesic AB. X exact intersection, X′ intersection point calculated by Oracle, Google, ArcGIS, etc., AX′ = Geodesic distance between A and X′, X″ projection of X′ on the geodesic AB.

**Table 1.**Point coordinates (latitude and longitude, in degrees) of the geodetic intersections AB-CD.

Case 1 | Case 2 | Case 3 | Case 4 | |
---|---|---|---|---|

Oracle 21c Enterprise ^{1} | 54°3′26.28372″ | 51°51′56.38240″ | 54°40′18.17727″ | 50°26′39.17191″ |

14°31′42.77221″ | 5°13′38.84516″ | −14°32′38.87404″ | −79°15′0.53507″ | |

Google EE ^{1} | 54°3′26.28372″ | 51°51′56.38240″ | 54°40′18.17727″ | 50°26′39.17191″ |

14°31′42.77221″ | 5°13′38.84516″ | −14°32′38.87404″ | −79°15′0.53507″ | |

ArcGIS Geodetic densify ^{1} | 54°3′26.12068″ | 51°51′56.30439″ | 54°43′1.24560″ | 50°28′44.64889″ |

14°31′43.06034″ | 5°13′38.81383″ | −14°33′50.01202″ | −79°16′58.15173″ | |

ArcGIS Geodetic densify + local projection ^{2} | 54°3′26.28418″ | 51°51′56.30657″ | 54°43′1.30662′ | 50°28′44.65060′ |

14°31′42.77421″ | 5°13′38.81450″ | −14°33′49.88149″ | −79°16′58.15211″ | |

PostGIS with local projection ^{2} | 54°3′26.28481″ | 51°51′56.39039″ | 40°53′7.05995″ | 36°1′21.18886 |

14°31′42.77452″ | 5°13′38.68764″ | −10°56′51.90552″ | −63°19′23.10184″ | |

Sphere ^{3} | 54°3′26.28372″ | 51°51′56.38240″ | 54°40′18.17727″ | 50°26′39.17191″ |

14°31′42.77221″ | 5°13′38.84516″ | −14°32′38.87404″ | −79°15′0.53507″ |

^{1}Software with computation on the ellipsoid.

^{2}Software with computation using Cartesian coordinates (local projection).

^{3}By using a sphere and great circles (not geodesics on the ellipsoid).

Case | Deviation | Oracle and Google and Sphere | ArcGIS Geodetic Densify | ArcGIS Geodetic Densify + Projection | PostGIS Geography with Local Projection |
---|---|---|---|---|---|

1 | Test | 0.57991 | 200.47001 | 1.20114 | 1.20015 |

2 | A | 2.83179 | 23.37880 | 22.83089 | 21.01461 |

3 | (arcsec) | −306.75159 | −0.13274 | −3.11587 | −11,408.23193 |

4 | 434.55065 | −0.10260 | −0.10084 | 187,542.96132 | |

1 | Test | 0.0272 | 6.4566 | 0.03871 | 0.03867 |

2 | B | 0.3093 | 2.4525 | 2.3951 | 2.20421 |

3 | (m) | 4887.9260 | 2.8386 | 0.0133 | 1,524,432.6228 |

4 | 4148.9669 | 3.0971 | 3.0440 | 2,227,566.4855 |

**Table 3.**Intersection of geodesics with an accuracy better than 100 nm using PostGIS through the STX_GeodesicIntersection new implemented SQL function. Values of latitude and longitude given in deg min sec as well as in decimal degrees. The column Ite is the number of iterations, the last column (Deviation) gives the computation of deviation with extended precision using the Maxima framework as explained in the next section.

Case | Latitude | Longitude | Ite | Deviation Test A (m) | Deviation Test B (Arcsec) |
---|---|---|---|---|---|

1 | 54°3′26.284713088″ 54.0573013091912° | 14°31′42.772259538″ 14.5285478498717° | 2 | 5.87 × 10^{−8} | 2.74 × 10^{−9} |

2 | 51°51′56.395444955″ 51.8656654013763° | 5°13′38.845612278″ 5.22745711452158° | 2 | 3.10 × 10^{−8} | 4.01 × 10^{−9} |

3 | 54°43′1.306592212″ 54.7170296089477° | −14°33′49.880679508″ −14.5638557443078° | 4 | 1.18 × 10^{−10} | 2.82 × 10^{−9} |

4 | 50°28′44.750808360″ 50.4790974467667° | −79°16′58.086071846″ −79.2828016866240° | 5 | 2.02 × 10^{−10} | 5.75 × 10^{−9} |

Case | Oracle ans | ArcGIS Geodetic Densify | ArcGIS Geodetic Densify + Local Projection | PostGIS Geography with Local Projection | The Proposed Algorithm: PostGIS Implementation |
---|---|---|---|---|---|

1 | 0.0307 | 7.2926 | 0.03912 | 0.04127 | 2.74 × 10^{−9} |

2 | 0.4032 | 2.8792 | 2.8107 | 3.02666 | 4.01 × 10^{−9} |

3 | 5202.0851 | 3.0142 | 0.0145 | 1,561,032.211 | 2.82 × 10^{−9} |

4 | 4520.2567 | 3.4048 | 3.3589 | 2,054,619.652 | 5.75 × 10^{−9} |

**Table 5.**Examples for the minimum distance tests. φ and λ denote latitude and longitude, in degrees, for points A, B and C, respectively.

Case | φ_{A} | λ_{A} | φ_{B} | λ_{B} | φ_{C} | λ_{C} | XP (km) | AX (km) | BX (km) |
---|---|---|---|---|---|---|---|---|---|

1 | 52 | 5 | 51.4 | 6 | 52 | 5.5 | 23 | 24 | 71 |

2 | 42 | 29 | 39 | −77 | 64 | −22 | 1010 | 3928 | 4411 |

3 | 42 | 29 | −35 | −70 | 64 | −22 | 3928 | 1012 | 12,166 |

**Table 6.**Minimum distance solution using PostGIS through the ST_GeodesicMinDistance new implemented SQL function. Values of latitude and longitude given in deg min sec as well as in decimal degrees. The column Ite is the number of iterations, the last column (Deviation) gives the computation of deviation with extended precision using the Maxima framework as explained in the next section.

Case | Latitude | Longitude | Ite | Deviation Test A (m) | Deviation Test B (Arcsec) |
---|---|---|---|---|---|

1 | 51°50′45.921217384″ 51.8460892270512° | 5°15′37.542581860″ 5.26042849496107° | 3 | 4.1 × 10^{−9} | 3.49 × 10^{−8} |

2 | 54°55′42.713389621″ | −21°56′14.247837776″ | 4 | 2.2 × 10^{−9} | 4.41 × 10^{−10} |

54.9285314971169° | −21.9372910660488° | ||||

3 | 37°58′41.223640784″ 37.9781176779955° | 18°20′56.627934277″ 18.3490633150769° | 4 | 6.0 × 10^{−10} | 5.59 × 10^{−11} |

Case | Oracle | Geocentric φ, λ Sphere | ArcGIS Geodetic Densify | PostGIS with Local Projection | |
---|---|---|---|---|---|

1 | −44.068 | 0.456 | −17.51 | 0.0015 | 0.403 |

2 | 3141.29 | 5029.43 | 4275.75 | 0.0103 | 5024.505 |

3 | −4914.61 | 1572.71 | −523.79 | 0.0082 | 1565.01 |

Publisher’s Note: MDPI stays neutral with regard to jurisdictional claims in published maps and institutional affiliations. |

© 2021 by the authors. Licensee MDPI, Basel, Switzerland. This article is an open access article distributed under the terms and conditions of the Creative Commons Attribution (CC BY) license (https://creativecommons.org/licenses/by/4.0/).

## Share and Cite

**MDPI and ACS Style**

Martínez-Llario, J.C.; Baselga, S.; Coll, E.
Accurate Algorithms for Spatial Operations on the Spheroid in a Spatial Database Management System. *Appl. Sci.* **2021**, *11*, 5129.
https://doi.org/10.3390/app11115129

**AMA Style**

Martínez-Llario JC, Baselga S, Coll E.
Accurate Algorithms for Spatial Operations on the Spheroid in a Spatial Database Management System. *Applied Sciences*. 2021; 11(11):5129.
https://doi.org/10.3390/app11115129

**Chicago/Turabian Style**

Martínez-Llario, José Carlos, Sergio Baselga, and Eloína Coll.
2021. "Accurate Algorithms for Spatial Operations on the Spheroid in a Spatial Database Management System" *Applied Sciences* 11, no. 11: 5129.
https://doi.org/10.3390/app11115129