Help needed: spatialite_tool imports shape file with DateTime field

[Jason Williams]

I am using Spatialite 5 spatialite_tool to import shape files. Is there a parameter to specify Date value as plain text instead of Julian Day Numbers? By default the datetime field is imported as Julian Day Numbers but I need plain datetime text.

There is an option to specify Date value format in spatialite GUI but I need to use spatialite_tool to create a batch import script. 

Thank you in advance!

[Jason Williams]

If spatialite_tool does not have optional parameter for "text_dates", maybe I should use ImportSHP:

ImportSHP ( filename Text , table Text , charset Text , srid Integer , geom_column Text , pk_column Text , geometry_type Text , coerce2D Integer ,
        compressed Integer , spatial_index Integer , text_dates Integer , colname_case Text , update_statistics Integer , verbose Integer ) : Integer

One question about ImportSHP parameters :

pk_column  and geometry_type 

If I pass in empty text ('') to these 2 parameters, would it be OK?

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

On Thu, 18 Aug 2022 08:15:19 -0700 (PDT), Jason Williams wrote:
> If spatialite_tool does not have optional parameter for "text_dates",
> maybe I should use ImportSHP:
>

> IMPORTSHP ( filename _Text_ , table _Text_ , charset _Text_ , srid
> _Integer_ , geom_column _Text_ , pk_column _Text_ , geometry_type
> _Text_ , coerce2D _Integer_ ,
> compressed _Integer_ , spatial_index _Integer_ , text_dates
> _Integer_
> , COLNAME_CASE _TEXT_ , UPDATE_STATISTICS _Integer_ , verbose
> _Integer_ ) : _Integer_
>

Hi Jason,

yes, calling this SQL function is always the best option for
loading any shapefile into the DB.

note: the wizard supported by the GUI tool simply is a thin
wrapper internally calling ImportSHP()

> One question about ImportSHP parameters :
>
> pk_column and geometry_type
>
> If I pass in empty text ('') to these 2 parameters, would it be OK?
>

no: if you dont care to explicitly set both pk_column and
geometry_type you should set them as NULL, thus making
crystal clear they lack any value.

an empty text string ('') actually is a valid text string
and could easily cause some confusion.

bye Sandro

[Jason Williams]

Thank you so very much Sandro!

[Antonio Valanzano]

Following the advice of Sandro about NULL values for some columns,

I have tried to skip  the value of the primary key column in IMPORTSHP but I was unable to correctly create a table

SELECT ImportSHP
(
-- absolute or relative path leading to the Shapefile (omitting any .shp, .shx or .dbf suffix)
'D:\TEMP\Com2011_WGS84',
-- name of the table to be created.
'comuni_prova',
-- the character encoding adopted by the DBF member, as e.g. UTF-8 or CP1252
'UTF-8',
-- EPSG SRID value of shape file (has found in the .prj file)
32632,
-- name to assigned to the Geometry column
'Geom',
-- name of a DBF column to be used in the Primary Key role
NULL,
-- geometry_type of shap file
'MULTIPOLYGON',
-- casting to 2D or not; 0 by default
0,
-- compressed geometries or not
0,
-- immediately build a Spatial Index
1,
-- interpreting DBF dates as plaintext or not
0
);

The result is NULL.

What I am doing wrong ?

Antonio Valanzano

[Andrea Peri]

Try to use

C:/.../

Instead of

C:\...\

I remenber something of an issues on this usage due to the use of language C.

Regards,

A.

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/b3d1f39f-9b2c-4f92-96ff-4002091cf906n%40googlegroups.com.

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

On Sat, 20 Aug 2022 12:49:40 +0200, Andrea Peri wrote:
> Try to use
>
> C:/.../
> Instead of
> C:...
>
> I remenber something of an issues on this usage due to the use of
> language C.
>

yes, always using the "Unix style" for passing any pathname to
SpatiaLite surely is best option (as in dir/subdir/file)
the "MS-Dos" style (as in dir\subdir\file) can easily cause problems.
but in this case it was not the cause of failure.

Antonio, the Shapfiles released by ISTAT (the Italian National
Statistcs Authority) are encoded as CP1252 "Windows Latin-1"

but you've set UTF-8 instead, and this caused the incorrect
translation of all accented vowels (àèìòù) thus raising
a fatal error condition.

bye Sandro

[Antonio Valanzano]

Dear Andrea and Sandro

the problem is not the / or \ nor the CP 1252.

