SQL VirtualShape

[Joe]

Anyone,

I need to load shapefiles into a spatialite database from Visual
Studio 2008 C# code. The SQL commands do not work in VS 2008 and it
was suggested that I try them in the 2.4 Spatialite GUI. They work
better in the GUI but the still aren't 100%. The SRID Proj data is
there.

DROP TABLE IF EXISTS tmpTable;
CREATE VIRTUAL TABLE tmpTable USING VirtualShape('D:\SharpMap\src
\SharpMapDemo\bin\Debug\gis_data\Soils', 'CP1252', 3746);
This partially works in the GUI. It creates a table with all of the
fields from the Soils shapefile but it doesn't recognize the result as
a spatial table (globe icon) and there are no entries added the
geometry_columns table.

Also, using SQL how do you copy a spatial table to another table and
maintain the spatial qualities?
'vSoils' is a valid spatial table created with Load Shapefile within
the GUI.
DROP TABLE IF EXISTS Soils;
CREATE TABLE Soils AS SELECT * FROM vSoils;
SELECT AddGeometryColumn('tmpTable','Geometry',3746,'MULTIPOLYGON',2);

but this doesn't work. The data is there but the 'Geometry' field is
not recognized and the other spatial data is missing
(ggi_vSoils_geometry & ggu_vSoils_geometry ).

Thanks,
Joe

[James Card]

On Mon, 14 Dec 2009 07:40:32 -0800, Joe <joe.w...@gmail.com> wrote:

> Also, using SQL how do you copy a spatial table to another table and
> maintain the spatial qualities?
> 'vSoils' is a valid spatial table created with Load Shapefile within
> the GUI.
> DROP TABLE IF EXISTS Soils;
> CREATE TABLE Soils AS SELECT * FROM vSoils;
> SELECT AddGeometryColumn('tmpTable','Geometry',3746,'MULTIPOLYGON',2);
>
> but this doesn't work. The data is there but the 'Geometry' field is
> not recognized and the other spatial data is missing
> (ggi_vSoils_geometry & ggu_vSoils_geometry ).

I was just browsing
<http://www.gaia-gis.it/spatialite-2.4.0/spatialite-sql-2.4.html#p16>.
Does the RecoverGeometryColumn function accomplish what you want?

--
James Card -- <http://jdcard.com/>
Often there are several ways to understand a given set of
facts; some of them may be more useful than others.

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

Hi Joe,

1) VirtualShape creates a VIRTUAL TABLE, that isn't
at all an 'ordinary' table, but a completely different
thing.
So you cannot expect the GUI tool to show the usual
"world" icon: a different icon is used to mark
VirtualShapes [world+chain]

2) you cannot expect to found a corresponding entry in
GEOMETRY_COLUMNS for a VirtualShape.
This is for 'normal' tables, and a VIRTUAL TABLE
isn't at all 'normal'.
there is no need to specify Metadata for VirtualShape,
because they are 'internally' stored.

Please, check better the documentation about the Metadata
tables SPATIAL_REF_SYS and GEOMETRY_COLUMNS:
http://www.gaia-gis.it/spatialite/spatialite-tutorial-2.3.1.html

------------

DROP TABLE IF EXISTS Soils;
CREATE TABLE Soils AS SELECT * FROM vSoils;
SELECT AddGeometryColumn('tmpTable','Geometry',3746,'MULTIPOLYGON',2);

1) obviously this cannot work, because AddGeometryColumn()
will attempt to CREATE a new column.
but 'Geometry' already exists in this case, because you've
used CREATE TABLE ... AS SELECT ..., and this will clone any
existing column.

2) in this case [already existing and populated column]
you have to call RecoverGeometryColumn() instead.

Please note: using the GUI tool you can perform
all the above steps in an absolutely easy way, simply
clicking the appropriate menu item.

bye,
Sandro

[markb]

I don't know about your first example, that seems like it should work
to me, maybe its a data anomaly?

