[Qgis-user] postgis/gresql views

[Brent Wood]

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 <mattslists at gmail.com>
 To: Brent Wood <pcreso at yahoo.com> 
Cc: qgis-user <qgis-user at 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|volume1name1|zone2|date2|volume2name1|zone1|date2|volume3
all with (I think) a working geometryWhich 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 datename1 (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 <pcreso at yahoo.com> wrote:

Hi Matt,

What is the actual SQL you use to create the view?Something like:create view v_geo asselect tab1.location,       tab1.zone,       tab1.date,       tab1.value,       tab2.geomfrom tab1, tab2where 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 <mattslists at gmail.com>
 To: qgis-user <qgis-user at 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 | value1location1 | zone1 | date2 | value2continued 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.
ThanksMatt


_______________________________________________
Qgis-user mailing list
Qgis-user at lists.osgeo.org
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

  



 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20151210/05673f1f/attachment.html>