CentOS 5.5 MariaDB 5.1.49 で geohash の UDF を作って入れてみる
ご無沙汰しております。IT7Cです。MySQL*1でgeohashのUDFがなかったので自作してみました。間違い等あると思いますが、とりあえずソースを晒して寝ます。メモリーリークとかあるかもしれないので、本番環境に入れるのはやめて下さい。あとで、コードレビューやら、隣接geohashの関数を追加してgithubにでも正式に上げておきます。
尚、base64encodeからbase32encode、geohashのUDFに進めようとして失敗したのは内緒です。ぶっちゃけ、geohashにおけるbase32はテーブルだけで、ちゃんとしたbase32は必要としていませんでした。しっぱいしっぱい。
/* MySQL geohash UDF IT7C <7c.it.kei｡mail.com> Install: gcc -Wall -I/usr/local/include -shared geohash.c -o geohash.so cp geohash.so /usr/local/mariadb/lib/mysql/plugin/ CREATE FUNCTION geohash_encode RETURNS STRING SONAME 'geohash.so'; CREATE FUNCTION geohash_decode RETURNS STRING SONAME 'geohash.so'; Usage: SELECT geohash_encode(latitude,longtitude,precision); SELECT geohash_encode(57.64911,10.40744,11); +--------------------------------------+ | geohash_encode(57.64911,10.40744,11) | +--------------------------------------+ | u4pruydqqvj | +--------------------------------------+ SELECT geohash_decode(geohash); SELECT geohash_decode('u4pruydqqvj'); +-------------------------------------------------------------------+ | geohash_decode('u4pruydqqvj') | +-------------------------------------------------------------------+ | 57.6491089,57.6491089,10.4074392,10.4074402,57.6491089,10.4074402 | +-------------------------------------------------------------------+ wikipedia : http://en.wikipedia.org/wiki/Geohash javascript : http://github.com/davetroy/geohash-js/blob/master/geohash.js Mysql Stored Function : http://github.com/nowelium/geohash-mysql-func PHP : http://mtcn.ko-me.com/Entry/1/ */ #include <stdlib.h> #include <stdio.h> #include <string.h> #include <mysql/mysql.h> static char base32[] = "0123456789bcdefghjkmnpqrstuvwxyz"; static int mask[5] = {16,8,4,2,1}; typedef unsigned char uchar; my_bool geohash_encode_init(UDF_INIT* initid, UDF_ARGS* args, char* message) { if (args->arg_count < 2 || args->arg_count > 3) { strcpy(message,"Wrong arguments tos geohash_encode; must be (real,real,int)"); return 1; } if ( args->arg_type[0] != REAL_RESULT ) { args->arg_type[0] = REAL_RESULT; } if ( args->arg_type[1] != REAL_RESULT ) { args->arg_type[1] = REAL_RESULT; } if (args->arg_count > 2) { if ( args->arg_type[2] != INT_RESULT ) { args->arg_type[2] = INT_RESULT; } } return 0; } void geohash_encode_deinit(UDF_INIT* initid) { if (initid->ptr) free(initid->ptr); } char *geohash_encode(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *ret_length, char *is_null, char *error) { const double (*latlon_i[2]); double latlon[4] = { -90.0, 90.0 , -180.0, 180.0}; double mid = 0; unsigned long i,j,ch = 0; unsigned long precision = 12; unsigned int is_even = 1; latlon_i[0] = (double*)args->args[0]; latlon_i[1] = (double*)args->args[1]; if (args->arg_count > 2) { precision = *((long long*) args->args[2]); } if (!( result = (char*) malloc( sizeof(char) * (precision +1) ) ) ) { /* strmov(message,"Couldn't allocate memory in geohash_encode_init"); */ *is_null=1; *error=1; *ret_length = 0; return 0; } if (initid->ptr) free(initid->ptr); initid->ptr = result; for(j=0;j<precision;j++){ for(i=0;i<5;i++){ mid = (latlon[(is_even<<1)] + latlon[(is_even<<1)+1]) /2; if(*latlon_i[is_even] > mid){ ch|=mask[i]; latlon[(is_even<<1)] = mid; }else{ latlon[(is_even<<1)+1] =mid; } is_even=!is_even; } result[j]= base32[ch]; ch = 0; } result[precision]= '\0'; *ret_length = precision; return result; } my_bool geohash_decode_init(UDF_INIT* initid, UDF_ARGS* args, char* message) { if (args->arg_count != 1) { strcpy(message,"Wrong arguments to geohash_decode; must be (str)"); return 1; } if ( args->arg_type[0] != STRING_RESULT ) { args->arg_type[0] = STRING_RESULT; } return 0; } void geohash_decode_deinit(UDF_INIT* initid) { if (initid->ptr) free(initid->ptr); } char *geohash_decode(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *ret_length, char *is_null, char *error) { const unsigned char *current; unsigned long i,ch; unsigned long len; static short base32d[256]; static int table_built; float latlon[6] = { -90.0, 90.0 , -180.0, 180.0}; int is_even = 1; int v = 0; if (++table_built == 1) { char *chp; for(ch = 0; ch < 256; ch++) { chp = strchr(base32, ch); if(chp) { base32d[ch] = chp - base32; } else { base32d[ch] = -1; } } } current = args->args[0]; len = args->lengths[0]; if (len <= 0 || args->arg_type[0] != STRING_RESULT ) { *is_null=0; *ret_length = 0; return 0; } if (!args || !args->args[0]) { *is_null=1; *ret_length = 0; return 0; } if ( len < 1) { len=1; } if (!( result = (char*) malloc( sizeof(char) * 77 ) ) ) { *is_null=1; *error=1; *ret_length = 0; return 0; } if (initid->ptr) free(initid->ptr); initid->ptr = result; while ((ch = *current) != '\0') { current++; v = base32d[ch]; for(i=0;i<5;i++){ latlon[!(v & mask[i])+(is_even<<1)]= (latlon[(is_even<<1)] + latlon[(is_even<<1)+1])/2; is_even=!is_even; } } latlon[4]=(latlon[0]+latlon[1])/2; latlon[5]=(latlon[2]+latlon[3])/2; sprintf(result,"%.7f,%.7f,%.7f,%.7f,%.7f,%.7f",latlon[0],latlon[1],latlon[2],latlon[3],latlon[4],latlon[5]); *ret_length = strlen(result); return result; }
