QGIS: Creating custom CRS without using the GUI

Question

What would be the appropriate way to create a custom CRS without using the GUI of QGIS on Windows?

I am trying to create a custom CRS but my GUI seems to be broken. I cannot use the normal way ("Settings -> User Projections") because no matter what I do the OK-Button does not work. I have tried several things, but none worked (details in this question: Creating custom CRS in QGIS)

So, I'm following the approach of nmtoken mentioned in this question. According to him I could edit the SQLite database where QGIS stores all the CRS. However, the QGIS website advises against messing with this db and I'm not familiar with SQL, anyway.

Does anyone have experience with this db? Or is there another way to either export it from one system to another or to create it via Python code?

Answer 1

This works for me, but please take a copy of the database for piece of mind...

First find the qgis.db sqlite database in your user profile, for me it was at:

C:\Users\MYUSER\AppData\Roaming\QGIS\QGIS3\profiles\default\qgis.db

Don't use the database of the same name in your QGIS installation folder something like C:\QGIS 3.6\apps\qgis\resources

Using DB Browser for SQLite open the qgis.db and then Browse Data for table tbl_srs then select New Record

Give a description a projection acronym and the proj parameters

In this case I gave:

• Albers_Central_Asia2
• aea
• +proj=aea +lat_1=21 +lat_2=49 +lat_0=37 +lon_0=87 +x_0=0 +y_0=0 +datum=WGS84 +units=m +no_defs

Then select Write Changes then Close Database

When you open the Custom Coordinate dialogue in QGIS the new projection is shown:

If the tables don't exist you could try creating them:

CREATE TABLE tbl_bookmarks (
  bookmark_id integer PRIMARY KEY,
  name varchar(255) NOT NULL,
  project_name varchar(32),
  xmin double,
  ymin double,
  xmax double,
  ymax double,
  projection_srid integer
)

CREATE TABLE tbl_ellipsoid (
  acronym char(20) NOT NULL default '',
  name char(255) NOT NULL default '',
  radius char(255) NOT NULL default '',
  parameter2 char(255) NOT NULL default '',
  PRIMARY KEY  (acronym))

CREATE TABLE tbl_projection (
  acronym varchar(20) NOT NULL PRIMARY KEY,
  name varchar(255) NOT NULL default '',
  notes varchar(255) NOT NULL default '',
  parameters varchar(255) NOT NULL default ''
)

CREATE TABLE tbl_srs (
    srs_id INTEGER PRIMARY KEY,
    description text NOT NULL,
    projection_acronym text NOT NULL,
    ellipsoid_acronym NOT NULL,
    parameters text NOT NULL,
    srid integer,
    auth_name varchar,
    auth_id varchar,
    is_geo integer NOT NULL,
    deprecated boolean)

CREATE INDEX idx_srsauthid on tbl_srs(auth_name,auth_id)