<html><head></head><body><div style="color:#000; background-color:#fff; font-family:verdana, helvetica, sans-serif;font-size:16px"><div id="yui_3_16_0_1_1449615165849_145342"><span id="yui_3_16_0_1_1449615165849_145344">Hi Matt,<br></span></div><div><span id="yui_3_16_0_1_1449615165849_145344"><br></span></div><div id="yui_3_16_0_1_1449615165849_150756"><span id="yui_3_16_0_1_1449615165849_145344">What is the actual SQL you use to create the view?</span></div><div id="yui_3_16_0_1_1449615165849_146359"><span id="yui_3_16_0_1_1449615165849_145344">Something like:</span></div><div id="yui_3_16_0_1_1449615165849_145787"><font id="yui_3_16_0_1_1449615165849_146652" face="Courier New, courier, monaco, monospace, sans-serif">create view v_geo as</font></div><div id="yui_3_16_0_1_1449615165849_145788"><font id="yui_3_16_0_1_1449615165849_146175" face="Courier New, courier, monaco, monospace, sans-serif">select tab1.location,</font></div><div id="yui_3_16_0_1_1449615165849_146077"><font id="yui_3_16_0_1_1449615165849_146173" face="Courier New, courier, monaco, monospace, sans-serif"> tab1.zone,</font></div><div id="yui_3_16_0_1_1449615165849_146358" dir="ltr"><font id="yui_3_16_0_1_1449615165849_146173" face="Courier New, courier, monaco, monospace, sans-serif"> tab1.date,</font></div><div id="yui_3_16_0_1_1449615165849_146357" dir="ltr"><font id="yui_3_16_0_1_1449615165849_146173" face="Courier New, courier, monaco, monospace, sans-serif"> tab1.value,</font></div><div id="yui_3_16_0_1_1449615165849_146356" dir="ltr"><font id="yui_3_16_0_1_1449615165849_146173" face="Courier New, courier, monaco, monospace, sans-serif"> tab2.geom</font></div><div id="yui_3_16_0_1_1449615165849_146352" dir="ltr"><font id="yui_3_16_0_1_1449615165849_146173" face="Courier New, courier, monaco, monospace, sans-serif">from tab1, tab2</font></div><div id="yui_3_16_0_1_1449615165849_145933" dir="ltr"><font id="yui_3_16_0_1_1449615165849_146355" face="Courier New, courier, monaco, monospace, sans-serif">where tab1.location=tab2.location;</font></div><div id="yui_3_16_0_1_1449615165849_146649" dir="ltr"><br></div><div id="yui_3_16_0_1_1449615165849_146650" dir="ltr"><font id="yui_3_16_0_1_1449615165849_146355" face="Courier New, courier, monaco, monospace, sans-serif"><font face="HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif">Then do a </font><br></font></div><div id="yui_3_16_0_1_1449615165849_146970" dir="ltr"><font id="yui_3_16_0_1_1449615165849_146355" face="Courier New, courier, monaco, monospace, sans-serif">select * from v_geo order by location, zone, date;</font></div><div id="yui_3_16_0_1_1449615165849_146971" dir="ltr"><font id="yui_3_16_0_1_1449615165849_146355" face="Courier New, courier, monaco, monospace, sans-serif"><br></font></div><div id="yui_3_16_0_1_1449615165849_146972" dir="ltr"><font id="yui_3_16_0_1_1449615165849_146355" face="Courier New, courier, monaco, monospace, sans-serif"><font id="yui_3_16_0_1_1449615165849_147141" face="HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif">To check the records are as expected, before you try to open in QGIS. Also note that ideally you should include a integer primary key to clearly identify each record uniquely.</font></font></div><div id="yui_3_16_0_1_1449615165849_148617" dir="ltr"><br></div><div id="yui_3_16_0_1_1449615165849_148618" dir="ltr"><font id="yui_3_16_0_1_1449615165849_146355" face="Courier New, courier, monaco, monospace, sans-serif"><font id="yui_3_16_0_1_1449615165849_147141" face="HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif">eg: <br></font></font></div><div id="yui_3_16_0_1_1449615165849_148619" dir="ltr"><font id="yui_3_16_0_1_1449615165849_146355" face="Courier New, courier, monaco, monospace, sans-serif"><font id="yui_3_16_0_1_1449615165849_147141">alter table tab1 add column id serial primary key;</font></font></div><div id="yui_3_16_0_1_1449615165849_148633" dir="ltr"><font id="yui_3_16_0_1_1449615165849_146355" face="Courier New, courier, monaco, monospace, sans-serif"><font id="yui_3_16_0_1_1449615165849_147141" face="HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif">and include this id column in the view.</font></font></div><div id="yui_3_16_0_1_1449615165849_149335" dir="ltr"><br></div><div id="yui_3_16_0_1_1449615165849_149636" dir="ltr"><font id="yui_3_16_0_1_1449615165849_146355" face="Courier New, courier, monaco, monospace, sans-serif"><font id="yui_3_16_0_1_1449615165849_147141" face="HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif">You have not described the relationship between zones & locations. My assumption would be that locations are point features & zones represent polygons that the locations lie within, but this doesn't make sense with your example - "for all zones in location"</font></font></div><div id="yui_3_16_0_1_1449615165849_149846" dir="ltr"><br></div><div id="yui_3_16_0_1_1449615165849_150004" dir="ltr"><font id="yui_3_16_0_1_1449615165849_146355" face="Courier New, courier, monaco, monospace, sans-serif"><font id="yui_3_16_0_1_1449615165849_147141" face="HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif">It is also unclear whether you want to view categorised data in QGIS, or create Postgis views in Postgis from QGIS:</font></font></div><div id="yui_3_16_0_1_1449615165849_150166" dir="ltr"><font class="" id="yui_3_16_0_1_1449615165849_146355" face="Courier New, courier, monaco, monospace, sans-serif"><font class="" id="yui_3_16_0_1_1449615165849_147141" face="HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif">"</font></font>Ideally I'd like to be able to create labels and views from within qgis ..."<br></div><div id="yui_3_16_0_1_1449615165849_150016" dir="ltr"><br></div><div id="yui_3_16_0_1_1449615165849_150017" dir="ltr"><font id="yui_3_16_0_1_1449615165849_146355" face="Courier New, courier, monaco, monospace, sans-serif"><font id="yui_3_16_0_1_1449615165849_147141" face="HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif">Unless I understand your problem better, I can't offer useful advice.<br></font></font></div><div id="yui_3_16_0_1_1449615165849_148795" dir="ltr"><br></div><div id="yui_3_16_0_1_1449615165849_150313" dir="ltr">Cheers</div><div dir="ltr"><br></div><div dir="ltr">Brent Wood<br></div><div id="yui_3_16_0_1_1449615165849_148944" dir="ltr"><font id="yui_3_16_0_1_1449615165849_146355" face="Courier New, courier, monaco, monospace, sans-serif"><font id="yui_3_16_0_1_1449615165849_149318" face="HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif"></font></font><br></div><div id="yui_3_16_0_1_1449615165849_146084"><br> </div><div id="yui_3_16_0_1_1449615165849_145331" style="font-family: verdana, helvetica, sans-serif; font-size: 16px;"> <div id="yui_3_16_0_1_1449615165849_145330" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div id="yui_3_16_0_1_1449615165849_145329" dir="ltr"> <hr id="yui_3_16_0_1_1449615165849_145328" size="1"> <font id="yui_3_16_0_1_1449615165849_145341" face="Arial" size="2"> <b><span style="font-weight:bold;">From:</span></b> Matt Boyd <mattslists@gmail.com><br> <b><span style="font-weight: bold;">To:</span></b> qgis-user <qgis-user@lists.osgeo.org> <br> <b><span style="font-weight: bold;">Sent:</span></b> Wednesday, December 9, 2015 7:08 PM<br> <b><span style="font-weight: bold;">Subject:</span></b> [Qgis-user] postgis/gresql views<br> </font> </div> <div id="yui_3_16_0_1_1449615165849_145332" class="y_msg_container"><br><div id="yiv4839004729"><div id="yui_3_16_0_1_1449615165849_145333" dir="ltr">Hi QGISers.<div id="yui_3_16_0_1_1449615165849_145334">I'm trying to work out how to display some geological data on map.</div><div id="yui_3_16_0_1_1449615165849_145335">Basically </div><div id="yui_3_16_0_1_1449615165849_145336">Table x; </div><div id="yui_3_16_0_1_1449615165849_146353">location1 | zone1 | date1 | value1</div><div id="yui_3_16_0_1_1449615165849_146354">location1 | zone1 | date2 | value2</div><div id="yui_3_16_0_1_1449615165849_146085">continued with variations in location/zone etc..</div><div id="yui_3_16_0_1_1449615165849_146172"><br></div><div id="yui_3_16_0_1_1449615165849_146086">spatial table </div><div id="yui_3_16_0_1_1449615165849_146655">geometry Column | location1</div><div id="yui_3_16_0_1_1449615165849_146656"><br></div><div id="yui_3_16_0_1_1449615165849_146657"><br></div><div id="yui_3_16_0_1_1449615165849_146087">I create a view in postgresql using the location as the common column. However, when I try to display my data, zone2 is shown with value1 and I can't work out how to get 2 values and 2 zones to display at a time.</div><div id="yui_3_16_0_1_1449615165849_146088"><br></div><div id="yui_3_16_0_1_1449615165849_146089">All the data is there and shown correctly in the combined attributes table, however labels don't show correctly.</div><div id="yui_3_16_0_1_1449615165849_146090"><br></div><div id="yui_3_16_0_1_1449615165849_146091">Ideally I'd like to be able to create labels and views from within qgis using functions (eg, for all zones in location1, what is the sum of the values).</div><div id="yui_3_16_0_1_1449615165849_146092"><br></div><div id="yui_3_16_0_1_1449615165849_149316">I've only been at this a couple of hours this afternoon but thought I'd check here in case there's a simpler solution than the one I'm heading towards.</div><div><br></div><div>Thanks</div><div>Matt</div><div><br></div><div><br></div></div></div><br>_______________________________________________<br>Qgis-user mailing list<br><a ymailto="mailto:Qgis-user@lists.osgeo.org" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a><br>List info: <a href="http://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">http://lists.osgeo.org/mailman/listinfo/qgis-user</a><br>Unsubscribe: <a href="http://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">http://lists.osgeo.org/mailman/listinfo/qgis-user</a><br><br></div> </div> </div></div></body></html>