A lot of organisations don’t realise this but nearly all simple datasets with more than 10 records with 10 distinct attributes which need to be regularly edited by one or more persons would be better managed in a database. The issue that organisations have is that they struggle to interface with those records because they have no front end user interface tools and no one knows how to set those up. Thus understandably a lot of individuals default to excel and can in a fashion manage somewhat larger datasets although chances are that as soon as more than one person starts editing the spreadsheet all sorts of issues arise around access and version control.

For town planners and for defining graphical boundaries excel simply cannot be used to store boundary information. Spatial data even a single complicated boundary is impossible to store in anything other than a format that supports geometry (coordinates) this means either a flat file format (often good old ESRI shape files) or a geography enabled database.

Postgres with the PostGIS extension is an ideal tool to store spatial information for things like local plan boundaries and should be considered as a default datastore for valuable boundary information. For four important reasons

1. VERSION CONTROL (Postgres allows for significant concurrent access to a single truth while managing risk of edit corruption)
2. SECURITY (Automated Backup and Restore on a centrally supported computer hardware either locally, corporately or on vendor cloud)
3. AUTOMATED ANALYSIS and REPORTING (Read only access can be given to users to allow them to create reports and write SQL)
4. QGIS EXISTS! – which is an open source desktop front end program with specific provision to allow users to edit and create spatial boundaries stored in postgres.

If you find yourself as the custodian of an important spatial dataset either as the domain expert or as the devops support and it needs to be continually updated and backed up I would definitely recommend you use PostgreSQL with PostGIS.

What is PostGIS to PostgresSQL? – think Mercedes G3 Wagon and think Brabus version – Its an aftermarket addition to a base original which makes the base significantly more powerful – in this case PostGIS optimises PostgreSQL for real world spatial geometry.

What are the basics I need to know?

You are an Accidental Database Administrator and there is some basic knowledge that you should know when managing your data.

• CAUTION : If you are doing non simple tasks in a database ALWAYS take a backup before you start.
• Only name tables and views in Postgres in lowercase alphanumeric, don’t use special characters or spaces ( link postgres lexical structures )
• Understand how to Backup and Restore entire databases quickly and accurately – Take frequent and multiple backups see first point
• Configure all tables with a Primary Key which is also classed as Identity
• Configure all tables with sequences so that new values do not clash with existing values (without this inserts can fail)