storing altitude/height with MySQL geometry

Question

We are using MySQL geometry data type to store polygons, polylines, multi-polygons and multi-polylines.

We have come across the need to store depth/height data along with some of these features. My first thought was to store this data as altitude, but then on looking at the docs I found that MySQL/MariaDB does not support altitude. Porting to PostGIS is not really an option. (in hindsight, the whole project should have been written for PostGIS).

Is there a good way to store this data? my thought was to store it in meters, positive for above ground, negative for below.

One thought was to strip the altitude property from the GeoJSON before insert into the database into an identical array structure (did some testing and it is relatively easy to do, just requires a lot of recursive looping) then stringify the data to store as a string, then do the reverse of retrieval.

at this point we don't need to do any queries on this data (but they are famous last words) so this would work.

Then I got thinking, is the order of coordinates retrieved from MySQL always guaranteed to be in the same order? as if the order changes for any reason my data becomes invalid.

I had briefly thought of storing each point individually in a table but as we use a fair few geometry functions on the data, that seems like a very poor way to do it as we would have to build the data back into geometry on the fly to do this.

Is there a better way that I am missing? unfortunately each point can potentially be a different height, so we can't just have a column of double type

Answer 1

What operations will you do with the data? Especially the altitude?

If it is just extra info, simply have another column, altitude, of type FLOAT or DOUBLE, depending on the precision needed.

If you are computing distances in 3D or trying to do 3D "CONTAINS", then MySQL's SPATIAL won't help.

"... order of coordinates ..." -- You must handle this as you store the info.

Is JSON really needed? It gets in the way of many operations. If you don't need a Spatial index, then simply have 3 Float or Double columns.

"... storing each point individually ..." -- Again, what will you do with the points? If the only 'entity' you have is a 'point', the 3 columns, one row per point seems right. If multiple 'points' are defining a 'polygon', that is another matter.

One altitude per polygon

A single row per polygon -- one GEOMETRY column for the polygon, one FLOAT for altitude.

Each point has a different altitude

Now it gets messy. Is "CONTAINS" referring to the projection of the polygon on the surface (of the earth or a plane)? Or something else? You might need two tables -- one table with one row for each polygon stored as a GEOMETRY; the other table has one point per row, perhaps as 3 FLOATs.