database

[:it]Importare un file GDB in un database postGIS[:en]Import file GDB to postGIS[:]

[:it]Questa è la sintassi che ho utilizzato per importare un geodatabase ESRI (formato GDB) in un geodatabase Postgis 2.0:

ogr2ogr -f "PostgreSQL" PG:"host=hostname user=username dbname=mydb password=mypwd" mygeodb.gdb/ -a_srs EPSG:3003

Fonte: gdal.org[:en]This is the syntax I used to import an ESRI geodatabase (GDB format) into an existing postGIS database:

ogr2ogr -f "PostgreSQL" PG:"host=hostname user=username dbname=mydb password=mypwd" mygeodb.gdb/ -a_srs EPSG:3003 - overwtrite

Font: gdal.org[:]

PSQL command linePSQL da riga di comando

Below some commands line I used to manage spatial data:

Import shape to postgis

shp2pgsql -s epsg_code -c -g geometry_column -I -t 2D -W LATIN1 shapename schemaname.tablename | psql -h hostname -d mydb -U username

Di seguito alcuni comandi psql che uso per gestire i dati geografici:

Import shape to postgis

shp2pgsql -s epsg_code -c -g geometry_column -I -t 2D -W LATIN1 shapename schemaname.tablename | psql -h hostname -d mydb -U username

Installing postgis 2.x as extension on Ubuntu 12.04 LTSInstallare postgis 2.x come estensione su Ubuntu 12.04 LTS

This is the procedure that I follow to implement postgis (vers.2.0) as extension:

  1. Installation of the  postgis 2.x packages  from the Ubuntu official repository;
  2. creation of a new fresh postgreSQL database “mydb” without any postgis function;
  3. creation  of the extension with pgAdminIII:
CREATE EXTENSION "postgis";
CREATE EXTENSION "postgis_topology";
CREATE SCHEMA postgis;
ALTER DATABASE mydb SET search_path="$user", public, postgis,topology;
GRANT ALL ON SCHEMA postgis TO public;
ALTER EXTENSION postgis SET SCHEMA postgis;

Questa è la procedura che ho seguito per implementare postgis (vers.2.0) come estensione:

  1. installazione del pacchetto postgis 2.x dal repository uffciale di Ubuntu;
  2. creazione di un nuovo database postgreSQL “mydb” senza installare nessuna funzione postgis;
  3. creazione dell’estensione attraverso i seguenti comandi eseguiti con pgadminIII:
CREATE EXTENSION "postgis";
CREATE EXTENSION "postgis_topology";
CREATE SCHEMA postgis;
ALTER DATABASE mydb SET search_path="$user", public, postgis,topology;
GRANT ALL ON SCHEMA postgis TO public;
ALTER EXTENSION postgis SET SCHEMA postgis;

Installation and configuration of a geodatabase Installazione e configurazione di un database geografico

Procedure:

  1. repository and software installation:
sudo add-apt-repository ppa:ubuntugis/ubuntugis-unstable
sudo apt-get update
sudo apt-get install -y postgresql-9.0 postgresql-server-dev-9.0 postgresql-contrib-9.0 projlibgeos-3.2.2 libgeos-c1 libgeos-dev libgdal1-1.8.0 libgdal1-dev build-essential libxml2 libxml2-dev checkinstall
wget http://postgis.refractions.net/download/postgis-1.5.3.tar.gztar zxvf postgis-1.5.3.tar.gz && cd postgis-1.5.3/
sudo ./configure && sudo make && sudo checkinstall --pkgname postgis-1.5.3 --pkgversion 1.5.3-src --default

2. creation of the geodatabase template

sudo su postgrescreatedb --template template0 template_giscreatelang plpgsql template_gis
psql -U postgres -d template_gis -f /usr/share/postgresql/9.0/contrib/postgis-1.5/postgis.sql
psql -U postgres -d postgis -f /usr/share/postgresql/9.0/contrib/postgis-1.5/spatial_ref_sys.sql
psql -U postgres -d postgis -c"select postgis_lib_version();"

4. Creation of the geodatabase and optionally his admin user:

CREATE USER dbuser PASSWORD 'secret';
CREATE DATABASE dbname OWNER dbuser TEMPLATE template_gis
\connect dbname
GRANT ALL ON geometry_columns TO dbuser;
GRANT SELECT ON spatial_ref_sys TO dbuser;

SO: Ubuntu 12.04 LTS 64 bit
Fonts: (Nicolo Rigacci, Simon Tokumine)

1. Installazione software e repository:

sudo add-apt-repository ppa:ubuntugis/ubuntugis-unstable
sudo apt-get update
sudo apt-get install -y postgresql-9.0 postgresql-server-dev-9.0 postgresql-contrib-9.0 projlibgeos-3.2.2 libgeos-c1 libgeos-dev libgdal1-1.8.0 libgdal1-dev build-essential libxml2 libxml2-dev checkinstall
wget http://postgis.refractions.net/download/postgis-1.5.3.tar.gztar zxvf postgis-1.5.3.tar.gz && cd postgis-1.5.3/
sudo ./configure && sudo make && sudo checkinstall --pkgname postgis-1.5.3 --pkgversion 1.5.3-src --default

2. creazione template Database geografico

sudo su postgrescreatedb --template template0 template_giscreatelang plpgsql template_gis
psql -U postgres -d template_gis -f /usr/share/postgresql/9.0/contrib/postgis-1.5/postgis.sql
psql -U postgres -d postgis -f /usr/share/postgresql/9.0/contrib/postgis-1.5/spatial_ref_sys.sql
psql -U postgres -d postgis -c"select postgis_lib_version();"

3. creazione_di_un_database_geografico

A partire dal template creato si crea un database geografico. Per semplicità di amministrazione si crea un utente che sarà proprietario del database:

CREATE USER dbuser PASSWORD 'secret';
CREATE DATABASE dbname OWNER dbuser TEMPLATE template_gis
\connect dbname
GRANT ALL ON geometry_columns TO dbuser;
GRANT SELECT ON spatial_ref_sys TO dbuser;

SO: Ubuntu 12.04 LTS 64 bit
Fonti: (Nicolo Rigacci, Simon Tokumine)