Dumping GIS data

Today I needed to dump a table containing GIS data and I could not find a quick way of exporting such data into a WKT format. Since I had also some more small problem with my data, I called MySQL technical support (I have a MySQL Network account) and I got the small problem solved, but the dump seems to be still a pending issue.
If you are used to dump data often, you may know that mysqldump has two useful options: --hex-blob, which will dump binary data in hexadecimal format, and --compatible=target, which will adjust the dump format to the requested target. However, none of these options has any effect on GIS data. It is always dumped in binary format, hardly compatible with any other DBMS.

So I cooked up an alternative way of dumping such data.
First, I created a stored function to create the query I needed

delimiter //

drop function if exists gis_dump_query //

create function gis_dump_query
(
 p_db_name     varchar(50),
 p_table_name  varchar(50),
 p_file_name   varchar(100)
)
returns text
reads sql data
begin
 declare done            boolean default false;
 declare is_first        boolean default true;
 declare col_list        text;
 declare cname           varchar(50);
 declare ctype           varchar(50);
 declare column_descr    varchar(100);
 declare get_cols        cursor for
     select column_name, data_type
     from
         information_schema.columns
     where
             table_schema= p_db_name
             and
             table_name = p_table_name;
 declare continue        handler for not found
     set done = true;

 set col_list = '';
 open get_cols;
 GET_COLUMNS:
 loop
     fetch get_cols into cname,ctype;
     if (done) then
         leave GET_COLUMNS;
     end if;
     if (is_first) then
         set is_first = false;
     else
         set col_list = concat(col_list, ',');
     end if;
     set column_descr = cname;
     if is_geometry(ctype) then
         set column_descr = concat('astext(',cname, ')');
     end if;
     set col_list = concat(col_list, column_descr);
 end loop;
 return concat('SELECT ', col_list, ' INTO OUTFILE ', "'",
     p_file_name, "'",
     ' FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY "''" ESCAPED BY ''\\\\'' ',
     ' FROM ', p_db_name, '.', p_table_name);
end //

drop function if exists is_geometry //
create function is_geometry (ctype varchar(50))
returns boolean
deterministic
begin
 return ctype in (
     'geometry',             'polygon',      'linestring',
     'point',                'curve'   ,     'surface',
     'geometrycollection',   'multipoint',   'multicurve',
     'multilinestring',      'multisurface', 'multipolygon');
end//

delimiter ;

After that, I had a quick way of exporting data. All I have to do is a select:

desc test_gis;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | NO   | PRI |         |       |
| sometext   | varchar(100) | YES  |     | NULL    |       |
| somemore   | varchar(100) | YES  |     | NULL    |       |
| gis1       | polygon      | NO   | MUL |         |       |
| gis2       | linestring   | NO   | MUL |         |       |
+------------+--------------+------+-----+---------+-------+

SELECT gis_dump_query(database(),'test_gis','/path/dumpfile.csv') as q\G
*************************** 1. row ***************************
q: SELECT id,sometext,somemore,astext(gis1),astext(gis2) INTO OUTFILE '/path/dumpfile.csv' FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY "'" ESCAPED BY '\\'  FROM mydb.test_gis

and I get a ready to use query to export my data. But of course, instead of cutting and pasting, I could go the whole nine yards, with another simple stored procedure.

drop procedure if exists dump_gis_table //

create procedure dump_gis_table (
 p_db_name     varchar(50),
 p_table_name  varchar(50),
 p_file_name   varchar(100)
)
reads sql data
begin
 set @_dump_query = gis_dump_query(p_db_name, p_table_name, p_file_name);
 prepare dump_query from @_dump_query;
 execute dump_query;
 deallocate prepare dump_query;
 set @_dump_query = null;
end //

Now, the task is even simpler:

call dump_gis_table(database,'test_gis','/path/dumpfile.csv');