[GRASS-user] Copy and concatenate a vector attribute between layers

[Moritz Lennert]

Am 7. Dezember 2017 14:42:38 MEZ schrieb Markus Metz <markus.metz.giswork at gmail.com>:
>On Thu, Dec 7, 2017 at 9:21 AM, Moritz Lennert
><mlennert at club.worldonline.be>
>wrote:
>>
>>
>>
>> Am 6. Dezember 2017 19:45:49 MEZ schrieb Peter Tittmann <
>ptittmann at gmail.com>:
>> >Moritz,
>> >
>> >v.db.connect -p c34_patch
>> >Vector map <c34_patch> is connected by:
>> >layer <1/c34_patch> table <c34_patch> in database
>> ></Users/pete/grassdata/<FOO>/project_area/sqlite/sqlite.db> through
>> >driver <sqlite> with key <cat>
>> >
>> >So it seems that only layer 1 is connected.
>> >
>> >When try to connect layer 2:
>> >
>> >v.db.connect map=c34_patch at project_area table=c34_patch layer=2
>> >ERROR: Use -o to overwrite existing link for layer <2>
>>
>> This is weird. This message normally only comes when there really is
>a
>connection defined for this layer:
>
>The reason for the error could be that the table c34_patch is already
>attached to layer 1.

In my NC data example you can see that it works as expected when attaching layer 2 to the same table as layer 1.

>
>I guess what is needed here is v.db.addtable map=c34_patch layer=2

v.db.addtable will create a new, empty table containing only the vector features' cat values. In the OP, Peter indicated that he had two layers with respective attribute tables, containing the attributes Strata and Stand. Unless these two attributes are included in the two layers as the respective category values, I don't think v.db.addtable will help.

But that's why we need more info...

Moritz


