Vithun's Blog
Hibernate & MySQL Spatial Index
While working on a Grails application which required spatial queries, I came across a problem. I had to make use of the MBRContains function in MySQL. Since I was using Grails which in turn uses Hibernate, the HQL query I would write had to support these spatial functions.
I used an extension of Hibernate Spatial dialect for the application’s data source dialect which I have explained in the previous post.
A few days after writing the code to perform some complex queries (which included spatial clauses in it), we noticed that the site’s performance was getting slower. While investigating, I found that for some reason, the MBRContainsfunction was not making use of the spatial index we had added on a column. This was a bit strange.
I then obtained the SQL query that was being generated from the HQL query. We had the following as part of the where clause in HQL query:
1
... and mbrcontains(GeomFromText(:boundary), location) = true ...
As expected, this was generating an SQL query in this form:
1
... and MBRContains(GeomFromText(?), location) = 1 ...
When I tested the above generated SQL query using EXPLAIN, I found that the spatial index was not being used by the query. A quick search on Google showed me that I was not the only one with this problem. The problem here was the “= 1” being generated in the SQL query because of the “= true” in the HQL query. This, for some reason, was preventing the spatial index from being used. If I removed the “= true” in HQL query, it was resulting in some Unexpected AST node exception in Hibernate.
I couldn’t find a proper solution for the problem anywhere on the net, and ironically XKCD published this cartoon on the very same day. We finally decided to rewrite the whole complex query in plain SQL to overcome this problem.
About a week later, when it was time to start rewriting the query, I still felt a bit lazy about it. If only there was a simple way to solve the above problem, it would prevent me from having to sit for the next 2 to 3 days and rewrite a pretty complex query and test it thoroughly. And then I realised there was actually a way. It was quite simple actually, and sometimes it is the simpler things we tend to miss.
All I did was register a new function with a tiny bit of hack in the MySQLDialect of our application:
1
registerFunction("mbr_contains", new SQLFunctionTemplate(Hibernate.BOOLEAN, "MBRContains(?1, ?2) and 1"));
And then I used this function in the HQL query. The query now became something like this:
1
... and mbr_contains(GeomFromText(:${boundaryVariable}), location) = 1 ...
which is valid HQL, and also generates SQL which makes use of the spatial index:
1
... and MBRContains(GeomFromText(?), location) and 1 = 1 ...