Aggregate trick for Spatial Data
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