Changes between Version 3 and Version 4 of UsersWikiQGIS
- Timestamp:
- Oct 30, 2013, 5:29:35 AM (11 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
UsersWikiQGIS
v3 v4 16 16 == Why use QGIS with PostGIS == 17 17 18 QGIS is an open source software with lot's of functionalities. PostGIS is natively integrated in it, and it works relatively well. 19 Also QGis as an extension repository and is fully scriptable with Python, which makes it a cornerstone for working in GIS with Open Source software. 18 QGIS is an open source software with lot's of functionalities. PostGIS is natively integrated in it, and it works relatively well. 19 20 Also QGIS has an extension repository and is fully scriptable with Python, which makes it a cornerstone for working in GIS with Open Source software. 20 21 When using PostGIS you always end up to have to display some geometry, so better use the right tool for it. 21 22 22 23 == Workflow == 23 24 24 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 sas 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.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. 25 26 26 27 == QGIS limitations == 27 28 28 Every loaded PostG is geometry layer must have a Primary key! That is, a way of uniquely identifying every row of the table. In PostGIS it is usually done adding angid column in table definiton, like this : `CREATE TABLE mytable AS (idauto SERIAL int, other_columns...)`. If you have no other option , you can always use a trick to generate on the fly a fake uid (see Tricks).29 Every loaded PostGIS geometry layer must have a Primary key! That is, a way of uniquely identifying every row of the table. In PostGIS it is usually done adding a gid column in table definiton, like this : `CREATE TABLE mytable AS (idauto SERIAL int, other_columns...)`. If you have no other option , you can always use a trick to generate on the fly a fake uid (see Tricks). 29 30 30 PostGIS srid are not supported, so you have to manually choose the right projection and coordinate system when adding a PostG isgeometry layer.31 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 . Us St_CollectionExtract or ST_Dump to break GeometryCollection appart.31 PostGIS srid are not supported, so you have to manually choose the right projection and coordinate system when adding a PostGIS geometry layer. 32 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. 32 33 33 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 postgislayer.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 35 35 PostG isis 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.36 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. 36 37 37 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) … … 49 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 51 You 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. 52 51 53 Remember each line must have an unique id (see tricks), uni-type geometry, and proper formatting. 52 54 … … 67 69 68 70 69 You can use QG isvisualization settings to make the element appears dynamically.71 You can use QGIS visualization settings to make the element appears dynamically. 70 72 This is better than just waiting with blank screen while objects are loading, at least when trying to define an interesting area. 71 73 For this, open Option/Rendering, then put something superior to 1000 in the appropriated field. … … 74 76 When you don't have a unique id for each row you want to display, you can create on on the fly using the SQL windows to load your geometries. 75 77 Simply add in the select : 76 `SELECT row_number() OVER() AS id_qgis` , and select id_qgis as the id column in the qgislist.78 `SELECT row_number() OVER() AS id_qgis` , and select id_qgis as the id column in the QGIS list.
