<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Markus Neteler napisał(a):
<blockquote cite="mid20070209234144.GC31181@bartok.itc.it" type="cite">
<pre wrap="">On Fri, Feb 09, 2007 at 06:59:06PM +0100, Jarek Jasiewicz wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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!
</pre>
</blockquote>
<pre wrap=""><!---->
Please post the command to the list...
thanks,
markus
</pre>
</blockquote>
Hi<br>
<br>
Here is the detail on what I worked (it is testing work for
demonstration only)<br>
<br>
1) I created two postgreSQL tables:<br>
<br>
<font face="monospace">CREATE TABLE profile --data with 5 geological
profiles<br>
(<br>
cat int2,<br>
elev int2,<br>
CONSTRAINT profile_pkey PRIMARY KEY (cat)<br>
) <br>
WITHOUT OIDS;<br>
<br>
<br>
CREATE TABLE samples --geological sampels in profiles (25 samples, 5
samples for every profiles)<br>
(<br>
id int4,<br>
pcat int2,<br>
depth float4,<br>
caco3 float4,<br>
res float4,<br>
sit float4,<br>
sio float4,<br>
loi float4<br>
) <br>
WITH OIDS;</font><br>
<br>
2) Next I created simple vector point layer with five points, with cat
values from 1 to 5, but without table <br>
(it is my way, I rather prefare to create vector and join it with
external table, than use grass form)<br>
<br>
3) I created view with following definition. It is very simple view,
but it can be expanded with, for example, WHERE cluase<br>
<br>
<pre>CREATE OR REPLACE VIEW vinet AS </pre>
<pre> SELECT p.cat, avg(s.sit)::float4 AS sit</pre>
<pre> FROM samples s</pre>
<pre> JOIN profile p ON p.cat = s.pcat</pre>
<pre> GROUP BY p.cat;</pre>
<br>
4) Nex I joined view to vector:<br>
<br>
<pre>v.db.connect map=vinet driver=pg database=testy table=vinet key=cat layer=1 </pre>
<br>
nd I recived messages (my comments after //)<br>
<br>
<pre><font face="monospace">Table <vinet> does not exist in database <testy> //there is no table</font></pre>
<pre><font face="monospace">
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</font></pre>
<pre><font face="monospace">DBMI-Postgres driver error:</font></pre>
<pre><font face="monospace">Cannot create index:</font></pre>
<pre><font face="monospace">create unique index vinet_cat on vinet ( cat )</font></pre>
<pre><font face="monospace">ERROR: "vinet" is not a table //something is, but it is not a table
</font></pre>
<pre><font face="monospace">
GRASS_INFO_WARNING(7111,3): Cannot create index</font></pre>
<pre><font face="monospace">
GRASS_INFO_WARNING(7111,4): Select privileges were granted on the table //but finally all is OK!
</font></pre>
<br>
As You can see, grass mesages are contray eachother<br>
<br>
5) For the end I tested it with :<br>
<br>
<pre>v.what -a map=vinet east_north=496736.547013,535807.533363</pre>
<br>
And I recived, as I expected:<br>
<br>
<pre>East: 496679.185592</pre>
<pre>North: 535864.894784</pre>
<pre>Map: vinet </pre>
<pre>Mapset: widoki</pre>
<pre>Point</pre>
<pre>
Layer: 1</pre>
<pre>Category: 2 </pre>
<pre>driver: pg</pre>
<pre>database: testy</pre>
<pre>table: vinet</pre>
<pre>key column: % //what that???????</pre>
<pre>cat : 2</pre>
<pre>sit : 25.44
</pre>
Addationally:<br>
<br>
<pre>v.surf.idw input=vinet output=result npoints=12 layer=1 column=sit</pre>
<br>
End I recived raster map as I expected.<br>
<br>
<br>
It seems grass can handle postgreSQL views but "he" don't know about it?<br>
So if it can handle views it shall also handle joins respectively?<br>
<br>
regards<br>
Jarek<br>
<br>
<br>
</body>
</html>