Create vector layers using PostGIS and QGIS
The goal of this post is to show how PostGIS and QGIS can be used to create spatial data not bound to Earth. Why bother? As I explained in a recent bug report, there are few valid examples of non-Earth use cases for PostGIS:
- Medical (Howard Hughes Medical Institute case study)
- Fictional worlds
- Example (teaching) data sets
For this post I focus on creating a fictional world for use in a novel. Most fictional worlds mimic familiar attributes and properties we know on Earth, such as roads, cities, government boundaries, buildings, rivers, lakes and so on. The difference in a fiction world are all the names, locations, routes and details!
This post is part of the series PostgreSQL: From Idea to Database.
Planning for the world
When starting a new database project, start by planning and documenting your expectations for the data. This is very important.
First up is the choice of units for distance.
I like using one kilometer (km = 1) as the base unit because of the ability
to easily convert between km and meters (m = 0.001).
Making this conversion a mentally-easy task is important because some elements
span large distances (government boundaries, rivers) while others are rather small
(buildings, trees, sidewalks). While QGIS and a GUI will be used for some tasks,
other tasks will be done in SQL code and these scale conversions will be done regularly.
If I were to choose to use miles, being the dominant distance measurement the U.S.,
all the conversions require a calculator in order for my conversions to be accurate.
mile = 1 would mean the small units to choose from are either the foot
(ft = 0.000189) or the yard (yd = 0.000568). Ew.
The other part of planning is to define the data are we collecting and how should it be organized. Lucky for me I have many years of experience working with OpenStreetMap (OSM) data in PostGIS. I'm not keeping things exactly the same, because there is a lot in OSM that we don't need right away. We will pull three basic layers from the list of tables under the New Structure section:
- boundary (polygon)
- road (line)
- building (point)
These layers allow us to define larger regions, such as the town the story's hero lives in, and smaller points of interest, such as the hero's house (point).
Prepare the database
Connect to your PostgreSQL server for the following steps.
The following two SQL commands ensure that the PostGIS extension is installed
and creates a schema (
my_world) to organize your database objects together.
I find using schemas to organize database objects helps keeps things
more manageable over the long term.
CREATE EXTENSION IF NOT EXISTS postgis; CREATE SCHEMA IF NOT EXISTS my_world;
Databases live a notoriously long time. Following best practices helps avoid database Anti-Patterns.
Create table for boundaries
boundary table in the
my_world schema. The
type column will allow us to
distinguish between towns, cities and countries both for querying purposes and formatting
the visualization in QGIS. While I would typically want to enforce constraints (limit the
types, require a
name, etc) for early exploring and testing this is
easier to work with. See my preceding note about following best practices!
CREATE TABLE my_world.boundary ( id BIGSERIAL NOT NULL PRIMARY KEY, type TEXT, name TEXT, geom GEOMETRY (POLYGON, 0) NOT NULL -- Sets SRID to 0, e.g. Undefined );
The spatial magic happens with the
geom GEOMETRY (POLYGON, 0) NOT NULL.
This creates the
geom column with the
PostGIS data type
in this case we are defining a
with SRID 0 (unknown).
To enable better spatial query performance as the data grows, add a GIST index
CREATE INDEX GIX_boundary ON my_world.boundary USING GIST (geom) ;
It is a good habit to comment your objects. It might seem silly in a small example such as this, but once your database reaches any sort of size, every ounce of (succinct) documentation is helpful.
COMMENT ON TABLE my_world.boundary IS 'Example polygons in the fictitious world created in the blog post https://blog.rustprooflabs.com/2019/08/postgis-qgis-create-layers';
I know there are folks that argue against comments like this. It might be a little unnecessary, but my experience has found too many unknown objects just accumulate and cause confusion.
Adding town boundary
With our table created for boundary polygons we can add a town polygon directly
within Postgres/PostGIS. The following query creates a square polygon with each side
1 km long (1 spatial unit in our
SRID = 0 world).
INSERT INTO my_world.boundary (type, name, geom) SELECT 'Town', 'Slonik-ville', ST_MakePolygon(ST_MakeLine(geom)) FROM ( VALUES (ST_MakePoint(0, 0)), (ST_MakePoint(1, 0)), (ST_MakePoint(1, 1)), (ST_MakePoint(0, 1)), (ST_MakePoint(0, 0)) ) AS points (geom) ;
If the above query makes your head spin a little, don't worry, it is a complex query. We will study that query after we check the results to ensure it worked:
SELECT name, ST_AsText(geom) AS geom FROM my_world.boundary; name |geom | ------------|------------------------------| Slonik-ville|POLYGON((0 0,1 0,1 1,0 1,0 0))|
De-construct the query
To understand how the above query works, we start inside the
above creates an in-memory table structure that can be queried like any regular database table.
The list is aliased as
points with the single column of data
AS points (geom)). Each of the
ST_MakePoint() calls generates
a point geometry at the specified coordinates (e.g.
(0, 1)). These points are needed
to construct our polygon, as polygons are created from lines, and lines consist of points.
To illustrate this step at work, we can run the following query to see our pseudo-table.
VALUES (ST_MakePoint(0, 0)), (ST_MakePoint(1, 0)), (ST_MakePoint(1, 1)), (ST_MakePoint(0, 1)), (ST_MakePoint(0, 0)); column1 | ----------| POINT(0 0)| POINT(1 0)| POINT(1 1)| POINT(0 1)| POINT(0 0)|
Note: Running this without the FROM clause took away the alias creation so our column here is simply named
VALUES query is then wrapped inside the
FROM, a simplified look at that
FROM (<value query>) AS points (geom). This makes the
VALUES list available
for our final aggregation steps.
SELECT clause of the outer query handles that task
ST_MakePolygon(ST_MakeLine(geom)). This nests two PostGIS functions,
ST_MakeLine(geom) turns the point geometry rows into a line
ST_MakePolygon() turns into a polygon.
The final part to examine is the
INSERT portion that defines where to save the results
of the query we have built. Because the
id column is controlled by an auto-incrementing
sequence (defined by
BIGSERIAL) we must define the remaining columns we wish to insert into.
INSERT INTO my_world.boundary (type, name, geom)
Load table as QGIS Layer
With our boundary table created and a simple polygon added, it is time to load the data
connection from QGIS
to your PostGIS server and navigate to and expand the
my_world schema in the Browser panel.
Tables can be added to QGIS by simply dragging them down into the Layers.
When prompted by the Coordinate Reference System Selector dialog I select
USER:100000 option so QGIS knows this doesn't relate to Earth.
I feel there should be a better SRID to use in QGIS but I haven't found any issue with this method to date.
The following animation shows the process of loading the data and making this selection. When it loads, you can see that the square polygon we created is really a square!
Edit Town Polygon from QGIS
The simple square is a bit bland for our fictional town Slonik-ville. We can use QGIS to edit the polygon to a more likely shape. Select the boundary layer and click the pencil icon to enable edit mode. The icon to the left of the layer will update with the pencil as an indicator of which layers are currently edit-able.
The QGIS docs covers editing data with QGIS in depth.
The following animation shows what the editing process can look like in QGIS.
Back in Postgres, rerun the previous query to see the edited polygon in its new raw form. Notice we no longer have nice, round, easy numbers! Also, the number of points included in this polygon has grown.
SELECT name, ST_AsText(geom) AS geom FROM my_world.boundary; |name |geom |------------|----------------------------------------------------------------------------- Slonik-ville |POLYGON((0 0,1 0,1.00069444444444 0.594791666666666,0.549826388888889 0.760069444444444,0.876041666666667 1.07413194444444,0.085590277777778 0.823263888888888,0.406423611111111 0.238715277777777,0 0))
With basic data entry and editing covered we will create the other two tables identified
at the beginning. The major difference on these tables is the
road table creates a
LINESTRING and the
building table uses
CREATE TABLE my_world.road ( id BIGSERIAL NOT NULL PRIMARY KEY, surface TEXT, width_m INT DEFAULT 3, name TEXT, geom GEOMETRY (LINESTRING, 0) NOT NULL ); CREATE INDEX GIX_road ON my_world.road USING GIST (geom) ; COMMENT ON TABLE my_world.road IS 'Example lines for the fictitious world created in the blog post https://blog.rustprooflabs.com/2019/08/postgis-qgis-create-layers'; CREATE TABLE my_world.building ( id BIGSERIAL NOT NULL PRIMARY KEY, name TEXT, geom GEOMETRY (POINT, 0) NOT NULL ); CREATE INDEX GIX_building ON my_world.building USING GIST (geom) ; COMMENT ON TABLE my_world.building IS 'Example points for the fictitious world created in the blog post https://blog.rustprooflabs.com/2019/08/postgis-qgis-create-layers';
Add new features via QGIS
Before adding new features via QGIS there is a QGIS Project setting that needs adjusting.
Data Sources options,
ensure "Evaluate default values on provider side" is checked.
Enabling this setting allows PostGIS to retain full control over default values, such as
PRIMARY KEY values (
BIGSERIAL) or other defaults (
width_m INT DEFAULT 3).
Create more data
To add your first road, select the road layer in QGIS Layers, activate editing and use
the "Add Line Feature" option. Left-click to start adding your new feature, each left
click adds a new point. To stop adding points, right-click to open the Feature Attributes
dialog for adding attribute details. The following animation illustrates this process,
and also highlights how QGIS detects the next
PRIMARY KEY value and the
Adding points is straight forward, each left click creates a new point.
Layer Styles (saving them!)
One more great thing about using PostGIS with QGIS is it's extremely simple to save your layer styles directly to the database. This way, your database backups (you do have reliable, tested backups, right?!) will include all the symbology as well as the underlying data! Styling layers (symbology) in QGIS is beyond the scope of this blog post. The QGIS training manual has a chapter on symbology that provides a thorough overview of the process. For this example, I quickly added some simple styling to the three layers to improve colors and add labels.
To save the formatting to PostGIS, open the layer's properties dialog and click the "Styles" button at the lower left. Click "Save Style", change the "save style" option at the top to "in the database (postgres)" and name the style. Checking the "use as default style for this layer" allows QGIS to automatically load this style any time the layer is added to a project. This is very handy, and only available when saving the data and the style both in PostGIS.
This creates a new table in Postgres in the
public schema named
Examining the data stored in Postgres for the boundary table looks like this, note the
XML data for the style itself is not shown for brevity.
SELECT * FROM public.layer_styles WHERE f_table_schema = 'my_world' AND f_table_name = 'boundary' ; Name |Value | -----------------|-----------------------| id |1 | f_table_catalog |your_db | f_table_schema |my_world | f_table_name |boundary | f_geometry_column|geom | stylename |boundary_polygon | styleqml |[XML] | stylesld |[XML] | useasdefault |true | description |Sun Aug 4 08:36:06 2019| owner |your_gis_user | ui | | update_time |2019-08-04 14:36:06 |
When saving QGIS styles to PostGIS, ensure your Postgres instance is up-to-date on minor releases that include the XML bug fix.
I expect to write more posts that expand on this topic in the future. To make this easier
I have taken a logical dump of the database using
It is available to download
pg_dump -d qgis_demo -U your_gis_user \ --no-owner \ -f slonikville-postgis-v1.sql
In this post I have covered the basics of using QGIS and PostGIS together to create and visualize vector data. I have focused on spatial data for a fictitious world, not related to Earth. In future posts I plan to cover more of the advanced features available in PostGIS that make this such an amazing system.
Need help with your PostgreSQL servers or databases? Contact us to start the conversation!
Published August 04, 2019
Last Updated August 04, 2019