Category Archives: Database

Creating Circles and Ellipses in MySQL Spatial

MySQL doesn’t have any dedicated functions to generate circles or ellipses.   You can generate circles in newer versions of MySQL (5.6 and above) using the Buffer function.   For older versions of MySQL or for ellipses you need to roll your own function.   Here’s one simple function that generates an ellipse or circle as WKT.

DELIMITER $$

DROP FUNCTION IF EXISTS make_ellipse$$

CREATE FUNCTION
make_ellipse (center_x INT, center_y INT, horizontal_axis INT,  vertical_axis INT, points INT)
RETURNS varchar(5000)
BEGIN
DECLARE semi_horizontal FLOAT;
DECLARE semi_vertical FLOAT;
DECLARE counter INT;
DECLARE x_increment FLOAT;
DECLARE x_ratio FLOAT;
DECLARE x_pos FLOAT;
DECLARE x_start FLOAT;
DECLARE y FLOAT;
DECLARE y_start FLOAT;
DECLARE wkt varchar(5000);

SET semi_horizontal = horizontal_axis / 2;
SET semi_vertical = vertical_axis / 2;

SET x_increment = horizontal_axis / (points/2);

SET wkt = ('POLYGON(( ');
-- start at far left and go clockwise
SET x_pos = center_x - semi_horizontal;

-- top (positive y)
SET counter = 0;
WHILE counter  < (points/2) DO SET x_ratio = x_pos / semi_horizontal; SET y = semi_vertical * SQRT(1 - x_ratio*x_ratio); IF (counter > 0) THEN SET wkt = CONCAT(wkt, ',');
ELSE
SET x_start = x_pos;
SET y_start = y;
END IF;
SET wkt = CONCAT(wkt, x_pos, ' ', y);
SET x_pos = x_pos + x_increment;
SET counter = counter + 1;
END WHILE;

IF (counter > 0) THEN SET wkt = CONCAT(wkt, ',');
END IF;

-- bottom (negative y)
SET x_pos =  center_x + semi_horizontal;
SET counter = 0;
WHILE counter  < (points/2) DO SET x_ratio = x_pos / semi_horizontal; SET y = -1 * semi_vertical * SQRT(1 - x_ratio*x_ratio); IF (counter > 0) THEN SET wkt = CONCAT(wkt, ',');
END IF;
SET wkt = CONCAT(wkt, x_pos, ' ', y);
SET x_pos = x_pos - x_increment;
SET counter = counter + 1;
END WHILE;

-- close the loop
SET wkt = CONCAT(wkt, ',');
SET wkt = CONCAT(wkt, x_start, ' ', y_start);

SET wkt = CONCAT(wkt, ' ))');

RETURN wkt;

END$$
DELIMITER ;

The arguments to this function are the ellipse center, the lengths of the horizontal and vertical axes, and the number of points used to represent the ellipse.    The more points you give, the smoother the representation of the ellipse

This command creates an ellipse with with 20 points, centered at (0,0),  where the horizontal axis is 200 units long, and the vertical axis is 100 units long:

select make_ellipse(0, 0, 200, 100, 20);

You can use GeomFromText to convert the WKT to a geometry.   Here’s how that geometry looks when plotted in OpenJump.

ellipseEllipse in MySQL

ellipse

The function has a couple of deficiencies, which I’ll leave as an exercise to the reader to solve (or not).

  • The algorithm “samples” the ellipse at even intervals along the horizontal axis.   This works great when the value of the vertical axis doesn’t change quickly.  However, when the vertical axis changes quickly and there aren’t many vertices, the ellipse has sharp edges instead of being nicely rounded.
  • The ellipse is always aligned along the horizontal and vertical axis.  There’s no way to rotate them.
  • If you’re using a latitude longitude coordinate system, this doesn’t handle the anti-meridian (where longitude jumps from 180 to -180).