<div dir="ltr"><p class="MsoNormal" style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif">Hi Brent,</span></p><p class="MsoNormal" 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></p><p class="MsoNormal" style="margin-bottom:0.0001pt">table1 has the geometry.</p><p class="MsoNormal" style="margin-bottom:0.0001pt">table2 has the data</p><p class="MsoNormal" style="margin-bottom:0.0001pt"><br></p><p class="MsoNormal" style="margin-bottom:0.0001pt">for each location (name) there are multiple zones and multiple dates in the data table.</p><p class="MsoNormal" style="margin-bottom:0.0001pt">So, a join on name gives </p><p class="MsoNormal" style="margin-bottom:0.0001pt">name1|zone1|date1|volume1</p><p class="MsoNormal" style="margin-bottom:0.0001pt">name1|zone2|date2|volume2</p><p class="MsoNormal" style="margin-bottom:0.0001pt">name1|zone1|date2|volume3</p><p class="MsoNormal" style="margin-bottom:0.0001pt"><br></p><p class="MsoNormal" style="margin-bottom:0.0001pt">all with (I think) a working geometry</p><p class="MsoNormal" style="margin-bottom:0.0001pt">Which are all valid results. </p><p class="MsoNormal" style="margin-bottom:0.0001pt"><br></p><p class="MsoNormal" 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</p><p class="MsoNormal" style="margin-bottom:0.0001pt">name1 (as at x day)</p><p class="MsoNormal" style="margin-bottom:0.0001pt">zone1:volume1,</p><p class="MsoNormal" style="margin-bottom:0.0001pt">zone2:volume2,</p><p class="MsoNormal" style="margin-bottom:0.0001pt"><br></p><p class="MsoNormal" 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></p><p class="MsoNormal" style="margin-bottom:0.0001pt"><font face="Arial, sans-serif"><span style="font-size:16px"><br></span></font></p><p class="MsoNormal" style="margin-bottom:0.0001pt"><font face="Arial, sans-serif"><span style="font-size:16px">My view create script is below.</span></font></p><p class="MsoNormal" style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"><br></span></p><p class="MsoNormal" style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif">CREATE OR REPLACE VIEW joined_table2_locations AS </span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> SELECT <a href="http://table1.id">table1.id</a>,</span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> table1.geom,</span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> <a href="http://table1.name">table1.name</a>,</span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> table1.type,</span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> <a href="http://table2.name">table2.name</a>,</span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> table2.zone,</span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> table2.date_from,</span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> table2.date_to,</span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> table2.volume,</span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> table2.key_colum</span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> FROM table1</span></p>
<p class="MsoNormal" style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"> JOIN table2 ON table1.name::text = <a href="http://table2.name">table2.name</a>;</span></p><p class="MsoNormal" style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"><br></span></p><p class="MsoNormal" style="margin-bottom:0.0001pt"><span style="font-size:12pt;font-family:Arial,sans-serif"><br></span></p></div><div class="gmail_extra"><br><div class="gmail_quote">On Thu, Dec 10, 2015 at 3:36 AM, 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><span>Hi Matt,<br></span></div><div><span><br></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></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></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></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></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></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></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></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></div><div dir="ltr"><br></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></font></font></div><div dir="ltr"><br></div><div dir="ltr">Cheers</div><div dir="ltr"><br></div><div dir="ltr">Brent Wood<br></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></div><div><br> </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 href="mailto:mattslists@gmail.com" target="_blank">mattslists@gmail.com</a>><br> <b><span style="font-weight:bold">To:</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> 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><div><div class="h5"><br><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></div><div>spatial table </div><div>geometry Column | location1</div><div><br></div><div><br></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></div><div>All the data is there and shown correctly in the combined attributes table, however labels don't show correctly.</div><div><br></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></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></div><div>Thanks</div><div>Matt</div><div><br></div><div><br></div></div></div><br></div></div>_______________________________________________<span class=""><br>Qgis-user mailing list<br><a href="mailto:Qgis-user@lists.osgeo.org" target="_blank">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></span></div> </div> </div></div></div></blockquote></div><br></div>