Problem loading shapefile's dbf

[Steven Elster]

Hi: The shapefiles are too large to attach but open nicely in qGIS and arcGIS.  However, with Spatialite, I get the error the dbf file contains unsupported data types. Do you have any suggestions?  Thanks, Steve

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

Hi Steven,

unhappily DBF is a file format based on a rather weak specification,
and several variants were introduced during the time by different
vendors.
SpatiaLite supports only the most classical datatypes (i.e. the
main core supported by all implementaions) and may well be that
some of the most exotic and rarely used datatypes could be
unsupported; but I absolutely need a sample of the failing DBF to
be inspected in order to correctly identify the real nature of
the problem you are facing.

note: this seems to be a DBF-specific problem, so there is no
reason to send the whole shapefile; just sending the DBF alone
should be enough to start a debugging session.
if the size of the DBF still continues to be too large even
after applying a 7z compression you can try to reduce it
by deleting a large number or records; you can use Open/Libre
Office Calc for this task.

bye Sandro

[Steven Elster]

Sandro: Ok. Attached is the file with 4 records. Thank you very much, Steve

[Jukka Rahkonen]

Hi,

When checked with ogrinfo your data seems to have a few dummy attributes
without a name and defined as strings with zero length. That does not
feel normal. However, OpenOffice does not list those dummy attributes.
Which software creates such DBF files?

-Jukka Rahkonen-


INFO: Open of `backup.dbf'
using driver `ESRI Shapefile' successful.

Layer name: backup
Metadata:
DBF_DATE_LAST_UPDATE=1916-12-30
Geometry: None
Feature Count: 4
Layer SRS WKT:
(unknown)
APN: String (9.0)
OWNER_NAME: String (36.0)
NUMBER: Integer (9.0)
PREDIR: String (1.0)
STREETNAME: String (20.0)
STREETTYPE: String (4.0)
CITY: String (20.0)
STATE: String (2.0)
COMMAREA: String (3.0)
ZIP: String (5.0)
OWNER_ADDR: String (60.0)
OWNER_ZONE: String (60.0)
HOX_FLAG: String (1.0)
BASE_YEAR: Integer (9.0)
TAX_STATUS: String (1.0)
TRA: String (7.0)
SHAPE_LEN: Real (19.11)
SHAPE_AREA: Real (19.11)
LAND_VALUE: Real (13.11)
IMPR_VALUE: Real (13.11)
PERS_VALUE: Real (13.11)
EXEM_VALUE: Real (13.11)
TYPEUSE: Integer (4.0)
: String (0.0)
: String (0.0)
: String (0.0)
: String (0.0)
: String (0.0)
: String (0.0)
: String (0.0)
: String (0.0)
OGRFeature(backup):0
APN (String) = 010831304
OWNER_NAME (String) = CHRISTENSEN, STANLEY D
NUMBER (Integer) = 0
PREDIR (String) = (null)
STREETNAME (String) = (null)
STREETTYPE (String) = (null)
CITY (String) = (null)
STATE (String) = (null)
COMMAREA (String) = (null)
ZIP (String) = 00000
OWNER_ADDR (String) = 1668 N BAKER AVE
OWNER_ZONE (String) = ONTARIO CA 91764
HOX_FLAG (String) = (null)
BASE_YEAR (Integer) = 0
TAX_STATUS (String) = 1
TRA (String) = 0004010
SHAPE_LEN (Real) = 346.63714945700
SHAPE_AREA (Real) = 7230.52834476000
LAND_VALUE (Real) = 15230.00000000000
IMPR_VALUE (Real) = 0.00000000000
PERS_VALUE (Real) = 0.00000000000
EXEM_VALUE (Real) = 0.00000000000
TYPEUSE (Integer) = 0
(String) = (null)
(String) = (null)
(String) = (null)
(String) = (null)
(String) = (null)
(String) = (null)
(String) = (null)
(String) = (null)

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

