[Qgis-user] postgis/gresql views

[Matt Boyd]

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 <pcreso at 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 <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|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 <pcreso at 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 <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 | 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 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/0ec0473b/attachment.html>