<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_236006">You want to select by date but you don't have a date to select on - just the interval between dates.</div><div id="yui_3_16_0_1_1449615165849_236633"><br></div><div id="yui_3_16_0_1_1449615165849_236637">If you want individual day based data, you'll need to specify an absolute date somewhere. You could use generate_series to create a table with dates from min start to max end dates, then join to that where date between start_date and end_date.</div><div id="yui_3_16_0_1_1449615165849_236954"><br></div><div id="yui_3_16_0_1_1449615165849_239379" dir="ltr">So for every date in the interval for that record, there will be a record in the view... select by date in QGIS to see the data.</div><div id="yui_3_16_0_1_1449615165849_237497" dir="ltr"><br></div><div id="yui_3_16_0_1_1449615165849_237496" dir="ltr">You can either use an expression in QGIS to join columns to make the label, or concatenate them in the SQL to form a new column called, say, label, like: <br></div><div id="yui_3_16_0_1_1449615165849_238119" dir="ltr"><br></div><div dir="ltr"><br></div><div id="yui_3_16_0_1_1449615165849_237957" dir="ltr">select ...</div><div id="yui_3_16_0_1_1449615165849_237804" dir="ltr">...</div><div id="yui_3_16_0_1_1449615165849_237805" dir="ltr">name::':'::zone::' '::value as label<br></div><div id="yui_3_16_0_1_1449615165849_235703"><span>from<br></span></div><div><span>...;</span></div><div id="yui_3_16_0_1_1449615165849_239068"><br></div><div>I'm surprised your view works - you have two columns called name, you only need one of them.<br></div><div><br></div><div id="yui_3_16_0_1_1449615165849_239383"><br></div><div>Cheers,</div><div><br></div><div> Brent<br></div><div id="yui_3_16_0_1_1449615165849_239384"><br></div><div><br> </div><div id="yui_3_16_0_1_1449615165849_236636" style="font-family: verdana, helvetica, sans-serif; font-size: 16px;"> <div id="yui_3_16_0_1_1449615165849_236635" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div id="yui_3_16_0_1_1449615165849_236634" dir="ltr"> <hr id="yui_3_16_0_1_1449615165849_236676" size="1"> <font id="yui_3_16_0_1_1449615165849_238450" face="Arial" size="2"> <b id="yui_3_16_0_1_1449615165849_238914"><span id="yui_3_16_0_1_1449615165849_238913" style="font-weight:bold;">From:</span></b> Matt Boyd <mattslists@gmail.com><br> <b><span style="font-weight: bold;">To:</span></b> Brent Wood <pcreso@yahoo.com> <br><b><span style="font-weight: bold;">Cc:</span></b> qgis-user <qgis-user@lists.osgeo.org><br> <b><span style="font-weight: bold;">Sent:</span></b> Thursday, December 10, 2015 3:42 PM<br> <b><span style="font-weight: bold;">Subject:</span></b> Re: [Qgis-user] postgis/gresql views<br> </font> </div> <div id="yui_3_16_0_1_1449615165849_236677" class="y_msg_container"><br><div id="yiv7805972134"><div id="yui_3_16_0_1_1449615165849_236682"><div id="yui_3_16_0_1_1449615165849_236681" dir="ltr"><div id="yui_3_16_0_1_1449615165849_236694" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><span style="font-size:12pt;font-family:Arial, sans-serif;">Hi Brent,</span></div><div id="yui_3_16_0_1_1449615165849_236680" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><font id="yui_3_16_0_1_1449615165849_236679" face="Arial, sans-serif"><span id="yui_3_16_0_1_1449615165849_236678" style="font-size:16px;">my problem at the moment is SQL based I think, it's been a while and I was never that good at it.</span></font></div><div id="yui_3_16_0_1_1449615165849_236691" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;">table1 has the geometry.</div><div id="yui_3_16_0_1_1449615165849_236690" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;">table2 has the data</div><div id="yui_3_16_0_1_1449615165849_236689" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><br clear="none"></div><div id="yui_3_16_0_1_1449615165849_236683" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;">for each location (name) there are multiple zones and multiple dates in the data table.</div><div id="yui_3_16_0_1_1449615165849_236688" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;">So, a join on name gives </div><div id="yui_3_16_0_1_1449615165849_236684" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;">name1|zone1|date1|volume1</div><div id="yui_3_16_0_1_1449615165849_236687" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;">name1|zone2|date2|volume2</div><div id="yui_3_16_0_1_1449615165849_236686" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;">name1|zone1|date2|volume3</div><div id="yui_3_16_0_1_1449615165849_236685" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><br clear="none"></div><div id="yui_3_16_0_1_1449615165849_238115" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;">all with (I think) a working geometry</div><div id="yui_3_16_0_1_1449615165849_236699" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;">Which are all valid results. </div><div id="yui_3_16_0_1_1449615165849_238451" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><br clear="none"></div><div id="yui_3_16_0_1_1449615165849_236701" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;">However, when I go to display the results as labels in QGIS I can only see one result at a time. Ideally I'd to be able to separate them by date</div><div id="yui_3_16_0_1_1449615165849_236702" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;">name1 (as at x day)</div><div id="yui_3_16_0_1_1449615165849_236704" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;">zone1:volume1,</div><div class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;">zone2:volume2,</div><div id="yui_3_16_0_1_1449615165849_236706" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><br clear="none"></div><div id="yui_3_16_0_1_1449615165849_237356" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><font id="yui_3_16_0_1_1449615165849_237358" face="Arial, sans-serif"><span id="yui_3_16_0_1_1449615165849_237357" style="font-size:16px;">I'm basically having trouble creating a single combined string from the SQL that I can use as a label.</span></font></div><div id="yui_3_16_0_1_1449615165849_237340" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><font face="Arial, sans-serif"><span style="font-size:16px;"><br clear="none"></span></font></div><div class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><font face="Arial, sans-serif"><span style="font-size:16px;">My view create script is below.</span></font></div><div id="yui_3_16_0_1_1449615165849_236727" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><span style="font-size:12pt;font-family:Arial, sans-serif;"><br clear="none"></span></div><div id="yui_3_16_0_1_1449615165849_236708" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><span style="font-size:12pt;font-family:Arial, sans-serif;">CREATE OR REPLACE VIEW joined_table2_locations AS </span></div>
<div id="yui_3_16_0_1_1449615165849_236726" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><span style="font-size:12pt;font-family:Arial, sans-serif;"> SELECT <a rel="nofollow" shape="rect" target="_blank" href="http://table1.id/">table1.id</a>,</span></div>
<div id="yui_3_16_0_1_1449615165849_236720" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><span style="font-size:12pt;font-family:Arial, sans-serif;"> table1.geom,</span></div>
<div id="yui_3_16_0_1_1449615165849_236725" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><span style="font-size:12pt;font-family:Arial, sans-serif;"> <a rel="nofollow" shape="rect" target="_blank" href="http://table1.name/">table1.name</a>,</span></div>
<div id="yui_3_16_0_1_1449615165849_238286" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><span style="font-size:12pt;font-family:Arial, sans-serif;"> table1.type,</span></div>
<div id="yui_3_16_0_1_1449615165849_238149" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><span style="font-size:12pt;font-family:Arial, sans-serif;"> <a rel="nofollow" shape="rect" target="_blank" href="http://table2.name/">table2.name</a>,</span></div>
<div id="yui_3_16_0_1_1449615165849_238443" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><span style="font-size:12pt;font-family:Arial, sans-serif;"> table2.zone,</span></div>
<div id="yui_3_16_0_1_1449615165849_237304" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><span style="font-size:12pt;font-family:Arial, sans-serif;"> table2.date_from,</span></div>
<div id="yui_3_16_0_1_1449615165849_238442" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><span style="font-size:12pt;font-family:Arial, sans-serif;"> table2.date_to,</span></div>
<div id="yui_3_16_0_1_1449615165849_238148" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><span style="font-size:12pt;font-family:Arial, sans-serif;"> table2.volume,</span></div>
<div id="yui_3_16_0_1_1449615165849_237312" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><span style="font-size:12pt;font-family:Arial, sans-serif;"> table2.key_colum</span></div>
<div id="yui_3_16_0_1_1449615165849_238287" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><span style="font-size:12pt;font-family:Arial, sans-serif;"> FROM table1</span></div>
<div id="yui_3_16_0_1_1449615165849_238288" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><span id="yui_3_16_0_1_1449615165849_238290" style="font-size:12pt;font-family:Arial, sans-serif;"> JOIN table2 ON table1.name::text = <a id="yui_3_16_0_1_1449615165849_238289" rel="nofollow" shape="rect" target="_blank" href="http://table2.name/">table2.name</a>;</span></div><div id="yui_3_16_0_1_1449615165849_237307" class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><span style="font-size:12pt;font-family:Arial, sans-serif;"><br clear="none"></span></div><div class="yiv7805972134MsoNormal" style="margin-bottom:0.0001pt;"><span style="font-size:12pt;font-family:Arial, sans-serif;"><br clear="none"></span></div></div><div class="qtdSeparateBR"><br><br></div><div class="yiv7805972134yqt2191202750" id="yiv7805972134yqt25684"><div id="yui_3_16_0_1_1449615165849_236716" class="yiv7805972134gmail_extra"><br clear="none"><div id="yui_3_16_0_1_1449615165849_236715" class="yiv7805972134gmail_quote">On Thu, Dec 10, 2015 at 3:36 AM, Brent Wood <span dir="ltr"><<a rel="nofollow" shape="rect" ymailto="mailto:pcreso@yahoo.com" target="_blank" href="mailto:pcreso@yahoo.com">pcreso@yahoo.com</a>></span> wrote:<br clear="none"><blockquote id="yui_3_16_0_1_1449615165849_237317" class="yiv7805972134gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div id="yui_3_16_0_1_1449615165849_237316"><div id="yui_3_16_0_1_1449615165849_237315" style="color:#000;background-color:#fff;font-family:verdana, helvetica, sans-serif;font-size:16px;"><div><span>Hi Matt,<br clear="none"></span></div><div id="yui_3_16_0_1_1449615165849_237332"><span><br clear="none"></span></div><div><span>What is the actual SQL you use to create the view?</span></div><div id="yui_3_16_0_1_1449615165849_237314"><span>Something like:</span></div><div id="yui_3_16_0_1_1449615165849_237318"><font face="Courier New, courier, monaco, monospace, sans-serif">create view v_geo as</font></div><div><font face="Courier New, courier, monaco, monospace, sans-serif">select tab1.location,</font></div><div id="yui_3_16_0_1_1449615165849_237335"><font face="Courier New, courier, monaco, monospace, sans-serif"> tab1.zone,</font></div><div dir="ltr"><font face="Courier New, courier, monaco, monospace, sans-serif"> tab1.date,</font></div><div dir="ltr"><font face="Courier New, courier, monaco, monospace, sans-serif"> tab1.value,</font></div><div id="yui_3_16_0_1_1449615165849_237320" dir="ltr"><font face="Courier New, courier, monaco, monospace, sans-serif"> tab2.geom</font></div><div id="yui_3_16_0_1_1449615165849_237323" dir="ltr"><font face="Courier New, courier, monaco, monospace, sans-serif">from tab1, tab2</font></div><div dir="ltr"><font face="Courier New, courier, monaco, monospace, sans-serif">where tab1.location=tab2.location;</font></div><div dir="ltr"><br clear="none"></div><div dir="ltr"><font face="Courier New, courier, monaco, monospace, sans-serif"><font face="HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif">Then do a </font><br clear="none"></font></div><div dir="ltr"><font 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_237325" dir="ltr"><font face="Courier New, courier, monaco, monospace, sans-serif"><br clear="none"></font></div><div id="yui_3_16_0_1_1449615165849_237328" dir="ltr"><font id="yui_3_16_0_1_1449615165849_237327" face="Courier New, courier, monaco, monospace, sans-serif"><font id="yui_3_16_0_1_1449615165849_237326" 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 dir="ltr"><br clear="none"></div><div dir="ltr"><font face="Courier New, courier, monaco, monospace, sans-serif"><font face="HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif">eg: <br clear="none"></font></font></div><div dir="ltr"><font face="Courier New, courier, monaco, monospace, sans-serif"><font>alter table tab1 add column id serial primary key;</font></font></div><div dir="ltr"><font face="Courier New, courier, monaco, monospace, sans-serif"><font face="HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif">and include this id column in the view.</font></font></div><div dir="ltr"><br clear="none"></div><div dir="ltr"><font face="Courier New, courier, monaco, monospace, sans-serif"><font 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 dir="ltr"><br clear="none"></div><div dir="ltr"><font face="Courier New, courier, monaco, monospace, sans-serif"><font 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 dir="ltr"><font face="Courier New, courier, monaco, monospace, sans-serif"><font 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 clear="none"></div><div dir="ltr"><br clear="none"></div><div dir="ltr"><font face="Courier New, courier, monaco, monospace, sans-serif"><font face="HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif">Unless I understand your problem better, I can't offer useful advice.<br clear="none"></font></font></div><div dir="ltr"><br clear="none"></div><div dir="ltr">Cheers</div><div dir="ltr"><br clear="none"></div><div dir="ltr">Brent Wood<br clear="none"></div><div dir="ltr"><font face="Courier New, courier, monaco, monospace, sans-serif"><font face="HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif"></font></font><br clear="none"></div><div><br clear="none"> </div><div style="font-family:verdana, helvetica, sans-serif;font-size:16px;"> <div style="font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px;"> <div dir="ltr"> <hr size="1"> <font face="Arial" size="2"> <b><span style="font-weight:bold;">From:</span></b> Matt Boyd <<a rel="nofollow" shape="rect" ymailto="mailto:mattslists@gmail.com" target="_blank" href="mailto:mattslists@gmail.com">mattslists@gmail.com</a>><br clear="none"> <b><span style="font-weight:bold;">To:</span></b> qgis-user <<a rel="nofollow" shape="rect" ymailto="mailto:qgis-user@lists.osgeo.org" target="_blank" href="mailto:qgis-user@lists.osgeo.org">qgis-user@lists.osgeo.org</a>> <br clear="none"> <b><span style="font-weight:bold;">Sent:</span></b> Wednesday, December 9, 2015 7:08 PM<br clear="none"> <b><span style="font-weight:bold;">Subject:</span></b> [Qgis-user] postgis/gresql views<br clear="none"> </font> </div> <div><div><div class="yiv7805972134h5"><br clear="none"><div><div dir="ltr">Hi QGISers.<div>I'm trying to work out how to display some geological data on map.</div><div>Basically </div><div>Table x; </div><div>location1 | zone1 | date1 | value1</div><div>location1 | zone1 | date2 | value2</div><div>continued with variations in location/zone etc..</div><div><br clear="none"></div><div>spatial table </div><div>geometry Column | location1</div><div><br clear="none"></div><div><br clear="none"></div><div>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><br clear="none"></div><div>All the data is there and shown correctly in the combined attributes table, however labels don't show correctly.</div><div><br clear="none"></div><div>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><br clear="none"></div><div>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 clear="none"></div><div>Thanks</div><div>Matt</div><div><br clear="none"></div><div><br clear="none"></div></div></div><br clear="none"></div></div>_______________________________________________<span class="yiv7805972134"><br clear="none">Qgis-user mailing list<br clear="none"><a rel="nofollow" shape="rect" ymailto="mailto:Qgis-user@lists.osgeo.org" target="_blank" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a><br clear="none">List info: <a rel="nofollow" shape="rect" target="_blank" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a><br clear="none">Unsubscribe: <a rel="nofollow" shape="rect" target="_blank" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a><br clear="none"><br clear="none"></span></div> </div> </div></div></div></blockquote></div><br clear="none"></div></div></div></div><br><br></div> </div> </div></div></body></html>