[Qgis-user] Help on creating a view

[Bernhard Ströbl]

Dear Francis,

afaik views work in QGIS now (i.e. Version 0.10), so your second view
"SELECT a.gid, a.level, a.the_geom FROM myPolyTable a" should definitely
work.
However your first view lacks a primary-key field, neither a.gid nor
b.gid are unique if you really have a n to m join.
What do you want to achieve with your view? You use a.the_geom
(polygons) for your geometry and want to have all polygons from "a" that
contain any points from "b" and you include every polygon as many times
as points from "b" fall within. If you want to keep it that way you
should create a new primary-key field from a.gid and b.gid, e.g.
SELECT to_char(a.gid,'9999') || to_char(b.gid,'9999') as gid .....
If you want each polygon _only once_ use
SELECT DISTINCT a.gid, a.the_geom..... (which should be a lot faster
than the above)

hope this helps

Benno

> Hello!
> 
> I have two tables named myPtTable and myPolyTable on a PostGIS/PostgreSQL
> 8.3 server.  I want to join the two table based on a spatial query has
> following:
> 
> SELECT a.gid, b.gid, a.level, b.name a.the_geom
> FROM myPolyTable a, myPtTable b
> WHERE st_contains(a.the_geom, b.the_geom)
> ORDER BY a.level, a.gid;
> 
> The columns a.gid and b.gid are both primary key of type integer.  I read on
> the forum that the way to join those tables and see them in QGIS (Io 0.10)
> is done by using a view.  So I've done it but QGIS always give me the same
> error that my view need a primary key.  The real primary key is the two gid
> field because I have a N to N relationship between my two tables.  I saw on
> the forum that it is a bug in QGIS that will be fixed someday.  What's the
> solution?  To quick test if the view functionnality works in QGIS, I have
> create the following query:
> 
> SELECT a.gid, a.level, a.the_geom FROM myPolyTable a
> 
> But in fact, my view still doesn't work in QGIS and a.gid is a PRIMARY KEY
> with UNIQUE VALUE constraint.
> 
> I would like help from the community on that problem.  
> 
> Does the view functionnality work in QGIS?  If so, can I have a sample to
> try it.  To join my two table, how can I create a primary key based on my
> two fields in my view?
> 
> Thank you,
> 
> Francis


________ Information from NOD32 ________
This message was checked by NOD32 Antivirus System for Linux Mail Server.
http://www.nod32.com