[Qgis-user] postgis/gresql views

[Matt Boyd]

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/9ac6dacb/attachment.html>