[GRASS-user] Joining vectors by location

[Daniel Lee]

Alright, I think the last suggestion got me a LOT further.

2011/11/26 Micha Silver <micha at arava.co.il>

> My guess: Wasn't your Gebaeude_Globalstrahlung table also a GRASS vector?
> So it also has a cat column?
> 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.
>
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 ;)


> 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:
> (in PostgreSQL:)
> # CREATE VIEW building_data AS
>     SELECT b.<column1>, b.<column2>,.... g.<column1>, g.column2> ,...
>     FROM buildings AS b JOIN  Gebaeude_Globalstrahlung AS g ON b.alk_id=
> g.cat;
>
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:
CREATE VIEW buildings_globalstrahlung AS
  SELECT g.cat, b.objektid, b.oska, g.globalstra
  FROM buildings AS b JOIN gebaeude_globalstrahlung AS g ON b.cat=g.alk_id;
CREATE VIEW buildings_globalstrahlung_dachflaeche AS
  SELECT b.cat, b.objektid, b.oska, b.globalstra, d.dachflaech
  FROM buildings_globalstrahlung AS b JOIN gebaeude_dachflaeche AS d ON
b.cat=d.alk_id;
CREATE VIEW buildings_globalstrahlung_dachflaeche_nutzflaeche AS
  SELECT b.cat, b.objektid, b.oska, b.globalstra, b.dachflaech, n.nutzflaech
  FROM buildings_globalstrahlung_dachflaeche AS b JOIN gebaeude_nutzflaeche
AS n ON b.cat=n.alk_id;

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?


>
> (and in GRASS)
> db.login driv=pg database="host=localhost,dbname=isis_db_extern" user=lee
> <enter your password>
> v.db.connect buildings driv=pg
> database="host=localhost,dbname=isis_db_extern" user=lee
> table=building_data layer=2
>
Now you should see all the columns from both buildings and
> Gebaeude_Globalstrahlun thru layer 2:
> v.db.select buildings layer=2
>
That worked too.
v.db.connect -o map=Buildings at PERMANENT table=public.results layer=2
got me connected to it. I'm really, really happy now :D If I copy the table
now, will I have a hard copied version of the table, or will it still be
dependent on the joins that went before?

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

Boy, is that a load off, I'm quite relieved. Thanks again for all the
suggestions!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/grass-user/attachments/20111126/843cf545/attachment.html