>
>Now you could proceed as suggested by Moritz:
>
>- Create a new column in layer 1 where you can put the info from layer
>2
>- Fill this column with v.to.db option=query query_layer=2, etc
>- Create a second new column to hold the concatenation
>- v.db.update to update this new column with the concatenation of the
>two
>others (something like qcol = "STRATA || '_' || STAND")
>
>Markus M
>
>>
>> GRASS 7.2.2 (nc_spm_08):~ > g.copy v=hospitals,test
>> Copy vector <hospitals at PERMANENT> to current mapset as
>> <test>
>>  GRASS 7.2.2 (nc_spm_08):~ > v.db.connect -p test
>> Vector map <test> is connected by:
>> layer <1/hospitals> table <test> in database
></data/home/mlennert/GRASSDATA/nc_spm_08/user1/sqlite/sqlite.db>
>> through driver <sqlite> with key <cat>
>> GRASS 7.2.2 (nc_spm_08):~ > v.db.connect test layer=2 table=test
>> The table <test> is now part of vector map <test> and may be deleted
>or
>> overwritten by GRASS modules
>> Select privileges were granted on the table
>> GRASS 7.2.2 (nc_spm_08):~ > v.db.connect -p test
>> Vector map <test> is connected by:
>> layer <1/hospitals> table <test> in database </data/hom
>> e/mlennert/GRASSDATA/nc_spm_08/user1/sqlite/sqlite.db>
>> through driver <sqlite> with key <cat>
>> layer <2/test> table <test> in database
></data/home/mlennert/GRASSDATA/nc_spm_08/user1/sqlite/sqlite.db>
>through
>driver <sqlite> with key <cat>
>> GRASS 7.2.2 (nc_spm_08):~ > v.db.connect test layer=2 table=test
>> ERROR: Use -o to overwrite existing link for layer <2>
>>
>> So I don't really understand why you get the error message...
>>
>> >
>> >I’m little unclear on the ‘table’ argument, maybe that should be a
>now
>> >table?
>>
>> Although there might be situations where one could want to connect
>two
>layers to the same table, generally each layer is connected to a
>different
>table.
>>
>> In your original post you said the vector had two layers with
>attributes.
>This does not seem to be the case when I see the v.db.connect -p
>output.
>Maybe if you go a bit back and explain to us in what format the
>original
>data is and how you imported it, this might help us help you.
>>
>> Moritz
>>
>> >
>> >Thanks very much.
>> >
>> >Peter
>> >> On Dec 6, 2017, at 10:27 AM, Moritz Lennert
>> ><mlennert at club.worldonline.be> wrote:
>> >>
>> >>
>> >>
>> >> Am 6. Dezember 2017 18:42:22 MEZ schrieb Peter Tittmann
>> ><ptittmann at gmail.com <mailto:ptittmann at gmail.com>>:
>> >>> Thank you, Moritz.
>> >>>
>> >>> I'm pretty familiar with SQL but can’t figure out how layer 2 is
>> >stored
>> >>> in the database. When I `pragma table_info(<vector>)` I only see
>the
>> >>> layer 1 attributes.
>> >>
>> >> v.db.connect -p (or grass.script.vector_db() )will show you all
>> >connections between the map and database tables.
>> >>
>> >>
>> >> Moritz
>> >>>
>> >>> I’ll work with the GRASS approach you suggested.
>> >>>
>> >>> Best,
>> >>> Peter
>> >>>
>> >>>
>> >>>> On Dec 1, 2017, at 11:53 PM, Moritz Lennert
>> >>> <mlennert at club.worldonline.be> wrote:
>> >>>>
>> >>>> On 02/12/17 02:03, Peter Tittmann wrote:
>> >>>>> Hi,
>> >>>>> I have a vector (`POINT`) which has two layers containing
>relevant
>> >>> attributes (’strata’ and ’stand’). I would like to concatenate
>the
>> >two
>> >>> values into a new attribute for Layer 1 (’<STRATA>_<STAND>’).
>I’ve
>> >been
>> >>> struggling to sort this out for too long and am tapping out. Its
>> >>> probably blatantly obvious but it has escaped me.
>> >>>>
>> >>>>
>> >>>> Probably the easiest way if your not very familiar with SQL:
>> >>>>
>> >>>> - Create a new column in layer 1 where you can put the info from
>> >>> layer 2
>> >>>> - Fill this column with v.to.db option=query query_layer=2, etc
>> >>>> - Create a second new column to hold the concatenation
>> >>>> - v.db.update to update this new column with the concatenation
>of
>> >the
>> >>> two others (something like qcol = "STRATA || '_' || STAND")
>> >>>>
>> >>>> Otherwise, if there is a common key in both tables, you can just
>> >>> create a new column and use db.execute to launch a UPDATE TABLE
>> >>> directly, grabbing the info from the two table and concatenating
>it
>> >>> directly in the query.
>> >>>>
>> >>>> Moritz
>> >>>>
>> >>>>
>> >>>>> Thank you!
>> >>>>> Here are some specifics.
>> >>>>> east, north: 651849.842735, 6604949.78485
>> >>>>> c34_patch at project_area:
>> >>>>>  Type: Point
>> >>>>>  Id: 21622
>> >>>>>  Layer: 1
>> >>>>>  Category: 21623
>> >>>>>  Driver: sqlite
>> >>>>>  Database:
>> >>> /Users/pete/grassdata/PortGraham/project_area/sqlite/sqlite.db
>> >>>>>  Table: c34_patch
>> >>>>>  Key_column: cat
>> >>>>>  Attributes:
>> >>>>>    cat: 21623
>> >>>>> *strata: 3  — STRATA*
>> >>>>> c34_patch at project_area:
>> >>>>>  Type: Point
>> >>>>>  Id: 21622
>> >>>>>  Layer: 2
>> >>>>> *Category: 10  — STAND*
>> >>>>> _______________________________________________
>> >>>>> grass-user mailing list
>> >>>>> grass-user at lists.osgeo.org <mailto:grass-user at lists.osgeo.org>
>> ><mailto:grass-user at lists.osgeo.org
><mailto:grass-user at lists.osgeo.org>>
>> >>>>> https://lists.osgeo.org/mailman/listinfo/grass-user
>> ><https://lists.osgeo.org/mailman/listinfo/grass-user>
>> >>> <https://lists.osgeo.org/mailman/listinfo/grass-user
>> ><https://lists.osgeo.org/mailman/listinfo/grass-user>>
>> _______________________________________________
>> grass-user mailing list
>> grass-user at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/grass-user