Debian 10 GIS Server: Unterschied zwischen den Versionen

Aus Geoportal
 
(10 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt)
Zeile 17: Zeile 17:
 
apt-get clean
  
apt-get clean
 
</pre>
  
</pre>
  +
== Activate mapserver ==
  +
<pre>a2enmod cgi
  +
systemctl restart apache2</pre>
 
== Adopt epsg file to support beta2007 transformation ==
  
== Adopt epsg file to support beta2007 transformation ==
 
<pre>
  
<pre>
 
cd /usr/share/proj
  
cd /usr/share/proj
 
cp epsg epsg_old
  
cp epsg epsg_old
  +
sed -i "s/<31466> +proj=tmerc +lat_0=0 +lon_0=6 +k=1 +x_0=2500000 +y_0=0 +datum=potsdam +units=m +no_defs <>/<31466> +proj=tmerc +lat_0=0 +lon_0=6 +k=1 +x_0=2500000 +y_0=0 +datum=potsdam +ellps=bessel +nadgrids=@BETA2007.gsb,null +units=m +no_defs <>/g" epsg
  +
sed -i "s/<31467> +proj=tmerc +lat_0=0 +lon_0=9 +k=1 +x_0=3500000 +y_0=0 +datum=potsdam +units=m +no_defs <>/<31467> +proj=tmerc +lat_0=0 +lon_0=9 +k=1 +x_0=3500000 +y_0=0 +datum=potsdam +ellps=bessel +nadgrids=@BETA2007.gsb,null +units=m +no_defs <>/g" epsg
  +
sed -i "s/<31468> +proj=tmerc +lat_0=0 +lon_0=12 +k=1 +x_0=4500000 +y_0=0 +datum=potsdam +units=m +no_defs <>/<31468> +proj=tmerc +lat_0=0 +lon_0=12 +k=1 +x_0=4500000 +y_0=0 +datum=potsdam +ellps=bessel +nadgrids=@BETA2007.gsb,null +units=m +no_defs <>/g" epsg
  +
sed -i "s/<31469> +proj=tmerc +lat_0=0 +lon_0=15 +k=1 +x_0=5500000 +y_0=0 +datum=potsdam +units=m +no_defs <>/<31469> +proj=tmerc +lat_0=0 +lon_0=15 +k=1 +x_0=5500000 +y_0=0 +datum=potsdam +ellps=bessel +nadgrids=@BETA2007.gsb,null +units=m +no_defs <>/g" epsg
 
</pre>
  
</pre>
  +
 
== Create postgres cluster and start it ==
  
== Create postgres cluster and start it ==
 
<pre>
  
<pre>
Zeile 31: Zeile 39:
 
The port of the first cluster will be 5433, it will increment with the number of clusters.
  
The port of the first cluster will be 5433, it will increment with the number of clusters.
 
== Edit conf files ==
  
== Edit conf files ==
−
/etc/postgresql/11/{clustername}/postgresql.conf
 +
<pre>vi /etc/postgresql/11/{clustername}/postgresql.conf</pre>
−
#Uncomment listen_addresses ...
 +
* Uncomment listen_addresses ...
−
#excahnge localhost with *
 +
* exchange localhost with *
  +
== Restart cluster ==
  +
<pre>pg_ctlcluster 11 {clustername} restart</pre>
  +
== Install first db on cluster ==
  +
<pre>
  +
su postgres
  +
createuser -S -D -R -P -p 5433 {dbusername}
  +
createdb -p 5433 -O {dbusername} db1 -E utf-8
  +
psql -p 5433 -d db1 -c 'CREATE EXTENSION postgis'
  +
psql -p 5433 -d db1 -c 'CREATE EXTENSION postgis_topology'
  +
psql -p 5433 -d db1 -f /usr/share/postgresql/11/contrib/postgis-2.5/legacy.sql
  +
psql -p 5433 -d db1 -c 'CREATE SCHEMA {schemaname}'
  +
psql -p 5433 -d db1 -c 'ALTER DATABASE db1 SET search_path TO {schemaname},public'
  +
</pre>
  +
== Update PostGIS to use beta2007 transformation ==
  +
<pre>
  +
psql -p 5433 -d db1 -c "update spatial_ref_sys set proj4text = '+proj=tmerc +lat_0=0 +lon_0=6 +k=1 +x_0=2500000 +y_0=0 +datum=potsdam +units=m +no_defs' where srid = 31466"
  +
psql -p 5433 -d db1 -c "update spatial_ref_sys set proj4text = '+proj=tmerc +lat_0=0 +lon_0=9 +k=1 +x_0=3500000 +y_0=0 +datum=potsdam +units=m +no_defs' where srid = 31467"
  +
