Skip to content

3. Managing Database

Jump to bottom
Uroš Preložnik edited this page Aug 11, 2020 · 67 revisions

gisportal now allows complete database management in web interface for administrators. When you enable gisportal just login as default user (admin, admin) and use web interface. Read text below more as database description.

Clients

Think of it as a customers, every project belongs to certain customer (=client). If you open table clients you already see client DEMO with id=1. You can edit this record or insert new clients.

  • id unique integer
  • name unique
  • display_name is shown in Title panel in QWC.
  • theme_id currently not used, leave default value 1
  • url activates display and link on client logo. Insert custom client logo as [name].png in folder /gisapp/admin/resources/.
  • description short text for gisportal

Layers

Table contains definitions of all external layers (Overlays and Base layers) you wish to use with any of your projects. You define each layer only once and then you can use it in many projects with referencing it's id as Overlay or Base layer.

  • id unique integer
  • name unique used for alias
  • display_name
  • type OpenLayers Layer class. Currently you can use values: WMS, Google (only Desktop client), Bing, XYZ, WMTS and OSM.
  • definition Paste Layer definition. Refer to existing records and examples below.

Google

To use Google Maps layers you will also have to provide a Google Maps Javascript API key. Insert this value with your key in /admin/settings.php. Look in settings_template.php:

define('GOOGLE_MAPS_KEY','your_key');

SQL insert queries examples for (OSM and Google are already included in setup script):

NOTE: Google type layer does not work in Mobile client, but there is another option to add Google layers as XYZ. Read below about that.

OSM

Openlayers 2 initially loads OSM layer only for 19 zoom levels with maximum zoom scale 1:2257. OSM contains 20 zoom levels, so to use zoom 20 (scale 1:1128), use this query to update OSM layer in database (if you setup your database after January 30, 2018 your database is already updated with this):

UPDATE layers SET definition='{"numZoomLevels": 20, "serverResolutions": [156543.03390625, 78271.516953125, 39135.7584765625, 19567.87923828125, 9783.939619140625, 4891.9698095703125, 2445.9849047851562, 1222.9924523925781, 611.4962261962891, 305.74811309814453, 152.87405654907226, 76.43702827453613, 38.218514137268066, 19.109257068634033, 9.554628534317017, 4.777314267158508, 2.388657133579254, 1.194328566789627, 0.5971642833948135, 0.29858214169740677]}'
WHERE type='OSM';

You can also increase maximum map scale by changing numZoomLevels to 21 (1:564) or 22 (1:282). That will allow user to zoomin on map with OSM images just enlarging max available zoom level.

WMS

INSERT INTO layers(name, display_name, type, definition) 
VALUES ('arso_kcn', 'Aglomeracije', 'WMS', '{
	"url": "http://gis.arso.gov.si/geoserver/ows",
	"params": {
		"VERSION": "1.1.0",		
		"LAYERS": "arso:AGLOM_KCN",
		"STYLES": "",
		"FORMAT": "image/png",
		"TRANSPARENT": true
	},
	"options": {
		"opacity": 0.7,
		"buffer": 0,
		"displayOutsideMaxExtent": true,
		"isBaseLayer": false,
		"singleTile": false
	}
}');

In case of using WMS layer as overlay (extra) layer you can add visibility property to definition object to control initial visibility of layer on project start.

"visibility": true

Bing

INSERT INTO layers(name, display_name, type, definition)
VALUES ('bing_sat', 'BingMaps Aerial', 'Bing', '{
	"imagerySet": "Aerial",
	"key": "YOUR BINGMAPS API KEY HERE"
}');

XYZ (Mapbox, Google workaround)

INSERT INTO layers(name, display_name, type, definition)
VALUES ('mapbox_hibrid', 'MapBox  Hybrid', 'XYZ', '{
	"url": "https://api.mapbox.com/v4/mapbox.streets-satellite/{z}/{x}/{y}.png?access_token=YOUR_MAPBOX_ACCESS_TOKEN",
	"options": {
		"sphericalMercator": true,
		"wrapDateLine": true,
		"numZoomLevels": 20
	}
}');

Google workaround

JSON Definition to add Google Map layer as XYZ:

{
    "url": "http://mt0.google.com/vt/lyrs=m&hl=en&x={x}&y={y}&z={z}",
    "options": {
        "sphericalMercator": true,
        "wrapDateLine": true,
        "numZoomLevels": 23
    }
}

This way you can also add Google layers to QGIS (Data Source Manager -> Browser -> XYZ tiles). URL works for now but it's possible to change in the future, also keep in mind that such use is not strict by Google license, search web for more information.

TMS (via XYZ)

To use TMS type layers, use XYZ definition and type with following changes:

  1. In the provided URL replace {y} with {-y}
  2. Add tmsUrl,tmsLayer,tmsType to the definition
  3. Add minZoom,maxZoom to the definition

Example (just definition JSON)

