[GRASSLIST:338] vector and postgresql questions

Thu Mar 23 09:16:32 EST 2006

Thanks Eric and everyone else,
Using your tip I managed to do the job in OpenOffice Calc. But in the last
few days with a friend's help i am moving over to postgresql as my database
driver, since i got the impression from this list that its better.

now i do need help figuring out how to use vector and database tabl,es in
GRASS.
So far I have 2 tables in my database. One is my masterfile, a table called
'stations ' that looks like this: WMoID, city, lat, long

420710,AMRITSAR, 31.63,74.86
421010,PATIALA,30.33,76.46
421310,HISSAR,29.16,75.73
421820,DELHI,28.58,77.20

and i prepared a table called 'data', which looks like this (i'm copying
from openoffice thats why it looks a bot strange):

   WMOID,N,10,0 CITY,C,50 YEAR,N,5,0 YDAY,N,4,0 TMAX,N,10,2 TMIN,N,10,2
TDAY,N,10,2 RAIN,N,10,2 VPD,N,10,2 SRAD,N,10,2 DAYLEN,N,10,0

 427240 Agartala 1995 1 26 8.5 21.19 0 1112.83 341.2 37998

 427240 Agartala 1995 2 24 7.39 19.43 0 1082.93 339.33 38019

 427240 Agartala 1995 3 22.72 4.89 17.82 0 1097.42 360.24 38041

 427240 Agartala 1995 4 24 4 18.5 0 1084.58 377.3 38065

 427240 Agartala 1995 5 25.11 5.22 19.64 0 1155.05 374.59 38091

for each station.

What i want to do, and dont know how to (i looked at the GRASS online help
which is just not enough for someone new to databases):
1. I want to use the 'stations' table to bring in as a vector map. When i
had the 'stati

ons' as a csv file, i used v.in.ascii. i dont know what to use when its in a
table in a pg database.

2. I also want to link the 'stations' to the 'data' table. i did this using
v.db.connect and it seemed to work. But i dont know how its linking. When i
had used v.in.ascii on a csv file, i had defined the WMOID as cat; and then
in ' v.db.connect i had WMOID in the 'data' file be the key. But then it
did'nt like it at all when i tried a SQL query- it seemed to not like having
WMOID as the key, because it had duplicates. But how else can i link the 2
tables?

3. My final goal is to be able to write a script, where i can have every
day's data from a particular column, become a vector map. To do this would i
need my intial points vector file to be a 3d vector file?

any help would be great!
thanks,
vishal

