Servage Magazine

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”.

Examples

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 :-)