With the second example, if 'vSoils' is truly a full-on spatial table,
then it should already have a 'Geometry' column with the globe icon
(though it doesn't have to named 'Geometry', your choice on import).
So using the 'AddGeometryColumn' fx on the new Table 'Soils" wouldn't
register the already created geom column (from "... Select * From
vSoils"). Use the 'RecoverGeometryColumn' fx instead. It won't
necessarily be a perfect copy schema wise (e.g. data types are boiled
down to SQLite's basic types and you lose all constraints), but one is
usually just after the data itself. Good Luck -Mark

[markb]

Guess I was beat to the punch (& misread your first question). Thanks
Sandro & Jason.

[Joe]

James,

I must have found the same discussion as you did because I did try
RecoverGeometryColumn() and I thought I posted the results.

This works in the GUI -- vSoils was created using Load Shapefile in
the GUI

DROP TABLE IF EXISTS Soils;
CREATE TABLE Soils AS SELECT * FROM vSoils;

SELECT RecoverGeometryColumn('Soils','Geometry',
3746,'MULTIPOLYGON',2);

The Create/Select copies the data but the PK_UID is not set to the
PRIMARY KEY. Is there any way to make PK_UID the primary key after the
fact?

Joe

[markb]

I mean James, sorry. (maybe I need to start drinking coffee, got to
love Monday)

[markb]

Joe,
You will need to create your table differently (i.e. not with "As
Select * From Table"), since 'Alter Table' doesn't currently support
the functionality you need (http://www.sqlite.org/
lang_altertable.html).

[Joe]

Sandro,

I did find the RecoverGeometryColumn() post and it works.

There doesn't seem to be an easy way to create a Spatialite table from
a shapefile using SQL code (C#). I'm using SharpMap to display the
layers and have just incorporated their Spatialite Data Provider. I
hope to find some alternatives there.

Thanks,
Joe

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

Hi Joe,

if you wish to preserve the Primary Key [and any
other special constraint], never use:

CREATE TABLE ... AS SELECT ...

this is because SQLite in this case will simply
clone the very 'basic' column defs while creating
the new table.

a by far better way is the following one:

CREATE TABLE ... (...) [yes, do it yourself ...]
SELECT AddGeometryColumn(....)

once you've created the table you can then use:
INSERT INTO ... (....) SELECT ... FROM ...

bye,
Sandro

[Joseph Wolter]

Sandro,

I don't think creating the table manually is an option for me. The
import has to be very general and I will not know the structure of the
shape files the user will be importing. Rather than build the Data
Provider code myself, I am looking at the SharpMap Spatialite Data
Provider for my needs.

Time will tell.

Thanks,
Joe

[markb]

Would it be possible to use the sql text from the sqlite_master table
by replacing "CREATE TABLE OldTableName" with "CREATE TABLE
NewTableName", then executing that modified sql. Then running your
"INSERT INTO NewTableName SELECT * FROM OldTableName", and finally
doing a RecoverGeometryColumn()? You'll have to be pretty generic
with your replacement, but maybe this is a possible method,
particularly if your Shapefiles are always imported consistently (e.g.
always the same "CREATE TABLE TableName (" template). You wouldn't
even need to know the column list, which is optional with INSERT INTO,
except for the Geometry field of course. Just a brainstorm. -Mark

[Joseph Wolter]

Mark,

The problem is that the virtual table created from my C# code doesn't
carry any of the shape file data with it just a PKUID and Geometry.
If I execute the same command from the Spatialite GUI I get shape file
data as well.

The Soils.dbf fields are
"areasymbol" TEXT,
"spatialver" INTEGER,
"musym" TEXT,
"muname" TEXT,
"mukey" TEXT

After executing:

DROP TABLE IF EXISTS tmpTable;
CREATE VIRTUAL TABLE tmpTable USING
VirtualShape('D:\SharpMap\src\SharpMapDemo\bin\Debug\gis_data\Soils',
'CP1252', 3746);

SELECT RecoverGeometryColumn('tmpTable','Geometry',3746,'MULTIPOLYGON',2);

The string stored in 'sql' field for the virtual table (tmpTable) in
the sqlite_master table is:
CREATE VIRTUAL TABLE tmpTable USING VirtualShape(' <...Path to
shape file...>', 'CP1252', 3746)

The columns in the virtual table (tmpTable) are:
"PKUID" INTEGER,
"Geometry" MULTIPOLYGON

The create statement for the virtual table created using the GUI "Load
Shapefile" is:
CREATE TABLE "vSoils" (
"PK_UID" INTEGER PRIMARY KEY AUTOINCREMENT,
"areasymbol" TEXT,
"spatialver" INTEGER,
"musym" TEXT,
"muname" TEXT,
"mukey" TEXT,
"Geometry" MULTIPOLYGON )

Thanks,
Joe


theTableName

> --
>
> 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.
>
>
>

[Felix Obermaier]

Hello Joe,

if you got SharpMap to work, let me point you to the FormatConverter
Project in the demo section, which can add shapefiles with dbf to
spatialite2 databases. Atm it does not work in batch mode. If you want
that, you might consider the development version of ogr2ogr.

Hth FObermaier

[markb]

I guess I'm not really following you. From your first post I just
figured you were trying to 'load' a shapefile into a database, not
specifically trying to link a shapefile as a virtual table. Just
thought you where trying out some import methods that might possibly
work for you (of course in a general sense), virtualshape being one of
them. Then being able to copy said table without ruining the spatial
metadata (of course also generically). The Load Shapefile command
doesn't create a virtual table, it creates an actual table (with a
Create Table statement that I thought could be the basis for a proper
copy operation). I guess you can't use loadshp from SharpMap (maybe
that is what I was missing) or for some other reason? I know its not
preferred, but couldn't you Shell out from C# to use the loadshp
command as a last resort? -Mark

[Joseph Wolter]

Mark,

I tried the shell game but didn't get too far. When a stumbled across
the VirtualShape() SQL statement I thought this would be much cleaner
- but it doesn't work.

I'm going to look at SharpMap 2.0 Spatialite Data Provider next to see
if I can get that working. If not I'll revisit my shell code.

Thanks,
Joe

[a.furieri]

Hi Joe,
if I understand well, this is the state-of-the-art:

a) you are able to succesfully connect VirtualShape
from the GUI tool.
b) but any attempt to connect VirtualShape from C#
fails, because you always get an empty VIRTUAL TABLE,
showing only two columns: PKUID, Geometry

this make no sense at all:
hypothesis A] there is some obscure bug into the .NET
connector specifically related with VirtualShape:
not so easily credible.
hypothesis B] there is something wrong into your own
C# code: more like to be.

