[GRASS-user] Joining vectors by location

[Daniel Lee]

Okay, first of all, thanks once again for all the patience and the huge
amount of help. I'm now quite a bit further and just wanted to report back
on how it all worked out so that people in similar quandries will be able
to deal :) It doesn't work all the way yet, but at least I'm a lot closer
now. Unfortunately there is a question at the end, but I'm going to ask it
in the Postgres list too. I'm sure they're as helpful as our community.

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

> Maybe I'm not explaining well. v.db.join is usually for joining a table
> (non-spatial, *not* a GRASS vector) to an existing vector map. Typically
> you'd have a map with nothing but "cat" values, and a table with an "id"
> column matching the "cat" values. You can use any pair of columns to match
> the features from the map to the rows from the table (as long as they are
> unique). However you can not have any column in the attrib that already
> exists in the map's table.
>
Okay, so I guess the solution is really making sure that the data matches
redundancy standards.


>
> It still seems to me the easiest method for your case would be to export
> all your GRASS vectors totally to a PostGIS database. Then create in
> PostGIS the views you want with whatever columns you want.  You'll only
> need to work out the access permissions to your database. From the "ident"
> failure you mentioned some mails back, it's a misconfiguration in
> pg_hba.conf. You need lines at the top something like
> local  all  all trust
> host all all 127.0.0.1/32 trust
>
Good idea. I've now set all the local accesses to trust. I know that's
probably not the most safe thing, but on the other hand, nobody can
physically access my computer except for the people I work with, so it
should be okay. Plus, I'm using the computer currently as a development
machine. Interestingly enough, it only worked after I'd edited pg_hba.conf
to trust IPv6 connections. Does GRASS use IPv6?


>
>
 Jump over to the PostGIS maillist or check the PostgreSQL documentation
> for better help.
>
I'll do that. I hope I can get some more help there, but in the meantime
I'm definitely grateful for the tons of help I've gotten in the GRASS list.

Current status:

   1. Once I set the DB to trust IPv6 connections to localhost GRASS could
   upload the vectors to PostGIS. Incidentally, these vectors were then also
   available in Geoserver after trusting IPv4 connections to localhost. Very
   interesting. QGIS apparently uses a local connection, which is why I never
   really had the idea of letting IPv4/6 access the DB. The whole time the
   password and username were correct, so it was kind of confusing, but oh
   well. That's solved for now.
   2. I tried exporting the attribute tables of the "spatially joined"
   tables into PostGIS, but for some reason GRASS didn't export anything. It
   said that I hadn't requested to export the geometries and should verify the
   'type' parameter, which is nonexistent in that db.out.ogr. I ended up just
   leaving that alone because those tables were already contained in the
   Postgres DB, even though they were still associated with the vectors in
   GRASS.
   3. I then exported the building vectors with the nice smooth boundaries
   to PostGIS using v.out.ogr. No problem after turning on IPv6 trust. I
   deleted the duplicate columns from the tables I wanted to join to and tried
   using v.db.join, but got the following error:
   ERROR:  more than one row returned by a subquery used as an expression
   Really strange, because I'd used v.distance with dmax=0 on the
   centroids, updating the centroids with the values of the areas they lay in,
   so that it seemed unlikely that they would have multiple rows returned by
   that query. I came up with what I thought was a solution, but it only
   worked on two rows:
   4. Rather than using v.db.join I did an SQL query in pgsql:
   ALTER TABLE alk_flaechen ADD COLUMN globalstra float, ADD COLUMN
   dachflaech float, ADD COLUMN nutzflaech float; UPDATE alk_flaechen SET
   nutzflaech = (SELECT DISTINCT nutzflaech FROM gebaeude_nutzflaeche WHERE
   gebaeude_nutzflaeche.alk_cat=alk_flaechen.cat); UPDATE alk_flaechen SET
   dachflaech = (SELECT DISTINCT dachflaech FROM gebaeude_dachflaeche WHERE
   gebaeude_dachflaeche.alk_cat=alk_flaechen.cat); So far, so good, but
   then... UPDATE alk_flaechen SET globalstra = (SELECT DISTINCT globalstra
   FROM gebaeude_globalstrahlung WHERE gebaeude_globalstrahlung.alk_cat=
   alk_flaechen.cat); ERROR: more than one row returned by a subquery used
   as an expression


I have no clue why that's the case, and I can't for the life of me find
anything on Google at all about how I should fix it. This error occurs
consistently with that table, but not with the other tables, and as far as
I can understand it should be impossible, because it's constructed so that
only one value is returned. Nonetheless, that's the way it is.

Anyway, I'll pass this on to the Postgres people, but if anybody else has
an idea, it would be really nice... In the end, all I want is a table
joined by attributes, which is an easy thing with every other GIS I know.
I'm not meaning to say that it can't be done in GRASS - I'm just saying
that it's GOT to be possible, since I've never crossed the boundaries of
GRASS' capabilities before, no matter how hard I've tried ;) Thanks again!

Daniel
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/grass-user/attachments/20111127/a6b6bda2/attachment-0001.html