Import shapefiles to a table.

[Kyle Felipe Vieira Roberto]

Hi guys, im new here.

I've been trying to use python to import shafiles to a spatialite db.

first i tryed to use spatialite_tool to import, but it show a error when i use the  [-s SRID].... and if i dont use -s.. it creates a new table with no SRID and i cant set the SRID in this tabel, it must be empity to do this...

then, i tryied to use the SQL function ImportSHP but it show an error "spatialite.dbapi2.OperationalError: no such function: ImportSHP"

how can i make this import??

There is a way to make this import to a table previusly created on my DB???

Im using this 2 imports:

from sqlite3 import dbapi2 as bd

and 

from pyspatialite import dbapi2 as db


this is the code to create and connect to db:

conn = db.connect(dir_analises + "DB_ANALISE.sqlite")
cur = conn.cursor()

this is to enable  Spatial Metadata if the bd was not created before:
sql = 'SELECT InitSpatialMetadata(1)'

[mj10777]

On Wednesday, 13 December 2017 16:12:20 UTC+1, Kyle Felipe Vieira Roberto wrote:

Hi guys, im new here.

I've been trying to use python to import shafiles to a spatialite db.

first i tryed to use spatialite_tool to import, but it show a error when i use the  [-s SRID]....

I assume you replaced 'SRID' with the srid-number to use?

and if i dont use -s.. it creates a new table with no SRID and i cant set the SRID in this tabel, it must be empity to do this...

You can, when using the sql command: 'RecoverGeometryColumn' 

then, i tryied to use the SQL function ImportSHP but it show an error "spatialite.dbapi2.OperationalError: no such function: ImportSHP"

Did you set the environment variable SPATIALITE_SECURITY=relaxed, befor calling the python script

- as shown of the command ImportSHP description at:

http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html

 

how can i make this import??

There is a way to make this import to a table previusly created on my DB???

Use the sql command: 'RecoverGeometryColumn'  

Mark

[Kyle Felipe Vieira Roberto]

Em quarta-feira, 13 de dezembro de 2017 13:23:43 UTC-2, mj10777 escreveu:

On Wednesday, 13 December 2017 16:12:20 UTC+1, Kyle Felipe Vieira Roberto wrote:

Hi guys, im new here.

I've been trying to use python to import shafiles to a spatialite db.

first i tryed to use spatialite_tool to import, but it show a error when i use the  [-s SRID]....

I assume you replaced 'SRID' with the srid-number to use?

Yes..., i did it with 31983 

and if i dont use -s.. it creates a new table with no SRID and i cant set the SRID in this tabel, it must be empity to do this...

You can, when using the sql command: 'RecoverGeometryColumn' 

It returns  RecoverGeometryColumn(): validation failed

then, i tryied to use the SQL function ImportSHP but it show an error "spatialite.dbapi2.OperationalError: no such function: ImportSHP"

Did you set the environment variable SPATIALITE_SECURITY=relaxed, befor calling the python script

- as shown of the command ImportSHP description at:

http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html

Yes, and stil showing the error: spatialite.dbapi2.OperationalError: no such function: ImportSHP

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

Hi Kyle,

which version of libspatialite / spatialite-tools are
you using ?
you can easily check by executing this SQL query:

SELECT spatialite_version();

> Im using this 2 imports:
> from sqlite3 import dbapi2 as bd
> and
> from pyspatialite import dbapi2 as db
>

the first one (from sqlite3) is ok, but the second
one (from pyspatialite) should be carefully avoided
as far as possibile, because pyspatialite is usually
based on libspatialite-2.3.1, that is nowadays badly
outdated.

please read the following recent post about
pyspatialite and more modern alternatives:

[1]
https://groups.google.com/forum/#!topic/spatialite-users/o0jUwMUqx_g

bye Sandro

[Kyle Felipe Vieira Roberto]

Hello @Sandro
my version is 4.3.0a

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

On Wed, 13 Dec 2017 11:53:13 -0800 (PST), Kyle Felipe Vieira Roberto
wrote:

> Hello @Sandro
> my version is 4.3.0a
>

All right Kyle,

I've tested your issues using 4.3.0a, and here are
mi findings:

> first i tryed to use spatialite_tool to import, but it show a error
> when i use the [-s SRID]....
>
>> I assume you replaced 'SRID' with the srid-number to use?
>
> Yes..., i did it with 31983
>

spatialite_tool -i -shp testshp -d db.sqlite -t tbl -c CP1252 -s 31983
-----------------------------
SQLite version: 3.8.11.1
SpatiaLite version: 4.3.0a
Inserted 24323 rows into 'tbl' from 'testshp'

