[GRASS-user] pg driver and views (long)

[Jarek Jasiewicz]

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;

2) 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)

3) 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;


4) 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

5) 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


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