Bug #119566 Add the new spatial function ST_PERIMETER()
Submitted: 17 Dec 2:38 Modified: 17 Dec 2:40
Reporter: Kei SAKAI Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: GIS Severity:S4 (Feature request)
Version:9.5.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: contributioin, gis, new function, spatial

[17 Dec 2:38] Kei SAKAI 
Description:
I added a spatial function ST_Perimeter(). This function returns the perimeter of a polygon.

I implemented it based on ST_Area() function.

How to repeat:
Please apply the attached patch to the MySQL 9.5.0 source code. (I downloaded mysql-9.5.0.tar.gz)

Suggested fix:
Apply the patch.
[17 Dec 2:39] Kei SAKAI 
the "a" is original 9.5.0 code, "b" is new code.

Attachment: diff_for_st_perimeter.txt (text/plain), 4.02 KiB.
[17 Dec 2:40] Kei SAKAI 
-- Test 1: Basic
SELECT ST_Perimeter(ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')) pm;
	mysql> SELECT ST_Perimeter(ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')) pm;
	+------+
	| pm   |
	+------+
	|   40 |
	+------+
	1 row in set (0.001 sec)

-- Test 2: Hole
SELECT ST_Perimeter(ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 8 2, 8 8, 2 8, 2 2))')) pm;
	mysql> SELECT ST_Perimeter(ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 8 2, 8 8, 2 8, 2 2))')) pm;
	+------+
	| pm   |
	+------+
	|   64 |
	+------+
	1 row in set (0.001 sec)

-- Test 3: MultiPolygon
SELECT ST_Perimeter(ST_GeomFromText('MULTIPOLYGON(((0 0, 5 0, 5 5, 0 5, 0 0)), ((10 10, 15 10, 15 15, 10 15, 10 10)))')) pm;
	mysql> SELECT ST_Perimeter(ST_GeomFromText('MULTIPOLYGON(((0 0, 5 0, 5 5, 0 5, 0 0)), ((10 10, 15 10, 15 15, 10 15, 10 10)))')) pm;
	+------+
	| pm   |
	+------+
	|   40 |
	+------+
	1 row in set (0.001 sec)

-- Test 4a: with SRID
SELECT ST_Perimeter(ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))', 4326)) pm;
	mysql> SELECT ST_Perimeter(ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))', 4326)) pm;
	+--------------------+
	| pm                 |
	+--------------------+
	| 443768.41657864203 |
	+--------------------+
	1 row in set (0.001 sec)

-- Test 4b: with SRID
SELECT ST_Perimeter(ST_GeomFromText('POLYGON((35 135, 35 136, 36 136, 36 135, 35 135))', 6668)) pm;
	mysql> SELECT ST_Perimeter(ST_GeomFromText('POLYGON((35 135, 35 136, 36 136, 36 135, 35 135))', 6668)) pm;
	+--------------------+
	| pm                 |
	+--------------------+
	| 403352.30562126776 |
	+--------------------+
	1 row in set (0.018 sec)

-- Test 5a: Error（POINT）
SELECT ST_Perimeter(ST_GeomFromText('POINT(1 1)'));
	mysql> SELECT ST_PERIMETER(ST_GeomFromText('POINT(1 1)'));
	ERROR 3516 (22S01): POLYGON/MULTIPOLYGON value is a geometry of unexpected type POINT in st_perimeter.

-- Test 5b: Error (LINESTRING）
SELECT ST_Perimeter(ST_GeomFromText('LINESTRING(1 1,2 2)'));
	mysql> SELECT ST_Perimeter(ST_GeomFromText('LINESTRING(1 1,2 2)'));
	ERROR 3516 (22S01): POLYGON/MULTIPOLYGON value is a geometry of unexpected type LINESTRING in st_perimeter.

-- Test 6: Null
SELECT ST_Perimeter(null) pm;
	mysql> SELECT ST_Perimeter(null) pm;
	+------+
	| pm   |
	+------+
	| NULL |
	+------+
	1 row in set (0.001 sec)
[17 Dec 5:33] Kei SAKAI 
new file sql/gis/perimeter.cc

Attachment: perimeter.cc (application/octet-stream, text), 3.93 KiB.
[17 Dec 5:33] Kei SAKAI 
new file sql/gis/perimeter.h

Attachment: perimeter.h (application/octet-stream, text), 2.21 KiB.
[17 Dec 5:34] Kei SAKAI 
new file sql/gis/perimeter_functor.h

Attachment: perimeter_functor.h (application/octet-stream, text), 2.61 KiB.