Passing Curiosity: Posts tagged mysqlhttps://passingcuriosity.com/tags/mysql/mysql.xmlThomas Suttonme@thomas-sutton.id.au2009-08-05T00:00:00ZAdding a distance function to MySQLhttps://passingcuriosity.com/2009/adding-a-distance-function-to-mysql/2009-08-05T00:00:00Z2009-08-05T00:00:00ZI’ve been working on a project which involves a little bit of geographical information lately (using Django, if that’s important to you) and one of the problems I’ve encountered is MySQL’s incomplete implementation of the various functions that you’d expect of a geographically-capable database system. One particular lack is a function to calculate the distance between two points (or, even, other geometric forms). Thankfully, it’s possible to define it yourself (though convincing GeoDjango to run it is another matter). There are copies floating around in the ’tubes, but the one I found at MySQL forge (and also on the author’s blog) is a bit broken.

Below, I’ll mention why I think it’s broken and the small change required to “fix” it.

Alas, it doesn’t work very well on current versions of MySQL (and judging by a comment on the page, it didn’t work meaningfully back in 2006): it defines a new function distance from two POINTs to a DOUBLE. For some bizarre reason, though, it rounds it’s result to 0 decimal places. This makes it pretty useless for local POINTs in a coarse metric like degrees (where 1° of latitude is around 111km) when your POINTs are local: you’ll often get a whole lots of 0s in your results:

Thankfully, it’s easy to fix: just remove the call to round (or, I suppose, give it an accuracy rather than let it default to 0 places).

To create the function simply run the following command somehow – I prefer the mysql command-line, others [phpMyAdmin][], and still more will put it in an XML file to be interpreted by a rule engine which is called as part of a semi-automated deployment process (these are the people that enjoy using Java):

You’ll notice that this is calling both asbinary (to convert WKB values into internal MySQL values) and then linestringfromwkb (to convert WKB into internal MySQL values). Exactly whylinestring takes MySQL values and returns a WKB value, I’m not sure, but it does. If you need to support WKT inputs, then you’ll wind up calling conversion functions four or five times per query.

Convincing Django to call this new function is another matter all together. After a day and a half of trying to understand the GeoDjango back-ends, I gave up (see this thread on the GeoDjango mailing list for a little more information) and just used the extra() method to add a call to the function as a new column and order by it:

]]>Specifying a UNIX socket using MySQL with Djangohttps://passingcuriosity.com/2009/specifying-a-unix-socket-using-mysql-with-django/2009-04-16T00:00:00Z2009-04-16T00:00:00ZIt is sometimes necessary to specify a particular UNIX socket for MySQLclient libraries to use (for example, when you have more than one MySQL server on the machine and wish to use one other than the default). The canonical way to specify a particular UNIX socket for Django is to give the full path as the DATABASE_HOST option in the project settings file1:

This works properly and reliably for Django itself but, on my system at least, it also breaks the manage.py dbshell command: rather than starting and connecting to the correct database, the mysql errors out with the message

The reason for this should be fairly obvious: /tmp/mysql.dev.sock is not, in fact, a host name. In fact, this whole solution seems pretty wacky to me (why put a value that is distinctly not a host name in the “hostname” value?). The correct way to specify a UNIX socket for the MySQL client libraries to connect to is using the DATABASE_OPTIONS ([most of] the options can be seen in the MySQLdb API documentation):

Doing so ensures that Django is able to connect (using a UNIX socket on the local host) and that the mysql shell is able to connect (also using a UNIX socket on the local host). Everything works, everyone is happy, and all of our options have values that actually make sense. Hoorah!