{
    "url": "https://tms_url/1.0.0/tms_layer/{z}/{x}/{-y}.jpeg",
    "minZoom": 0,
    "maxZoom": 20,
    "tmsUrl": "https://tms_url/",
    "tmsLayer": "tms_layer",
    "tmsType": "jpeg",
    "options": {
        "sphericalMercator": true,
        "wrapDateLine": true
    }
}

WMTS

WMTS format is most complex to support. First you need to examine capabilities XML format and then setup your layer using examples below.

WMTS - EPSG:3857, Example of Orthofoto Austria

INSERT INTO layers(name, display_name, type, definition)
VALUES ('basemap_at_ortho', 'Geoland Basemap Orthofoto', 'WMTS', '{
	"displayOutsideMaxExtent": false,
	"url": "https://maps.wien.gv.at/basemap/bmaporthofoto30cm/{Style}/{TileMatrixSet}/{TileMatrix}/{TileRow}/{TileCol}.jpeg",
	"layer": "bmaporthofoto30cm",
	"matrixSet": "google3857",
	"requestEncoding": "REST",
	"style": "normal",
	"tileFullExtent": "new OpenLayers.Bounds(-20037508.34,-20037508.34,20037508.34,20037508.34)",
	"tileOrigin": "new OpenLayers.LonLat(-20037508.34,20037508.34)",
	"maxExtent": "new OpenLayers.Bounds(-20037508.34,-20037508.34,20037508.34,20037508.34)",
	"numZoomLevels": 20,
	"serverResolutions": [156543.03390625, 78271.516953125, 39135.7584765625, 19567.87923828125, 9783.939619140625, 4891.9698095703125, 2445.9849047851562, 1222.9924523925781, 611.4962261962891, 305.74811309814453, 152.87405654907226, 76.43702827453613, 38.218514137268066, 19.109257068634033, 9.554628534317017, 4.777314267158508, 2.388657133579254, 1.194328566789627, 0.5971642833948135, 0.29858214169740677],
	"capabilitiesUrl": "https://www.basemap.at/wmts/1.0.0/WMTSCapabilities.xml"
}');

In case of using WMTS layer as overlay (extra) layer you can add visibility property to definition object to control initial visibility of layer on project start.

"visibility": true

Projects

Every QGIS project you wish to use, has to be added to database. REQUIRED fields are BOLD, others can be default.

  • ID unique integer,
  • NAME unique, must be equal to QGIS project file name, including same letter case, without qgs extension,
  • overview_layer_id id of layer used in overview map. Can be blank for no overview.
  • base_layer_ids integer array of ids of layers you wish to use as Base layers with this project. Example: {1,2}.
  • extra_layers_ids integer array of ids of layers you wish to use as Overlay layers with this project. User can select many overlay layers, but only one Base Layer.
  • CLIENT_ID referencing id in client table,
  • tables_onstart text array of layer names from QGIS project that will have open attribute table on start. Example: {country,pop_places}
  • public set this to true to enable public (guest) access for this project.
  • display_name title of this project. Maintained by code and comes from QGIS project. Do not change manually!
  • crs CRS of this project. Maintained by code and comes from QGIS project. Do not change manually!
  • description short description for gisportal
  • contact short info for gisportal
  • restrict_to_start_extent set this to true to restrict map extent of client to start extent. This disables user to pan outside of start extent of the map set from QGIS project.
  • geolocation enable/disable geolocation control for project
  • measurements enable/disable measure length and area controls for project
  • feedback enable/disable user feedback control for project. To use this control you must set up gisportal.
  • feedback_email email address for receiving user feedbacks for project. Leave blank for default company email from gisportal.
  • project_path optional, only if you want to override default project location. Write complete path with full projectname and extension!
  • print show print button, default true
  • zoom_back_forward show zoom navigation buttons, default true
  • identify_mode show combo to select identification mode (all layers, tophit, selected layer), default false and set to all layers
  • permalink show permalink button, default true

Users

0. Default user

Since 2018-11-19 setup script contains default administrator user, with username: admin and password: admin. With this user you can open every project and use gisportal for complete web administration. If you wish to change password to admin or any other user it's currently not possible with web interface. You have to create new user, make him admin and then you can delete old user.

1a. Registration old way

For registering new users use this URL:

http://localhost/gisapp/admin/index.php?action=register

Currently this is very basic, without mail support and without password recovery option. Passwords should be remembered, since they are encoded in database. At this point user is just in a database not able to opening any projects.

1b. Registration on gisportal

gisportal

2. Permissions

You have to delegate projects to each registered user manually:

  • project_ids integer array referencing ids from table projects, that user is allowed to open.

Update query example:

UPDATE users SET project_ids='{1}'; (it adds permissions to use project 1 to all users, add WHERE user_name = 'XXX' to restrict to specific user)

All other fields in Users table are maintained by code.

3. Administrators

Users with admin field set to true have permission to use every project in database without specifying project_ids and they can use gisportal also for administration.

Upgrading

Current database version is displayed in version.txt. Check your version with this query:

SELECT max(version) FROM settings;

If your version is lower than current, run all scripts from your version number and higher. Scripts are located in /gisapp/_scripts.

Clone this wiki locally