Postgis data type Geometry on the example of imported OpenStreetMap maps

For my project, it was necessary to build pedestrian routes and count their length.
I solved this problem with pgrouting , which in turn relies on postgis .
Postgis is an extension to Postgresql that implements the OpenGis standard.
This extension contains extensive functionality for working with spatial data.
This allows you to write interesting applications.
In particular, OpenStreetMap uses postgis to display its maps.
I will try to tell you about how the imported osm maps look like in postgis.

I will skip the description of how to install postgres and postgis.
I'll start by creating a database for storing spatial data.

Create a database:

create database openstreetmap; 

We initialize postgis:
')

 create extension postgis; 

We’ll have the spatial_ref_sys table in our database, which will immediately contain almost 4 thousand records.
Each record corresponds to a certain spatial coordinate system, which defines the projection of longitude and latitude on the plane.
With this table, postgis can convert data from one projection to another.

Create a table in our spatial data:

 create table my_map( id bigserial primary key, name text, shape geometry ); 

In this table, the shape field has the geometry type.
In postgis, this data type is essential for representing objects in the Euclidean coordinate system.

Add to the newly created table a point corresponding to the center of St. Petersburg:

 insert into my_map(name, shape) values (' ', ST_Point(30.3250575, 59.9174455)); 

The ST_Point function creates an object point of type geometry.
There are also ST_MakeLine, ST_MakeEnvelope, ST_MakePolygon and other useful constructors.

Now you can go to download the osm card.
For this we need the map itself and osm2pgsql .
I will not tell you how to install osm2pgsql.
It depends on the system and on windows seems non-trivial.
After downloading the file russia-european-part-latest.osm.pbf, we start the import:

 osm2pgsql -d openstreetmap -U iakov -C 2000 russia-european-part-latest.osm.pbf 

Only the -C option seems to me unclear. It defines the size in megabytes of the cache used when loading.
By default, it is equal to 800 and was not enough for a specific file, so I increased it to 2000.
Immediately after launch appears:

 NOTICE: table "planet_osm_point" does not exist, skipping 

It's not a mistake. Postgis tries to delete the tables it needs. So the base is not empty yet.

Considerable time is spent processing data:

 Processing: Node(83577k 1816.9k/s) Way(8382k 37.42k/s) Relation(245290 939.81/s) parse time: 531s 

This is handled by osm objects. Node are nodes that correspond to points on the map. Way is a path, ordered list of nodes.
Relation - a relationship in which you can combine other elements (nodes, paths, relationships).
Read more about this on the openstreetmap wiki .

At the end of the work, 4 tables appeared in the database: planet_osm_line, planet_osm_point, planet_osm_polygon, planet_osm_roads.
The planet_osm_point stores the processed nodes, the planet_osm_line stores unclosed paths, the planet_osm_polygon stores closed paths, and the planet_osm_roads stores paths that correspond to the roads.
Each table has a way field of type geometry.
You can see the type of geometry using GeometryType:

 openstreetmap=# select GeometryType(way) from planet_osm_point group by GeometryType(way); geometrytype -------------- POINT (1 row) openstreetmap=# select GeometryType(way) from planet_osm_line group by GeometryType(way); geometrytype -------------- LINESTRING (1 row) openstreetmap=# select GeometryType(way) from planet_osm_polygon group by GeometryType(way); geometrytype -------------- MULTIPOLYGON POLYGON (2 rows) openstreetmap=# select GeometryType(way) from planet_osm_roads group by GeometryType(way); geometrytype -------------- LINESTRING (1 row) 

Four types of POINT, LINESTRING, POLYGON, MULTIPOLYGON are enough to store a card.