<div dir="ltr">Thanks, I'm surprised as well, though each time the qgis window refreshes the values change, so it's not quite!<div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Thu, Dec 10, 2015 at 2:32 PM, Brent Wood <span dir="ltr"><<a href="mailto:pcreso@yahoo.com" target="_blank">pcreso@yahoo.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div style="color:#000;background-color:#fff;font-family:verdana,helvetica,sans-serif;font-size:16px"><div>You want to select by date but you don't have a date to select on - just the interval between dates.</div><div><br></div><div>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><br></div><div 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 dir="ltr"><br></div><div 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 dir="ltr"><br></div><div dir="ltr"><br></div><div dir="ltr">select ...</div><div dir="ltr">...</div><div dir="ltr">name::':'::zone::' '::value as label<br></div><div><span>from<br></span></div><div><span>...;</span></div><div><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><br></div><div>Cheers,</div><div><br></div><div> Brent<br></div><div class="hm HOEnZb"><div><br></div><div><br> </div></div><div style="font-family:verdana,helvetica,sans-serif;font-size:16px"><div class="hm HOEnZb"> </div><div style="font-family:HelveticaNeue,Helvetica Neue,Helvetica,Arial,Lucida Grande,sans-serif;font-size:16px"><div class="hm HOEnZb"> <div dir="ltr"> <hr size="1"> <font face="Arial" size="2"> <b><span style="font-weight:bold">From:</span></b> Matt Boyd <<a href="mailto:mattslists@gmail.com" target="_blank">mattslists@gmail.com</a>><br> <b><span style="font-weight:bold">To:</span></b> Brent Wood <<a href="mailto:pcreso@yahoo.com" target="_blank">pcreso@yahoo.com</a>> <br><b><span style="font-weight:bold">Cc:</span></b> qgis-user <<a href="mailto:qgis-user@lists.osgeo.org" target="_blank">qgis-user@lists.osgeo.org</a>><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><div><div class="h5"> <div><br><div><div><div dir="ltr"><div style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif">Hi Brent,</span></div><div style="margin-bottom:0.0001pt"><font face="Arial, sans-serif"><span 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 style="margin-bottom:0.0001pt">table1 has the geometry.</div><div style="margin-bottom:0.0001pt">table2 has the data</div><div style="margin-bottom:0.0001pt"><br clear="none"></div><div style="margin-bottom:0.0001pt">for each location (name) there are multiple zones and multiple dates in the data table.</div><div style="margin-bottom:0.0001pt">So, a join on name gives </div><div style="margin-bottom:0.0001pt">name1|zone1|date1|volume1</div><div style="margin-bottom:0.0001pt">name1|zone2|date2|volume2</div><div style="margin-bottom:0.0001pt">name1|zone1|date2|volume3</div><div style="margin-bottom:0.0001pt"><br clear="none"></div><div style="margin-bottom:0.0001pt">all with (I think) a working geometry</div><div style="margin-bottom:0.0001pt">Which are all valid results. </div><div style="margin-bottom:0.0001pt"><br clear="none"></div><div 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 style="margin-bottom:0.0001pt">name1 (as at x day)</div><div style="margin-bottom:0.0001pt">zone1:volume1,</div><div style="margin-bottom:0.0001pt">zone2:volume2,</div><div style="margin-bottom:0.0001pt"><br clear="none"></div><div style="margin-bottom:0.0001pt"><font face="Arial, sans-serif"><span 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 style="margin-bottom:0.0001pt"><font face="Arial, sans-serif"><span style="font-size:16px"><br clear="none"></span></font></div><div 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 style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"><br clear="none"></span></div><div 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 style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> SELECT <a rel="nofollow" shape="rect" href="http://table1.id/" target="_blank">table1.id</a>,</span></div>
<div style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> table1.geom,</span></div>
<div style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> <a rel="nofollow" shape="rect" href="http://table1.name/" target="_blank">table1.name</a>,</span></div>
<div style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> table1.type,</span></div>
<div style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> <a rel="nofollow" shape="rect" href="http://table2.name/" target="_blank">table2.name</a>,</span></div>
<div style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> table2.zone,</span></div>
<div style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> table2.date_from,</span></div>
<div style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> table2.date_to,</span></div>
<div style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> table2.volume,</span></div>
<div style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> table2.key_colum</span></div>
<div style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> FROM table1</span></div>
<div style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> JOIN table2 ON table1.name::text = <a rel="nofollow" shape="rect" href="http://table2.name/" target="_blank">table2.name</a>;</span></div><div style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"><br clear="none"></span></div><div style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"><br clear="none"></span></div></div><div><br><br></div><div><div><br clear="none"><div>On Thu, Dec 10, 2015 at 3:36 AM, Brent Wood <span dir="ltr"><<a rel="nofollow" shape="rect" href="mailto:pcreso@yahoo.com" target="_blank">pcreso@yahoo.com</a>></span> wrote:<br clear="none"><blockquote style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div 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><span><br clear="none"></span></div><div><span>What is the actual SQL you use to create the view?</span></div><div><span>Something like:</span></div><div><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><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 dir="ltr"><font face="Courier New, courier, monaco, monospace, sans-serif"> tab2.geom</font></div><div 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 dir="ltr"><font face="Courier New, courier, monaco, monospace, sans-serif"><br clear="none"></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">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" href="mailto:mattslists@gmail.com" target="_blank">mattslists@gmail.com</a>><br clear="none"> <b><span style="font-weight:bold">To:</span></b> qgis-user <<a rel="nofollow" shape="rect" href="mailto:qgis-user@lists.osgeo.org" target="_blank">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><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><br clear="none">Qgis-user mailing list<br clear="none"><a rel="nofollow" shape="rect" href="mailto:Qgis-user@lists.osgeo.org" target="_blank">Qgis-user@lists.osgeo.org</a><br clear="none">List info: <a rel="nofollow" shape="rect" href="http://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">http://lists.osgeo.org/mailman/listinfo/qgis-user</a><br clear="none">Unsubscribe: <a rel="nofollow" shape="rect" href="http://lists.osgeo.org/mailman/listinfo/qgis-user" target="_blank">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> </div></div></div></blockquote></div><br></div>