1

I know that I can add a new column to a spatialite table either through the Database Manager (Edit Table – Add Column) or through the Attribute Table – Field Calculator (create new field) but I don´t know which settings to use there to get a new column populating itself with an autoincrementing primary key. I guess it must be possible...

I also have the Plugins Table Manager and QSpatialite installed.


I´m still a bit confused. Why should the newly created "Test_table2" have a geometry column after I create it with above code. The symbol is just the normal table symbol and the field list looks like this

test_table2 after creation

If I then try the "Add Geometry" code above I do not see a "0" im the output but this:

Trying to add geometry column to test_table2

After this step the symbol changes from table symbol to the points symbol in the browser tab but its still not a valid table with geometry. I cant see the two points from my original layer when I show this table as a layer.

table changed but not everything is correct

If I try to "create the spatial index" by clicking next to the attention sign and confirm the following window with "Yes" (Do you want to create..." then something happens, the red sign disappears but still I can´t see my points in the map canvas.

If I try your other tipp and use the Recover Geometry though I get the following result and not even the symbol changes from the tables symbol into anything else:

Trying "Recover Geometry column"

I´d just like to understand whats going on here.

It´s probably got to do something with me being new at using code that I don´t recognize a mistake in dealing with this problem.

  • Read this stackoverflow.com/questions/631060/… – user30184 Aug 17 '14 at 20:30
  • Thanks for the link! I will try this, looks pretty simple and not too difficult concerning the code. I´ll tell you how it works out and change question to "answered" if I get it right. – Taya Aug 18 '14 at 21:02
1

The information in the other post helped and solved like "half" of my problem. A very easy solution is to make a new table with a pkuid field an get all records from the old table into the new one. I did that in Database Manager using the modified code provided by Freeman:

; Creating new table

CREATE TABLE test_table2
( PKUID INTEGER PRIMARY KEY AUTOINCREMENT ,
  short   TEXT,
  name    TEXT
);

; putting in old data

INSERT INTO test_table2(short, name)
SELECT short, name
FROM old_test_table;

; rename the new table to old name

DROP TABLE old_test_table;
ALTER TABLE test_table2 RENAME TO old_test_table;

I then had a new table with autoid but it wasn´t a spatialite point layer. So looking around i found that after the step of creating the table the following code would make it a spatialite table:

SELECT addGeometryColumn('test_table2', 'geometry', 4326, 'POINT', 2);

But somehow that doesn´t work. The table just stays the same, no additional geometry column after refreshing everything in DB Manager. Did I get the code wrong?

I then just did everything without using code by making a new spatialite layer/table through the menus. Here I set an autoincrementing pk and the same number and order of columns (with the same column headings) that the old table has. Then I manually copy all records from the old table into the new one. That solved the problem but I´d like to know whats wrong with my code above. Thanks for helping :)

0

If your first "test_table2" already had a geometry column, then the AddGeometryColumn() function will fail. (You will see a '0' in the resulting output).

To do this correctly try the RecoverGeometryColumn() function instead. It takes exactly the same parameters as AddGeometryColumn().

Alternately, you can check the meta table called "geometry_columns" and delete the old entry for the original test_table2, then retry AddGeometryC0lumn()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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