Use of database views

    Use of database views

    You can create a view over a SELECT query, which gives a name to the query that you can refer to like an ordinary table. Views are very useful for example to filter and sort data, without having to create a new table.

    Views can be used in almost any place a real table can be used.

    Create a view

    1. Click on the "New Layer".
    2. Click on the "Database view".
    3. Give the new view a name.
    4. Input the SELECT SQL, which will define the view.
    5. Click "Create."

    Both a table and a view must have a primary key. Ramani Cloud detects primary keys on tables, but views do not have primary keys, so Ramani Cloud falls back on the field "gid". This means that the view should have a field "gid" with unique values. It must also have a geometry field, so it appears in the list of layers (there is no requirement for the naming of geometry fields). If a table is created through Ramani Cloud, the table will have "gid" as the primary key. So a SELECT like this will work:

    SELECT * FROM foo WHERE bar=1

    If * is not used in the query, the gid field and geometry field must be queried:

    SELECT gid,the_geom FROM foo WHERE bar=1

    There may also be made a "gid" field with "As" syntax. Here a view with one single point:

    SELECT 1 As gid, ST_SetSRID (ST_Point(-123.365556, 48.428611),4326)::geometry(Point,4326) AS the_geom

    Management of views

    Views behave like tables in nearly all contexts. In order to know the views from real tables, the views is given a blue background in the layer list.

    A difference between views and tables is that maps views can not be edited.

    Get the definition of a existing view

    1. Select a view-layer in the list so that the background becomes gray.
    2. Click Advanced.
    3. The SELECT SQL that defines the view, can be seen in the field View definition.