<html style="direction: ltr;">
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
<style>body p { margin-bottom: 0cm; margin-top: 0pt; } </style>
</head>
<body style="direction: ltr;"
bidimailui-detected-decoding-type="UTF-8" bgcolor="#FFFFFF"
text="#000000">
On 26/11/2011 21:40, Daniel Lee wrote:
<blockquote
cite="mid:CAJHDHfqrfHawcVOwe0HP=rfVW9jj_zhEMpyWLOvMNAZYhvQ_gQ@mail.gmail.com"
type="cite">
<div class="gmail_quote">Alright, I think the last suggestion got
me a LOT further.</div>
<div class="gmail_quote"><br>
</div>
<div class="gmail_quote">2011/11/26 Micha Silver <span dir="ltr"><<a
moz-do-not-send="true" href="mailto:micha@arava.co.il">micha@arava.co.il</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div style="direction:ltr" bgcolor="#ffffff" text="#000000">My
guess: Wasn't your Gebaeude_Globalstrahlung table also a
GRASS vector? So it also has a cat column?<br>
What v.db.join does is try to create a new column in the
map's table for *each* column in the joined table. If a
column name already exists, it bails out. <br>
</div>
</blockquote>
<div>Gotcha. What I don't understand though is how you join a
table at all then. Don't you have to base the join on columns
that are already there? No matter if I'm working with vectors
or pure tables or a combination thereof, the columns with the
key have to be there in order to join them, right? Or maybe
I'm understanding it completely wrong ;)</div>
</div>
</blockquote>
<br>
v.db.join isn't strictly a database "join". It copies attribute
columns from one table to the map's attribute table. Yes, you
certainly need a key column in both tables, with matching values.
But in addition there can not be any column from the joined table
already existing in the map's table.<br>
<br>
<br>
<blockquote
cite="mid:CAJHDHfqrfHawcVOwe0HP=rfVW9jj_zhEMpyWLOvMNAZYhvQ_gQ@mail.gmail.com"
type="cite">
<div class="gmail_quote">
<div> </div>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div style="direction:ltr" bgcolor="#ffffff" text="#000000">If
both of these tables are attrib tables for existing GRASS
vectors, then you can go about this in another way: Within
in postgresql create a view which joins the attribs of both
tables, then connect the GRASS vector "buildings" to this
view thru a second layer. So something like:<br>
(in PostgreSQL:)<br>
# CREATE VIEW building_data AS <br>
SELECT b.<column1>, b.<column2>,....
g.<column1>, g.column2> ,... <br>
FROM buildings AS b JOIN Gebaeude_Globalstrahlung AS g
ON b.alk_id=<a moz-do-not-send="true" href="http://g.cat"
target="_blank">g.cat</a>;<br>
</div>
</blockquote>
<div>Step one, that worked great. In fact, I'm working with four
different maps that I want to join, so I went ahead and did
the following:</div>
<div>
<div>
<div>CREATE VIEW buildings_globalstrahlung AS</div>
<div> SELECT <a moz-do-not-send="true" href="http://g.cat">g.cat</a>,
b.objektid, b.oska, g.globalstra</div>
<div> FROM buildings AS b JOIN gebaeude_globalstrahlung AS
g ON <a moz-do-not-send="true" href="http://b.cat">b.cat</a>=g.alk_id;</div>
<div>CREATE VIEW buildings_globalstrahlung_dachflaeche AS</div>
<div> SELECT <a moz-do-not-send="true" href="http://b.cat">b.cat</a>,
b.objektid, b.oska, b.globalstra, d.dachflaech</div>
<div> FROM buildings_globalstrahlung AS b JOIN
gebaeude_dachflaeche AS d ON <a moz-do-not-send="true"
href="http://b.cat">b.cat</a>=d.alk_id;</div>
<div>CREATE VIEW
buildings_globalstrahlung_dachflaeche_nutzflaeche AS</div>
<div> SELECT <a moz-do-not-send="true" href="http://b.cat">b.cat</a>,
b.objektid, b.oska, b.globalstra, b.dachflaech,
n.nutzflaech</div>
<div> FROM buildings_globalstrahlung_dachflaeche AS b JOIN
gebaeude_nutzflaeche AS n ON <a moz-do-not-send="true"
href="http://b.cat">b.cat</a>=n.alk_id;</div>
</div>
</div>
<div><br>
</div>
<div>I'm sure there's a much more elegant way of doing it in a
single command, but I don't know it. Any suggestions?</div>
</div>
</blockquote>
<br>
Looks OK to me. This is a one time thing. You could dump those
commands into a text file for future reference, and for use with
psql -f in case you need to recreate these views.<br>
<br>
<blockquote
cite="mid:CAJHDHfqrfHawcVOwe0HP=rfVW9jj_zhEMpyWLOvMNAZYhvQ_gQ@mail.gmail.com"
type="cite">
<div class="gmail_quote">
<div> </div>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div style="direction:ltr" bgcolor="#ffffff" text="#000000"> <br>
(and in GRASS)<br>
db.login driv=pg
database="host=localhost,dbname=isis_db_extern" user=lee<br>
<enter your password><br>
v.db.connect buildings driv=pg
database="host=localhost,dbname=isis_db_extern" user=lee
table=building_data layer=2 </div>
</blockquote>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div style="direction:ltr" bgcolor="#ffffff" text="#000000">Now
you should see all the columns from both buildings and
Gebaeude_Globalstrahlun thru layer 2:<br>
v.db.select buildings layer=2</div>
</blockquote>
</div>
<div>That worked too.</div>
<div>v.db.connect -o map=Buildings@PERMANENT table=public.results
layer=2</div>
<div>got me connected to it. I'm really, really happy now :D If I
copy the table now, will I have a </div>
</blockquote>
<br>
Whew, Good to hear that...<br>
<blockquote
cite="mid:CAJHDHfqrfHawcVOwe0HP=rfVW9jj_zhEMpyWLOvMNAZYhvQ_gQ@mail.gmail.com"
type="cite">
<div>hard copied version of the table, or will it still be
dependent on the joins that went before?</div>
</blockquote>
<br>
Not sure what you mean. The views are *not* tables, i.e. they don't
take any space in the DB, and what any values change in the
underlying real tables, the view will, of course, reflect those
changes. But a view is just a stored query, not a "real" table. So
each time you want the results, it runs the query. But this is what
PostgreSQL does for a living ;-)<br>
<br>
<blockquote
cite="mid:CAJHDHfqrfHawcVOwe0HP=rfVW9jj_zhEMpyWLOvMNAZYhvQ_gQ@mail.gmail.com"
type="cite">
<div> </div>
<div>Thanks a lot for the help! It's still a bit strange that my
attribute tables are in Postgres but my geometries not, so that
I have to export them to PostGIS by v.out.ogr --> *.shp |
shp2pgsql, but I can live with that if nobody else can figure
out the reasons why. I sure can't at the moment either :D</div>
<div><br>
</div>
</blockquote>
<br>
Don't know why you can't get v.out.ogr working to export to a
PostGIS database, other than the database access permissions I
mentioned before. <br>
Keep in mind that there's some advantage to splitting the the
vectors between geometry in GRASS and attribs in PostgreSQL: You
keep GRASS's strict topology, while gaining all the speed and
features of a real database for attributes.<br>
<br>
On The other hand, the reason you'd want to export totally out of
GRASS is if you don't need the layers in GRASS any more, and want to
use them as pure PostGIS. You mentioned Geoserver, so I guess this
is actually your scenario. So it's probably worth trying to crack
that v.out.ogr problem, and get the layers moved out to PostGIS.
<br>
<br>
<blockquote
cite="mid:CAJHDHfqrfHawcVOwe0HP=rfVW9jj_zhEMpyWLOvMNAZYhvQ_gQ@mail.gmail.com"
type="cite">
<div>Boy, is that a load off, I'm quite relieved. Thanks again for
all the suggestions!</div>
<br>
This mail was received via Mail-SeCure System.<br>
</blockquote>
<p><br>
</p>
</body>
</html>