Data migrations, postgresql, postgis extension

 PostgreSQL is a business-class relational database management system, on par with the best commercial database systems such as Oracle, MicrosoftSQL Server and IBM DB2. PostgreSQL is special because it's not just a database, it's also an application platform, and it's impressive at that. PostgreSQL is fast to work with, in Benchmarks PostgreSQL either exceeds or matches the performance of many other databases, both open source and proprietary.

One of the most important advantages of PostgreSQL is that it is open source, which means that you have a license to install, use and distribute PostgreSQL without paying anyone a fee or copyright. Additionally, PostgreSQL is well known as a database that remains operational for long periods of time and requires little or no maintenance.

What surprises many people is that PostgreSQL's feature set is more comparable to Oracle or SQL Server than it is to MySQL. The only connection between MySQL and PostgreSQL is that these two databases are open source, apart from that the features and philosophies are almost completely different.

Who uses PostgreSQL? Famous users include

 Apple, BASF, IMDB.com, SKYPE, Yahoo and many others. Over a million downloads per year according to the data provided. When PostgreSQL was first developed, it was named Postgres, and many aspects of the project still refer to the word 'postgres'. For example, the default database is called postgres, and software is often installed using the postgres username. as a result, people shorten the name PostgreSQL to simply Postgres.

Postgis extension

PostGis is an extension or extension for the spatial database Postgresql open source database management system. It is currently the most powerful open source spatial database system. PostGis adds to Postgresql several spatial data types and over 400 functions to work with those spatial data types.

In the database that we form, we simply create the query create extension postgis, after which a new table will appear in the left browser.

Before we continue, it is necessary to explain the spatial table. The spatial table has a field of a special type called geometry, this is a data type that allows us to store geographic data and it is available only after a spatial extension has been created in our database. There is another type of geographic data called geography, it is mainly used to obtain greater accuracy in measurements over long distances. The problem here is that it uses more complex math and has fewer spatial functions that accept this data type as a parameter.

So, what does the spatial database offer us:

It provides the ability to easily mix spatial data with other corporate data, such as financial information, observation data and marketing information. We can do this with Esri shapefiles, KML files and other GiS file formats.

A database such as Postgresql has features such as a query scheduler that improves the speed of your joins and many commonly used statistical functions for building complex queries.

While many think of PostGis as a Geographic Information System tool, as the name implies, we see it as a tool for spatial analysis. The difference is that while geography focuses on the earth and the reference system that binds the earth, spatial analysis focuses on space and the use of space.

After setting the data in Qgis, we will save the data in SHP format > Export > Save feature As > Save Vector Layer As and select Esri Shapefile.

PostGIS comes bundled with a pair of command-line tools, shp2pgsql and pgsql2shp, for importing and exporting Esri shapefiles. The GUI version is shp2pgsql gui. The gui version imports and exports data.

After saving the data in .shp format, the first thing you do is connect postgis to the postgresql database. Enter the username and password you chose during installation. Localhost is 5432 by default, of course you must enter the name of the database you have in postgresql.

SRID is short for spatial reference identifier and is an integer that refers to the primary key of the spatial_ref_sys table. PostGIS uses this table to catalog all spatial reference systems available to the database. The spatial_ref_sys table contains the name of the spatial reference system (SRS), the parameters needed to reproject from one SRID to another, and the organization that led to the particular SRID. It should be kept in mind that a similar term in GIS jargon is called SRS ID (spatial reference system identifier). This identifier adds the authority created by the SRID.

For example, the common WGS 84 lon/lat has an SRID of 4326 but an SRS ID of EPSG:4326, where EPSG stands for European Petroleum Survey Group (www.epsg.org). Most SRIDs in PostGIS come from EPSG, so the SRID used in the table according to PostGIS convention is the same as the EPSG identifier.

In postgis, double-click SRID to enter the SRID, postgresql will not display the data correctly if this parameter is not set.

Now we have spatial tables, one with data on substations (Figure 30), represented as a PostGis Point type, the other with streets.

After importing the data, it is necessary to refresh the database in order to display the data. An important note is that in the PostGis settings, we only show the incoming SRID, we do not perform any transformation.