PHP PostGIS connection – Codeigniter and GIS Database

PHP PostGIS connection.  Let see first, what is PHP (Code igniter), PostGIS and why we are connecting them? PHP (Hypertext Preprocessor) is a server scripting language, and a powerful tool for making dynamic and interactive Web pages where as PostGIS is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL. We are connecting both of them to query spatial data stored in PostGIS and send them server.

Before looking into this post you may try to look for installing postgis and postgresql in your system or server.

PHP PostGIS connection – Codeigniter and GIS Database

There are many exciting things that you can do, for example you can use pgrouting and see the shortest path. Codeigniter is an Application Development framework. It is well designed structure to write code in PHP.

PHP codeigniter Desciption – connection with postgis

There are three main component, which separates the logical and presentation.

Part – 1. Model – This component connects you with your database. Here you can insert, update or delete data from your connected database.

Part – 2. View – This component is mainly about presentation part. This is PHP file which contains information, which you need to show to users.

Part – 3. Controller – This one is main component of the codeigniter as model and view or any other resources are loaded here.

This helps to process the HTTP request and generates the web pages.

Step for PHP PostGIS connection – Codeigniter and GIS Database

There are few steps for doing this-

1. Very first step is to download the code igniter, PostGIS and have knowledge about PHP.

2. Download  codeigniter framework or which every framework you use. Then unzip the downloaded file. If you are using Ubuntu system then place it inside /var/www/html folder.

3. You need to do some settings such as set base url by navigating your self to application/config/config.php path. (for Codeigntier php people)

4. As in controller the index function loads the welcome page so by entering base URL in browser you will see the welcome page.

5. Installation of PostGIS in described in last article please follow that.

6. Now as we want Postgres to connect with code igniter So we need to open application/config/database.php file.

Here in the end of page you will see the

$db['default'] =  array( 'dsn' => '', 
 'hostname' => 'localhost', 
 'username' => 'root', 
 'password' => '', 
 'database' => 'database_name', 
 'dbdriver' => 'mysqli', 
 'dbprefix' => '', 
 'pconnect' => TRUE, 
 'db_debug' => TRUE, 
 'cache_on' => FALSE, 
 'cachedir' => '', 
 'char_set' => 'utf8', 
 'dbcollat' => 'utf8_general_ci', 
 'swap_pre' => '', 
 'encrypt' => FALSE, 
 'compress' => FALSE, 
 'stricton' => FALSE, 
 'failover' => array() 
 );

Here Mysqli driver is connected. Now we will change the Username by Postgres. If you have change the username of Postgres replace ‘root’ by that name. If this this password protected then provide the password in third row. Change the database name with one available in postgres. Now write postgre in place of mysqli. Let rest all fields as it is. In the last you can add port number for postgres by typing this ‘port’ => 5432.

So this will look like:

$db['default'] = array( 
 'dsn' => '', 
 'hostname' => 'localhost', 
 'username' => 'Postgres', 
 'password' => 'password', 
 'database' => 'postgres', 
 'dbdriver' => 'postgre', 
 'dbprefix' => '', 
 'pconnect' => FALSE, 
 'db_debug' => (ENVIRONMENT !== 'production'), 
 'cache_on' => FALSE, 
 'cachedir' => '', 
 'char_set' => 'utf8', 
 'dbcollat' => 'utf8_general_ci', 
 'swap_pre' => '', 
 'encrypt' => FALSE, 
 'compress' => FALSE, 
 'stricton' => FALSE, 
 'failover' => array(), 
 'save_queries' => TRUE, 
 'port'=> 5432 
 );

Now to check that your database is successfully connected with codeigniter you can run query to insert data in available table.

As after connection with PostGIS i.e with GIS extension enabled, you may look forward to insert shapefile data into postgis or import osm data into postgis.

For example- First of all create a view which shows that we are passing username and password, Which will get store in database table. Go to the application/view and create php file and write the given code. For this can use any test editor.

    <h2>Login Details</h2>

<form action="<?php echo base_url()?>index.php/User_Authentication/checkUser" method="post">
<label>UserName :</label>
<input type="text" name="username" id="username" placeholder="username"/><br /><br />
<label>Password :</label>
<input type="password" name="password" id="password" placeholder="**********"/><br/><br />
<input type="submit" value=" Login " name="submit"/><br />
</form>

Model will have query to insert data. Provide proper table name and data to be entered coming from view page.
public function registration_insert($data) 
{
  $rs=  $this->db->insert('user_login',$data);
  if($rs)
  {
      return TRUE;
  }else{
      return FALSE;
  }
}

Finally in controller load the model and created views.
class User_Authentication extends CI_Controller {

public function __construct() {
parent::__construct();

// Load form helper library
$this->load->helper('form');

// Load form validation library
$this->load->library('form_validation');

$this->load->helper(array('form', 'url'));
// Load database
$this->load->model('Login_Database');
}


// After loading model and views write a function whcih will take data coming from view, will make a array and sent this data to function registration_insert() in model.

public function newuser(){

$data = array(
   // 'id'=>'2',
'user_name' => $this->input->post('username'),
'user_email' => $this->input->post('email'),
'user_password' => $this->input->post('password')
);
$this->Login_Database->registration_insert($data);
$this->load->view('Login_Form');
}

Want to visualize the postGIS data, you can use QGIS to connect with PostGreSql and postgis.

Similarly after processing the data, you may be looking for exporting the PostGIS postgresql data in Shapefile or other format.

After this you can load the base URL in browser and pass data needed to insert in database table. Now open postgres and see the data hase updated accordingly. If you find any problem in connecting between php and postgis, do comment below.