psql -p 5433 -d db1 -c "update spatial_ref_sys set proj4text = '+proj=tmerc +lat_0=0 +lon_0=12 +k=1 +x_0=4500000 +y_0=0 +datum=potsdam +units=m +no_defs' where srid = 31468"
  +
psql -p 5433 -d db1 -c "update spatial_ref_sys set proj4text = '+proj=tmerc +lat_0=0 +lon_0=15 +k=1 +x_0=5500000 +y_0=0 +datum=potsdam +units=m +no_defs' where srid = 31469"
  +
</pre>
  +
== Allow access from remote ==
  +
<pre>
  +
su
  +
echo "#add for postgis user" >> /etc/postgresql/11/{clustername}/pg_hba.conf
  +
echo "local db1 postgres trust" >> /etc/postgresql/11/{clustername}/pg_hba.conf
  +
echo "local db1 {dbusername} md5" >> /etc/postgresql/11/{clustername}/pg_hba.conf
  +
pg_ctlcluster 11 {clustername} restart
  +
</pre>
  +
== Grant all rights to special dbuser ==
  +
<pre>
  +
su postgres
  +
psql -p 5433 -d db1 -c 'GRANT SELECT, INSERT, USAGE, UPDATE , DELETE ON ALL TABLES IN SCHEMA {schemaname} TO {dbusername};'
  +
psql -p 5433 -d db1 -c 'GRANT SELECT, INSERT, UPDATE , DELETE ON ALL TABLES IN SCHEMA {schemaname} TO {dbusername};'
  +
psql -p 5433 -d db1 -c 'GRANT USAGE , EXECUTE ON ALL FUNCTIONS IN SCHEMA {schemaname} TO {dbusername};'
  +
psql -p 5433 -d db1 -c 'GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA {schemaname} TO {dbusername};'
  +
psql -p 5433 -d db1 -c 'GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO {dbusername};'
  +
psql -p 5433 -d db1 -c 'GRANT USAGE ON SCHEMA public TO {dbusername};'
  +
psql -p 5433 -d db1 -c 'GRANT USAGE ON SCHEMA {schemaname} TO {dbusername};'
  +
psql -p 5433 -d db1 -c 'GRANT ALL ON SCHEMA {schemaname} TO {dbusername};'
  +
psql -p 5433 -d db1 -c 'GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA {schemaname} TO {dbusername};'
  +
</pre>

Aktuelle Version vom 24. Juli 2020, 10:28 Uhr

Installation

Howto turn a Debian 10 box (netinstall) into a OWS Server - with apache2 & PostgreSQL

Update System

apt-get update
apt-get -y upgrade
apt-get clean

Install Admin Tools

apt-get -y install curl dos2unix htop mlocate p7zip unzip vim mc
apt-get clean

Install further helpful things

apt-get -y install apache2 libapache2-mod-php cgi-mapserver postgresql-11-postgis-2.5 gdal-bin postgresql-11-ogr-fdw php-xml libproj13
apt-get clean

Activate mapserver

a2enmod cgi
systemctl restart apache2

Adopt epsg file to support beta2007 transformation

cd /usr/share/proj
cp epsg epsg_old
sed -i "s/<31466> +proj=tmerc +lat_0=0 +lon_0=6 +k=1 +x_0=2500000 +y_0=0 +datum=potsdam +units=m +no_defs  <>/<31466> +proj=tmerc +lat_0=0 +lon_0=6 +k=1 +x_0=2500000 +y_0=0 +datum=potsdam +ellps=bessel +nadgrids=@BETA2007.gsb,null +units=m +no_defs  <>/g" epsg
sed -i "s/<31467> +proj=tmerc +lat_0=0 +lon_0=9 +k=1 +x_0=3500000 +y_0=0 +datum=potsdam +units=m +no_defs  <>/<31467> +proj=tmerc +lat_0=0 +lon_0=9 +k=1 +x_0=3500000 +y_0=0 +datum=potsdam +ellps=bessel +nadgrids=@BETA2007.gsb,null +units=m +no_defs  <>/g" epsg
sed -i "s/<31468> +proj=tmerc +lat_0=0 +lon_0=12 +k=1 +x_0=4500000 +y_0=0 +datum=potsdam +units=m +no_defs  <>/<31468> +proj=tmerc +lat_0=0 +lon_0=12 +k=1 +x_0=4500000 +y_0=0 +datum=potsdam +ellps=bessel +nadgrids=@BETA2007.gsb,null +units=m +no_defs  <>/g" epsg
sed -i "s/<31469> +proj=tmerc +lat_0=0 +lon_0=15 +k=1 +x_0=5500000 +y_0=0 +datum=potsdam +units=m +no_defs  <>/<31469> +proj=tmerc +lat_0=0 +lon_0=15 +k=1 +x_0=5500000 +y_0=0 +datum=potsdam +ellps=bessel +nadgrids=@BETA2007.gsb,null +units=m +no_defs  <>/g" epsg

