This short article is designed to help you better understand how to get the most out of your data in both XMAP and QGIS by applying simple but effective 'best practice' principles to your PostGIS database.
The golden rules:
Schema names, table names and column names are always kept in LOWER CASE
Schema names, table names and column names never have spaces between words, spaces are replaced with UNDERSCORES
- Schema names, table names and column names never contain special characters or start with numbers
- All tables must have a PRIMARY KEY, with a preference for an ID column set as a SERIAL type.
Following the golden rules:
Schema names:
planning_policy_local_plan_2019_to_2031 - A good example of following the first 3 golden rules. No upper cases, no spaces and no special characters
Planning Policy Local_Plan_2019 to 2031 - A bad example with a mixture of cases, and containing spaces.
Table names:
msc_conservation_areas - A good example of following the first 3 golden rules. No upper cases, no spaces and no special characters
2019_Conservation_Areas - A bad example, starts with a number and is mixed case
Column names:
ca_name - A good example of following the first 3 golden rules. No upper cases, no spaces and no special characters
CA_name - A bad example, mixed case
Primary Keys and Serials:
A Primary Key is a defined column into your table that contains a unique value (normally a number), and can not be set (or left) as null. To make database administration easier, setting the Primary Key column to a type of serial is the best option, as will take care of things like sequences for you. While you can have Primary Key column types set to integer, there comes a lot more SQL admin when it comes to applying permissions so that a user can edit the table.
Create table and grant permissions using the best golden rules:
- CREATE TABLE planning_policy_local_plan_2019_to_2031. msc_conservation_areas (
- gxid SERIAL PRIMARY KEY,
- ca_name text,
- geom GEOMETRY(MULTIPOLYGON, 27700)
- );
- GRANT ALL PRIVILEGES ON TABLE planning_policy_local_plan_2019_to_2031.msc_conservation_areas TO fred;
- GRANT ALL PRIVILEGES ON SEQUENCE msc_conservation_areas_gxid_seq TO fred;
Create table and grant permissions not following the golden rules. Note how harder it is to read and how much more SQL you need to write
- CREATE TABLE "Planning Policy Local_Plan_2019 to 2031". "2019_Conservation_Areas" (
- gxid int4,
- "CA_name" text,
- geom GEOMETRY(MULTIPOLYGON, 27700)
- );
You will have to create a sequence for the primary key as well, which is not needed when using a serial type.
- CREATE SEQUENCE "2019_Conservation_Areas_gxid_seq"
- START 1
- INCREMENT 1
- MINVALUE 1
- NO MAXVALUE
- CACHE 1;
You will have to update the table to let Postgres know which column is to be used as a primary key
- ALTER TABLE "Planning Policy Local_Plan_2019 to 2031"."2019_Conservation_Areas" ALTER COLUMN gxid SET DEFAULT nextval('regions_gxid_seq');
- GRANT ALL PRIVILEGES ON SEQUENCE "2019_Conservation_Areas_gxid_seq" TO fred;