Extract top, bottom, left, and right (as numeric values) from a polygon in MariaDB?

Question

Is there a way to extract the minimum and maximum x and y values separately from a polygon value in MariaDB (I'm using 10.5.8 with no extensions)?

Eg:

set @poly = ST_GeomFromText('POLYGON((1.2 1.2,2 1,2 0,1 0,1.2 1.2))');

What I want is to extract the top (max_y), bottom (min_y), left (min_x), and right (max_x) values separately. So for example here "top" is 1.2, "bottom" is 0, "left" is 1, and "right" is 2.

I can get a "minimum bounding rectangle" containing the polygon, which might help, but I don't know how to extract the individual X and Y coordinates:

select ST_Envelope(@poly);
# POLYGON ((1 0, 2 0, 2 1.2, 1 1.2, 1 0))

What I'd like is something like

select ST_MinX(@poly);
# 1

Answer 1

I found a way, which seems a little hacky to me, but does what I'm trying to achieve.

The logic here is:

1. polygon input
2. ST_Envelope to get a "minimum bounding box" for the polygon
3. ST_ExteriorRing converts the bounding box into a linestring
4. ST_PointN takes a particular point from the linestring
5. ST_X and ST_Y take the X or Y coordinate from that point.

# left (MinX)
select ST_X(ST_PointN(ST_ExteriorRing(ST_Envelope(@poly)), 1));

# right (MaxX)
select ST_X(ST_PointN(ST_ExteriorRing(ST_Envelope(@poly)), 2));

# top (MaxY)
select ST_Y(ST_PointN(ST_ExteriorRing(ST_Envelope(@poly)), 3));

# bottom (MinY)
select ST_Y(ST_PointN(ST_ExteriorRing(ST_Envelope(@poly)), 1));

This relies on the output of ST_ExteriorRing always producing the linestring in a consistent order (seems to be counter-clockwise from bottom left), which is apparently the case (thank you @user30184 for clarifying). With that, I think this method should at least be reliable.