Create postgres cluster and start it

pg_createcluster 11 {clustername}
pg_ctlcluster 11 {clustername} start

Path for conf files: /etc/postgresql/11/{clustername}

The port of the first cluster will be 5433, it will increment with the number of clusters.

Edit conf files

vi /etc/postgresql/11/{clustername}/postgresql.conf
  • Uncomment listen_addresses ...
  • exchange localhost with *

Restart cluster

pg_ctlcluster 11 {clustername} restart

Install first db on cluster

su postgres
createuser -S -D -R -P -p 5433 {dbusername}
createdb -p 5433 -O {dbusername} db1 -E utf-8
psql -p 5433 -d db1 -c 'CREATE EXTENSION postgis'
psql -p 5433 -d db1 -c 'CREATE EXTENSION postgis_topology'
psql -p 5433 -d db1 -f /usr/share/postgresql/11/contrib/postgis-2.5/legacy.sql
psql -p 5433 -d db1 -c 'CREATE SCHEMA {schemaname}'
psql -p 5433 -d db1 -c 'ALTER DATABASE db1 SET search_path TO {schemaname},public'

Update PostGIS to use beta2007 transformation

psql -p 5433 -d db1 -c "update spatial_ref_sys set proj4text = '+proj=tmerc +lat_0=0 +lon_0=6 +k=1 +x_0=2500000 +y_0=0 +datum=potsdam +units=m +no_defs' where srid = 31466"
psql -p 5433 -d db1 -c "update spatial_ref_sys set proj4text = '+proj=tmerc +lat_0=0 +lon_0=9 +k=1 +x_0=3500000 +y_0=0 +datum=potsdam +units=m +no_defs' where srid = 31467"
psql -p 5433 -d db1 -c "update spatial_ref_sys set proj4text = '+proj=tmerc +lat_0=0 +lon_0=12 +k=1 +x_0=4500000 +y_0=0 +datum=potsdam +units=m +no_defs' where srid = 31468"
psql -p 5433 -d db1 -c "update spatial_ref_sys set proj4text = '+proj=tmerc +lat_0=0 +lon_0=15 +k=1 +x_0=5500000 +y_0=0 +datum=potsdam +units=m +no_defs' where srid = 31469"

Allow access from remote

su
echo "#add for postgis user" >> /etc/postgresql/11/{clustername}/pg_hba.conf
echo "local   db1       postgres                              trust" >> /etc/postgresql/11/{clustername}/pg_hba.conf
echo "local   db1       {dbusername}                              md5" >> /etc/postgresql/11/{clustername}/pg_hba.conf
pg_ctlcluster 11 {clustername} restart

Grant all rights to special dbuser

su postgres
psql -p 5433 -d db1 -c 'GRANT SELECT, INSERT, USAGE, UPDATE , DELETE ON ALL TABLES IN SCHEMA {schemaname} TO {dbusername};'
psql -p 5433 -d db1 -c 'GRANT SELECT, INSERT,  UPDATE , DELETE ON ALL TABLES IN SCHEMA {schemaname} TO {dbusername};'
psql -p 5433 -d db1 -c 'GRANT USAGE , EXECUTE ON ALL FUNCTIONS IN SCHEMA {schemaname} TO {dbusername};'
psql -p 5433 -d db1 -c 'GRANT  EXECUTE ON ALL FUNCTIONS IN SCHEMA {schemaname} TO {dbusername};'
psql -p 5433 -d db1 -c 'GRANT  EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO {dbusername};'
psql -p 5433 -d db1 -c 'GRANT  USAGE ON SCHEMA public TO {dbusername};'
psql -p 5433 -d db1 -c 'GRANT  USAGE ON SCHEMA {schemaname} TO {dbusername};'
psql -p 5433 -d db1 -c 'GRANT ALL  ON SCHEMA {schemaname} TO {dbusername};'
psql -p 5433 -d db1 -c 'GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA {schemaname} TO {dbusername};'
Abgerufen von „https://www.geoportal.rlp.de/mediawiki/index.php?title=Debian_10_GIS_Server&oldid=2750“