Bug #13020 spatial area function no longer working

• Submitted: 6 Sep 2005 18:10
• Modified: 6 Sep 2005 18:33
• Reporter: Richard Watson
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.12
• OS: Linux (Linux)
• CPU Architecture: Any

[6 Sep 2005 18:10] Richard Watson

Description:
SELECT Area(
boundpath
) *526 AS  'Area (km^2)'
FROM political_unit, boundary
WHERE unitname =  'Northern Ireland'
AND political_unit.unitcode = boundary.unitcode
LIMIT 0 , 30

MySQL said: 

#1305 - FUNCTION textbook.Area does not exist 

I have tested this GIS function before, but now get this error.

textbook is the name of the database containing the tables.

Similar problems for

SELECT GLength(LineStringFromWKB(LineString(AsBinary(orig.cityloc),     AsBinary(dest.cityloc))))*23 as 'Distance (miles)'
 FROM city orig, city dest
 WHERE orig.cityname = 'Belfast' 
 AND dest.cityname = 'Londonderry';

and

SELECT north.cityname FROM city north
WHERE not exists
  (select * from city other where Y(other.cityloc) < Y(north.cityloc)); 

which had both worked previously under an earlier version of 5.0

How to repeat:
CREATE TABLE  political_unit (
 unitname       VARCHAR(30) not null,
 unitcode       CHAR(2),
 unitpop        DECIMAL(6,2),
 PRIMARY KEY(unitcode));

CREATE TABLE boundary (
 boundid        INTEGER,
 boundpath      POLYGON NOT NULL,
 unitcode       CHAR(2),
 PRIMARY KEY(boundid),
 CONSTRAINT fk_boundary_polunit FOREIGN KEY(unitcode)
   REFERENCES political_unit(unitname)) TYPE MYISAM;

CREATE TABLE city (
 cityname       VARCHAR(30),
 cityloc        POINT NOT NULL,
 unitcode       CHAR(2),
 PRIMARY KEY(unitcode,cityname),
 CONSTRAINT fk_city_polunit FOREIGN KEY(unitcode) 
   REFERENCES political_unit(unitname)) TYPE MYISAM; 		

INSERT INTO political_unit VALUES ('Republic of Ireland','ie', 3.9);
INSERT INTO political_unit VALUES ('Northern Ireland','ni', 1.7);
INSERT INTO boundary VALUES
  (1,GeomFromText('polygon((9 8, 9 3, 4 1, 2 2, 1 3, 3 5, 3 6, 2 6,
	2 9, 5 9, 5 10, 6 11, 7 11, 7 10, 6 9, 7 8, 7 9, 8 9, 8 8, 9 8))'),'ie');
INSERT INTO boundary VALUES
  (2,GeomFromText('polygon((7 11, 9 11, 10 9, 10 8, 8 8, 8 9, 7 9,
	7 8, 6 9, 7 10, 7 11))'),'ni');
INSERT INTO city VALUES ('Dublin',GeomFromText('POINT(9 6)'),'ie');
INSERT INTO city VALUES ('Cork',GeomFromText('POINT(5 2)'),'ie');
INSERT INTO city VALUES ('Limerick',GeomFromText('POINT(4 4)'),'ie');
INSERT INTO city VALUES ('Galway',GeomFromText('POINT(4 6)'),'ie');
INSERT INTO city VALUES ('Sligo',GeomFromText('POINT(5 8)'),'ie');
INSERT INTO city VALUES ('Tipperary',GeomFromText('POINT(5 3)'),'ie');
INSERT INTO city VALUES ('Belfast',GeomFromText('POINT(9 9)'),'ni');
INSERT INTO city VALUES ('Londonderry',GeomFromText('POINT(7 10)'),'ni');

[6 Sep 2005 18:29] MySQL Verification Team

I was unable to repeat with server from today BK source:

mysql> SELECT Area(
    -> boundpath
    -> ) *526 AS  'Area (km^2)'
    -> FROM political_unit, boundary
    -> WHERE unitname =  'Northern Ireland'
    -> AND political_unit.unitcode = boundary.unitcode
    -> LIMIT 0 , 30
    -> ;
+-------------+
| Area (km^2) |
+-------------+
|        4208 |
+-------------+
1 row in set (0.00 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.0.13-beta-debug |
+-------------------+
1 row in set (0.00 sec)

[6 Sep 2005 18:33] Jorge del Conde

mysql> SELECT Area(
    -> boundpath
    -> ) *526 AS  'Area (km^2)'
    -> FROM political_unit, boundary
    -> WHERE unitname =  'Northern Ireland'
    -> AND political_unit.unitcode = boundary.unitcode
    -> LIMIT 0 , 30
    -> ;
+-------------+
| Area (km^2) |
+-------------+
|        4208 |
+-------------+
1 row in set (0.00 sec)

[8 Sep 2005 0:34] Richard Watson

It looks like it is a phpmyadmin problem.

I am impressed by how quickly I received a response.