help for multiple import shape files into the same spatialite database

[ugo montorsi]

Good morning,
I wanted to know if there is a way to create a script for multiple import shape files into the same spatialite database.
Thanks for your help
Greetings,
Ugo

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

On Fri, 29 Apr 2016 02:50:55 -0700 (PDT), ugo montorsi wrote:
> Good morning,
> I wanted to know if there is a way to create a script for multiple
> import shape files into the same spatialite database.
>

Ciao Ugo,

you are absolutely free to create a single DB-file containing an
impressively high number of GeoTables: sometimes I've personally
tested some DB containing several hundreds GeoTables and anything
worked in the smoothest way.

basically you can choose between two possible alternative ways
in order to load one or more Shapefiles into some DB-file.

1. you can use the SpatiaLite GUI tool; it directly supports
an user friendly "load Shapefile" dialog, so you simply
have to repeatedly call it once for each SHP to be imported.
this surely is the easiest and more intuitive way, but it
could quickly become boring if you have to import many
tenth or hundredth shapefiles.

2. you can use the shell and the CLI tool; in this case
writing some SQL script can effectively help to simply
and automate the whole process. A SQL script just is
a plain text file, so you can easily write and check
your own script by using any text editor.

step#1:
==========================================================
write your SQL script, something like this:

--
-- initializing all spatial metadata tables
--
SELECT InitSpatialMetadata(1);

--
-- importing the first SHP (buildings)
--
SELECT ImportSHP('C:/myshapes/buildings', 'buildings',
'CP1252', 3003);

--
-- importing a second SHP (roads)
--
SELECT ImportSHP('C:/myshapes/roads', 'roads',
'CP1252', 3003);

--
-- importing a third SHP (railways)
--
SELECT ImportSHP('C:/myshapes/railways', 'railways',
'CP1252', 3003);

a. lines starting with two hyphens simply are comments
b. the ImportSHP() SQL function will attempt to import
an external Shapefile
c. the first argument passed to ImportSHP() is the absolute
or relative path of the Shapefile (note well: you must
omit any .shp, .shx or .dbf suffix)
d. the second argument is the name of the DB table to
be created
e. the third argument is the charset encoding adopted by
the SHP; I'm assuming that your Shapefiles have been
very probably created on Windows in Italy, so CP1252
aka Windows Latin 1 should be the most probable setting.
f. and finally the fourth argument is the SRID; I'm
assuming 3003 aka Monte Mario / Italy zone 1
g. usually SQL scripts are identified by a ".sql" suffix,
but it's not a strict mandatory requirement.


step#2:
==========================================================
executing the SQL script.
just open a command shell and type:

export "SPATIALITE_SECURITY=relaxed"
spatialite mynewdb.sqlite <mysqlscript.sql

a. first you have to set the environment
variable SPATIALITE_SECURITY so to enable
the CLI tool to access local files.
b. then you simply have to launch the CLI
tool by passing the path of the DB-file
to be opened/created; the SQL script will
simply be assigned as the standard input file.

NOTE:
if you are working on a Windows platform and not
on Linux the syntax required in order to set an
environment variable is slightly different:

SET SPATIALITE_SECURITY=relaxed
spatialite mynewdb.sqlite <mysqlscript.sql

bye Sandro

[Roberto Angeletti]

Sandro,

what is the syntax to export a table in SHP format ?

And, there is a way to import and export SHP doing quey from DOS, instead of SpatiaLite_gui ?

Thank you

Roberto

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

On Fri, 29 Apr 2016 14:46:28 +0200, Roberto Angeletti wrote:
> Sandro,
>
> what is the syntax to export a table in SHP format ?
>

Ciao Roberto,

please consult the standard documentation [1][2] about these two
SQL functions:

ImportSHP()
ExportSHP()

[1] http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.3.0.html
[2] http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.4.0.html

> And, there is a way to import and export SHP doing quey from DOS,
> instead of SpatiaLite_gui ?
>

certainly yes, because the SQL core implemented by libspatialite
is exactly the same on both the GUI and the CLI tools.
the GUI tool simply adds few user friendly "visual gadgets",
that simply are a thin "visual" layer (dialogs or wizards)
encapsulating the standard SQL functions.

the most striking difference between the GUI and the command
line simply is that the CLI for security reasons always
requires an explicit authorization to access external files
on the filesystem, so you are always expected to appropriately
set the SPATIALITE_SECURITY=relaxed environment variable
before launching the command line tool.

bye Sandro