jeudi 12 avril 2018

How to create a Spatial DataBase

In this document, we are going to create and load a spatial database.
Our environment is made up of :
Debian Linux server
Postgres DBMS

To start, you have to install PostGis that will add support for Geographic Object in your Postgres.
Go to you command line and enter these commands  as root user
apt-get update
apt-get install postgis

You can verify the installation of postgis with the following commands
dpkg-query -l "postgis"
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name                            Version              Architecture         Description
+++-===============================-====================-====================-====================================================================
ii  postgis                         2.4.4+dfsg-1.pgdg90+ amd64                Geographic objects support for PostgreSQL

Install extension for you DATABASE
(you may need to be root user first : sudo su )
psql -U postgres
=#CREATE DATABASE vn_gis;
=#\c vn_gis
=#CREATE EXTENSION postgis;
This last command will create all the Spatial functions, and Object types in the DataBase
To see the version of postgis install for you database :
( Make =#\c vn_gis
You are now connected to database "vn_gis" as user "postgres".
vn_gis=# select postgis_version();
            postgis_version
---------------------------------------
 2.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)

Download you shap files at : http://download.geofabrik.de/
Convert your shap files ( No just the .shp file) to your .sql file.
For this, you will need at least :
.shp - geometry data
.dbf - attribute data
.shx - geometry index file
If not you my use all the extensions you have ( .prj, .fbn, .fbx, .ain, .aih, .ixs, .mxs, .atx, .shp.xml and .cpg)
The command need for the convertion is as follow :
shp2pgsql -l -s <SRID> file_path.shx <DATABASENAME> new.sql 
NB:You have to run this command being in the folder containing you shap files

A Spatial Reference System Identifier ( SRID) is a unique value used to unambiguously identify projected, unprojected and local spatial coordinate system definitions. These coordinate systems form the heart of all GIS application  
 
You can check for the SRID of you zone at : https://epsg.io/

shp2pgsql -I-s 3405  gis.osm_landuse_a_free_1.shp vn_gis  > shapfile.sql
psql -U postgres  -d vn_gis -f shapfile.sql

You can view you database structure.
Connect to you database  in our case : vn_gis then use the following command:

=# \d
                List of relations
 Schema |       Name        |   Type   |  Owner
--------+-------------------+----------+----------
 public | geography_columns | view     | postgres
 public | geometry_columns  | view     | postgres
 public | raster_columns    | view     | postgres
 public | raster_overviews  | view     | postgres
 public | spatial_ref_sys   | table    | postgres
 public | vn_gis            | table    | postgres
 public | vn_gis_gid_seq    | sequence | postgres


 View data in tables : case spatial_ref_sys
=# select * from spatial_ref_sys ;

Thanks for using