Please, can you carefully check if the strings stored
in 'sql' column / 'sqlite_master' table are *EXACTLY*
identical in both cases ?
I mean: GUI tool vs. C#

I strongly suspect you'll then discover some difference
exists, after all. look carefully if the PATH is the one
you're expecting to be.

My 5 cents bet: [a really common issue related with
Microsoft environments]
I suspect you've used a string path coded as: "C:\dir\file"

but in C syntax the reverse slash '\' is a special marker,
so you'll actually get: "C:dirpath"
not at all the path you are expecting to get.

the right way to encode C string paths under windows is:
"C:\\dir\\file"
or
"C:/dir/file"

bye,
Sandro

[Joseph Wolter]

Sandro,

You were partially right.

I was using a relative path to the shape file. I tested
@"gis_data\Soils" and @".\gis_data\Soils" and VirtualShape()
apparently didn't like that. When I used the full path to the file I
got all of the data.
theShpFile = @"D:\SharpMap\src\SharpMapDemo\bin\Debug\gis_data\Soils";
cmd.CommandText = "CREATE VIRTUAL TABLE tmpTable USING
VirtualShape('" + theShpFile + "', 'CP1252', 3746)";
Note: In C# the @ will generate

"D:\\SharpMap\\src\\SharpMapDemo\\bin\\Debug\\gis_data\\Soils"

After the succesfull creation of the vitrual shape table I execute:
cmd.CommandText = "SELECT
RecoverGeometryColumn('Soils','Geometry',3746,'MULTIPOLYGON',2);";

The end result is a spatial table and the only thing missing is the
primary key. There is a "PKUID" field in the temporary virtual table
but it is not designated as a primary key. The vSiols table created
with Load Shapefile has a "PK_UID" field which is designated a primary
key. "PKUID" and "PK_UID" have the identical data.


So to summarize:
1. Using SharpMap v0.9, Spatialite SQL VirtualShape() was the only
straight forward way I've found to read shape files in code.
a. I was unable to add spatial characteristics to the Virtual
Table using RecoverGeometryColumn()
1) The geometry field was tagged as geometry but NO
ggi_tmpTable_Geometry & ggu_tmpTable_Geometry fields
2) An entry was added to geometry_columns
b. I copied the Virtual table to a Soils Table
c. Finally, I added the spatial Geometry to the Soils table using
RecoverGeometryColumn()
d. ** Dropping the tmpTable didn't clean up the geometry_columns table

2. You might use the SpatialTool .Loadshp ... command in a shell.
a. I had some problems with this too that I haven't resolved.
b. Requires delivering spatialite_tool.exe which I'd rather NOT do.

3. You might use the SharpMap v2.0 - Not stable - Spatialite Data Provider.
a. May be added to SharpMap v0.9 and built
b. Download and build SharpMap v2.0

** Dropping any spatial table with code doesn't clean up the
geometry_columns table.

Thanks,
Joe

[markb]

Just a quick note pertaining to item 1.a.1):
VirtualShape creates a ReadOnly VirtualTable, so those spatial
metadata triggers aren't useful anyhow. Also, I don't think Virtual
Tables can even have triggers (or indexes).

and **:
It does seem like when dropping a spatial table that the corresponding
geometry_columns table entry should be removed, but I don't believe
this could be automatic from standard SQLite sql. I can certainly see
how the GUI would do it, or a custom program, but not via sql
triggers. Particularly with VirtualTables (as stated above), but even
with standard tables, since there aren't schema change triggers.

I'm glad you have a working solution now (or closer to it).

-Mark

[Joseph Wolter]

Mark,

If I delete the 'Soils' table in the GUI the 'geometry_columns' table
is cleaned up automatically.

The problem I was having while testing was if the 'geometry_columns'
table had an entry for 'Soils' the
RecoverGeometryColumn('Soils','Geometry',3746,'MULTIPOLYGON',2); query
wouldn't create the ggi_Soils_Geometry & ggu_Soils_Geometry triggers
for the 'Soils' table.

So I now do "DELETE FROM geometry_columns WHERE f_table_name='Soils';"
after I drop the table.

Joe

[markb]

Oh, your summary said you were "unable to add spatial characteristics
to the Virtual Table using RecoverGeometryColumn()", you meant to say
a normal Table, I see (that changes everything). I got that the GUI
cleans up automatically, and that you would need to do it yourself
(i.e. your "DELETE FROM...") in your own code. Best Wishes -Mark