MySQL Forums
Forum List  »  GIS

Spatial stored procedure view causes instance to crash
Posted by: Dan Jones
Date: December 08, 2008 09:51AM

Hello.

I've written a stored procedure to turn a UK grid reference into a square polygon and return it as a Geometry. This works fine an I can do a select from a table of grid references and use AsText to render the polygon WKTs to the screen.

I can create a view from the same select statement and that works too but when I remove the AsText function to actually create a spatial view with a Geometry column the instance crashes.

I can't see a way to specify the engine for a view but the source table engine is MyISAM and the default engine for the instance is also MyISAM.

Anyone got any ideas? Thanks. Dan

I've included the stored procedure below in case that's useful.

DELIMITER $$

DROP FUNCTION IF EXISTS `gs_recorder`.`gs_spatial_grid_square` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `gs_spatial_grid_square`(gridsquare VARCHAR(14),maxres INT) RETURNS geometry
BEGIN
DECLARE len INT;
DECLARE figs INT;
DECLARE istetrad INT;

DECLARE sq CHAR(2);
DECLARE tetrad CHAR(1);

DECLARE trdv INT;
DECLARE trdx INT;
DECLARE trdy INT;

DECLARE t1 INT;
DECLARE t2 INT;

DECLARE wkt VARCHAR(200);
DECLARE poly POLYGON;

DECLARE diff INT;

DECLARE minx INT;
DECLARE maxx INT;
DECLARE miny INT;
DECLARE maxy INT;

SET len = LENGTH(gridsquare);
SET istetrad = (len % 2);
IF (istetrad <> 0)
THEN
SET len = len - 1;
SET tetrad = SUBSTRING(gridsquare,(len+1),1);
SET trdv = ASCII(tetrad) - 65;
IF (trdv > 13) THEN SET trdv = trdv - 1; END IF;
SET trdy = (trdv % 5);
SET trdx = FLOOR(trdv / 5);
END IF;
SET figs = (len - 2) / 2;

SET sq = SUBSTRING(gridsquare,1,2);
SET diff = POW(10,(5-figs));
SET minx = CAST(SUBSTRING(gridsquare,3,figs) AS UNSIGNED);
SET minx = minx * diff;
SET miny = CAST(SUBSTRING(gridsquare,(figs+3),figs) AS UNSIGNED);
SET miny = miny * diff;

SET t1 = ASCII(SUBSTRING(sq,1,1)) - 65;

IF (t1 > 8) THEN SET t1 = t1 -1; END IF;
SET t2 = FLOOR(t1 / 5);
SET miny = miny + 500000 * (3 - t2);
SET minx = minx + 500000 * (t1 - 5 * t2 - 2);

SET t1 = ASCII(SUBSTRING(sq,2,1)) - 65;
IF (t1 > 8) THEN SET t1 = t1 - 1; END IF;
SET t2 = FLOOR(t1 / 5);
SET miny = miny + 100000 * ( 4 - t2);
SET minx = minx + 100000 * ( t1 - 5 * t2);

IF (istetrad <> 0)
THEN
SET diff = 2000;
SET miny = miny + (trdy * diff);
SET minx = minx + (trdx * diff);
END IF;

IF (maxres > diff)
THEN
SET diff = maxres;
SET minx = minx - (minx % maxres);
SET miny = miny - (miny % maxres);
END IF;

SET maxx = minx + diff;
SET maxy = miny + diff;
SET wkt = CONCAT('POLYGON((',minx,' ',miny,',',minx,' ',maxy,',',maxx,' ',maxy,',',maxx,' ',miny,',',minx,' ',miny,'))');
SET poly = GeomFromText(wkt);

RETURN poly;

END $$

DELIMITER ;

Options: ReplyQuote


Subject
Views
Written By
Posted
Spatial stored procedure view causes instance to crash
4544
December 08, 2008 09:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.