Bug: Problems with big objects, especially MultiPolygons and GeometryCollections

[Andi Kalsch]

I have made some experiments with MySQL GIS and there are some problems with big objects. I have gisified all German OpenStreetMap objects and implemented an experimental map. It can load all objects in the current map section or just by clicking on the map. It seems to work properly for Points, LineStrings and Polygons. Big MultiPolygons and GeometryCollections seem to imply the old behavior - MySQL just uses bounding boxes for functions like Crosses() and Intersects().

The result is that large routes or polygons will be found although they are not visible in the map.

Demonstration of the inconsistent behavior:

$C->DB->query("SELECT @geom:=geom
FROM feature_geom WHERE feature = 'relation' AND feature_id = 68156");

$C->DB->query("SET @p:=GeomFromText('Point(49.64185 6.69685)')");

// 1st case
var_dump($C->DB->query("SELECT Contains(@geom, @p)")->fetchColumn(0));


$C->DB->query("drop table if exists geom");
$C->DB->query("create table geom like feature_geom");
$C->DB->query("alter table geom drop key geom");

// populate table with features, starting from a higher ID
$C->DB->query("INSERT INTO geom
SELECT * FROM feature_geom
WHERE feature = 'relation' AND feature_id>=68156");

// index
$C->DB->query("alter table geom add spatial key(geom)");

// 2nd case
var_dump($C->DB->query("SELECT count(*) FROM geom WHERE Contains(geom, @p)")->fetchColumn(0));

Output:

string(1) "0"
string(1) "0"

... which is correct.


In a second run I will populate the database beginning from a lower ID:

$C->DB->query("INSERT INTO geom
SELECT * FROM feature_geom
WHERE feature = 'relation' AND feature_id>=10");

Output:

string(1) "0"
string(1) "1"

... which is incorrect. It seems that MySQL will just index the MBR.



Edited 1 time(s). Last edit at 06/30/2009 06:30PM by Andi Kalsch.