Changes between Version 4 and Version 5 of UsersWikiQGIS


Ignore:
Timestamp:
Oct 30, 2013, 5:34:29 AM (10 years ago)
Author:
remic
Comment:

—

Legend:

Unmodified
Added
Removed
Modified

  • UsersWikiQGIS

    v4 v5  
    1111 * Workflow
    1212 * QGIS limitations
    13  * 4 ways to display a postgis geometry in QGIS
     13 * 4 ways to display a PostGIS geometry in QGIS
    1414 * Tricks
    1515
    … …  
    2323== Workflow ==
    2424
    25 The workflow is always identical. The first time, you have to define a connection to PostGIS database ((default values are : host:localhost , port:5432 , Database:postgres)). Then you will load a postgis table containing a geometry column as a new QGIS layer. Then this layer can be edited, and you have access to all the other columns (if the type is known by QGIS, else cast to ::text), for example to use it as a color categories, or as proportion field, etc.
     25The workflow is always identical. The first time, you have to define a connection to PostGIS database ((default values are : host:localhost , port:5432 , Database:postgres)). Then you will load a PostGIS table containing a geometry column as a new QGIS layer. Then this layer can be edited, and you have access to all the other columns (if the type is known by QGIS, else cast to ::text), for example to use it as a color categories, or as proportion field, etc.
    2626 
    2727== QGIS limitations ==
    … …  
    3232 Mixed geometry are not supported ( neither are GeometryCollections). So when using mixed geometries, you have to load 3 times the table each time selecting a different type of geometry . Use St_CollectionExtract or ST_Dump to break GeometryCollection appart.
    3333 
    34  Loaded Postgis layers are intended for stable use. So when loading a layer from a postgis table, don't delete table or change table def, as it will crash QGIS. Reloading will discard bad PostGIS layer.
     34 Loaded PostGIS layers are intended for stable use. So when loading a layer from a PostGIS table, don't delete table or change table def, as it will crash QGIS. Reloading will discard bad PostGIS layer.
    3535
    3636 PostGIS is made to display up to a few hundred thousand geometry with beautiful styling. More geometries than this is going to be slow, or will crash. You may want to clip you data to keep only a work set if working with massive data.
    3737
    38  Funky type : QGIS works well with points, line , polygon. It will work to a certain extent with Multi, but will definitively not work with more subtle geometry type, like curve, mesh, tin, etc. Usually Postgis has function to convert to classical type (ST_CurveToLine for example)
     38 Funky type : QGIS works well with points, line , polygon. It will work to a certain extent with Multi, but will definitively not work with more subtle geometry type, like curve, mesh, tin, etc. Usually PostGIS has function to convert to classical type (ST_CurveToLine for example)
    3939
    40 == 5 ways to display a postgis geometry in QGIS ==
     40== 5 ways to display a PostGIS geometry in QGIS ==
    4141 
    4242 From easiest to most complicated and powerful
    … …  
    4444 * Load the "QuickWKT" Extension. It should add a button "WKT". This open a text field where you can past WKT geometries. Warning : doesn't support curve or mixed type, but does support EWKT (you can specidy srid). This adds a layer to QGIS, you can add as many layers as you want. This should be used only for small tests.
    4545
    46  * Select the "Add a PostGis Layer" in the "Layer" menu. This open a windows where you have to define a new connection to your postgis server (default values are : host:localhost , port:5432 , Database:postgres). You have several options you can try afterward by clicking on the edit button to edit the connection. When connected to a database, you can browse to your table and select it to display it in QGIS. You can select multiple table. A same time will appear X times if it has X geometry columns. If your table doesn't show, it is probably not registered in the public.geometry_columns PostGIS view. You have other option to display content.
     46 * Select the "Add a PostGIS Layer" in the "Layer" menu. This open a windows where you have to define a new connection to your PostGIS server (default values are : host:localhost , port:5432 , Database:postgres). You have several options you can try afterward by clicking on the edit button to edit the connection. When connected to a database, you can browse to your table and select it to display it in QGIS. You can select multiple table. A same time will appear X times if it has X geometry columns. If your table doesn't show, it is probably not registered in the public.geometry_columns PostGIS view. You have other option to display content.
    4747
    48  * Select the DBManager in the menu Database. In the left panel, browse to your connection, then choose a table with geometry. You can right click on this table and chose "Add as a QGIS layer". This will load table even if geometry column is not listed in POstGIS view public.geometry_columns. This is useful if you create your table with queries like `CREATE TABLE my_table AS SELECT geom ...`.
     48 * Select the DBManager in the menu Database. In the left panel, browse to your connection, then choose a table with geometry. You can right click on this table and chose "Add as a QGIS layer". This will load table even if geometry column is not listed in PostGIS view public.geometry_columns. This is useful if you create your table with queries like `CREATE TABLE my_table AS SELECT geom ...`.
    4949
    50  * Select the DBManager in the DataBase menu, then browse to Postgis/your_connection. Now click on the "SQL WIndow" icon. This open a text editor where you can use any query you like. It is very useful when geometry are not directly accessible. For example, you have a table with id, X and Y columns, but no geometry column. You can use the following query `SELECT id, X, Y, ST_SetSRID(ST_MakePoint(X,Y), your_srid) AS geom FROM your_table`
     50 * Select the DBManager in the DataBase menu, then browse to PostGIS /your_connection. Now click on the "SQL WIndow" icon. This open a text editor where you can use any query you like. It is very useful when geometry are not directly accessible. For example, you have a table with id, X and Y columns, but no geometry column. You can use the following query `SELECT id, X, Y, ST_SetSRID(ST_MakePoint(X,Y), your_srid) AS geom FROM your_table`
    5151You can test query with F5. There is auto completion. CTE are allowed, but you need to be strict on query formatting (no leading spaces or tabs, neither at the end). When you are satisfied, you can click on the "load as a new layer" box and choose an id and geom column.
    5252
    5353Remember each line must have an unique id (see tricks), uni-type geometry, and proper formatting.
    5454
    55  * If you prefer, there are various QGIS extensions allowing sql windows /and/or specific Postgis management.
     55 * If you prefer, there are various QGIS extensions allowing sql windows /and/or specific PostGIS management.
    5656
    5757== Tricks ==
    … …  
    6363See http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html
    6464
    65 If you have a *very* recent QGIS version you can just add view like any other Postgis table. Else, you have to create a simple view querying you materilized view.
     65If you have a *very* recent QGIS version you can just add view like any other PostGIS table. Else, you have to create a simple view querying you materilized view.
    6666For example :
    6767 `CREATE VIEW my_proxy_view AS SELECT * FROM my_materialized_view`