Create Spatialite database from Shapefile, Join CSV table and Dump Shapefile - Problems
Noli Sicad
I am trying to create spatialite database from Shapefile, join CSV and
dump the newly created spatial layer i.e. table into new shapefile.
I want to load shapefile into spatialite database instead of using
VirtualShape since I want to move it around.
I got this error: "columns f_table_name, f_geometry_column are not
unique"
0
I only created 1 row / 1 polygon out of 101 polygons that I wanted.
What is wrong with my Steps in creating spatialite database, anything missing?
Please have a look on my log below.
Thanks in advance.
Noli
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
C:\spatialite-tools-win-x86-2.4.0\bin>spatialite VicMap10.sqlite
SpatiaLite version ..: 2.4.0 Supported Extensions:
- 'VirtualShape' [direct Shapefile access]
- 'VirtualDbf' [direct DBF access]
- 'VirtualText' [direct CSV/TXT access]
- 'VirtualNetwork' [Dijkstra shortest path]
- 'RTree' [Spatial Index - R*Tree]
- 'MbrCache' [Spatial Index - MBR cache]
- 'VirtualFDO' [FDO-OGR interoperability]
- 'SpatiaLite' [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.7.1, 23 September 2009
GEOS version ........: 3.2.0-CAPI-1.6.0
SQLite version ......: 3.6.22
Enter ".help" for instructions
spatialite> .tables
geom_cols_ref_sys property_mp virts_geometry_columns
geometry_columns spatial_ref_sys
geometry_columns_auth views_geometry_columns
spatialite> SELECT * FROM geometry_columns;
property_mp|Geometry|MULTIPOLYGON|XY|4326|0
spatialite> UPDATE property_mp SET Geometry = SetSrid(Geometry, 4326);
spatialite> SELECT RecoverGeometryColumn('property_mp', 'Geometry',4326, 'MULTIP
OLYGON', "XY");
RecoverGeometryColumn() error: "columns f_table_name, f_geometry_column are not
unique"
0
spatialite> SELECT RecoverGeometryColumn('property_mp', 'Geometry',4326, 'MULTIP
OLYGON', 2);
RecoverGeometryColumn() error: "columns f_table_name, f_geometry_column are not
unique"
0
spatialite> CREATE VIRTUAL TABLE planning USING VirtualText(planning.csv, CP1252
, 1, COMMA, DOUBLEQUOTE, ',');
spatialite>
spatialite> CREATE TABLE workplan AS SELECT * FROM property_mp JOIN planning
...> ON (property_mp.PR_PROPNUM = planning.PR_PROP_No);
spatialite> SELECT RecoverGeometryColumn('workplan', 'Geometry',4326, 'MULTIPOLY
GON', 2);
1
spatialite> .dumpshp workplan Geometry shape_workplan CP1252 POLYGON
========
Dumping SQLite table 'workplan' into shapefile at 'shape_workplan'
SELECT * FROM "workplan" WHERE GeometryAliasType("Geometry") = 'POLYGON' OR Geom
etryAliasType("Geometry") = 'MULTIPOLYGON' OR "Geometry" IS NULL;
Exported 1 rows into SHAPEFILE
========
spatialite> SELECT * FROM planning ORDER BY PR_PROP_No;
75|206114
77|206116
83|206276
48|206286
61|206289
6|206386
10|206392
11|206394
4|206401
19|206446
24|206454
26|206460
7|206461
31|206480
89|206676
25|206698
2|206712
9|206718
35|206736
5|206747
3|206752
93|206921
91|206952
60|206956
74|206985
72|206994
64|206999
73|207073
70|207077
51|207080
46|207086
39|207092
97|207156
68|207234
81|207237
94|207263
53|207276
43|207436
36|207445
22|207455
79|207496
80|207506
63|207513
65|207519
84|207524
71|207529
86|207535
87|207543
69|207779
98|208309
99|208311
67|208322
52|208327
45|208332
29|208872
15|208883
17|208885
32|208888
33|208890
20|208895
23|208903
82|209024
58|209029
37|209033
34|209035
96|209123
50|209135
90|209349
55|209356
49|209358
44|209360
95|209406
85|209409
66|209422
47|209429
41|209430
78|209524
76|209528
57|210017
42|210027
30|210034
21|210050
18|210063
12|210066
38|210097
54|210100
56|210104
59|210110
40|210375
13|210395
8|210399
28|224745
16|224933
27|225229
1|226072
88|226607
92|226608
62|226980
14|230218
0|563544
spatialite>
Noli Sicad
The previous log is not complete.
Here's the complete one (below).
Thanks.
Noli
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
C:\spatialite-tools-win-x86-2.4.0\bin>spatialite VicMap20.sqlite
SpatiaLite version ..: 2.4.0 Supported Extensions:
- 'VirtualShape' [direct Shapefile access]
- 'VirtualDbf' [direct DBF access]
- 'VirtualText' [direct CSV/TXT access]
- 'VirtualNetwork' [Dijkstra shortest path]
- 'RTree' [Spatial Index - R*Tree]
- 'MbrCache' [Spatial Index - MBR cache]
- 'VirtualFDO' [FDO-OGR interoperability]
- 'SpatiaLite' [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.7.1, 23 September 2009
GEOS version ........: 3.2.0-CAPI-1.6.0
SQLite version ......: 3.6.22
Enter ".help" for instructions
spatialite> .read init_spatialite-2.4.sql ASCII;
*** charset ERROR *** cannot convert from 'ASCII;' to 'UTF-8'
1
spatialite> .nullvalue NULL
spatialite> .headers on
spatialite> .mode column
spatialite> .tables
geom_cols_ref_sys geometry_columns_auth views_geometry_columns
geometry_columns spatial_ref_sys virts_geometry_columns
spatialite> .loadshp PROPERTY_MP property_mp CP1252 4326;
========
Loading shapefile at 'PROPERTY_MP' into SQLite table 'property_mp'
BEGIN;
CREATE TABLE property_mp (
PK_UID INTEGER PRIMARY KEY AUTOINCREMENT,
"PROP_PFI" TEXT,
"PR_LGAC" TEXT,
"PR_PROPNUM" TEXT,
"PR_PTYPE" TEXT,
"PR_TRANS" TEXT,
"PR_MULTASS" TEXT,
"PR_STAT" TEXT,
"PR_FQID" TEXT,
"PR_PFI_CR" DOUBLE,
"PROP_UFI" INTEGER,
"PR_UFI_CR" DOUBLE,
"PR_UFI_OLD" INTEGER,
"PROPV_PFI" TEXT,
"PV_BASEPFI" TEXT,
"PV_CNT_PFI" TEXT,
"PV_GRTYPE" TEXT,
"PV_FTTYPE" TEXT,
"PV_ZLEVEL" TEXT,
"PV_FTYPE" TEXT,
"PV_FQID" TEXT,
"PV_PFI_CR" DOUBLE,
"PROPV_UFI" INTEGER,
"PV_UFI_CR" DOUBLE,
"PV_UFI_OLD" INTEGER);
SELECT AddGeometryColumn('property_mp', 'Geometry', 4326, 'MULTIPOLYGON', 'XY');
COMMIT;
Inserted 59279 rows into 'property_mp' from SHAPEFILE
========
spatialite> UPDATE property_mp SET Geometry = SetSrid(Geometry, 4326);
spatialite> SELECT RecoverGeometryColumn('property_mp', 'Geometry',4326, 'MULTIP
OLYGON', "XY");
RecoverGeometryColumn() error: "columns f_table_name, f_geometry_column are not
unique"
RecoverGeometryColumn('property_mp', 'Geometry',4326, 'MULTIPOLYGON', "XY")
---------------------------------------------------------------------------
0
spatialite> CREATE VIRTUAL TABLE planning USING VirtualText(planning.csv, CP1252
, 1, COMMA, DOUBLEQUOTE, ',');
spatialite> PRAGMA table_info(planning);
cid name type notnull dflt_value pk
---------- ---------- ---------- ---------- ---------- ----------
0 ROWNO INTEGER 0 NULL 0
1 PR_PROP_No INTEGER 0 NULL 0
spatialite> PRAGMA table_info(property_mp);
cid name type notnull dflt_value pk
---------- ---------- ---------- ---------- ---------- ----------
0 PK_UID INTEGER 0 NULL 1
1 PROP_PFI TEXT 0 NULL 0
2 PR_LGAC TEXT 0 NULL 0
3 PR_PROPNUM TEXT 0 NULL 0
4 PR_PTYPE TEXT 0 NULL 0
5 PR_TRANS TEXT 0 NULL 0
6 PR_MULTASS TEXT 0 NULL 0
7 PR_STAT TEXT 0 NULL 0
8 PR_FQID TEXT 0 NULL 0
9 PR_PFI_CR DOUBLE 0 NULL 0
10 PROP_UFI INTEGER 0 NULL 0
11 PR_UFI_CR DOUBLE 0 NULL 0
12 PR_UFI_OLD INTEGER 0 NULL 0
13 PROPV_PFI TEXT 0 NULL 0
14 PV_BASEPFI TEXT 0 NULL 0
15 PV_CNT_PFI TEXT 0 NULL 0
16 PV_GRTYPE TEXT 0 NULL 0
17 PV_FTTYPE TEXT 0 NULL 0
18 PV_ZLEVEL TEXT 0 NULL 0
19 PV_FTYPE TEXT 0 NULL 0
20 PV_FQID TEXT 0 NULL 0
21 PV_PFI_CR DOUBLE 0 NULL 0
22 PROPV_UFI INTEGER 0 NULL 0
23 PV_UFI_CR DOUBLE 0 NULL 0
24 PV_UFI_OLD INTEGER 0 NULL 0
25 Geometry MULTIPOLYG 0 NULL 0
spatialite> CREATE TABLE workplan AS SELECT * FROM property_mp JOIN planning
...> ON (property_mp.PR_PROPNUM = planning.PR_PROP_No);
spatialite> PRAGMA table_info(worplan);
spatialite> .tables
geom_cols_ref_sys planning views_geometry_columns
geometry_columns property_mp virts_geometry_columns
geometry_columns_auth spatial_ref_sys workplan
spatialite> CREATE TABLE workplan2 AS SELECT * FROM property_mp, planning
...> WHERE property_mp.PR_PROPNUM = planning.PR_PROP_No;
spatialite> PRAGMA table_info(worplan2);
spatialite> SELECT * FROM property_mp JOIN planning
...> ON (property_mp.PR_PROPNUM = planning.PR_PROP_No);
PK_UID PROP_PFI PR_LGAC PR_PROPNUM PR_PTYPE PR_TRANS PR_MULTA
SS PR_STAT PR_FQID PR_PFI_CR PROP_UFI PR_UFI_CR PR_UFI_OLD PROP
V_PFI PV_BASEPFI PV_CNT_PFI PV_GRTYPE PV_FTTYPE PV_ZLEVEL PV_FTYPE
PV_FQID PV_PFI_CR PROPV_UFI PV_UFI_CR PV_UFI_OLD Geometry ROWNO
PR_PROP_No
---------- ---------- ---------- ---------- ---------- ---------- --------
-- ---------- ---------- ---------- ---------- ---------- ---------- ----
------ ---------- ---------- ---------- ---------- ---------- ----------
---------- ---------- ---------- ---------- ---------- ---------- --------
-- ----------
1 208266942 308 563544 O N
A 2454021.5 301390498 2455337.5 301355996 2082
66941 208266938 P G 5721
2454021.5 301648427 2455356.5 301390515 0
563544
spatialite> SELECT RecoverGeometryColumn('workplan', 'Geometry',4326, 'MULTIPOLY
GON', 2);
RecoverGeometryColumn('workplan', 'Geometry',4326, 'MULTIPOLYGON', 2)
---------------------------------------------------------------------
1
spatialite> SELECT RecoverGeometryColumn('workplan', 'Geometry',4326, 'MULTIPOLY
GON', "XY");
RecoverGeometryColumn() error: "columns f_table_name, f_geometry_column are not
unique"
RecoverGeometryColumn('workplan', 'Geometry',4326, 'MULTIPOLYGON', "XY")
------------------------------------------------------------------------
0
a.furieri
I notice a couple of trivial errors in
your session log:
1)
> *** charset ERROR *** cannot convert from 'ASCII;' to 'UTF-8'
the end of any 'macro' aka 'dot' special directive.
This is an error, because SQLite/SpatiaLite will interpreter
this extra-char as being part of the invocation arguments.
the 'right' syntax is:
> .read init_spatialite-2.4.sql ASCII<return>
2)
> f_geometry_column are not unique"
table you already contains a row identified by the same Primary Key:
f_table_name='property_mp', f_geometry_column='Geometry'
and this obviously raises a constraint violation exception
(Duplicate Key).
bye Sandro
Noli Sicad
> 1)
>> spatialite> .read init_spatialite-2.4.sql ASCII;
>> *** charset ERROR *** cannot convert from 'ASCII;' to 'UTF-8'
> Please note well: never append a semi-colon (;) at
> the end of any 'macro' aka 'dot' special directive.
> This is an error, because SQLite/SpatiaLite will interpreter
> this extra-char as being part of the invocation arguments.
> the 'right' syntax is:
>> .read init_spatialite-2.4.sql ASCII<return>
OK. I missed this one.
> 2)
>> spatialite> SELECT RecoverGeometryColumn('property_mp',
>> 'Geometry',4326, 'MULTIPOLYGON', 2);
>> RecoverGeometryColumn() error: "columns f_table_name,
>> f_geometry_column are not unique"
>
> carefully check your DB: for sure in the GEOMETRY_COLUMNS
> table you already contains a row identified by the same Primary Key:
> f_table_name='property_mp', f_geometry_column='Geometry'
>
> and this obviously raises a constraint violation exception
> (Duplicate Key).
This shapefile is from the one of the council in Melbourne, Victoria,
Australia. I think the shapefiles that I have been working were
converted from Mapinfo. I see a lot of problems - data integrity.
I figure out the my problem - vitual tables are not save in the
spatialite. No must create real table and then perform the join. This
solve the problem and I created 101 polygons.
Thanks.
Noli