Programmatically Load a Shapefile

[silasmerlin]

Heloo all-

I am writing a Python program that will convert Shapefiles to KMLs. I
was wondering if anyone has a good idea for programmatically adding a
shapefile to a Spatialite database; i.e, how can I programmatically
imitate the following command line command:

.loadshp shape_regions NewRegions CP1252

I am not sure if this is something for a the os module, or something
that a cursor could perform, or if I should invoke the spatialite.exe
(which I would prefer to avoid).

thanks for any and all suggestions!
-Silas

[silasmerlin]

I decided I'll be using ogr to read the shapefile's geometry instead
of Spatialite. Still,
I'm interested in figuring out how to load a shapefile from the
program, so don't be shy!

[Keith]

Silas,

This topic was discuss a while ago. Here are a few ideas that might be
helpful.

http://groups.google.com/group/spatialite-users/browse_thread/thread/4bcdafd93f3c9c93/93cfdffaeba4bedb?lnk=gst&q=loadshp#93cfdffaeba4bedb

Keith

[silasmerlin]

That's great, Keith! Thanks for the link. It's even easier than I
thought. i think I will just use Spatialite instead of ogr. Thanks
again!

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

Hi Silas,

just few words about the "how to load shapefiles" problem:
this one is a frequently asked question, so I think examining
it in some depth may be useful for many developers.

a valid shp is a "triple" file:
-------------------------------
- the .shp member stores geometries
- the .dbf member stores attribute values
- the .shx member set correspondences between
.shp and .dbf entities

Accordingly to this, reading a shapefile isn't a trivial
task: shapefiles architecture is quite obsolescent, and
is intrinsically weak 'by design'.
Anyway, they are so widely available that they are the
de-facto universal GIS interchange format.
If you are interested to better understand shapefiles,
you can consult the 'shapefiles.c' source code you can
find within 'spatialite-tools' sources.


common shp issues:
------------------
- malformed paths: lots of shps only works under Windows,
not under Unix/Linux. this is because the WinOz filesystem
is case insensitive, but Linux filesystem is case sensitive
instead.
- access permissions: quite often not at all an issue under
Windows, but sometimes a real trap under Linux.
- crazy geometries: it's quite common to found odd
geometries in shapefiles:
* polylines containing a single point [not a segment]
* polygons containing less than 4 points [not a polygon]
* unclosed rings
- obscure charset encoding: at least in the US and in Europe
you can assume the .dbf to be CP1252 encoded, but sometimes
this isn't true.
Guessing the original charset (by try and error) may be a
real headache.
- and after all, it's not at all uncommon to find heavily
corrupted or malformed shapefiles: they work with some specific
GIS application, but cannot be safely opened using *ANY* GIS app,
because they are intrinsically broken.


=================================
useful suggestions:

if you suspect you have to cope with some invalid/broken/odd
shapefile, please use the 'shp_doctor' CLI tool.
this will help you a lot in understanding why your shapefile
cannot be loaded [and hopefully, it can help you in fixing
any related issue]
=================================


using .loadbf
-------------
this one IS NOT an SQL command: simply is a keyboard
shortcut internally implemented by the 'spatialite'
CLI tool.
Really useful to import shapefiles by hand, one at
each time, but not usable in a programmatic way.
Analogously, you can use the GUI tools to import
shapefiles, with similar limitations.


using 'spatialite_tool'
-----------------------
this one is a plain SHELL COMMAND: so I suppose any
developer can easily invoke it using ANY programming
language.
and/or you can invoke it from shell scripts under
Unix/Linux, or from .BAT files under Windows.


using VirtualShape
------------------
the main advantage in using VirtualShape is that it's
PURE SQL. so it's really easy to take full advantage from
VirtualShape when importing external shapefiles:
- you can freely use VirtualShape in sql scripts.
- and you can it in any programming language supporting
a sqlite+spatialite connection.


well-known VirtualShape limits:
-------------------------------
- data access is quite slow
- spatial indexing isn't supported
- JOIN ops involving a VIRTUAL TABLE misbehaves on SQLite

So the best thing a developer can do with any VirtualShape
is to immediately transfer data into a 'normal' SQL table.


creating a 'normal' table form a VirtualShape: A]
--------------------------------------------------
CREATE VIRTUAL TABLE ... USING VirtualShape(..);
CREATE TABLE ... AS SELECT * FROM ...;
SELECT RecoverGeometryColumn(...);

note well: RecoverGeometryColumn() is absolutely
required in order to get a 'spatial' table.
anyway I discourage you to implement your own code
this way, because CREATE TABLE AS SELECT sucks under
many aspects.


creating a 'normal' table form a VirtualShape: B]
--------------------------------------------------
CREATE VIRTUAL TABLE ... USING VirtualShape(..);
CREATE TABLE (...); /* omitting geometry */
SELECT CreateGeometryColumn(...);
INSERT INTO ... (...) SELECT ... FROM ...;

this one way is by far a better choice.

yes, there is some extra complexity because you firstly
have to programmatically discover which one columns
are actually stored within the VirtualShape.
But this one is an absolutely trivial task if you are
using any programming language: you just have to scroll
the resultset returned by: PRAGMA table_info(...);


I hope this will be useful for many :-)

bye,
Sandro

[silasmerlin]

As a follow up, I created the program and turned it into a Windows exe
with an MSI available at http://code.google.com/p/kmlkonverter/
The program allows the user to easily convert a shapefile into a
Google Earth KML.
Please test it out and let me know if it is useful for your own work.
I also included the scripts that I used to create the exe. If you like
what you see, let me know; if you know of a way to do something
better, let me know (I would have loved to have the program
automatically determine the SRID of the input shapefile, for example,
but could not figure that one out). Sandro, thanks for the all that
SpatiaLite allows me to do!