Spatial data storage in PostGIS / PostgeSQL

When I came to the same office, which develops 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 work. And if there was a need to take something from another project, you had to either run with a “flash drive” or copy files over the network. What 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 the storage of cartographic material centralized, with delineation of access rights to individual projects, and also monitoring changes made to the projects.

The choice fell on PostgeSQL DBMS with the PostGIS extension, which allows geographic data (geometry of objects) to be stored in the database. One of the determining factors of choice is the possibility of software products with which users work to work with the database without additional crutches. Plus you can note the openness of the project and the possibility of multi-user work with the same layer.

I will not talk about the DBMS itself - it’s already written a lot, both good and bad. Nor will I talk about setting it up.
')
I’ll dwell on the very extension of PostGIS.

PostGIS was released in 2001 by Refractions Research and competes with commercial solutions, 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 perform any kind of operations on them.

Actually, further we will be engaged in the organization of storage of spatial data in a DB. For the convenience of visualizing the structure taken pgAdmin3.

The specificity of working with digital maps is such that in order to obtain a full-fledged map, several layers are needed containing various objects. For example, a city needs at least two layers: buildings and roads. Assume that we have several cities, and each contains data independent of 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 already existing data on them into the created charts using one of the free downloaders for this: shp2pgsql, OGR2OGR , QuantumGIS SPIT, shp for PostGIS and others.
Assign the table owner:

 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 numeric identifiers and textual descriptions of the coordinate systems used in the spatial database.

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

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

We set them the right to use these tables:

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

In this form, permissions are read only.

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


Accordingly, by combining these privileges, you can set the user the right only to create or modify already existing objects, or the opportunity for full editing.

Next, set permissions on tables with spatial information:

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

If you specify only SELECT, the user will only view the layers. If ALL - 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 records in the table (edit the map), we allow him to use the sequence of the table:

 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 more than one scheme is used in the database, then when assigning rights to the table, we specify 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”. What, in fact, we needed.

In the next article, we will look at auditing tables with spatial objects.