How to export a spatialite database with geometry columns as text?

[Dominique Pellé]

Hi

The command line tool 'spatialite' has a command to dump
the content of a spatialite database as SQL statements:

spatialite> .dump

But the geometry columns are dumped in the internal spatialite format.
For example:

INSERT INTO foo VALUES(..., X'0001FF...40FE');

I'd prefer the .dump command to dump the geometry columns as text like this:

INSERT INTO foo VALUES(..., GeomFromText('POINT(123.45 543.21)'));

Is this possible?

It would make it possible export a spatialite database and import
it into another database such as MySQL.

Thanks
Dominique

[Adam Estrada]

Dominique,

Have you tried running the AsText() function on your geom column? Try
that and see if it helps.

Adam

2010/8/6 Dominique Pellé <dominiq...@gmail.com>:

> --
> You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
> To post to this group, send email to spatiali...@googlegroups.com.
> To unsubscribe from this group, send email to spatialite-use...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/spatialite-users?hl=en.
>
>

[Dominique Pellé]

Hi

I know about AsText(...) but it's a quite inconvenient to use for
dumping an entire database which may contain several tables
with several geometry columns.

The best I found so far is this:

spatialite> .mode insert
spatialite> select *, AsText(geometry) from myTable;
INSERT INTO table VALUES(..., 'POINT(123.45 543.21)');
...

But then I have to write a script to transform the output of
the command to:

- substitute "INSERT INTO table" with "INSERT INTO myTable"
- substitute things like 'POINT(123.45, 543.21)' with
GeomFromText('POINT(123.45, 543.21)')
- do this it for all tables, figuring out what are the geometry columns
to be able to put them in the select statement.

There might (or should) be a simpler way.

Regards
Dominique

[Keith]

Dominique,

You might want to try this SQL tool. It has good import/export
functions that transfer data from database to database. It is a free
tool written in Java.

http://www.sql-workbench.net/

Keith

On Aug 6, 9:16 am, Dominique Pellé <dominique.pe...@gmail.com> wrote:
> Hi
>
> I know about AsText(...) but it's a quite inconvenient to use for
> dumping an entire database which may contain several tables
> with several geometry columns.
>
> The best I found so far is this:
>
> spatialite> .mode insert
> spatialite> select *, AsText(geometry) from myTable;
> INSERT INTO table VALUES(..., 'POINT(123.45 543.21)');
> ...
>
> But then I have to write a script to transform the output of
> the command to:
>
> - substitute "INSERT INTO table" with "INSERT INTO myTable"
> - substitute things like 'POINT(123.45, 543.21)' with
>   GeomFromText('POINT(123.45, 543.21)')
> - do this it for all tables, figuring out what are the geometry columns
>   to be able to put them in the select statement.
>
> There might (or should) be a simpler way.
>
> Regards
> Dominique
>

> On Fri, Aug 6, 2010 at 4:48 PM, Adam Estrada <estrada.a...@gmail.com> wrote:
> > Dominique,
>
> > Have you tried running the AsText() function on your geom column? Try
> > that and see if it helps.
>
> > Adam
>

> > 2010/8/6 Dominique Pellé <dominique.pe...@gmail.com>:

> >> Hi
>
> >> The command line tool 'spatialite' has a command to dump
> >> the content of a spatialite database as SQL statements:
>
> >> spatialite> .dump
>
> >> But the geometry columns are dumped in the internal spatialite format.
> >> For example:
>
> >> INSERT INTO foo VALUES(..., X'0001FF...40FE');
>
> >> I'd prefer the .dump command to dump the geometry columns as text like this:
>
> >> INSERT INTO foo VALUES(..., GeomFromText('POINT(123.45 543.21)'));
>
> >> Is this possible?
>
> >> It would make it possible export a spatialite database and import
> >> it into another database such as MySQL.
>
> >> Thanks
> >> Dominique
>
> >> --
> >> You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
> >> To post to this group, send email to spatiali...@googlegroups.com.
> >> To unsubscribe from this group, send email to spatialite-use...@googlegroups.com.

> >> For more options, visit this group athttp://groups.google.com/group/spatialite-users?hl=en.

[a.furieri]

Hi Dominique,

thank a lot for submitting your .dump patch.
anyway IMHO there are some considerations to
be further examined about dump-files:

a) dumping a DB is a common practice for client-server
DBMS: actually, this one is the only way allowing
to transfer data between different locations and/or
to perform a backup.
and very often (e.g. PostgreSQL) you must dump/load
data when performing a major version update.

b) but in the SQLite/SpatiaLite peculiar implementation,
dumping the DB simply is meaningless.
a complete DB merely is a single file: and this file
has a cross-platform architecture.
so it is much more easier to directly copy the whole
DB-file between different locations.
I don't see any practical usage for a dump file in
SQLite/SpatiaLite.

c) in my personal experience, dump files aren't at all
useful to cross-transfer data between different DMBS,
simply because MySQL, Postgres, SQLite ... have their
own 'dialectal' SQL idiosyncrasies.
so you'll discover that (for one reason or another)
any attempt to load a dump-file intended i.e. for
Postgres will fail on MySQL and so on.
and a dump-file generated by SpatiaLite will fail
for sure to load on MySQL, because there are lots
of incompatibilities between them, most notably
in geometry handling.

d) exporting Geometries as WKT is possible, but it has
several strong disadvantages:
- you'll introduce some approximation in coordinate
values (decimal truncation), due to converting
doubles to text back and forth
- WKT requires a wide amount of disk space: don't be
surprised if the dump-file will require much more
space than the corresponding DB
- parsing lots and lots of WKT is a very time
consuming task at load time
- so using WKB instead seems to be a better choice

e) last but not least: the actual .dump implementation
(hexadecimal representation of any blob value) fully
preserves Geometry values.
your proposed patch on the other side completely miss
to support SRID values (they simply vanish), thus
producing a completely useless output.
and correcting this doesn't appears to be feasible
within the current .dump implementation constraints
(which implements a very rude and basilar approach,
ignoring at all spatial metadata).

Conclusion:
I'm really sorry, but I'm forced to reject your proposed
patch:
- practical utility is very questionable
- it introduces an irreversible information loss (SRID),
and this in not acceptable.

Conclusion(2):
in a most general way you are completely right.
It's really a pity that we currently lack a good tool
supporting easy and painless data transfer of geographic
data between different DMBS: PostGIS -> SpatiaLite -> MySQL

Good new: this specific topic will be solved very soon,
because a "data exchange tool" will be supported in my
next-to-come project: I'm currently working on it.
Expected release time: Fall / Winter

bye Sandro

[Dominique Pellé]

a.furieri wrote:

Hi Sandro

Thanks for taking the time to respond in details. I'm new to
spatialite and I learned a I few things from your response.

For now, I think can still try to use my patch privately (possibly with
WKB instead of WKT to avoid losing precision). But in the longer
term, I look forward using your new "data exchange tool".

I indeed overlooked the vanishing SRID value since spatialite
stores SRID in the blob. I could in theory retrieve it from the blob
(4 bytes at offset #2) as described at:
http://www.gaia-gis.it/spatialite/spatialite-manual-2.3.1.html#t3.3
But since all my geometries use the same SRID, an option to specify
the SRID can also be a pragmatic short term solution until your "data
exchange tool" becomes available.

Regards
Dominique