GIS on CentOS 7 's PostgreSQL & PostGIS

PostgreSQL & PostGIS Installation PostgreSQL

After the Yum source is configured, the version with discoverableyum info postgresqlPostgreSQL is 9.2.23, if you want to install the latest version, please refer to the following operation

https://www.postgresql.org/download/linux/redhat/

Http://docs.nextgis.com/docs_ngweb/source/install-centos7.html

http://www.postgresonline.com/journal/archives/362-an-almost-idiots-guide-to-install-postgresql-9.5,- Postgis-2.2-and-pgrouting-2.1.0-with-yum.html

Sudo yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-3.noarch.rpm

# View the packages available in the above repository
Yum list | grep postgresql95
# Install PostgreSQL client server
Sudo yum install postgresql95 postgresql95-server postgresql95-libs postgresql95-contrib postgresql95-devel
# View help
Psql --help
Rpm -qa | grep postgresql* # View installed software
# Initialize the database and set it up with the system
Sudo /usr/pgsql-9.5/bin/postgresql95-setup initdb
Sudo systemctl start postgresql-9.5.service # service postgresql-9.5 start
Sudo systemctl enable postgresql-9.5.service

#Edit verification parameters (use the nano editor here, you can use vim)
# psql After entering the postgres database, use show hba_file; to view the file location
Sudo nano /var/lib/pgsql/9.5/data/pg_hba.conf
Sudo vim /var/lib/pgsql/9.5/data/pg_hba.conf

Modify ident to MD5

PostgreSQL ident and peer authentication PostgreSQL authentication method based on operating system user

If it is peer, there may be an error with peer authentication failure

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

# Further modify postgresql.conf to listen to the target address
Su postgres # switch to postgres or sudo su postgres
Psql -U postgres #login the database, there is no password by default
ALTER USER postgres WITH PASSWORD ‘password ‘; #change password sudo passwd postgres
# or use the \password command
\password postgres
Select * from pg_shadow; # view database information
\q #退
Psql -V
Sudo systemctl restart postgresql-9.5.service # Restart the service, or service postgresql-9.5 restart

# Create a new psql database user (ngw_admin) using postgres user
# \du command to view user information
# -P means password, and -e means display command. Grant superuser privileges with -s or --superuser
Sudo -u postgres createuser ngw_admin -P -e
# New database (db_ngw), owner ngw_admin
Sudo -u postgres createdb -O ngw_admin --encoding=UTF8 db_ngw

postgresql-client	libraries and client binaries
Postgresql-server	Core Database Server
Postgresql-contrib	Additional supplied modules
Postgresql-devel	Libraries and headers for C language development
Pgadmin4	Pgadmin 4 Graphical Administration utility

# # Install PostGIS

https://postgis.net/install/

The postgis2_95-client contains the PostGIS commandline tools Shp2gpsql, PGSQL2SHP, raster2pgsql that is useful for Loadi ng or exporting spatial data.

Sudo yum install epel-release # If no epel source is configured
Sudo yum install postgis2_95 postgis2_95-client #install
# When pgRouting is needed yum install pgrouting_95
# Login to the corresponding database
Psql -U ngw_admin -d db_ngw
#extend the postgis function for an existing database (db_ngw)
CREATE EXTENSION postgis;
SELECT PostGIS_Full_Version(); # Test if the database contains the functionality of postgis

or execute it directly in the shell.

sudo psql -u postgres -d db_ngw -c ‘CREATE EXTENSION postgis;‘
sudo psql -u postgres -d db_ngw -c ‘ALTER TABLE geometry_columns OWNER TO ngw_admin;‘
sudo psql -u postgres -d db_ngw -c ‘ALTER TABLE spatial_ref_sys OWNER TO ngw_admin;‘
sudo psql -u postgres -d db_ngw -c ‘ALTER TABLE geography_columns OWNER TO ngw_admin;‘
psql -h localhost -d db_ngw -U ngw_admin -c "SELECT PostGIS_Full_Version();"

Create the PostGIS database directly using the template method and specify the owner

#Login database
# postgis_21_sample is a version 2.1 postgis database
Create database geodataont template postgis_21_sample owner gdo;
# or
Createdb -O gdo -U postgres -T postgis_22_sample geodataont

Enabling SQL for the PostGIS feature

Don't INSTALL it in the database calledpostgres.

# After connecting to the database, execute the following sql command to enable the corresponding function.
# Enable PostGIS (includes raster)
CREATE EXTENSION postgis;
# Enable Topology
CREATE EXTENSION postgis_topology;
# Enable PostGIS Advanced 3D
# and other geoprocessing algorithms
# sfcgal not available with all distributions
CREATE EXTENSION postgis_sfcgal;
# fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
# rule based standardizer
CREATE EXTENSION address_standardizer;
# example rule data set
CREATE EXTENSION address_standardizer_data_us;
# Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;

CREATE EXTENSION pgrouting;
SELECT * FROM pgr_version();
# yum install ogr_fdw95
CREATE EXTENSION ogr_fdw; 

CentOS 7 Source Installation PostGIS

GIS on CentOS 7 's PostgreSQL & PostGIS