How to dump a table to Shape (without loosing int type) and to Spatialite?

[Stefan Keller]

Hi all,

Given this table:

spatialite> pragma table_info(plz_po2);
cid|name|type|notnull|dflt_value|pk
0|uid|INT|0||0
1|plz|INT|0||0
2|ortsname|TEXT|0||0
3|area|INT|0||0
4|z_studis|INT|0||0
5|geom|POLYGON|1|''|0

And data like this:
spatialite> select * from plz_po2 order by 1 limit 3;
uid|plz|ortsname|area|z_studis|geom
3692|8003|Zürich|1420242|25|
3637|8044|Zürich|2482381|2|
3637|8044|Gockhausen|2482381|2|

1. How can I dump that table to Spatialite format?
I've found dump to SQL, to Shape etc. but not to .sqlite/.spatialite...

2. When I dump that table to shapefile, like this:
.dumpshp plz_po2 geom plz_po2 CP1252
... the resulting shapefile hast field (4) z_studis with "C 1" meaning
Character of lenght 1 although this field is INT.
What am I doing wrong?

Yours, Stefan

[a.fu...@lqt.it]

Hi Stefan,

> 1. How can I dump that table to Spatialite format?
> I've found dump to SQL, to Shape etc. but not to
> .sqlite/.spatialite...
>

I suppose you are intending exporting an SQL dump script.
there is absolutely no reason to implement something like
this once considered the sqlite/spatialite architecture;
a DB-file already is a single file, so there is very little
scope in exporting an SQL dump.
directly exporting a DB-file is much more direct and safe.

you can eventually transfer a whole table (or even more tables)
between two DB-files [via the ATTACH DATABASE mechanism and
CREATE TABLE AS SELECT ...];
then you can easily transfer the output DB-file itself from
another location, may be a remote one.

> 2. When I dump that table to shapefile, like this:
> .dumpshp plz_po2 geom plz_po2 CP1252
> ... the resulting shapefile hast field (4) z_studis with "C 1"
> meaning
> Character of lenght 1 although this field is INT.
> What am I doing wrong?
>

I cannot confirm this; I've just checked your sample data, and I
get "z_studis N,2,0" (number, 2 integer digits, no decimals).

please note: I'm using splite 4.1.0-RC and sqlite 3.7.17; it could
probably be different using earlier versions of both libraries, because
sqlite doesn't support at all "strong datatypes" and practically tends
to ignore any datatype declaration.
what SQLite really does is simply attempting to to do its best effort
so to ensure a reasonable "type affinity" by applying heuristic
assumptions (and this obviously could change from version to version).

anyway you could check the "real" datatype of your stored values
by using the TypeOf() SQL function. e.g.

SELECT TypeOf (z_studis)
FROM plz_po2;

bye Sandro


--
Il messaggio e' stato analizzato alla ricerca di virus o
contenuti pericolosi da MailScanner, ed e'
risultato non infetto.

[Stefan Keller]

Hi Sandro

Thanks for the help.

2013/5/22 <a.fu...@lqt.it>:

> Hi Stefan,
>> 1. How can I dump that table to Spatialite format?
>> I've found dump to SQL, to Shape etc. but not to .sqlite/.spatialite...
>
> I suppose you are intending exporting an SQL dump script.

No, I'd like to dump to another binary spatialite file which only
contains this table.

Yours, Stefan


2013/5/22 <a.fu...@lqt.it>:

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

[JaakL]

You can attach several databases in one CLI session and do queries with both of them. Something like following should work fine with sqlite in general at least. You may need to recreate spatial metadata/indexes.

spatialite

>attach db1.db as db1;

>attach db2.db as db2;

>create table db1.copytable as select * from db2.sourcetable;

>.q

/Jaak