Thanks, I'm surprised as well, though each time the qgis window refreshes the values change, so it's not quite!
On Thu, Dec 10, 2015 at 2:32 PM, Brent Wood <pcr...@yahoo.com> wrote: > You want to select by date but you don't have a date to select on - just > the interval between dates. > > 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. > > 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. > > 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: > > > select ... > ... > name::':'::zone::' '::value as label > from > ...; > > I'm surprised your view works - you have two columns called name, you only > need one of them. > > > Cheers, > > Brent > > > ------------------------------ > *From:* Matt Boyd <mattsli...@gmail.com> > *To:* Brent Wood <pcr...@yahoo.com> > *Cc:* qgis-user <qgis-user@lists.osgeo.org> > *Sent:* Thursday, December 10, 2015 3:42 PM > *Subject:* Re: [Qgis-user] postgis/gresql views > > Hi Brent, > my problem at the moment is SQL based I think, it's been a while and I was > never that good at it. > table1 has the geometry. > table2 has the data > > for each location (name) there are multiple zones and multiple dates in > the data table. > So, a join on name gives > name1|zone1|date1|volume1 > name1|zone2|date2|volume2 > name1|zone1|date2|volume3 > > all with (I think) a working geometry > Which are all valid results. > > 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 > name1 (as at x day) > zone1:volume1, > zone2:volume2, > > I'm basically having trouble creating a single combined string from the > SQL that I can use as a label. > > My view create script is below. > > CREATE OR REPLACE VIEW joined_table2_locations AS > SELECT table1.id, > table1.geom, > table1.name, > table1.type, > table2.name, > table2.zone, > table2.date_from, > table2.date_to, > table2.volume, > table2.key_colum > FROM table1 > JOIN table2 ON table1.name::text = table2.name; > > > > > > On Thu, Dec 10, 2015 at 3:36 AM, Brent Wood <pcr...@yahoo.com> wrote: > > Hi Matt, > > What is the actual SQL you use to create the view? > Something like: > create view v_geo as > select tab1.location, > tab1.zone, > tab1.date, > tab1.value, > tab2.geom > from tab1, tab2 > where tab1.location=tab2.location; > > Then do a > select * from v_geo order by location, zone, date; > > 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. > > eg: > alter table tab1 add column id serial primary key; > and include this id column in the view. > > 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" > > It is also unclear whether you want to view categorised data in QGIS, or > create Postgis views in Postgis from QGIS: > "Ideally I'd like to be able to create labels and views from within qgis > ..." > > Unless I understand your problem better, I can't offer useful advice. > > Cheers > > Brent Wood > > > ------------------------------ > *From:* Matt Boyd <mattsli...@gmail.com> > *To:* qgis-user <qgis-user@lists.osgeo.org> > *Sent:* Wednesday, December 9, 2015 7:08 PM > *Subject:* [Qgis-user] postgis/gresql views > > Hi QGISers. > I'm trying to work out how to display some geological data on map. > Basically > Table x; > location1 | zone1 | date1 | value1 > location1 | zone1 | date2 | value2 > continued with variations in location/zone etc.. > > spatial table > geometry Column | location1 > > > 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. > > All the data is there and shown correctly in the combined attributes > table, however labels don't show correctly. > > 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). > > 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. > > Thanks > Matt > > > > _______________________________________________ > Qgis-user mailing list > Qgis-user@lists.osgeo.org > List info: http://lists.osgeo.org/mailman/listinfo/qgis-user > Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user > > > > >
_______________________________________________ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: http://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user