[Maria-discuss] Questions about GIS functionality (vs MySQL, InnoDB, PostGIS...)
Hi Holyfoot, others
I've been reading up on the state of GIS functionality. It would be great if I can get some answers directly from the source!
1) Comparing MariaDB and what's there in MySQL 5.6.1: are these identical but independent implementations? If there are differences, what are they?
2) Apparently PostGIS requires to do all kinds of gymnastics both when installing and when doing version upgrades. MySQL/MariaDB spatial functions are just there to be used, right?
3) Speaking of PostGIS, its manual reveals that their GiST R-tree index actually only works on the bounding rectangles of shapes. Is this the case also for MyISAM/Aria R-tree indexes, or would MariaDB actually filter the result set to only return shapes that are within the search shape for real. And btw, does the search shape have to be a box, or can it be any shape?
4) Lack of InnoDB R-tree indexes... What would it take to do that? Is the current implementation re-usable in any way (ie just store current R-tree indexes into InnoDB pages...) or would it have to be a separate implementation?
5) Euclidean geometrics only... PostGIS and SQLite use the separate GEOS library (and cousins like PROJ.4). Why isn't this used in the MySQL/MariaDB implementation? It seems to me, you could get accurate handling of different (non-euclidean) projections by using these libraries, "for free".
henrik
Hi, Henrik.
1) Comparing MariaDB and what's there in MySQL 5.6.1: are these identical but independent implementations? If there are differences, what are they?
Implementations are quite different by now. Most significant difference is precise arithmetic used in Maria. So coinciding shapes or nodes are handled properly unlike the MySQL. A number of bugs was fixed as well.
2) Apparently PostGIS requires to do all kinds of gymnastics both when installing and when doing version upgrades. MySQL/MariaDB spatial functions are just there to be used, right?
Right.
3) Speaking of PostGIS, its manual reveals that their GiST R-tree index actually only works on the bounding rectangles of shapes. Is this the case also for MyISAM/Aria R-tree indexes, or would MariaDB actually filter the result set to only return shapes that are within the search shape for real. And btw, does the search shape have to be a box, or can it be any shape?
RTree indexes operate with the 'bounding rectangles' in Maria as in PostGIS. It's just the RTree algorithm works that way. So Maria uses RTree key to get the preliminary result which is later filtered with the exact shape operation. So any shape should produce the proper result. It's just some shapes can't be effectively used by RTree to speed the execution.
4) Lack of InnoDB R-tree indexes... What would it take to do that? Is the current implementation re-usable in any way (ie just store current R-tree indexes into InnoDB pages...) or would it have to be a separate implementation?
It is reusable, and technically can be made inside the InnoDB. Unfortunately we have no plans for doing that.
5) Euclidean geometrics only... PostGIS and SQLite use the separate GEOS library (and cousins like PROJ.4). Why isn't this used in the MySQL/MariaDB implementation? It seems to me, you could get accurate handling of different (non-euclidean) projections by using these libraries, "for free".
For a number of reasons we didn't use the GEOS library. Though the geodetic coordinates (along with other reference systems) should be available soon.
Regards HF
06.08.2012 11:54, Henrik Ingo wrote:
Hi Holyfoot, others
I've been reading up on the state of GIS functionality. It would be great if I can get some answers directly from the source!
1) Comparing MariaDB and what's there in MySQL 5.6.1: are these identical but independent implementations? If there are differences, what are they?
2) Apparently PostGIS requires to do all kinds of gymnastics both when installing and when doing version upgrades. MySQL/MariaDB spatial functions are just there to be used, right?
3) Speaking of PostGIS, its manual reveals that their GiST R-tree index actually only works on the bounding rectangles of shapes. Is this the case also for MyISAM/Aria R-tree indexes, or would MariaDB actually filter the result set to only return shapes that are within the search shape for real. And btw, does the search shape have to be a box, or can it be any shape?
4) Lack of InnoDB R-tree indexes... What would it take to do that? Is the current implementation re-usable in any way (ie just store current R-tree indexes into InnoDB pages...) or would it have to be a separate implementation?
5) Euclidean geometrics only... PostGIS and SQLite use the separate GEOS library (and cousins like PROJ.4). Why isn't this used in the MySQL/MariaDB implementation? It seems to me, you could get accurate handling of different (non-euclidean) projections by using these libraries, "for free".
henrik
On Mon, Aug 6, 2012 at 2:14 PM, Alexey Botchkov holyfoot@askmonty.org wrote:
1) Comparing MariaDB and what's there in MySQL 5.6.1: are these identical but independent implementations? If there are differences, what are they?
Implementations are quite different by now. Most significant difference is precise arithmetic used in Maria. So coinciding shapes or nodes are handled properly unlike the MySQL. A number of bugs was fixed as well.
It seems MySQL 5.6.1 also supports accurate arithmetic now, not just the old bounding box arithmetic. Ie ST_*() functions. http://dev.mysql.com/doc/refman/5.6/en/functions-for-testing-spatial-relatio...
But I can't see from the documentation if they also support complex shapes in the R-tree index, or only in the functions.
3) Speaking of PostGIS, its manual reveals that their GiST R-tree index actually only works on the bounding rectangles of shapes. Is this the case also for MyISAM/Aria R-tree indexes, or would MariaDB actually filter the result set to only return shapes that are within the search shape for real. And btw, does the search shape have to be a box, or can it be any shape?
RTree indexes operate with the 'bounding rectangles' in Maria as in PostGIS. It's just the RTree algorithm works that way. So Maria uses RTree key to get the preliminary result which is later filtered with the exact shape operation. So any shape should produce the proper result. It's just some shapes can't be effectively used by RTree to speed the execution.
Right. But even if you do it in 2 steps, it's still transparent to the user. Apparently in PostGIS, unless you use a rectangle for searching, it won't use the index at all. So you have to explicitly supply first a rectangle, then AND combine it with the shape you really want. (Which works, you can easily get the MBR for your shape, but very annoying to have to do it explicitly.)
Imho the user friendliness here is the biggest point in favor of MariaDB.
5) Euclidean geometrics only... PostGIS and SQLite use the separate GEOS library (and cousins like PROJ.4). Why isn't this used in the MySQL/MariaDB implementation? It seems to me, you could get accurate handling of different (non-euclidean) projections by using these libraries, "for free".
For a number of reasons we didn't use the GEOS library. Though the geodetic coordinates (along with other reference systems) should be available soon.
Ah, that's good news. When that is done, the only complaint really would be no support for InnoDB. Which one can live with but still worth mentioning as most MySQL usage is InnoDB-only nowadays.
henrik
06.08.2012 15:32, Henrik Ingo wrote:
On Mon, Aug 6, 2012 at 2:14 PM, Alexey Botchkov holyfoot@askmonty.org wrote: It seems MySQL 5.6.1 also supports accurate arithmetic now, not just the old bounding box arithmetic. Ie ST_*() functions. http://dev.mysql.com/doc/refman/5.6/en/functions-for-testing-spatial-relatio...
They do have the exact spatial functions. I meant not all of them work properly. Particularly cases when nodes or edges of different shapes coincide can produce inpredictable results.
Right. But even if you do it in 2 steps, it's still transparent to the user. Apparently in PostGIS, unless you use a rectangle for searching, it won't use the index at all. So you have to explicitly supply first a rectangle, then AND combine it with the shape you really want. (Which works, you can easily get the MBR for your shape, but very annoying to have to do it explicitly.)
We suppose the query optimizer should do that, so you just specify your complicated shape and Maria takes the MBR internally for the index. The 'spatial' optimizer isn't too smart yet, still can handle simple ST_Intersection() queries.
Ah, that's good news. When that is done, the only complaint really would be no support for InnoDB. Which one can live with but still worth mentioning as most MySQL usage is InnoDB-only nowadays.
henrik
Best regards. HF
participants (2)
-
Alexey Botchkov -
Henrik Ingo