3

I'm developing a spatial database of administrative areas. I started out with PostGIS, but as MySQL is being used in our other projects, I thought I would at least try to create a spatial-enabled MySQL database.

However, spatial queries give me different results than PostGIS does. Let's say I have a query like this

SELECT nazev FROM orp as o, kraje as k WHERE Intersects(o.geom,k.geom) AND Within(Centroid(o.geom),k.geom) AND k.nazev = 'Nazev'.

It should return a list of administrative areas within a county with defined name (nazev). Instead, it gives me expected areas + areas that touch boundary of a county, but are not contained by this county. When I run the same query with PostGIS, I get the correct results.

Have anyone ever faced this issue? I am using a national CRS (EPSG:102067) and have tried also with EPSG:4326 with same result. Isn't the implementation of the OGC spec the same as in PostGIS? Thanks for any hint.

  • If i recall correctly mysql used to do comparison with bounding boxes. But i think it has lately started to do real spatial comparison. so it might be because you have old mysql or it is feature. can you post your mysql version numbr – simplexio Jan 17 '13 at 11:00
  • MySQL 5.5. It seems there are two sets of functions defined: one beginning with "MBR" and the other one using the real geometry. – Michal Zimmermann Jan 17 '13 at 11:11
  • MySQL is not quite the with spatial functions - Bound Box is use (MBR) bostongis.com/blog/index.php?/archives/… – Mapperz Jan 17 '13 at 14:35
  • 3
    MySQL 5.6 is the first release with true spatial relationships dev.mysql.com/doc/refman/5.6/en/… – Mapperz Jan 17 '13 at 14:37
  • The implementation of Centroid() might also be a gotcha, since it often is implemented as a center-of-mass, which for concave features might not fall within the feature. Probably not your problem, but always a surprise to new users. – Paul Ramsey Jan 17 '13 at 18:48
1

This question was asked ½ a decade ago, but things have changed since then. As stated in @Mapperz’s comment, 5.6 is the first version of MySQL with true full-shape spatial geometry operators and functions. In this version, however, most of the ones that have no prefix (such as your Contains(…) and Within(…) in your code sample but apparently not the Centroid(…) call) will still execute on Minimum Bounding Rectangles.

Starting in MySQL version 5.7.6, these non-prefixed operators and functions are deprecated and their use highly discouraged because of the ambiguity and incompatibility with the new SQL-MM standard. Instead, if you need full shape support, prefix the operators / functions with “ST_ (for “Spatial Type”). If you want Minimum Bounding Rectangles versions (say, for performance reasons in use cases where exact shapes aren’t really necessary), prefix with “MBR_”. Note also that PostGIS 2.x+ also supports the “ST_” prefix but apparently not the “MBR_” one, and it, too, has deprecated the non-prefixed variants.

So, your code example would read (and should work in MySQL 5.6+, MariaDB 5.3.3 or 10.x+, and PostGIS 2.x+):

SELECT nazev FROM orp as o, kraje as k WHERE
  ST_Intersects(o.geom,k.geom) AND ST_Within(ST_Centroid(o.geom),k.geom) AND
  k.nazev = 'Nazev'.

Note that you cannot use the “ST_” prefix prior to MySQL 5.6! It doesn’t support full-shape spatial functionality until version 5.6, and it’s never heard of keywords beginning with “ST_.”

MariaDB’s website has a handy Geospatial Support Matrix page comparing the geospatial support functionality of various versions of MySQL (not including the new MySQL 8.x which should at least equal 5.7.6 in terms of spatial support) and MariaDB (including the new MariaDB 10.x). Note that MariaDB, especially the 10.x versions, has better spatial support than the nearest equivalent MySQL version that it’s a drop-in replacement for, including the very useful ST_Relate(…).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.