[GRASS-user] pg driver and views

GRASS GRASS user
,
1

Hi all
Tis is not a question, rather remark

I experiment a little with using pg views with grass
generally grass can handle views via db.select and db.execute without problems and warnings

It is also possible to connect vetor file to view, and use attributes of view as a labels etc.. It also works in qgis
I add, the view is created from two joined tables!

I can't say nothing about digitising of vector connected with view becouse I plan to try it soon (first I must prepare view)

Connecting vector with view gives folloowing messages:

Table <widok> does not exist in database <testy>

The table <widok> is now part of vector map <punkty_test> and may be deleted or overwritten by GRASS modules
DBMI-Postgres driver error:
Cannot create index: // (jj-comment) it is because (kot) is not unique
create unique index widok_kot on widok ( kot )
ERROR: "widok" is not a table

GRASS_INFO_WARNING(5538,3): Cannot create index

GRASS_INFO_WARNING(5538,4): Select privileges were granted on the table

As you can see first grass is "suprised" that something isn't table but looks similar
   Next vector is joined to view as to table: look: grass see the view as a table!
Next grass "concludes" that "widok" is not the table and returns error, but vector is joined.

I'm iterested in this so my question is very simple. Is it somewhere described? (anything about views in grass - simple google query returns nothing). I only found in help recomendation to use view in v.in.db with huge tables

regards
Jarek

2

Markus Neteler napisał(a):

On Fri, Feb 09, 2007 at 06:59:06PM +0100, Jarek Jasiewicz wrote:
  

Hi all
Tis is not a question, rather remark

I experiment a little with using pg views with grass
generally grass can handle views via db.select and db.execute without 
problems and warnings

It is also possible to connect vetor file to view, and use attributes of 
view as a labels etc.. It also works in qgis
I add, the view is created from two joined tables!

    


Please post the command to the list...

thanks,
markus

Hi

Here is the detail on what I worked (it is testing work for demonstration only)

  1. I created two postgreSQL tables:

CREATE TABLE profile --data with 5 geological profiles
(
cat int2,
elev int2,
CONSTRAINT profile_pkey PRIMARY KEY (cat)
)
WITHOUT OIDS;

CREATE TABLE samples --geological sampels in profiles (25 samples, 5 samples for every profiles)
(
id int4,
pcat int2,
depth float4,
caco3 float4,
res float4,
sit float4,
sio float4,
loi float4
)
WITH OIDS;

  1. Next I created simple vector point layer with five points, with cat values from 1 to 5, but without table
    (it is my way, I rather prefare to create vector and join it with external table, than use grass form)

  2. I created view with following definition. It is very simple view, but it can be expanded with, for example, WHERE cluase

CREATE OR REPLACE VIEW vinet AS 

 SELECT p.cat, avg(s.sit)::float4 AS sit

   FROM samples s

   JOIN profile p ON p.cat = s.pcat

  GROUP BY p.cat;
  1. Nex I joined view to vector:
v.db.connect map=vinet driver=pg database=testy table=vinet key=cat layer=1

nd I recived messages (my comments after //)

Table <vinet> does not exist in database <testy> //there is no table


The table <vinet> is now part of vector map <vinet> and may be deleted or overwritten by GRASS modules //table is, and is connected to vector

DBMI-Postgres driver error:

Cannot create index:

create unique index vinet_cat on vinet ( cat )

ERROR:  "vinet" is not a table //something is, but it is not a table



GRASS_INFO_WARNING(7111,3): Cannot create index


GRASS_INFO_WARNING(7111,4): Select privileges were granted on the table //but finally all is OK!

As You can see, grass mesages are contray eachother

  1. For the end I tested it with :
v.what -a map=vinet east_north=496736.547013,535807.533363

And I recived, as I expected:

East: 496679.185592

North: 535864.894784

Map: vinet 

Mapset: widoki

Point

Layer: 1

Category: 2 

driver: pg

database: testy

table: vinet

key column: % //what that???????

cat : 2

sit : 25.44

Addationally:

v.surf.idw input=vinet output=result npoints=12 layer=1 column=sit

End I recived raster map as I expected.

It seems grass can handle postgreSQL views but “he” don’t know about it?
So if it can handle views it shall also handle joins respectively?

regards
Jarek