TwinTechs

SQLite is lacking a DAYOFWEEK equivalent. However, here is how to roll your own.

select case ((changetime / 86400) - 4) % 7
     when 0 then 'Mon.'
     when 1 then 'Tues.'
     when 2 then 'Wed.'
     when 3 then 'Thurs.'
     when 4 then 'Fri.'
     when 5 then 'Sat.'
     when 6 then 'Sun.'
     end as day_of_the_week

from ...

And yes, the epoch started on a Thursday.

After some hours of digging, it turns out that the mysqli module in PHP 5.2.5 has a critical bug if you want to use stored procedures. The fix is simple, but since it took me hours to find, I’ll post it here.

Basically, in 5.2.4 there was a change to the connect() code to always disable the CLIENT_MULTI_STATEMENTS option (passing this to connect is now ignored). But, implicitly under the hood, this option also enables CLIENT_MULTI_ROWS, and the mysqli module relied on this fact, never setting CLIENT_MULTI_ROWS directly itself. Unfortunately mysqli_multi_query() requires this option, and using CALL() in a sql statement requires multi_query().

So, this is fixed in CVS, but for those of us who need to run production systems compiled from otherwise stable source, a CVS tree is too wild, so here is the patch you need. Apply it to ext/mysqli/mysqli_api.c:

PHP mysqli Patch

After reading the OpenGIS for SQL specifications, it’s apparent that there is a layer of Spatial Representation control that is still missing from MySQL.

This makes the Distance functions as implemented not very useful for what most people use GIS for: latitude and longitude on the earth’s surface.

In the meantime, I have a patch that adds 2 functions: earth_distance_miles() and earth_distance_km() to the wl1326 branch.

To find the nearest X rows, on a database with 600k rows with a POINT column:
earth_distance_*() takes 3.04 seconds.
distance() takes 2.78 seconds.

[to download the patch right click on the link below and select save as]

Earth Distance Patch