3

I am see differences between tables when I view them in spatialite-gui and in QGIS. I have created a table, added columns, and updated the columns in spatialite-gui. Within spatialite-gui I can view all the added columns, as well as the updated fields. However, when I loaded the same table into QGIS 2.2 and open the attribute table, I do not see the columns I have added. I am able to see all the columns in QspatiaLite, but not the attribute table in QGIS (see graphic below).

I am using a Windows 7 64-bit computer with QGIS 2.2 from OpenGeoSuites4W, and spatialite-gui 1.7.1

The following are the queries I have written to create, add columns, and update the table.

 Creating table with three columns, then adding geometry, and finally inserting geometry into the table from another using a union statement. This was first performed in spatialite- gui 1.7.1

 CREATE TABLE smc_bldg2d (
     gid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
     city TEXT NULL, state TEXT NULL, country TEXT NULL);


 SELECT AddGeometryColumn('smc_bldg2d', 'geom', 4326, 'MULTIPOLYGON', 'XY');


 INSERT INTO smc_bldg2d
     (city, state, country, geom)
 SELECT f.city AS city, STATE AS state, COUNTRY AS country, st_unaryunion(st_collect(f.Geometry)) AS geom
 FROM smc_bldg2d_usa_poly AS f
 GROUP BY city
 ORDER BY city;


 ALTER TABLE smc_bldg2d ADD COLUMN type TEXT;


 UPDATE smc_bldg2d
 SET type = 'vector'
 WHERE gid > 0;

enter image description here

Improve this question
6
  • Can you add some detail of how you added columns, and what versions of each tool you are using? Also, which version of the spatialite data format is your database in? Finally, can you update the question with details of how you updated columns and what the results of that were in each application?
    – BradHards
    Commented Apr 21, 2014 at 21:54
  • You still haven't provided all the information that I asked for. We can't see your screen, and have no idea what you've done. Please provide a coherent description instead.
    – BradHards
    Commented Apr 21, 2014 at 22:07
  • 1
    Brad I have added everything you asked for. The only thing I haven't told is that I added the table into QGIS straight from Qspatialite plugin. I have also added the table directly from the Spatialite import in QGIS. What you see is everything I see and have done. What it is showing is that the same table has different column results two packages (spatialite-GUI and QGIS). Is this a known problem with using spatialite?
    – Ryan Garnett
    Commented Apr 21, 2014 at 22:28
  • No you haven't. For example, you haven't shown how you created the geometry column. You haven't described exactly how you did the modifications (which software did you run those SQL commands in?). You still haven't described what happens in each software. Suggest you delete this question, and write a new one with step-by-step description of what you did in each application and what you see, and what happened instead.
    – BradHards
    Commented Apr 21, 2014 at 22:46
  • 2
    @BradHards Rather than deleting a Question that has not yet attracted any Answers, I would recommend that it is heavily revised instead (i.e. not by tacking on an update section). In other words, perhaps recommend deleting all the content and starting again rather than deleting the Question.
    – PolyGeo ♦
    Commented Apr 21, 2014 at 23:14

1 Answer 1

Reset to default
4

Spatialite manages its column information in additional tables. If these are out of sync with the column information of the database itself, QGIS is confused and does not always show the columns.

There is a bug report open with further details. You are more than welcome to help in implementing a proper fix or perform testing and provide further information.

As a temporary workaround: The following SQL query should help you to get the tables into sync again.

UPDATE geometry_columns_statistics set last_verified = 0;
SELECT UpdateLayerStatistics('smc_bldg2d');
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.