qgis - Adding columns with x/y data to SpatiaLite Layer?

I am quite new at working with QGIS.

My Spatialite Database looks like this (arch_locations.sqlite)

Fields:
pkuid

short_ID
name
geometry

I´d also like to have two additional columns in that table in QGIS (in the Spatialite Database) where it displays the X and Y coordinates. In the "geometry" field I only see a very long combination of numbers and letters.

While looking for a way to adding those columns I found this short guide:

http://pvanb.wordpress.com/2012/02/21/extract-x-and-y-coordinates-from-a-spatial-table-in-spatialite/

Using the Plugin "QSpatialite" and modifying the following code passage I managed to create another Table in QGIS with two columns containg the x and y value. I adapted this code:

CREATE TABLE 'Test' AS


SELECT PKUID, X(Geometry) as X, Y(Geometry) as Y

FROM Points

I did not even know where to enter the code until I installed the plugin.

Though, what I really want is to add two additional columns in the original table and to display the x/y values in those.

How do I do this?

Answer

If you want to add X & Y fields to an existing table and then populate it with the X & Y values, it is a two step process. Let us assume your table is called Capitals. You use the following SQL commands to achieve what you need.

• First add these two columns to your table:

ALTER TABLE 'Capitals' ADD COLUMN X REAL
ALTER TABLE 'Capitals' ADD COLUMN Y REAL

• Then populate these two columns with the X & Y values with the following command

UPDATE 'Capitals' SET X=ST_X("Geometry") , Y=ST_Y("Geometry")

As Andre has mentioned in his answer, please note that these values will not change if you update the geometry. you will have to repeat the second step if you need updated values.