Organization of storage of spatial data in PostGIS/PostgeSQL

On arrival in one office that develops maps, charts and plans, I was very surprised by one thing: there was no centralized storage of all materials. Users work with their experiences. And if there is a need to take something from another project – it was necessary or to run with the "plachecki", 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 the whole thing to "brush" and make centralized storage of cartographic material, with differentiation of access rights to individual projects, and even monitoring changes made to the draft.

The choice fell on the DBMS PostgeSQL with PostGIS extension, allowing to store in database of geographic data (feature geometry). One of the defining factors of a choice – the ability of software products, with users, to access the database without additional "crutches". Plus you can note the openness of the project and the multiuser work with the same layer.

Most of the DBMS I will not tell – and so much already has been written, both good and bad. Like not going to talk about how to configure it.

Stop point on the extension PostGIS.

PostGIS was released in 2001 by Refractions Research company and compete with commercial decisions, while being free software with open source. The main advantage of PostGIS is the possibility to use SQL in conjunction with spatial operators and functions. In addition to just storing the data, PostGIS allows you to perform any kind of operations over them.

In fact, let us further the organization of storage of spatial data in the database. For ease of visualization of the structure taken pgAdmin3.

The specificity of work with digital maps is that for a full map requires several layers containing different objects. For example, the city need at least two layers: buildings and roads. Let's say that cities have several, and each contains independent from each other data. For each of the cities create a separate schema in the database:

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

Further recorded in the generated schema, existing data, using one of the free downloaders: shp2pgsql, OGR2OGR, QuantumGIS SPIT, shp loader for PostGIS and others.
Appointed by the owner of the tables:

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

In the end, we obtain the following:

Besides building1 road1 and tables in the public schema formed two more: geometry_columns and spatial_ref_sys. The geometry_columns table stores information about database tables that contain spatial information. The spatial_ref_sys table contains the numeric IDs and textual descriptions of coordinate systems used in the spatial database.

With the owner of the tables figured out, now it's up to the users. Let's create them:

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

Put them right to use these tables:

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

In this view rights are only for reading.

For editing you can set the following rights:
UPDATE – ability to modify existing objects;
INSERT – adds a new object;
DELETE – delete objects.
In fact, all in standard SQL.


Accordingly, by combining these benefits, you can set the user may only create or modify existing objects, or the ability for full editing.
Further put right on the table with spatial information:

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

If you specify only a SELECT statement, the user will only view the layers. If ALL — it can create its tables (for example, a layer with bus stops). Give the second user the privilege

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

In order to allow the user to create records in the table (edit map) allowed him the use of sequence tables:

 
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 is used for more than one scheme, then the rights assignment table specified to it the full path: schema.table.

Now we have a database with spatial data and with users who have different access to them: from full to "just look". What exactly we needed.

The following article will look at the audit tables with spatial objects.

Article based on information from habrahabr.ru