2

In QGIS I am trying to join data from two postgis tables. I can not figure out how to do this at sql because add layer tools seem to allow one to select just one table to filter on. Instead I loaded all tables as separate layers and then used the join layers in the properties of one of the layers.

That worked but I end up with a lot of attributes that I don't need or want.

How do people handle this? One solution that occurs to me is to construct a view in postgres.

4

Try to add to your QGIS DB Manager plugin - the SQL window (under the Database menu) allows you to write any query to your database (including joins). Adding layers from sql window is little tricky because you have to tell him which column have unique values, and which is geometry. If you don't have column with unique values just use window functions 4 ex:

Select row_number() over() as ID, a.field, b.field, geom
from table_a a
join table_b b on st_intersects(a.geom, b,geom) 
0

After some playing around with SQL it would appear the constructing a view with exactly the fields you want is straight forward. Then you simply load the view as the layer.

I was going to delete the questions but decided to leave it in the hope that the next person who googles this will find the question and answer.

Better answers are, of course welcome!

  • 1
    Did you try DB Manager? – Nathan W Jun 2 '14 at 7:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.