[GRASS-user] grass v.db.join

GRASS GRASS user
1

Hi I have a set of points and I have a database table

I have imported the point dataset into the database and it is queriable from
grass

I want to join this data to another table

+---+-------------+ +-----------+-----------
|cat|SquareNumber| |FindNumber|More data....
|1 |1 | |1 |More data....
|2 |2 | |2 |More data....
|3 |3 | |2 |More data....

I want a join on SquareNumber=FindNumber

As you will see it is a 1 to many relationship

I use v.db.join bone_points col=SquareNumber otable=Zoological
ocol=FindNumber

and i get the error

DBMI-MySQL driver error:
Cannot execute:
UPDATE bone_points SET SiteCode=(SELECT SiteCode
FROM Zoological WHERE Zoological.FindNumber=bone_points.SquareNumber)
Subquery returns more than 1 row

ERROR: Error while executing: 'UPDATE bone_points SET SiteCode=(SELECT
       SiteCode
       FROM Zoological WHERE
       Zoological.FindNumber=bone_points.SquareNumber)'

This is because of more than 1 row being returned, is there anyway I can do
this?
I need a 1 to many relationship!

Thank you

Gary
--
View this message in context: http://n2.nabble.com/grass-v-db-join-tp4721541p4721541.html
Sent from the Grass - Users mailing list archive at Nabble.com.

2

Well I sorted it out, I assigned a new unique Identifer for each row.

the next problem I have is that v.db.join takes the table but changes
varchar(10) to char(1) and so truncates the records!

How do I get round this?

I'm using MySQL

Thanks

Gary
--
View this message in context: http://n2.nabble.com/grass-v-db-join-tp4721541p4723884.html
Sent from the Grass - Users mailing list archive at Nabble.com.

3

Gary Nobles wrote:

the next problem I have is that v.db.join takes the table
but changes varchar(10) to char(1) and so truncates the records!

How do I get round this?

I'm using MySQL

what does "db.describe -c" say about the table?

Hamish

4

db.descibe -c returns:

ncols: 27
nrows: 811
Column 1: cat:INTEGER:11
Column 2: SquareNumb:DOUBLE PRECISION:22
Column 3: UniqueIden:DOUBLE PRECISION:22
Column 4: UniqueIdentifier:INTEGER:11
Column 5: SiteCode:CHARACTER:1
Column 6: FindLocation:INTEGER:11
Column 7: SampleNumber:TEXT:65535
Column 8: TrenchNumber:INTEGER:11
Column 9: LayerNumber:INTEGER:11
Column 10: SquareNumber:CHARACTER:1
Column 11: IDZoological:INTEGER:11
Column 12: FindSubNumber:INTEGER:11
Column 13: Class:CHARACTER:1
Column 14: Type:CHARACTER:1
Column 15: Element:CHARACTER:1
Column 16: Part:CHARACTER:1
Column 17: l_r:CHARACTER:1
Column 18: Count:INTEGER:11
Column 19: Weight:DOUBLE PRECISION:22
Column 20: Proximal:INTEGER:11
Column 21: Distal:INTEGER:11
Column 22: Age:INTEGER:11
Column 23: sex:CHARACTER:1
Column 24: Sizes:CHARACTER:1
Column 25: Association:CHARACTER:1
Column 26: Comments:CHARACTER:1
Column 27: LASTMODIFIED:TIMESTAMP :19

The original table which was joined to this one, from which I want the same
structure is:

ncols: 21
nrows: 890
Column 1: UniqueIdentifier:INTEGER:10
Column 2: TrenchNumber:INTEGER:1
Column 3: LayerNumber:INTEGER:1
Column 4: SquareNumber:CHARACTER:50
Column 5: IDZoological:INTEGER:5
Column 6: FindSubNumber:INTEGER:3
Column 7: Class:CHARACTER:10
Column 8: Type:CHARACTER:10
Column 9: Element:CHARACTER:10
Column 10: Part:CHARACTER:10
Column 11: Position:CHARACTER:10
Column 12: Count:INTEGER:4
Column 13: Weight:DOUBLE PRECISION:5
Column 14: Proximal:INTEGER:5
Column 15: Distal:INTEGER:5
Column 16: Age:INTEGER:5
Column 17: Sex:CHARACTER:10
Column 18: Sizes:CHARACTER:10
Column 19: Association:CHARACTER:30
Column 20: Comments:CHARACTER:50
Column 21: LASTMODIFIED:TIMESTAMP :19

Thank you for your help

Gary
--
View this message in context: http://n2.nabble.com/grass-v-db-join-tp4721541p4743397.html
Sent from the Grass - Users mailing list archive at Nabble.com.