[GRASSLIST:9510] Re: switching to postgres

[Kirk Wythers]

On Dec 15, 2005, at 2:34 AM, Radim Blazek wrote:

> Each new vector will store attributes in database previously set by
> db.connect. If you have a vector with attributes in dbf files,
> you set your db to postgres (db.connect driver=pg datbase=...)
> and then copy the old vector with g.copy the new one will use
> postgres for attributes.

Here are the results of the above procedure using the spearfish data  
(I'm attempting to narrow down the possible sources of error):

1. I created a database named spearfish_soils in postgres
truffula:/usr/local/pgsql/bin kwythers$ psql -l
            List of databases
       Name       |  Owner   | Encoding
-----------------+----------+-----------
postgres        | postgres | SQL_ASCII
spearfish_soils | kwythers | SQL_ASCII
template0       | postgres | SQL_ASCII
template1       | postgres | SQL_ASCII
timber          | kwythers | SQL_ASCII
ts2325          | kwythers | SQL_ASCII
(6 rows)


2. I set the db connection to postgres with db.connect, then checked  
to make sure it was set properly

GRASS 6.1.cvs (spearfish60):~ > db.connect -p
driver:pg
database:host=localhost,dbname=spearfish_soils
schema:(null)
group:(null)

3. I used g.copy to copy soils from PERMANENT to spearfish_soils in  
the current mapset

GRASS 6.1.cvs (spearfish60):~ > g.copy vect=soils,spearfish_soils
COPY [soils at PERMANENT] to current mapset as [spearfish_soils]


>
>> GRASS 6.1.cvs (arrowhead_utm):~ > db.copy from_driver=dbf
>> from_database=./ from_table=townships to_driver=pg
>> to_database="host=localhost,dbname=ts2325" to_table=townships
>>
>> and got the error:
>>
>> DBMI-DBF driver error:
>> Table 'townships' doesn't exist.
>> Error in db_open_select_cursor()
>
> That could be a bug in case you have townships.shp in your current  
> directory.
>
>

4. Check to see that the vector file 'spearfish_soils' exists... it does

GRASS 6.1.cvs (spearfish60):~ > g.list vect
----------------------------------------------
vector files available in mapset user1:
spearfish_soils

vector files available in mapset PERMANENT:
archsites       soils           t_9961_300      t_9961_500_bks   
t_roads_second
bugsites        streams         t_9961_300_all  t_county        t_tracts
fields          t_9961_100      t_9961_300_bks  t_hydro         tractids
quads           t_9961_100_all  t_9961_400      t_powerlines    tracts
railroads       t_9961_100_bks  t_9961_400_all  t_rails          
transport_misc
roads           t_9961_200      t_9961_400_bks  t_roads          
trn_sites
rstrct_areas    t_9961_200_all  t_9961_500      t_roads_inf      
twp_range
sections        t_9961_200_bks  t_9961_500_all  t_roads_prime

5. Check to see that the data was copied from dbf 'soils' to pg  
'spearfish_soils'

spearfish_soils=# SELECT * FROM spearfish_soils;
cat | label
-----+-------
    1 | Aab
    2 | Ba
    3 | Bb
    4 | BcB
    5 | BcC
    6 | BeE
    7 | Bhe
    8 | Bkd
    9 | CBE
   10 | CaD
   11 | CaE
   12 | Cc
   13 | GBE
   14 | GaD
   15 | GcD
   16 | GdE
   17 | GeD
   18 | HBF
   19 | Ha
   20 | KaB
   21 | LaE
   22 | MaC
   23 | MaD
   24 | McD
   25 | NaB
   26 | NaC
   27 | Nac
   28 | NbD
   29 | NcD
   30 | NdB
   31 | NdC
   32 | PbE
   33 | PcB
   34 | PcD
   35 | Pe
   36 | RBF
   37 | RCF
   38 | RaE
   39 | SaA
   40 | SaB
   41 | SbB
   42 | Sd
   43 | ShA
   44 | Sk
   45 | TaA
   46 | TaB
   47 | TaC
   48 | VBF
   49 | VCE
   50 | VaA
   51 | VaB
   52 | VaC
   53 | WaA
   54 | Wb
(54 rows)

spearfish_soils=#


So far so good. This is looking great!

6. use db.describe to check out the vector in GRASS

GRASS 6.1.cvs (spearfish60):~ > db.describe table=spearfish_soils  
driver=pg database=spearfish_soils
DBMI-Postgres driver error:
select * from spearfish_soils where oid < 0
ERROR:  column "oid" does not exist

What is oid? I can see from the columns that postgres printed out  
that I have only columns cat and label, no oid. Is this something  
that should have copied and did not?

Thanks,

Kirk

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/grass-user/attachments/20051215/cfbd4e5c/attachment.html