by Spatial Tapestry
email: SpatialTapestry@gmail.com
QGIS has builtin tools to take Vector themes and save them out to Shape/TAB Files, GeoPackages, etc, but seems to be lacking tools in the GUI to bulk process Vector Themes into databases like Postgres, Oracle and MS SQL Server.
This plugin seeks to address that need and to be a building stone for other tools from the author.
Select the database where the data is to be stored. Note that Oracle is supported via both the 'oracledb' and 'Devart' connectors.
Enter the database connection parameters and the SRID. The SQL can be written to a local file, in the user's home directory on both Windows and Linux, if that is wanted then check the 'Write SQL to (ASCII) File' CheckBox. The SQL can also be executed against the database directly, if that is wanted then check the 'Execute SQL against the Database' CheckBox. If both are wanted then check both CheckBoxes.
Click "Save Project File'. The Project file will be loaded everytime that database type is selected.
Click "Test Database Connection, User Name and Password" if there is a need to test the connection. The plugin will attempt to open the connection and create a 'testtable'.
The 'Delete the QGIS Selected Themes from Serv' button can be used to delete one or more themes from the database. This may be useful for several reasons such as MSSQL not accepting invalid coordinates, where one fixes a geometry in QGIS and needs to reload the theme, bearing in mind that MSSQL does not support 'IF EXISTS' on a 'DROP THEME' command. It is also useful for dropping themes when no longer needed.
The 'Process the QGIS Selected Themes to Serv' is how the SQL is generated and stored to file and/or executed to the database. As this is running, one can look at their home directory and see the SQL and/or TXT (Report) Files being created as each theme is processed.
Postgres needs a schema to work in and is generally to easiest one to manipulate from SQL with the 'IF EXISTS' and 'IF NOT EXISTS' clauses. The SQL Files are compatible for PGAdmin 3 and 4, and DBeaver.
Oracle supports 4000 characters in client side SQL and 32700 characters with binding/clobs for the geometry so some very large features may not load. Thanks to Simon Greener for help with Bind Variables and Clobs. Oracle only supports schemas for the user themselves. The SQL Files are compatible for SQL Developer and DBeaver.
MS SQL does not support 'IF EXISTS' and 'IF NOT EXISTS' clauses so reprocessing can be tedious. Schemas are supported but seems to be simply prefixes to the table name. The SQL Files are compatible for MS SQL Server Admin, and DBeaver.
In all cases it falls to the user to have all the connection details sorted, so basically if DBeaver and other tools cannot connect then neither can this Plugin.
QGIS 3.28 is recommended. Do not install Python, instead we will use the Python installed with QGIS. (Installing Python 3.12 on Windows causes problems)
All 3 database connectors must be installed otherwise to code will return errors. Alternatively the Python Source code can be modified to remove references to any unwanted connectors.
pip3 install -U psycopg2
pip3 install -U oracledb
pip3 install -U pyodbc
On Windows the "PythonPath" Environment Variable must be set - be sure
to adjust this to your particular computer and QGIS Version:
PYTHONPATH
C:\Program Files\QGIS 3.28.15\apps\qgis\python;C:\Program Files\QGIS
3.28.15\apps\qgis\python\plugins;C:\Program Files\QGIS
3.28.15\apps\Qt5\plugins;C:\Program Files\QGIS 3.28.15\share\gdal;
C:\Program Files\QGIS 3.28.15\apps\Python39\Scripts\pip3 install -U
psycopg2
C:\Program Files\QGIS 3.28.15\apps\Python39\Scripts\pip3 install -U
oracledb
C:\Program Files\QGIS 3.28.15\apps\Python39\Scripts\ pip3 install -U
pyodbc
https://www.devart.com/python/sqlserver/download.html
Python Connector for SQL Server 1.0 - Python 3.9 for Windowscd C:\Users\r\Downloads\devart_sqlserver_cp39_win\DevartPythonSqlServer\whl
"C:\Program Files\QGIS 3.28.15\apps\Python39\Scripts\pip3" install
devart_sqlserver_connector-1.0.1-cp39-cp39-win_amd64.whl
The plugin stores the database connection details for easy reuse. These are included in the Plugin's folder but need to be moved to the user's home folder before they can be accessed.
The first 3 characters indicate the connection type:
host=192.168.56.1
database=gisdb
user=rogermsu
password=rogermsu
port=15432
schemaname=test123
tableSRID=EPSG:7844
DatabaseType=PGS
Output2=Serv
host=192.168.56.1
database=gisdb
user=drrog
password=drrog
port=11433
schemaname=testSchema
tableSRID=EPSG:7844
DatabaseType=MSS
Output2=Both
host=192.168.56.1
database=xe
user=drrog
password=drrog
port=11521
schemaname=testSchema
tableSRID=EPSG:7844
DatabaseType=ORA
Output2=Both
CREATE DATABASE gisdb WITH OWNER = postgres ENCODING = 'UTF8'
TABLESPACE = pg_default CONNECTION LIMIT = 300;
CREATE ROLE rogermsu LOGIN PASSWORD 'rogermsu' SUPERUSER INHERIT
CREATEDB CREATEROLE NOREPLICATION;
CREATE SCHEMA testdata AUTHORIZATION rogermsu;
Note: With Oracle each user has their own schema
Enter user-name: system
Enter password:
Last Successful login time: Wed Jan 10 2024 14:33:56 +08:00
Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> create user drrog identified by drrog;
create user drrog identified by drrog
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> alter session set "_ORACLE_SCRIPT"=true;
Session altered.
SQL> create user drrog identified by drrog;
User created.
SQL>
-- USER SQL
CREATE USER "drrog" IDENTIFIED BY "drrog" ;
-- ROLES
GRANT "CONNECT" TO "DRROG" ;
GRANT "DBA" TO "DRROG" ;
-- SYSTEM PRIVILEGES
GRANT ALTER ANY TABLE TO "DRROG" ;
GRANT DROP ANY TABLE TO "DRROG" ;
GRANT INSERT ANY TABLE TO "DRROG" ;
GRANT CREATE ANY TABLE TO "DRROG" ;
Remember to open up TCPIP on the server and create the user, etc.