MySQL Forums
Forum List  »  GIS

Aggregate trick for Spatial Data
Posted by: John van Zijl
Date: February 23, 2009 07:34AM

LS,

A trick to aggregate spatial data into an envelope.

First convert into a string
> astext(surf)
then combine the strings
>group_concat(astext(surf))
then put some obligatory text before and behind
>concat(concat("geometrycollection(",group_concat(astext(surf))),")")
and convert it back to geo_data
>Geomfromtext(concat(concat("geometrycollection(",group_concat(astext(surf))),")")))
then do your calculations
envelope(Geomfromtext(concat(concat("geometrycollection(",group_concat(astext(surf))),")")))

only one issue: group_concat only only makes a string 1024bytes long as a default setting, solution make it bigger->

set global group_concat_max_len=104448

here set to 100K

The complete Select:

select astext(envelope(Geomfromtext(concat(concat("geometrycollection(",group_concat(astext(surf))),")")))) from karten

It needed 0.0472 Seconds to calculate a Envelope from 580 polygons


My Table:

CREATE TABLE `hambach`.`karten` (
`ID` int(10) unsigned NOT NULL auto_increment,
`Kartenname` varchar(100) NOT NULL default '',
`Surf` geometry NOT NULL,
`Parent` int(10) unsigned default '0',
`D1Width` int(10) unsigned default '200',
`D1Height` int(10) unsigned default '200',
`D1ZoomLevel` tinyint(3) unsigned default '0',
PRIMARY KEY USING BTREE (`ID`,`Kartenname`),
UNIQUE KEY `Kartenname` USING BTREE (`Kartenname`),
SPATIAL KEY `Geom` (`Surf`)
) ENGINE=MyISAM AUTO_INCREMENT=1161 DEFAULT CHARSET=latin1;

Keep up the stuggle, the is always a solution, you just have to find the fool that posted it.

Kind regards, John

Options: ReplyQuote


Subject
Views
Written By
Posted
Aggregate trick for Spatial Data
9815
February 23, 2009 07:34AM
3843
February 23, 2009 07:48AM
3822
February 23, 2009 08:13AM


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.