While the underlying index should be opaque to the user of a DBMS with spatial features, the API used to define spatial types and operate on them is of course more visible. The relevant standard in this space is often referred to as "OpenGIS", however the Open Geospatial Consortium in fact defines a long list of standards. The standard relevant to SQL databases is known more precisely as "OpenGIS Implementation Specification for Geographic information - Simple feature access - Part 2: SQL option" aka "Simple feature access".

It is not meaningful to recite the standard at length in my blog, my focus is instead on actual implementations that I will blog about later. The following points are however worth noting:

• Products typically support either or both of a Geometry space and Geography space. Shapes in the Geometry type are located on a 2D plane and referred to with (x,y) coordinates. (There can also be more dimensions, but only 2 are supported in practice.) Shapes defined in the Geography space live on a sphere and are refererenced with (latitude, longitude) coordinates.
• Given that the world is neither a 2D plane, nor a perfect sphere, the GIS standard defines the concept of a SRID = Spatial Reference system ID. In practice this shows up in implementations as a "magic constant" that refers to a well defined projection of the real world to the coordinate system in use. PostGIS functions will use this information to calculate distances with the correct projection. MySQL will silently ignore the SRID and always use Euclidean geometry.

The MariaDB developers have published a nice webcast that demonstrates the operations that are possible to do with good GIS functions: https://blog.mariadb.org/screencast-mariadb-gis-demo/

The SQLite tutorial is easy to approach yet comprehensive overview of the functionality provided by a good implementation of the OpenGIS standard: https://www.gaia-gis.it/gaia-sins/spatialite-tutorial-2.3.1.html

The Simple Feature Access GIS standard: https://www.opengeospatial.org/standards/sfs