I have used the following syntax

SELECT ImportSHP
(
-- absolute or relative path leading to the Shapefile (omitting any .shp, .shx or .dbf suffix)
'D:\TEMP\Com2011_WGS84',
-- name of the table to be created.

'comuni_prova1',

-- the character encoding adopted by the DBF member, as e.g. UTF-8 or CP1252
'UTF-8',
-- EPSG SRID value of shape file (has found in the .prj file)
32632,
-- name to assigned to the Geometry column
'Geom'

);

and the result was

8092   (rows created)

and if I check the name of the communities with accented vowels (àèìòù)

I see all the names correctly shown

SELECT "pk_uid", "cod_reg", "cod_pro", "pro_com", "comune", "nome_ted"
FROM "comuni_prova1"
where comune like '%è%'
   OR comune like '%à%'
   OR comune like '%ù%';
-- 86 rows

62    1    1    1226    Rorà    
131    1    1    1295    Vestignè    
132    1    1    1296    Vialfrè    
149    1    1    1313    Viù    
159    1    2    2011    Bianzè    
224    1    2    2133    Santhià    
..
2519    3    16    16005    Almè    
2747    3    16    16239    Villa d'Almè    
2923    3    17    17164    Roè Volciano    
2943    3    17    17184    Temù    
2974    3    18    18009    Bascapè    
3024    4    21    21045    Magrè sulla strada del vino    Margreid an der Weinstraße
3103    4    22    22009    Baselga di Pinè    
3162    4    22    22073    Darè    
3172    4    22    22083    Fiavè    
3198    4    22    22110    Malè    
3220    4    22    22133    Palù del Fersina   

As reported in the cookbook

https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/cookbook_topics.01.html#topic_create_database_italy_2011

the shapefiles  which I have downloaded from [2018-09-06] Spatialite Server: Limiti_2011_WGS84.zip

are in EPSG:32632 and encoded as UTF-8 (the previuos ones used different EPSG and Charset Encoding).

So the problem is the NULL value assigned to :

- name of a DBF column to be used in the Primary Key role
NULL,

In the script which is in the Cookbook there is:

-- name of a DBF column to be used in the Primary Key role

 'pk_uid',

..

I have also tried this option but the result was still a NULL (no table was created)

SELECT ImportSHP
(

-- absolute or relative path leading to the Shapefile (omitting any .shp, .shx or .dbf suffix)
'D:\TEMP\Com2011_WGS84',
-- name of the table to be created.

'comuni_prova1',

-- the character encoding adopted by the DBF member, as e.g. UTF-8 or CP1252
'UTF-8',
-- EPSG SRID value of shape file (has found in the .prj file)
32632,
-- name to assigned to the Geometry column
'Geom',
-- name of a DBF column to be used in the Primary Key role

'pk_uid',

-- geometry_type of shap file
'MULTIPOLYGON',
-- casting to 2D or not; 0 by default
0,
-- compressed geometries or not
0,
-- immediately build a Spatial Index
1,
-- interpreting DBF dates as plaintext or not
0
);

Any help  will be appreciated.

Antonio

[Andrea Peri]

Are you using all that comments inside the function parameter ?

Can you try to exec the query removing all that comments and on one row only ?

A.

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/348cc7ee-6c80-4c37-8823-4f1aa7605b8an%40googlegroups.com.

[mj10777]

On Saturday, 20 August 2022 at 15:00:10 UTC+2 aper...@gmail.com wrote:

Are you using all that comments inside the function parameter ?

Can you try to exec the query removing all that comments and on one row only ?

There is no need for this, since no syntax error was reported.

 Comments are valid  within sql.

[Antonio Valanzano]

Hi Andrea

here are  the results obtained following your suggestions

SELECT ImportSHP ('D:\TEMP\Com2011_WGS84','comuni_prova2','UTF-8',32632,'geom',NULL,'MULTIPOLYGON',0,0,1,0);
-- risultato NULL


SELECT ImportSHP ('D:\TEMP\Com2011_WGS84','comuni_prova2','UTF-8',32632,'geom','pk_uid','MULTIPOLYGON',0,0,1,0);
-- 1 row
8092

It seems that NULL is not accepted (as it should be, if you do not want to use any existing column of the orginal shapefile as pk

and you just want  an INTEGER PRIMARY KEY AUTOINCREMENT column)  and pk_uid is just a name that you specify for this scope.

You could also use any name which doesn't already exists in the original dbf file

I did

SELECT ImportSHP ('D:\TEMP\Com2011_WGS84','comuni_prova3','UTF-8',32632,'geom','chiave','MULTIPOLYGON',0,0,1,0);

and everything worked fine.

Here is the result of the "Show Create Statement"

CREATE TABLE "comuni_prova3" (
"chiave" INTEGER PRIMARY KEY AUTOINCREMENT,
"cod_reg" INTEGER,
"cod_pro" INTEGER,
"pro_com" INTEGER,
"comune" TEXT,
"nome_ted" TEXT,
"pop_2011" DOUBLE,
"shape_area" DOUBLE,
"shape_len" DOUBLE, "geom" MULTIPOLYGON)

and as you can see the column 'chiave' is an INTEGER PRIMARY KEY AUTOINCREMENT column

The comments are not important and do not influence the results (as it should be).

There should probably  be a better explanation in the Cookbook about the possibility of skipping some parameters of the function ImportSHP

if someone wants  to set most of the parameters with default values  but other following parameters with not default values, without forcing the user to explicitly

input default values.

As an alternative could be appropriate to state that all the parameters before the last one that a user wants to set must be explicitly specified.

This is a matter of choice of Sandro.

Thanks for the collaboration

Antonio

[Antonio Valanzano]

For Sandro:

suggestion for an improvement of the explanation of ImportSHP function within the

SpatiaLite 5.0.1          SQL functions reference list

..

Will return the total number of imported rows.
NULL will be returned on invalid arguments.

But in one of my attempts (the one with 'pk_uid' value) the result was NULL not becuase there were invalid arguments but because the internal table I was going to create already existed into the DB.

It could be useful to integrate the explanation of the NULL result with also this situation.

[mj10777]

The goal of the Cookbook is to show the practical use of the functions (including the comments for each parameter) as a tutorial.

If you wish to use the shorten version of a function, your are expected to use:

SpatiaLite SQL functions reference list

https://www.gaia-gis.it/gaia-sins/spatialite-sql-5.0.1.html

where you can the remove what you don't require. 

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

final conclusion of the story: there was a bug.

when I said in a previous reply that NULL should be used in order
to mark an undefined argument I only checked the source code of
the GUI tool.

but there is a subtle difference between the GUI Wizard and
the SQL function:

- both call the same identical API

- but the SQL function checks the received arguments before
calling the API; and the bug was exactly here.
NULL values were incorrectly rejected for the "geom_column",
"pk_column" and "geometry_type" args.
the same issue affected ImportDBF() too ("pk_column").

the applied patch is already committed into the Fossil repo.

On Sat, 20 Aug 2022 06:42:27 -0700 (PDT), Antonio Valanzano wrote:
> It seems that NULL is not accepted (as it should be, if you do not
> want to use any existing column of the orginal shapefile as pk
>
> and you just want an INTEGER PRIMARY KEY AUTOINCREMENT column) and
> pk_uid is just a name that you specify for this scope.
>
> You could also use any name which doesn't already exists in the
> original dbf file
>

yes, that's correct: using the buggish version this is the best
workaround to circumvent the issue.

On Sat, 20 Aug 2022 06:59:13 -0700 (PDT), Antonio Valanzano wrote:
> For Sandro:
> suggestion for an improvement of the explanation of ImportSHP
> function
> within the SpatiaLite 5.0.1 SQL functions reference list
>

> NULL will be returned on invalid arguments.
>
> But in one of my attempts (the one with 'pk_uid' value) the result
> was
> NULL not becuase there were invalid arguments but because the
> internal
> table I was going to create already existed into the DB.
> It could be useful to integrate the explanation of the NULL result
> with also this situation.
>

I've corrected the documentation so to make clearer which arguments
could be legitimataly NUL and to add

NULL will be returned on invalid arguments or on any other error cause.

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

Just one last final invitation addressed to Antonio.

when you think that you've discovered some bug, just say

"I suspect there is a bug because passing NULL as the
'pk_column' argument make the SQL function to fail"

your initial report was full of (almost useles) details
and concluded "What I am doing wrong ?"

this was completely misleading, and led me, Andrea and
Mark to give you irrelevant answers because our attention
was diverted from some other details that seemed suspicious
and that ended up just making a big mess.

please go straight to the point next time; if you think
there is a bug, say it clear and don't ask
"where am I wrong?"

bye Sandro