Shulou(Shulou.com)06/02 Report--

Install PostgreSQL and PostGIS

PostgreSQL and PostGIS are already hot open source projects and have been included in the yum or apt packages of major Linux distributions. Take Ubuntu as an example, you can install the following package:

$sudo apt-get install postgresql-client postgresql postgis-y

For RedHat series, please install:

$sudo yum install postgresql-server postgresql postgis

After the initial installation, a database named postgres and a database user named postgres are generated by default. It is important to note that a Linux system user named postgres is also generated. We should all do this in this newly created postgres user when we operate PostgreSQL in the future.

PostgreSQL configuration

If it is installed from the source code

It is not recommended to install from the source code, I have tried to install from the source code, it is too troublesome, and all kinds of make install are error-prone. Finally, I installed it with rpm. However, since it took some time to study and I have successfully installed it, I'd better record it-- however, there may be errors and omissions, so readers should be prepared to roll back if they want to install from source code.

If you are using source compilation and make install installation, this section requires additional configuration.

It seems that the installation of the CentOS series also requires.

After the default make install, the PostgreSQL installation directory is at: / usr/local/pgsql/

First of all, according to the reference of this link, you need to configure the environment variable

$set $PGDATA = "/ usr/local/pgsql/database"

However, after the pg_ctl start is executed, an error occurs:

Pg_ctl: directory "/ usr/local/pgsql/database" is not a database cluster directory

In that case, you need to refer to the steps in the PostGreSQL official documentation to create a real database:

PostgreSQL: Documentation: 9.1: Creating a Database Cluster

First create a user account called postgres

$usradd postgres$ sudo chown postgres / usr/local/pgsql/database

Then enter this account and create a database

$sudo su postgres$ initdb-D / usr/local/pgsql/database/

At this point shell outputs:

The files belonging to this database system will be owned by user "postgres". This user must also own the server process.The database cluster will be initialized with locale "C" default database encoding has accordingly been set to "SQL_ASCII" default text search configuration will be set to "english" .data page checksums are disabled.fixing permissions on existing directory / usr/local/pgsql/database. Okcreating subdirectories... Okselecting default max_connections... 100selecting default shared_buffers... 128MBselecting dynamic shared memory implementation... Posixcreating configuration files... Okcreating template1 database in / usr/local/pgsql/database/base/1... Okinitializing pg_authid... Okinitializing dependencies... Okcreating system views... Okloading system objects' descriptions... Okcreating collations... Okcreating conversions... Okcreating dictionaries... Oksetting privileges on built-in objects... Okcreating information schema... Okloading PL/pgSQL server-side language... Okvacuuming database template1... Okcopying template1 to template0... Okcopying template1 to postgres... Oksyncing data to disk... OkWARNING: enabling "trust" authentication for local connectionsYou can change this by editing pg_hba.conf or using the option-A, or--auth-local and-- auth-host, the next time you run initdb.Success. You can now start the database server using:pg_ctl-D / usr/local/pgsql/database/-l logfile start

Congratulations, next you can start PostgreSQL:

After pg_ctl-D / usr/local/pgsql/database/-1 / usr/local/pgsql/database/psql.log startPostgreSQL is installed

Enter your postgres account and log in to the PostgreSQL console:

$sudo su postgres$ psql

At this time, it is equivalent to the system user postgres logging in to the database as a database user with the same name, otherwise we have to specify the user in the parameters every time we execute psql, which is easy to forget.

Set the password in psql-- note that the password set here is not the password for the postgres system account, but the user password in the database:

Postgres=#\ password postgres

Then follow the prompts to enter the password.

Install PostGIS from the source code

If you choose to install PostgreSQL from the source code, you first need to determine what version of PostgreSQL you installed

Then, go to the PostGIS page to find out which version of PostGIS it corresponds to.

Finally, download the corresponding source according to the version of PostGIS

The final import is very troublesome, and the author is stuck at this step, so I finally give up the installation from the source code.

Import PostGIS extension

The path varies depending on the version of postgresql and postgis, mainly because the path contains version information:

$sudo su postgres$ createdb template_postgis$ createlang plpgsql template_postgis$ psql-d template_postgis-f / usr/share/postgresql/9.5/contrib/postgis-2.2/postgis.sql$ psql-d template_postgis-f / usr/share/postgresql/9.5/contrib/postgis-2.2/spatial_ref_sys.sql

In the above operation, an empty database called "template_postgis" is created. The database is empty and belongs to the postgres user. Note that do not add data to this database, which is called a "template" because it is used for derivation.

The corresponding PostGIS path may be different, if it fails, try some more near the above path and find some .sql files to try.

Convert .shp file to PostGIS database

Convert .shp to .sql file

First find the file that needs to be converted, and suppose the .shp file that needs to be converted is: / tmp/demo.shp, then do the following:

$sudo su postgres$ cd / tmp$ shp2pgsql-W GBK-s 3857. / demo.shp entry > demo.sql

Here we need to explain what each part of the last sentence represents:

-W GBK: if your .shp file contains Chinese characters, please add this option-s 3857: indicates the reference coordinate system of the file. My .shp file uses the path entry of the EPSG:3857./demo.shp:.shp file: represents the name of the database table to be imported-assuming that the .shp file represents each entry, so I named it "entry" demo.sql

Once you have the .sql file, you can import it directly into the PostgreSQL database.

Create a PostGIS database

You need to use the previous template here.

Sudo su postgrespsqlCREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;newdb: new database name originaldb: that is, the previous template_postgisdbuser: your account name, I usually use postgres

Import .sql file

Sudo su postgrespsql\ c newdb\ I demo.sql\ d

As you can see, the .sql file has been imported.

Set database permissions

OK, now we log in to psql on the local machine (server IP assumes 192.168.1.111) with the following command, and we will find a piece of output:

$psql-h 192.168.1.111-p 5432psql: could not connect to server: Connection refused Is the server running on host "100.94.110.105" and accepting TCP/IP connections on port 5432?

This is because PostgreSQL does not open permissions to the public by default and only listens to loopback addresses. To modify it, you need to find the postgresql.conf file and modify the value listen_addresses:

Listen_addresses ='*'

The above is all about the installation and use of PostgreSQL and PostGIS in Linux. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.