[Even Rouault]

On vendredi 30 décembre 2016 16:47:37 CET Jukka Rahkonen wrote:

> Hi,

>

> When checked with ogrinfo your data seems to have a few dummy attributes

> without a name and defined as strings with zero length. That does not

> feel normal. However, OpenOffice does not list those dummy attributes.

> Which software creates such DBF files?

 

Jukka,

 

I think the .dbf is OK, although a bit unusual. There's a 0x0D header record terminator immediately after the end of the last valid field definition, and then substanial padding (more than 32 bytes) before the beginning of the record section, but the header length (bytes 8-9) is consistant with that.

 

(I've just fixed in GDAL trunk the DBF reader to take into account such cases)

 

Even

--

Spatialys - Geospatial professional services

http://www.spatialys.com

[Steven Elster]

I program in dbase/foxpro, both of which use dbf files.  A dbf is a table with a discrete number of fields.  You can see the structure for this one in the attached.  I am not sure if this link will work to download foxpro: https://msdn.microsoft.com/en-us/subscriptions/downloads/hh442898.aspx#searchTerm=&ProductFamilyId=90&Languages=en&PageSize=10&PageIndex=0&FileId=0  .  But ... Steve

On Thursday, December 29, 2016 at 4:02:49 PM UTC-8, Steven Elster wrote:

[Steven Elster]

Even, How do I download the fix?  What location does this refer to?  "I've just fixed in GDAL trunk the DBF reader to take into account such cases"
Thanks,

Steve

 


On Thursday, December 29, 2016 at 4:02:49 PM UTC-8, Steven Elster wrote:

[Steven Elster]

I am on windows.
Thanks again, Steve

On Thursday, December 29, 2016 at 4:02:49 PM UTC-8, Steven Elster wrote:

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

On Fri, 30 Dec 2016 06:21:25 -0800 (PST), Steven Elster wrote:
> Sandro: Ok. Attached is the file with 4 records.
>

Hi Steven,

I've just attempted to import your sample DBF by using the
current development version of spatialite_gui (based on
spatialite 4.4.0) and I've got the following error:

Invalid Magic Number 0x30 (Visual FoxPro format)

it's interesting to note that this error message is different
from the one you reported; I strongly suspect that you are
using some obsolete version of SpatiaLite.

Visual FoxPro [1] was a dBase derivative produced by Microsoft
(now discontinued), and presumably it added so many non standard
extensions to dBase to justify the introduction of a specific
"magic" signature of its own DBF files.

[1] https://en.wikipedia.org/wiki/Visual_FoxPro

saning this issue seems to be rather easy and simple
(at least, it worked well for me):

1. open the offending DBF by using Open/Libre Office Calc
2. immediately save another DBF with a different name; OOC
will take care to restore a standard bDase layout while
exporting the new DBF.
3. delete the initial DBF (keep a backup copy !!!!), then
rename the second DBF exported by OOC so to exactly
match the initial name.

bye Sandro

[Even Rouault]

On vendredi 30 décembre 2016 07:15:08 CET Steven Elster wrote:

> Even, How do I download the fix? What location does this refer to? "I've

> just fixed in GDAL trunk the DBF reader to take into account such cases"

 

Sorry: I side-tracked a bit the topic as Jukka mentionned the use of the ogrinfo utility that is part of the GDAL/OGR software : http://gdal.org/

 

I just committed the fix in the source tree (https://trac.osgeo.org/gdal/changeset/37038). Windows builds at http://gisinternals.com/development.php that done tomorrow (they must be at least 37038) will have the fix.

 

This will have no influence on Spatialite own DBF reader of course.

 

Even

[Steven Elster]

Sandro:  I made a mistake.  I opened up the shapefile dbf with foxpro, and mistakenly copied it out in the newest form of dbf.  arcGIS needs a dbase IV formatted dbf file.  The attached is such and can be read by qGIS or arcGIS.
Sorry and Thanks,
Steve


I see at: https://github.com/OSGeo/gdal that a change for "... support reading .dbf with substantial padding after last field… " added 20 minutes ago.  Great!  But how to I add this to my windows installation for Spatialite or would it be for gdal?

Thanks again, Steve


On Thursday, December 29, 2016 at 4:02:49 PM UTC-8, Steven Elster wrote:

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

it seems that Microsoft has publicly disclosed the format
specifications adopted by Visual FoxPro DBF files:

https://msdn.microsoft.com/en-us/library/aa975386(v=vs.71).aspx

bye Sandro

[Steven Elster]

I will try to open the original dbf with Libre but since it has 800k records, we'll see what happens.

Steve

On Thursday, December 29, 2016 at 4:02:49 PM UTC-8, Steven Elster wrote:

[Steven Elster]

Does GDAL have its own dbf file reader?

Steve

On Thursday, December 29, 2016 at 4:02:49 PM UTC-8, Steven Elster wrote:

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

On Fri, 30 Dec 2016 07:31:33 -0800 (PST), Steven Elster wrote:
> Sandro:  I made a mistake.  I opened up the shapefile dbf with
> foxpro, and mistakenly copied it out in the newest form of dbf. 
> arcGIS needs a dbase IV formatted dbf file. 
>

yes, many applications able to support the shapefile format
usually require a the dBase-compliant layout for the DBF
component. it's a rather common assumption.

> The attached is such and can be read by qGIS or arcGIS.
>

this latest version of your DBF was nicely imported by
spatialite_gui without any trouble.

please check which version of spatialite you are using
(it looks like something very archaic, may be an obsolete
2.1) and possibly update to some fresher version.

http://www.gaia-gis.it/gaia-sins/windows-bin-x86-test/
http://www.gaia-gis.it/gaia-sins/windows-bin-amd64-test/

bye Sandro

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

On Fri, 30 Dec 2016 07:36:52 -0800 (PST), Steven Elster wrote:
> Does GDAL have its own dbf file reader?
>

certainly yes, and you have already used it very frequently.
the whole shapefile support offered by QGIS is fully based
on GDAL

bye Sandro

[Steven Elster]

I just downloaded, ran: spatialite_gui-4.4.0-RC0-win-amd64.7z, and spatialite imported the entire shapefile with its 800k records.  Now, is there a guide that can show me how to connect qGis to this file?  So that I can edit the file in spatialite and see the changes in qGis?\
Thank you so much,

Steve

On Thursday, December 29, 2016 at 4:02:49 PM UTC-8, Steven Elster wrote:

[Steven Elster]

How can I send my Spatialite data to qGIS and update it when I make changes in Spatialite.
Thank you, Steve

On Thursday, December 29, 2016 at 4:02:49 PM UTC-8, Steven Elster wrote:

[mj10777]

On Friday, 30 December 2016 17:49:44 UTC+1, Steven Elster wrote:

How can I send my Spatialite data to qGIS and update it when I make changes in Spatialite.

With 'Drag and Drop'

- drag the spatialite Datbase on to the screen of QGIS

-- a dialog will pop up showing you the available TABLES

---> select all that you which to see or change

They will then be loaded into QGIS and can be seen

-  you can make changes and save them (right mouse: select 'Toggel Editing')

Mark

[Steven Elster]

When you do an SQL in Spatialite and makes changes, are these supposed to automatically show up in qGIS or do you need to save the file, then re-drag in into qGIS.

Thank you, Steve

On Thursday, December 29, 2016 at 4:02:49 PM UTC-8, Steven Elster wrote:

[mj10777]

On Friday, 30 December 2016 18:12:58 UTC+1, Steven Elster wrote:

When you do an SQL in Spatialite and makes changes, are these supposed to automatically show up in qGIS or do you need to save the file, then re-drag in into qGIS.

No, but you must refresh the screen (move it slightly) 

- OGR,the Vector portion of GDAL,which reads the Datasources that are opened with 'Drag and Drop,) will re-read the Data after such refreshes

-- thus the external changes will be shown 

Mark

[Steven Elster]

For each table, is a separate file saved on my hard drive.  If so, what is its extension and which folder is used?  Also, after I have filtered down a table, how to I save it?  And to the hard drive?

Thank you, Steve

On Thursday, December 29, 2016 at 4:02:49 PM UTC-8, Steven Elster wrote:

[Steven Elster]

Is this how its done: ALTER TABLE old_name RENAME TO new_name;

Also, I am trying to use Spatilite-GIS but can't figure out which folder to tell it to connect to?
Thank you very much, Steve

On Thursday, December 29, 2016 at 4:02:49 PM UTC-8, Steven Elster wrote:

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

On Fri, 30 Dec 2016 11:01:50 -0800 (PST), Steven Elster wrote:
> For each table, is a separate file saved on my hard drive. If so,
> what is its extension and which folder is used? Also, after I have
> filtered down a table, how to I save it? And to the hard drive?
>

Steven,

please stop thinking in terms of folders and files; SQLite (the
SQL database engine under SpatiaLite) works in a completely
different way.

1. a whole database is completely stored within just a single
file: even when a database contains hundredth or thousand
tables it still remains a single monolithic file.
2. establishing a connection to some db-file just requires
referencing its path. as a special case you can eventually
create a volatile database completely stored in RAM, but
in this case you must be conscious that the db will
vanish immediately after terminating the connection.
3. there is no practical limit to the size and complexity
of db-files: using some huge SQLite database containing
many hundredth tables and requiring many tenths GB of
storage is not at all exceptional.
4. any SQLite db-file is universally portable: you can
freely copy it from a computer to another even if they
use different operating systems (MS Windows, Linux,
MacOsX, Android etc).
5. you are never expected to be concerned about the
internal organization of a db-file; just execute your
SQL statements and SQLite will take care to interact
with the connected db-file in the most appropriate
and efficient way.

forget also the concept of "saving data"; SQLite is a
Transactional [1] Database, and most precisely it's an
ACID [2] Database.

A. each time you execute an SQL statement implying some
permanent change (INSERT, UPDATE, DELETE etc) it will
immediately affect the connected db-file.
B. as a partial exception to the above rule you can
eventually start a Transaction (BEGIN)
B1. all SQL statements being executed form within
a pending Transaction doesn't immediately
affect the db-file; they are instead temporarily
stored into a transient log-file supporting the
main db-file.
B2. you can close an open Transaction by issuing a
COMMIT, and in this case all pending changes
will become permanent.
B3. alternatively you can invalidate a whole Transaction
by issuing a ROLLBACK, and it this case all
pending changes will be discarded and the db-file
will remain exactly as it was before starting the
current transaction.
B4. all pending changes (uncommitted Transaction) are
only visible to the holding connection.
example: both spatialite_gui and QGIS connected
to the same db-file; spatialite_gui starts a
Transaction changing some way the db-file.
such changes will become visible to QGIS only
after committing the Transaction.
B5. in other words, an ACID Transaction strongly
ensures an "all or nothing" logic to be always
consistently applied:
- if a Transaction is correctly closed (COMMIT)
you are assured that all changes have been
successfully incorporated into the db-file.
- in the case of an aborted Transaction (explicit
or implicit ROLLBACK) you are assured that the
db-file will resume its initial unchanged state.

there are obviously many other concepts to be learned
about SQL Databases, but the above list is the absolute
minimum you are expected to correctly understand.

[1] https://en.wikipedia.org/wiki/Database_transaction
[2] https://en.wikipedia.org/wiki/ACID

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

On Fri, 30 Dec 2016 11:51:46 -0800 (PST), Steven Elster wrote:
> Also, I am trying to use Spatilite-GIS
>

Hi Steven,

I still continue to have the impression that you are
consulting some really outdated and obsolete documentation.
this is the current official Web page of the SpatiaLite
family: http://www.gaia-gis.it/gaia-sins/index.html

as you can easily notice spatialite_gis is nowadays
a discontinued project, it's no longer maintained by
many years, and it's not expected to correctly work
when using db-files created by the latest versions
of libspatialite.

if you need a Desktop GIS in order to visually explore
your SpatiaLite databases QGIS is the right tool for you.

bye Sandro

[Steven Elster]

Sandro, Wow! Clearly, I have an antiquated mind-set re tables.  Thank you very much!!  Now, since "a whole database is completely stored within just a single file" and since some other software programs want to know where my database(s) is, apparently I need to know the name of the file that I am working on?  For instance, I see a single file on my hard drive that has been getting larger and larger.  It is: C:\Users\se\Documents\spatialite\db.sqlite.  Could this be the file in question?  (I also see lots of new entries in temp directories: C:\Users\se\AppData\Local\Temp\XXX.)  I ask this, because right now, when I open up the Spatialite GUI, I see one database and the tables I have created, but when I load qGIS and the qSpatialite plug-in, it shows be another database.

And, what do I have to do in order for qGis to know that I have made changes and that it should update its screen?  Do an sql * into a new table?  Issue a COMMIT command or include ACID in a command?  Then drag qGis's screen a bit, thereby forcing it to reload the database?
Thank you again, Steve

On Thursday, December 29, 2016 at 4:02:49 PM UTC-8, Steven Elster wrote:

[mj10777]

On Saturday, 31 December 2016 14:28:43 UTC+1, Steven Elster wrote:

Sandro, Wow! Clearly, I have an antiquated mind-set re tables.  Thank you very much!!  Now, since "a whole database is completely stored within just a single file" and since some other software programs want to know where my database(s) is, apparently I need to know the name of the file that I am working on?  For instance, I see a single file on my hard drive that has been getting larger and larger.  It is: C:\Users\se\Documents\spatialite\db.sqlite.  Could this be the file in question?  (I also see lots of new entries in temp directories: C:\Users\se\AppData\Local\Temp\XXX.)  I ask this, because right now, when I open up the Spatialite GUI, I see one database and the tables I have created, but when I load qGIS and the qSpatialite plug-in, it shows be another database.

And, what do I have to do in order for qGis to know that I have made changes and that it should update its screen?  Do an sql * into a new table?  Issue a COMMIT command or include ACID in a command?  Then drag qGis's screen a bit, thereby forcing it to reload the database?

If you have made changes to a TABLE in 'db.sqlite' that is loaded in QGIS

- all you need to to is move the screen a bit

If QGIS is not active

- it will reread the TABLE when started

If you add a new TABLE to 'db.sqlite'

- then you must 'Drag and Drop' the Database again

-- and select the new TABLE

I would advise that you give 'db.sqlite' a more unique name

- but then you would have to re-load all the needed TABLEs in QGIS again

-- when QGIS does find the old Database 'db.sqlite'

Mark

[Steven Elster]

Nothing happens when I try to drag files between SpatiaLite-GUI, qSpatiaLite, or qGis.  However, I had previously imported a shapefile, using the SpatiaLite-GUI. I am assuming that this is now inside of db.sqlite.   So, I copied and gave db.sqlite a new name: z.sqlite.  I was able to drag z.sqlite into qGis.  Is that what you mean by dragging a file into qGIS?   qGIS took a moment and then displayed z's tables.  They have the same names as the one used in the SpatialLite GUI.  Good.  But qGIS recognized them as vector files with geometry type = 0, even thought the geometry was presumably imported form original shapefile(s)?   But without geometry,  qGIS will not map anything? Rather it simply shows the tables as tables, in its legend.  Thank you so much, Steve