Editing PostGIS layer in Lizmap?

Question

I've got a freshly Lizmap 3.2 installation. I can display PG-Layers but I cannot edit them. My setting is:

• Ubuntu Server 18.04.1
• PostgreSQL 10.6
• Apache2
• Lizmap 3.2
• PHP 7.2

I followed this documentation https://docs.lizmap.com/current/en/index.html with all extension mentioned installed.

When loading the map I can see the PG layer but when trying editing I get the following error log in Chrome's developer tools:

Failed to load resource: the server responded with a status of 500 (Internal Jelix Error) Error 500. A technical error has occured. Sorry for this trouble. jquery-1.12.4.min.js:4

Looking into Lizmap's error logs I get:

2018-12-10 11:38:08 80.149.89.58 error 2018-12-10 11:38:08 [402] Fehler während der Verbindung '' /var/www/lizmap-web-client-3.2.0/lib/jelix/plugins/db/pgsql/pgsql.dbconnection.php 153

(Fehler während der Verbindung = German for 'Error during Connection')

I have nothing added to profiles.ini or localconfig.ini

The QGIS' connection string in the .qgs file is:

dbname='postgis' host='' port=5678 user='' password='' sslmode=disable key='id' srid=3857 type=Polygon 'key='id' table="bike"."iso_vrs_db_edit" (geom) sql=

The table/the features are not in the public schema but in a schema called "bike". Does this cause any unexpected behavior?

I found this in the project's Lizmap .cfg file. Maybe something is missing?

"editionLayers": { "iso_vrs_db_edit": { "layerId": "iso_vrs_db_edit_f8a99ef9_2e50_40da_8b8a_036a8f9aefa3", "geometryType": "polygon", "capabilities": { "createFeature": "True", "modifyAttribute": "True", "modifyGeometry": "True", "deleteFeature": "True" }, "acl": "", "order": 0 } }

Do I have to change the profiles.ini file? I found Installing Lizmap with PostgreSQL but if I set up the profiles.ini that way Lizmap won't load and give a technical error when loading the lizmap start page.

Any hints how to attack this issue?

EDIT:

I changed my profiles.ini due to Piskr's to

[jdb:jauth]
driver="pgsql"
database="postgis"
host="localhost"
port=5678
user="<user>"
password="<password>"

;[jdb:jauth]
;driver=sqlite3
;database="var:db/jauth.db"

[jdb:lizlog]
driver="pgsql"
database="postgis"
host="localhost"
port=5678
user="<user>"
password="<password>"
;driver=sqlite3
;database="var:db/logs.db"

I then do a

 lizmap/install/clean_vartmp.sh
 lizmap/install/installer.php
 lizmap/install/clean_vartmp.sh

The installer.php returns

Installation start..
[notice] Installation starts for the entry point index
All modules dependencies are ok
All modules are installed or upgraded for the entry point index
[notice] Installation starts for the entry point admin
All modules dependencies are ok
All modules are installed or upgraded for the entry point admin
[notice] Installation starts for the entry point script
All modules dependencies are ok
All modules are installed or upgraded for the entry point script
Installation ended.

But now when trying to open the Lizmap page the browser shows:

Error 500. A technical error has occured. Sorry for this trouble.

The user I use in profiles.ini is a database SUPERUSER.
Where should the new table be created? Can I get any further information in any log? I can connect to the DB from another machine via ssh and the credentials without any problem.

Answer 1

I've tested this procedure on the QGIS 2.18 LTS on Ubuntu 16.04 LTS. NOTE: For safety reasons use the old version of the QGIS server and desktop!

Yes you have to change the profiles.ini.php.

If you take a look at my question, I had a similar problem.

IMPORTANT NOTE: Changing the profiles.ini.php won't do much if you've already executed the installation process, because you need to change it before running the installation process (php installer.php).

It is after you run install.php the process will write tables to a PostgreSQL database. After that all the logging, rights, users will be written to that database.

When you are creating your database, do not forget to execute: CREATE EXTENSION postgis; so your database will have gis capabilities, also I recommend a separate database for lizmap and another database for your data.

So this is what my profile.ini.php looks like:

[jdb:jauth]
driver="pgsql"
database="lizmap"
host="localhost"
port=5678
user="dummyuser"
password="dummypass"

[jdb:lizlog]
driver="pgsql"
database="lizmap"
host="localhost"
port=5678
user="dummyuser"
password="dummypass"

After that the installer should do the job. When you've executed the installer you should get the following warning:

pg_close(): supplied resource is not a valid PostgreSQL link resource /var/www/lizmap-web-client-3.2.1/lib/jelix/plugins/db/pgsql/pgsql.dbconnection.php 166

Now this is only a bug, everything is working as it should.

We can't really see what the installer does unless it crashes, that is the way it will display the error. The logs for lizmap-client are found in the lizmap-web-client-3.x.x/lizmap/var/log and in here you will find most errors that occur with your client. Other logs you can check are apache logs which are found in var/log/apache2.

The installer will write tables to the database written in your profiles.ini.php, these are the tables that it created (note that spatial_ref_sys is created by the postgis extension):

By default the scheme of the tables is public and it doesn't matter what the name of the database is, as long as it matches the name you wrote in the profiles.ini.php.

I would recommend to completely delete the lizmap-client folder and do a fresh install, because if you do the re-install written in the docs, it doesn't start fresh (like recreating tables), but takes some preset settings from index directory.

I've created a test database (different then my lizmap database) which contains a scheme named 'test' with my example vector data.

NOTE: Your data table should have 1 ID field which has auto-number (sequence) properties (in most cases it's called ogc_fid), so when you do an addition, lizmap will know the next index of the newly created polygon.

Here is my test data-table:

CREATE TABLE test_data
(
  ogc_fid integer NOT NULL DEFAULT nextval('test_data_ogc_fid_seq'::regclass),
  wkb_geometry geometry(Polygon,<your EPSG code>),
  sifko numeric(6,0),
  parcela character varying(10),
  CONSTRAINT test_datapkey PRIMARY KEY (ogc_fid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE test_data
  OWNER TO postgres, $user;

Then I added a new connection to QGIS and added the layer from the scheme and applied changes (added my layer in the Layer editing option of the plugin) with the lizmap plugin.

I had to create a new test repository to which I added my newly exported qgs and qgs.cfg files.

Then I simply edited my data in the client:

After I was done with drawing my triangle I clicked save and my new feature appeared: