[postgis-users] postgis & php

[Aaron Racicot]

Nelson,

Here is some code that I have from a project accessing postgis data from
PHP (using pgsql2shp):

// Now connect to the db
$host_str = "host=localhost";
$db_str = "dbname=foo_db";
$usr_str = "user=web_user";
$string = $host_str." ".$db_str." ".$usr_str;
$connection = pg_connect($string);
if (!$connection)
{
  pg_close($connection);
  echo "Error: Cannot connect to the database <br>\n";
}
else
{
  echo "Connected to the database <br>\n";
}

 ////////////////////  Lots of code deleted... ////////////////////


  // Create a view for the data needed... 
  $exec_str = sprintf("CREATE VIEW
".$previous_session_name."_".$previous_session_id." AS ".
		      "SELECT bk.the_geom, sum(tr.total_lbs*tb.percent)
as total_lbs ".
		      "FROM tow%02d_3kblk_intersect as tb, trawl%02d".
		      " as tr, cablk3km as bk, port as pt ".
		      "where tb.blk_gid=bk.gid ".$species_sql."
".$port_sql." ".$gear_sql.
		      " and tb.tow%02d_id=tr.tow_id2 ".
		      "GROUP BY bk.the_geom HAVING
count(tr.tow_id2)>2",$start_year_short,$start_year_short,$start_year_sho
rt);
  echo $exec_str."<br>\n";
  $result_pg_exec = pg_exec($connection, $exec_str);
  if (!$result_pg_exec) {
    printf("<B>%s</B><BR>\n", pg_errormessage());
  }

  // execute the pgsql2shp to create the output
  echo " execute the pgsql2shp to create the output<br>";
  $dir_exec = sprintf("/usr/local/pgsql/bin/pgsql2shp -u web_user -f
".$session_tmp_dir.
		      "data/target_areas/test_output.shp
shelf_closure_db ".
		      "\"SELECT * from
".$previous_session_name."_".$previous_session_id."\"");
  echo $dir_exec."<br>\n";
  exec($dir_exec,$arr,$err1); 
  // Write out the output from the psql command
  for ($ii=0;$ii<count($arr);$ii++)
    {
      echo $arr[$ii]."<br>\n";
    }
  // Drop the view
  $result_pg_exec = pg_exec($connection, "DROP VIEW
".$previous_session_name."_".$previous_session_id);
  if (!$result_pg_exec) {
    printf("<B>%s</B><BR>\n", pg_errormessage());
  }


Hope this helps...  One funky thing I found is that not every capability
available in normal SQL is available in the SQL fed to pgsql2shp, so I
tend to stick with creating views, dumping the contents, and then
dropping the view.  Just my preference.

A

+----------------------------------------+
| Aaron Racicot  | aaronr at ecotrust.org   |
| GIS Programmer | 503.467.0759          |
+----------------------------------------+
| e c o t r u s t                        |
| Jean Vollum Natural Capital Center     |
| 721 NW Ninth Avenue                    |
| Suite 200                              |
| Portland, OR 97209                     |
| www.ecotrust.org                       |
+----------------------------------------+


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Nelson Guda
Sent: Monday, April 03, 2006 2:00 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] postgis & php



On Apr 3, 2006, at 3:08 PM, Ries van Twisk wrote:

> Nelson Guda wrote:
>
>> Hi all,
>>
>> I am relatively new to postgis.  I've got a database up and
>> running,  and have used much of the language already.  I am  
>> looking for a php  protocol that can run shp2pgsql and pgsql2shp  
>> so that i can use these  functions via a web interface.
>
> You would beable to access this function by executing a shellcript. It

> would be nice to have a php classet ofcourse, but I think
> exsecuting these from a php shell would not be a problem.
>
> Ries

Yes, I think that is what I need, but i don't know how to code that.   
Can anyone help or point me to a good tutorial?

thanks much,
nelsong





>
>>
>> Unfortunately my understanding of programming languages is very   
>> limited - mostly I have used postgis through pgadmin.
>>
>> I found a set of php classes on the web that include a function
>> for  shp2pgsql, but I cannot seem to get it working.  I'll copy  
>> those  functions below.  I don't understand what I might need to  
>> change to  make this work.  Can anyone help me out with this?
>>
>> Thanks much,
>> nelsong
>>
>>
>>
>> <snip...... />
>
>
> --
> Ries van Twisk
> Freelance Typo3 Developer
> email: ries at vantwisk.nl
> web:   http://www.rvantwisk.nl/
> skype: callto://r.vantwisk
>
> _______________________________________________
> postgis-users mailing list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users