loadshp from python

[Alex Mandel]

I'm looking for ideas about how I could use the .loadshp macro from a
python application. Loading the spatialite extension was no problem but
I don't see any way to trigger a macro.

Functions work fine since they can be executed as SQL.

Ideas:
1. find some way to call the function via SQL
2. python bindings for spatialite
3. create a file with my operations and trigger an automated command
line execution
4. Rewrite the .loadshp function into a python function that can be run
via SQL (see http://initd.org/pub/software/pysqlite/doc/usage-guide.html)

Any have any tips or leads on a good way to do this?

Thanks,
Alex

[markb]

In VBA for .dumpshp I'm using your method number 3 via a sql text file
and the Shell function. I normally don't use the SpatiaLite EXE
directly, since most of my access is through ADO with the SQLite ODBC
driver (with the SpatiaLite dll loaded), but in this instance I use it
for command line execution.

[Keith]

Alex,

I was able to execute the .loadshp macro from Python. This is what
worked for me.

I created a text file named cmd.sql containing this text ".loadshp
testshp testshp CP1252"

From Python I ran this:

os.system('spatialite.exe my.db < cmd.sql')

You could then query the .table names to be sure the shapefile
imported correctly.

Keith

On Jan 6, 1:57 pm, Alex Mandel <tech_...@wildintellect.com> wrote:
> I'm looking for ideas about how I could use the .loadshp macro from a
> python application. Loading the spatialite extension was no problem but
> I don't see any way to trigger a macro.
>
> Functions work fine since they can be executed as SQL.
>
> Ideas:
> 1. find some way to call the function via SQL
> 2. python bindings for spatialite
> 3. create a file with my operations and trigger an automated command
> line execution
> 4. Rewrite the .loadshp function into a python function that can be run

> via SQL (seehttp://initd.org/pub/software/pysqlite/doc/usage-guide.html)

[Alex Mandel]

Ya, I'll go that route for now just because I need it working asap but
in the long run we should investigate Options 1 and 2. 3 is just a hack
that doesn't allow for much error catching and requires several extra
steps where potential errors could creep in.

I didn't want to add this dependency but I might use gdal to parse the
shapefile in python and feed that to an insert loop.

Thanks,
Alex

[Keith]

Alex,

That seems like a lot of work to construct the Sqlite table and
individually add the geometry and attributes. Why not build a python
function that you can pass the shapefile path, database path and table
name. The function will verify the paths, drop the table if needed,
run .loadshp, compare the new table row count to the shapefile count
and return an error code. Just a thought.

Keith

[Alex Mandel]

Well, you were right of sorts. It was easier than I thought to use
python. I forgot about virtual shapefiles until I read another thread.

Here's a snippet of code that seems to work assuming you pass the right
parameters and use pysqlite.

cur = con.cursor()
vtsql = ("CREATE VIRTUAL TABLE %s USING VirtualShape('%s', '%s', '%s');"
% (vtshpname, shpfile, coding, prj))
result1 = cur.execute(vtsql)
vtsql = ('CREATE TABLE %s AS SELECT * FROM %s' % (shpname, vtshpname))
result2 = cur.execute(vtsql)
#Recover spatial column
vtsql = ("SELECT RecoverGeometryColumn('%s','Geometry',prj,'POINT',2)" %
(shpname))
result3 = cur.execute(vtsql)

Alex