anything worked nicely and I was completely unable to see
any possible issue related to specifying "-s 31883"

> then, i tryied to use the SQL function ImportSHP but it show an error
> "spatialite.dbapi2.OperationalError: no such function: ImportSHP"
>
>> Did you set the environment variable SPATIALITE_SECURITY=relaxed,
>> befor calling the python script
>> - as shown of the command ImportSHP description at:
>> http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html
>
> Yes, and stil showing the error: spatialite.dbapi2.OperationalError:
> no such function: ImportSHP
>

my first attempt (without defining SPATIALITE_SECURITY)

sqlite3
-----------------
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT load_extension('mod_spatialite');

sqlite> SELECT spatialite_version();
4.3.0a
sqlite> SELECT ImportSHP('testshp', 'tbl', 'CP1252', 31983);
Error: no such function: ImportSHP
sqlite> .quit


my second attempt (this time correctly setting SPATIALITE_SECURITY)

set SPATIALITE_SECURIY=relaxed
sqlite3
-----------------
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT load_extension('mod_spatialite');

sqlite> SELECT spatialite_version();
4.3.0a
sqlite> SELECT ImportSHP('testshp', 'tbl', 'CP1252', 31983);
========
Loading shapefile at 'R15_11_WGS84' into SQLite table 'tbl'

BEGIN;
CREATE TABLE "tbl" (
...
"Geometry" BLOB);
COMMIT;

Inserted 24323 rows into 'tbl' from SHAPEFILE
========
24323
sqlite> .quit

once again, anything worked correctly without any issue,
so I'm unable to confirm your claims; the most reasonable
explanation is that you are not correctly setting
SPATIALITE_SECURITY=relaxed

note: properly setting an environment variable requires
using a different syntanx on Linux or Windows.
- on Linux: export "SPATIALITE_SECURITY=relaxed"
- on Windows: set SPATIALITE_SECURITY=relaxed

bye Sandro

[Kyle Felipe Vieira Roberto]

Hi Sandro, thanks so much spending this time with me.

I just find out, with a friend helping me, that in my python code i have so use the follow line

os.putenv("SPATIALITE", "relaxed") and it works like a charm...
later i will learn how to work only with the sqlite3 on python...

Thx thx thx...

[Kyle Felipe Vieira Roberto]

while import shp files to DB, im getting a message about the import:

========

Loading shapefile at '/home/x12239181/Dropbox/projetos pycharm/Algoritimo_pss/GERDAU_ACOS_LONGOS_LTDA_FAZENDA_CABANA_SANTA_BARBARA/09921952017_PSS_2017/21 ago aaaa/SHAPEFILE/PRODUÇÃO_FLORESTAL/PRODUCAO_FLORESTAL' into SQLite table 'prodflore1'

BEGIN;

CREATE TABLE "prodflore1" (

"PK_UID" INTEGER PRIMARY KEY AUTOINCREMENT,

"PLANTIO" TEXT,

"EXECUÇÃO" TEXT,

"TALHÃO" DOUBLE,

"ESPACAMENT" TEXT,

"DATAPLANTI" DOUBLE,

"AREA_1" DOUBLE,

"CONDUCAO" TEXT,

"ESPECIE" TEXT,

"ORIGEM" TEXT,

"TECNICO" TEXT,

"ART" TEXT,

"IDENTIFICA" TEXT,

"CNPJ" TEXT,

"QUANTO" TEXT);

SELECT AddGeometryColumn('prodflore1', 'Geometry', 31983, 'MULTIPOLYGON', 'XYZM');

COMMIT;

Inserted 867 rows into 'prodflore1' from SHAPEFILE

There is some way to not show this?

[mj10777]

I assume you are using 'spatialite_tool'

As you can see with:

spatialite_tool --help

there is no '-silent' for this tool.

You could try route the output to file with:

spatialite_tool whatever > dump.txt

which would write the non-error out put to the file 'dump.txt'.

spatialite_tool whatever > dump.txt 2>&1

which would write all error and non-errors to the file 'dump.txt'.

(these work for almost all systems, including windows)

Also:

spatialite_tool whatever &>/dev/null

which would send all messages to a 'garbage' queue.

(Not sure if the last works on non-linux systems)

Mark

[Kyle Felipe Vieira Roberto]

@mj10777, im not using spatiallite_tool, im using the SQL import on python "SELECT ImportSHP()" as sandro teaches how to make import...

Spatialite_tool does not works here (i dont know why)