1

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?

  • 2
    Welcome to gis.stackexchange! It's great to have you here! Please note though that the format of this site is one question per thread. Please edit this thread down to one question and open another thread for the second question. You'll find more information about how this site works in our faq. – underdark Apr 28 '14 at 19:09
  • 1
    Hi Taya, I have taken the liberty of editing your question to keep only one question in this post; I did this to avoid it getting closed. You can revert it back if you wish, but I wouldn't suggest it. I'll also request you to open a new question for the second question that you had. It is an interesting problem, and I had solved something similar last week. I could answer that for you and solve your issue. – Devdatta Tengshe Apr 29 '14 at 4:31
  • Do you really want to add columns to the table, or do you really just want to see it as X/Y? The difference is updating the table vs creating a VIEW. – BradHards Apr 29 '14 at 4:48
  • Thanks everyone for the hearty welcome :) @BradHards: Yes I want to have the columns with the x and y values in the same table. That would be good because I have linked to that table in MS Access via ODBC so I can get a "full" set of data for one archaeological site including x/y data. I´m not that far in constructing the database in Access but thats the longterm plan. – Taya Apr 29 '14 at 8:32
  • @Devdatta: Thanks for editing the question. I´ll post the second part separately :) – Taya Apr 29 '14 at 8:35
2

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.

  • Are you sure about data type NUMBER? I would suggest REAL: sqlite.org/datatype3.html – AndreJ Apr 29 '14 at 6:03
  • @AndreJoost: Yes you are right, it should be Real Data type. – Devdatta Tengshe Apr 29 '14 at 6:07
  • Problem: I opened QSpatialite in QGis, selected my Table named "Arch_Locations.sqlite" in the upper left dropdown menu and marked it in the left panel with the mouse. Then I altered the code above to: ALTER TABLE 'Arch_Locations' ADD COLUMN X REAL ALTER TABLE 'Arch_Locations' ADD COLUMN Y REAL I clicked on "Run SQL" and got the error: "Near ALTER : Syntax Error" Btw: I have QGIS in german, could that be the problem. If yes I´d reinstall in english. – Taya Apr 29 '14 at 9:01
  • Perhaps its better to work on the table in the Database Manager? I can add columns there through a simple click on "add column" and select "real" as column type? – Taya Apr 29 '14 at 9:08
  • I changed language to english in the Options - language menu. – Taya Apr 29 '14 at 9:16
3

For the X and Y columns, you can use the field calculator to add those fields. Under the Geometry node, you find $X and $Y.

But please consider that moving a node does not change the X and Y in the attribute table as well. It is just a static value from the moment you create the column.

0

After some frustrating hours of trial and error in QGIS as well as in Access I finally got it working the way I want it to. Here´s how:

In the attribute table I created two new columns (type decimal double). The trick was to enable editing ;) I saved edits and de- and reactivated editing.

In the Field calculator I then set the expression for those columns through "modifiy existing fields" to $x and $y. Saved everything and quit QGIS.

Its funny but sometimes the newly created columns are not visible in the attribute table after saving everything I have done. If that happens they are still listed in the Database Manager as existing though ...

In MS Access I then created a new ODBC Database Link (linkt to table) to this Layer and now I can see the following fields

Name Geometry X Y

X and Y are shown as Decimal number with 15 values behind the comma.

I tested what you said about values not changing when I move Points. I guess I´ll just have to go through the "Modifiy existing fields" process again after doing changes on the layer. Thats ok, I´ll just have to remember ;)

I´ll repost my second question later, need some time away from tables now ;) Thanks everyone for the help. Without your input I wouldn´t have persisted.

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