On 3/17/06, Patton, Eric < epatton at nrcan.gc.ca> wrote:
>
> Vishal,
>
> If you open your dbf in OpenOffice Calc, you are able to edit the data
> types
> for each column. Here's an example from one of my dbf's:
>
> CAT,N,11,0      CLASS_CODE,N,20,6       NEW_CLASS,C,50  Length,N,20,2
> 9       1       Rock Shores     961.07
> 178     1       Rock Shores     661.39
> 494     1       Rock Shores     768.71
> 508     1       Rock Shores     543.68
> 511     1       Rock Shores     989.52
>
> The columns aren't going to line up nice in this email, but you get the
> idea. Under the first column heading, 'CAT', there is an 'N' following it
> signifying that this is a number, that 11 digit maximum is acceptable, and
>
> that 0 decimals are allowed. db.columns in Grass would report that this is
> an INTEGER datatype. You can edit these values to accept larger integer
> values. If you want floating point datatype for this column, change the
> final zero to match whatever the desired precision of your data.
>
> The fourth column of data in my example corresponds to the Length column,
> and is floating point data as you can see. If I wanted more decimal
> places,
> I would chage the 2 to 6, say. The Length column could also be made into
> an
> integer column simply by changing the ending '2' to a 0.
>
>
> Hope this helps,
>
> ~ Eric.
>
>
>
>
>
>
>
>
> -----Original Message-----
> From: owner-GRASSLIST at baylor.edu
> To: David Finlayson
> Cc: Stefan Istvan; GRASS Users list
> Sent: 3/17/2006 3:00 AM
> Subject: [GRASSLIST:199] Re: trouble using v.in.ascii
>
> Hi all,
>
> ok i was able to get the points vector file done- silly syntax mistake
> on my part-sorry.
> But i could use some input on my attempts to connect my data dbf file to
> the vector points file as layer 2.
>
> What i did is, that i prepared a dbf file in Windows, then brought it
> into my /dbf folder in Linux/Grass. Now this file is recognized when i
> do db.describe(below):
>
> GRASS 6.0.1 (ncdc):~/Desktop/NCDC > db.describe -c Data
> ncols:11
> Column 1: WMOID
> Column 2: CITY
> Column 3: YEAR
> Column 4: YDAY
> Column 5: TMAX
> Column 6: TMIN
> Column 7: TDAY
> Column 8: RAIN
> Column 9: VPD
> Column 10: SRAD
> Column 11: DAYLEN
>
> BUT, the column 'WMOID' which i want to use as key, has suddenly become
> float (it was integer when i made it in windows), and in fact  all
> numeric columns are assigned double precision now when they were not
> originally. How do i rectify this, since to identify a key tolink my
> vector points to this data file, the key column should be integer?
>
> I dont know postgresql, mysql etc, therefore i'm trying things this
> way..
>
> thanks,
> vishal
>
>
> On 3/17/06, David Finlayson < david.p.finlayson at gmail.com
> <mailto:david.p.finlayson at gmail.com > > wrote:
>
> Breezy also has the "flip" command (search synaptic) for converting
> line endings.
>
> -David
>
> On 3/16/06, Stefan Istvan < stefi at geohidroterv.hu
> <mailto:stefi at geohidroterv.hu> > wrote:
> > On Fri, 2006-03-17 at 11:30 +0530, Vishal Mehta wrote:
> > > Hi all,
> > >
> > > I'm trying to bring in a comma separated text file as a point vector
> > > file in GRASS using v.in.ascii , before i try to link it to a large
> > > 33Mb dbf data file.
> > >
> > > The csv file looks like this with 39 rows and i removed the header
> > > because grass60 (on Ubuntu breezy) does not have the skip option in
> > > the v.in.ascii command: the columns are WMOID, cityname, Latitude
> and
> > > Longitude. I want to use the WMOID as 'cat' in order to link my dbf
> > > data later.
> > >
> > > 420710,AMRITSAR, 31.63,74.86
> > > 421010,PATIALA,30.33,76.46
> > > 421310,HISSAR,29.16,75.73
> > > 421820,DELHI,28.58,77.20
> > > 423390,JODHPUR,26.30,73.01
> > > 423480,JAIPUR,26.81,75.80
> > > 423690,LUCKNOW, 26.75,80.88
> > > 424100,GAUHATI,26.10,91.58
> > >
> > > I Tried the following command and got the error:
> > >
> > >  cat reformat/STATIONS2.dat | v.in.ascii output=stations2 fs=','
> > > columns='cat int, name(varchar(30), y double, x double' x=4 y=3
> cat=1
> > > WARNING: DOS text format found, attempting import anyway
> > > Maximum input row length: 39
> > > Maximum number of columns: 4
> > > Minimum number of columns: 4
> > > column: 1  type: integer
> > > column: 2  type: string  length: 18
> > > column: 3  type: double
> > > column: 4  type: double
> > > DBMI-DBF driver error:
> > > SQL parser error in statement:
> > > create table stations2 ( cat int, name(varchar(30), x double, y
> > > double )
> > > Error in db_execute_immediate()
> > >
> > > ERROR: Cannot create table: create table stations2 ( cat int,
> > >        name(varchar(30), x double, y double )
> > >
> > > I also get the same error with
> > >
> > > v.in.ascii input=reformat/STATIONS.csv output=gstations fs=','
> skip=1
> > > columns='cat int, label(varchar(30), y double, x double' x=4 y=3
> cat=1
> > >
> > There is an additional "(" sign in your columns definition. You should
> > write columns='cat int, label varchar(30), y double, x double'.
> > The other problem maybe that your file is in MSDOS format, but it
> could
> > be that it doesn't bother Grass. Try to use dos2unix command to
> convert
> > it to unix format.
> >
> > Regards,
> > Istvan
> >
> >
>
>
> --
> David Finlayson
>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/grass-user/attachments/20060323/e0bc3642/attachment.html