Monday, December 19, 2005

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');

No comments: