Loading shapefile in spatialite 4.3 set field value to 0 instead of NULL

31 views
Skip to first unread message

Amedeo Fadini

unread,
Jun 24, 2016, 4:13:22 AM6/24/16
to spatiali...@googlegroups.com
Hi everybody,

I'm using ImportSHP to load data from shapefiles like the one
attached, then I made some sql scripts to compere this (updated)
shapefiles with previous version.

It seems that one of the important fields (TIPO_MOD) that is
interpreted like integer lose its NULL values during importing and set
them to 0.
See for example the record with id_fabbric=2274 in file attached

It happens the same importing from spatialite GUI.
This could be very dangerous.. Sandro, i think it's a bug, could you
tell if there's a way to avoid this? How to explicty declare that field
as a string?

amedeo fadini

mj10777

unread,
Jun 24, 2016, 4:29:01 AM6/24/16
to SpatiaLite Users


On Friday, 24 June 2016 10:13:22 UTC+2, Amedeo Fadini wrote:
Hi everybody,

I'm using ImportSHP to load data from shapefiles like the one
attached, then I made some sql scripts to compere this (updated)
shapefiles with previous version.

It seems that one of the important fields (TIPO_MOD) that is
Does the CREATE TABLE command (shwo CREATE Statement) show 'TIPO_MOD' as TEXT or as INTEGER?
- I saw no attached file to check myself

Maurizio Trevisani

unread,
Jun 24, 2016, 7:09:13 AM6/24/16
to spatialite-users

You could use a virtual shapefile to connect to the .SHP and insert the records you read in a previously created geometric table. So you can define the table structure and test and cast the input fields.
Bye,
Maurizio

--
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 post to this group, send email to spatiali...@googlegroups.com.
Visit this group at https://groups.google.com/group/spatialite-users.
For more options, visit https://groups.google.com/d/optout.

a.fu...@lqt.it

unread,
Jun 24, 2016, 6:26:19 PM6/24/16
to spatiali...@googlegroups.com
On Fri, 24 Jun 2016 10:12:50 +0200, Amedeo Fadini wrote:
> Hi everybody,
>
> I'm using ImportSHP to load data from shapefiles like the one
> attached, then I made some sql scripts to compere this (updated)
> shapefiles with previous version.
>
> It seems that one of the important fields (TIPO_MOD) that is
> interpreted like integer lose its NULL values during importing and
> set
> them to 0.
> See for example the record with id_fabbric=2274 in file attached
>

Hi Amedeo,

the problem is basically simple; the Shapefile standard
specification based on the DBF format absolutely lacks
any concept of NULL.
all values stored into the DBF file _must_ have an
explicit value set, so when exporting data out from a
DBMS supporting NULLs all values of the NUMERIC type
are usually exported as ZEROes, and all text/char NULLs
are exported as empty strings.

that said, it should be rather obvious that when importing
data from some shapefile there is absolutely no way allowing
to discriminate between a "real zero" end a "null zero", or
between a "real empty text string" and an intended "null empty
string".


> It happens the same importing from spatialite GUI.
> This could be very dangerous.. Sandro, i think it's a bug, could you
> tell if there's a way to avoid this? How to explicty declare that
> field
> as a string?
>

it's not a bug, it's an intrinsic limitation imposed by using
an obsolete data exchange format.
there is no possible workaround (at least, not one robustly
working in a general way and expected to be always understood
in the same way across many different implementations)

bye Sandro

Jukka Rahkonen

unread,
Jun 25, 2016, 6:09:27 AM6/25/16
to spatiali...@googlegroups.com, a.fu...@lqt.it
a.fu...@lqt.it kirjoitti 2016-06-25 01:26:
> On Fri, 24 Jun 2016 10:12:50 +0200, Amedeo Fadini wrote:
>> Hi everybody,
>>
>> I'm using ImportSHP to load data from shapefiles like the one
>> attached, then I made some sql scripts to compere this (updated)
>> shapefiles with previous version.
>>
>> It seems that one of the important fields (TIPO_MOD) that is
>> interpreted like integer lose its NULL values during importing and set
>> them to 0.
>> See for example the record with id_fabbric=2274 in file attached
>>
>
> Hi Amedeo,
>
> the problem is basically simple; the Shapefile standard
> specification based on the DBF format absolutely lacks
> any concept of NULL.
> all values stored into the DBF file _must_ have an
> explicit value set, so when exporting data out from a
> DBMS supporting NULLs all values of the NUMERIC type
> are usually exported as ZEROes, and all text/char NULLs
> are exported as empty strings.

From the shapefile specification:

Numeric Types A shapefile stores integer and double-precision numbers.
The remainder of this document
will refer to the following types:
Integer: Signed 32-bit integer (4 bytes)
Double: Signed 64-bit IEEE double-precision floating point number (8
bytes)
Floating point numbers must be numeric values. Positive infinity,
negative infinity, and
Not-a-Number (NaN) values are not allowed in shapefiles. Nevertheless,
shapefiles
support the concept of "no data" values, but they are currently used
only for measures.
Any floating point number smaller than –10^38 is considered by a
shapefile reader to
represent a "no data" value.

So is a workaround mechanism in the specification for floating point
numbers. It may be unusable if field is defined as integer, though. Very
small floating point numbers should naturally be interpreted as NULLs in
reading as well.

-Jukka Rahkonen-

mj10777

unread,
Jun 25, 2016, 7:15:04 AM6/25/16
to SpatiaLite Users

Without the CREATE statement (show CREATE Statement) to see how  'TIPO_MOD' was defined
- or the ShapeFile to test what the code is reading when specific values are being read ( id_fabbric=2274)
there it nor much here that can be done.
On Friday, 24 June 2016 10:13:22 UTC+2, Amedeo Fadini wrote:
Reply all
Reply to author
Forward
0 new messages