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

[Moritz Lennert]

On 10/02/07 11:41, Jarek Jasiewicz wrote:
> 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?

I agree that these messages are annoying.


They come from the fact that v.db.connect uses the function 
db_table_exists from lib/db/dbmi_client/table.c which in turn uses 
db_list_tables from lib/db/dbmi_client/c_list_tabs.c which then calls 
backend specific commands to list tables. For example, the PostgreSQL 
driver uses (in function db__driver_list_tables in 
db/drivers/postgres/listtab.c):

select * from pg_tables where tablename !~ 'pg_*' order by tablename

which does not include views.

It should not be too difficult to amend the individual drivers to handle 
views. db_table_exists would probably have to call another function 
db_list_views for which the backend specific commands would need to be 
written ...

> So if it can handle views it shall also handle joins respectively?

I am afraid that this would mean a much more important rewrite of the 
GRASS db libs, but I am not expert enough to judge.

Moritz