Jump toUpdate content
Using the PostGIS extension on a Managed Database for PostgreSQL
PostGIS is an extension to the PostgreSQL database management system that allows to store and manage Geographic Information Systems (GIS) objects in PostgreSQL. It is available on Scaleway Managed Databases. It was developed by the Canadian company Refractions Research Inc. PostGIS was published under the GNU GPL license, which means that the solution is available as open source software, contrary to many other GIS system which are closed source software. Spatial data types refer to shapes such as point, line, and polygon. Spatial databases fully integrate spatial data with an object relational database. PostGIS includes support for GiST-based R-Tree spatial indexes, and functions for analysis and processing of GIS objects.
How close is the nearest metro station from my office? What is the shortest route from my home to the shopping mall? Where should a new hospital be build to be as near as possible to a maximum of the population of a city?
These questions and other like them are asked on every day by people all ofter the world. Answers to these questions were historically provided by specialised GIS (Geographic Information Systems) applications. While this is completely fine for answering a few questions, it is not a solution for our modern lives where massive spatial datasets have to be handled, such as all the routes in Europe in one dataset. This is where modern systems like PostGIS step in.
The following steps assume that you have already created a Database Instance. For more information how to create and setup a managed Database Instance, you may follow the database how to’s.
Connect to your Scaleway Console and enter the Database section.
Choose your Database Instance by clicking on its name.
Click the Databases tab, then on + Create Database.
Enter the name of the new database, in this tutorial we use
Click the Users tab, followed by … and Update Permissions.
Grant the permissions to the newly created
townssurveydatabase to your user.
Connect to your managed Database using the
psql -h <rbd_host> --port <rdb_port> -d townssurvey -U <rdb_user>
Enter the password for your database user when prompted.Tip:
You can find the
rdb_porton the database information page.
Once connected enable the
postgisextension for the database:
CREATE EXTENSION postgis;
Verify that everything went well, by running the following command:
You will see an output like the following example, which indicates that the extension has been correctly installed on the database:
townssurvey=> SELECT PostGIS_version(); postgis_version---------------------------------------3.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1(1 row)
The database is ready, log out of the PostgreSQL shell now:
PostgreSQL is designed to run in very different configurations, from small test or development setups to large databases handling millions rows. GIS database objects are large in comparison to plain text data. Scaleway Managed databases are optimized for performance by default, but it is possible to fine-tune them for some parameters.
Enter the configuration section of your Database Instance in the Scaleway console
Choose your Database Instance from the list, and click it to see the database information page. Then click Advanced Settings.
The advanced settings tab displays. Click Add Advanced Settings to configure the parameters of your PostgreSQL server.Tip:
work_memdefines the amount of memory that internal sorting operations and hash tables can consume in the RAM, set this value to 16MB.
Save the configuration by clicking on the check mark symbol (✔). The configuration of your Database Instance is being updated:
Your Database Instance is configured now, and it is time to load some spatial data.
To get familiar with spatial data, download some demo data and insert them in the database.
You can run the following commands either on a local computer running Ubuntu or on a virtual cloud instance.
Install the required software for the following steps using
apt update && apt install -y wget unzip postgis
Create a directory to store the downloaded data and enter the newly created directory:
mkdir -p /postgis/townssurvey && cd /postgis/townssurvey
Download a dataset. We use a sample provided by MassGIS Data which contains information about the legal boundary for each of the 351 municipalities in Massachusetts.
Unzip the downloaded data and list the unpacked data to view it:
unzip townssurvey_shp.zip && ls
You will notice several
.shpfiles in the folder. These files together are called a ShapeFile, a common digital vector storage format for storing geometric location an associated attribute information for legacy GIS software.
To import the data into the database, it has to be converted to SQL. To achieve this, PostGIS provides a tool called
shp2pgsqlwhich can automatically convert
.shpfiles into readable
.sqlfiles. Convert the file
TOWNSSURVEY_POLY.shpinto a SQL file by running the following command:
shp2pgsql -d ./TOWNSSURVEY_POLY.shp > /postgis/TOWNSSURVEY_POLY.sql
Import the generated SQL file into your database using the
psql -h <rbd_host> --port <rdb_port> -d townssurvey -U <rdb_user> -f /postgis/TOWNSSURVEY_POLY.sql
An output like the following displays on the screen:
INSERT 0 1INSERT 0 1[...]INSERT 0 1INSERT 0 1COMMITANALYZE
The import has been completed successfully once the word
Now as some demo data is available in the database, you can start querying it.
Connect yourself to the
psql -h <rbd_host> --port <rdb_port> -d townssurvey -U <rdb_user>
List the tables available in the database:
The command above will return a list of two tables:
List of relationsSchema | Name | Type | Owner--------+------------------+-------+-----------------public | spatial_ref_sys | table | _rdb_superadminpublic | townssurvey_poly | table | rdb_user(2 rows)
The table to query is
townssurvey_poly. To view a list of all available columns in the table, run the following command:
townssurvey=> \d townssurvey_poly
Which returns a list as follows:
Table "public.townssurvey_poly" Column | Type | Collation | Nullable | Default------------+------------------------+-----------+----------+-----------------------------------------------gid | integer | | not null | nextval('townssurvey_poly_gid_seq'::regclass)town | character varying(21) | | |town_id | integer | | |pop1980 | bigint | | |pop1990 | bigint | | |pop2000 | bigint | | |popch80_90 | integer | | |popch90_00 | bigint | | |type | character varying(2) | | |island | integer | | |coastal_po | character varying(3) | | |fourcolor | integer | | |fips_stco | bigint | | |ccd_mcd | character varying(3) | | |fips_place | character varying(5) | | |fips_mcd | bigint | | |fips_count | integer | | |acres | numeric | | |square_mil | numeric | | |pop2010 | bigint | | |popch00_10 | bigint | | |shape_leng | numeric | | |shape_area | numeric | | |geom | geometry(MultiPolygon) | | |Indexes: "townssurvey_poly_pkey" PRIMARY KEY, btree (gid)
geomcoulum data type contains spatial data of the
As the data is now ready, we can find answers to questions like ”What are the 10 towns that are located the in most northern part of Massachusetts?”
To get the answer to this question, it is possible to query the spatial data in the database. As the town boundaries are not linear, there is more than one value for a latitude. So to get the latitude for each town, the centroid of each town has to be gathered using the
ST_Centroidfunction of PostGIS. The centroids Y value is then extracted using the
ST_Yfunction and this value can be used as latitude.
As several cities have more than one entry in the database, the results are filtered using the
DISTINCT ONfunction from PostgreSQL.
The following query returns a list of the 10 most north towns in Massachusetts:
SELECT town, ST_Y(ST_Centroid(geom)) as latitude FROM ( SELECT DISTINCT ON (town) * FROM townssurvey_poly) tORDER BY latitude DESCLIMIT 10;
The result will look like the following list:
town | latitude--------------+------------------AMESBURY | 953947.884028235SALISBURY | 952906.606868324MERRIMAC | 952462.995879863NEWBURYPORT | 950674.541316222WEST NEWBURY | 949284.811099743NEWBURY | 945386.010693761HAVERHILL | 944694.91004576GROVELAND | 944664.684131484CLARKSBURG | 943028.430506038MONROE | 942395.280914487(10 rows)
As you have your answer to your question now, quit the database:
You have enabled the PostGIS extion for PostgreSQL, imported sample data and did your first queries now. For more information about PostGIS, refer to the official documentation.