IV. Support Mysql Spatial Extensions via erlydb_mysql
ErlyDB supports most database operations, and it can also be extended to support more complex operations. Here is an example, where I need to support Mysql spatial extensions.
Assume we have a table: places, which has a mysql point field: location, here is the code:
-module(places). -export([get_location_as_tuple/1, set_location_by_tuple/2 ]). %% @spec get_location_as_tuple(Id::integer()) -> {float(), float()} get_location_as_tuple(Id) -> ESQL = {esql, {select, 'AsText(location)', {from, ?MODULE}, {where, {'id', '=', Id}}}}, case erlydb_mysql:q(ESQL) of %% Example: %% {data, {mysql_result, [{<<>>, <<"AsText(location)">>, 8192, 'VAR_STRING'}], %% [[<<"POINT(-122.292 37.8341)">>]], %% 0, %% []}} {data, {mysql_result, [{_, <<"AsText(location)">>, _, _}], [[FirstResult]|_Rest], _, _}} -> case io_lib:fread("POINT(~f ~f)", binary_to_list(FirstResult)) of {ok, [X, Y], _} -> {X, Y}; _Else -> undefined end; _Error -> undefined end. %% @spec set_location_by_tuple(Id::integer(), {X::float(), Y::float()}) -> ok | error set_location_by_tuple(Id, {X, Y}) -> %% "UPDATE places SET location = PointFromText(\'POINT(1 1)\') WHERE (id = 1)" PointFromText = point_tuple_to_point_from_text({X, Y}), ESQL = {esql, {update, ?MODULE, [{location, PointFromText}], {'id', '=', Id}}}, Options = [{allow_unsafe_statements, true}], case erlydb_mysql:q(ESQL, Options) of {updated, {mysql_result, [], [], _UpdatedNum, []}} -> ok; _Error -> error end. point_tuple_to_point_from_text({X, Y}) -> %% as mysql support float in format of someting like 1.20002300000000005298e+02, %% we can just apply float_to_list/1 to X and Y PointFromText = lists:flatten(io_lib:fwrite("PointFromText('POINT(~f ~f)')", [X, Y])), list_to_atom(PointFromText). %% 'PointFromText(\'POINT(X Y)\')'
Now we can:
> places:set_location_by_tuple(6, {-11.11, 88.88}). > places:get_location_as_tuple(6). {-11.11, 88.88}