Servage Magazine

Information about YOUR hosting company – where we give you a clear picture of what we think and do!

Working with spatial functions in MySQL

Tuesday, July 17th, 2012 by Servage

Geo-based services are increasingly popular, and databases are getting better, faster and more advanced querieng functionality to deliver geo-based results fast and accurate. PostgreSQL with the PostGIS extenstion has for a long time been a great solution, and continues to deliver great database products to the open source community. However, MySQL is the by-far most implemented database software with hosting companies. It can be argued for and against, but the fact remains, and therefore it is great to see how spatial functionality continues to improve in MySQL. While the most stable and delpoyed MySQL versions are the lower version 5 distributions, the newer version 5.6 and upwards finally integrates rather advanced functions, which provide the required functionality for simple geo-based services.

The main reason why MySQL wasn’t a good choice for this before, is that it didn’t implement some of the necessary functions correctly. The functions were there, but used a more simple algorithm, hence providing an unprecise result. MySQL is continously rectyfing this, and implementing the correct versions of the functions, delivering the expected results.

So what can you do with spatial functions in MySQl?

You can define points, lines, polygons, and make calucaltions on them. Does a polygon contain another polygon? Does a point lie within a polygon? What is the minimum bounding rectangle for a geographic shape? This and more are examples, which enable you to do pretty nice geo-queries. Say “select all items within a geographical area”.


SELECT AsText(Envelope(GeomFromText('LineString(1 1,2 2)')));
SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Point(1 1)');
SELECT MBRContains(@g1,@g2), MBRContains(@g2,@g1);

Awesome :-)

Working with spatial functions in MySQL, 4.3 out of 5 based on 4 ratings
Categories: Tips & Tricks


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

No comments yet (leave a comment)

You are welcome to initiate a conversation about this blog entry.

Leave a comment

You must be logged in to post a comment.