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 remarkI experiment a little with using pg views with grass
generally grass can handle views via db.select and db.execute without problems and warningsIt 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,
markusHi
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