Organization of storage of spatial data in PostGIS / PostgeSQL

imageUpon arrival at one office, which deals with the development of maps, charts, and plans, I was very surprised by one thing: there was no centralized repository of all materials. Users worked each with their own best practices. And if there was a need to take something from another project - you had to either run with the "flash drive", or copy files over the network. That created an incredible amount of “garbage” in the form of duplicates of different freshness on many workstations.

After observing all this chaos, I decided to "comb" the whole thing and make centralized storage of cartographic material, with differentiation of access rights to individual projects, and even with monitoring of changes made to projects.

The choice fell on the PostgeSQL DBMS with the PostGIS extension, which allows storing geographic data (object geometry) in the database. One of the determining factors of choice is the ability of software products with which users work to work with the database without additional “crutches”. A plus is the openness of the project and the possibility of multi-user work with the same layer.

I will not talk about the DBMS itself - and so much has already been written up, both good and bad. As I will not talk about setting it up.

I will dwell on the PostGIS extension itself.

Postgiswas released in 2001 by Refractions Research and competes with commercial solutions, while being a free open source software product. The main advantage of PostGIS is the ability to use the SQL language in conjunction with spatial operators and functions. In addition to simple data storage, PostGIS allows you to carry out any kind of operations on them.

Actually, further we will be engaged in the organization of storage of spatial data in the database. For convenience of visualization of structure pgAdmin3 is taken.

The specifics of working with digital cards is such that in order to obtain a full-fledged card, several layers containing various objects are required. For example, a city needs at least two layers: buildings and roads. Suppose that we have several cities, and each contains data independent from each other. For each of the cities we create a separate scheme in the database:

CREATE DATABASE goroda OWNER postgres;
CREATE SCHEMA gorod1 OWNER postgres;
CREATE SCHEMA gorod2 OWNER postgres;


Next, we enter the existing data on them into the created schemes using one of the free loaders: shp2pgsql, OGR2OGR , QuantumGIS SPIT, shp loader for PostGIS and others.
Assign the owner of the tables:

 ALTER TABLE road1 OWNER TO postgres;
 ALTER TABLE building1 OWNER TO postgres;

As a result, we obtain the following form:

In addition to the road1 and building1 tables, two more were formed in the public schema: geometry_columns and spatial_ref_sys. The geometry_columns table stores information about database tables containing spatial information. The spatial_ref_sys table contains numerical identifiers and textual descriptions of coordinate systems used in the spatial database.

With the owner of the table sorted out, now it's up to the users. To begin, create them: We

CREATE USER user1 WITH PASSWORD 'psswd1';
CREATE USER user2 WITH PASSWORD 'psswd2';

expose them the rights to use these tables:

GRANT SELECT ON TABLE road1 TO user1;
GRANT SELECT ON TABLE building1 TO user1;

In this form, the rights are presented only for reading.

The following rights can be set for editing:
UPDATE - the ability to modify existing objects;
INSERT - adding new objects;
DELETE - delete objects.
Actually, everything is like in standard SQL.


Accordingly, combining these privileges, you can set the user the right only to create or modify existing objects, or the ability to fully edit.

Next, we set the permissions on the tables with spatial information:

GRANT SELECT ON geometry_columns TO user1;
GRANT SELECT ON spatial_ref_sys TO user1;

If you specify only SELECT, then the user will only view the layers. If ALL, then it can create its own tables (for example, a layer with bus stops). We give the second user the following privilege:

GRANT ALL ON geometry_columns TO user2;
GRANT ALL ON spatial_ref_sys TO user2;

In order for the user to create entries in the table (edit the map), we allow him to use the table sequence:

GRANT USAGE ON SEQUENCE road1_gid_seq TO user1;
GRANT USAGE ON SEQUENCE building1_gid_seq TO user1;

The same can be done for the second user.
Since the database uses more than one scheme, when assigning rights to the table, we indicate the full path to it: schema.table.

Now we have a database with spatial data and with users who have different access to them: from full to "just look." Which, in fact, was what we needed.

In the following article we will consider audit of tables with spatial objects.

Also popular now: