Install and using Spatialite with PHP (5.5.9-1) on Ubuntu (14.04.2 LTS)??

[cesare gerbino]

Hi all,

I'm trying to install Spatialite (4.3.0a) on Ubuntu (14.94.2 LTS) ..... I'd like to use it with PHP (5.5.9-1) ....

I've found these two posts:

• https://faimsproject.atlassian.net/wiki/display/FAIMS/Installing+Spatialite+4+on+Ubuntu 
  
• http://www.gaia-gis.it/spatialite-2.4.0-4/splite-php.html
  

and I've trìed to follow the instructions, obviously using the updated versions of the packages involved.

So I've now installed:

• PHP 5.5.9-1
  
• SQLite 3.9.2
• Proj4 4.9.2
• Geos 3.5.0
• Freexl 1.0.2
• Spatialite 4.3.0a

When I try to enter in SQLite3 I've the following error ...

SQLite version 3.9.2 2015-11-02 18:31:45

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> select load_extension('libspatialite.so');

Error: /usr/local/lib/libspatialite.so: undefined symbol: sqlite3_spatialite_init

When I try to execute the PHP test page found at the bottom of http://www.gaia-gis.it/spatialite-2.4.0-4/splite-php.html I obtain only the PHP version , so this code is executed

# reporting some version info

$rs = $db->query('SELECT sqlite_version()');

while ($row = $rs->fetchArray())

{

  print "<h3>SQLite version: $row[0]</h3>";

}

but this one that follow

$rs = $db->query('SELECT spatialite_version()');

while ($row = $rs->fetchArray())

{

  print "<h3>SpatiaLite version: $row[0]</h3>";

}

it's not executed at all and nothing is printed on the browser page.

I've also tried to use a SQLite3 database in a simply PHP code sarting with  

<?php

//conn parameter

$db = new PDO('sqlite:myDataBase');

//this will set to catch error

$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

//most sql will require you to escape ' by adding ' instead of backslash /

//$comune = str_replace("'","''",$_GET['comune']);

$comune = $_GET['comune'];

echo $comune;

echo '<br>';

.....

....

.....

but nothing happens ......

Any suggestions is appreciated ...... thank you very much in advance!!!

Cesare

[a.fu...@lqt.it]

On Wed, 2 Dec 2015 12:59:03 -0800 (PST), cesare gerbino wrote:
> When I try to enter in SQLite3 I've the following error ...
>

> _SQLite version 3.9.2 2015-11-02 18:31:45_
> _Enter ".help" for usage hints._
> _Connected to a transient in-memory database._
> _Use ".open FILENAME" to reopen on a persistent database._
> _sqlite> select load_extension('libspatialite.so');_
> _Error: /usr/local/lib/libspatialite.so: undefined symbol:
> sqlite3_spatialite_init_
>

carissimo Cesare,

any recent version of SpatiaLite (4.2.0 or later) will now
generate _two_ different flavors of the dynamic libraries
identified by different names and specifically intended for
different roles:
1. "libspatialite" (.so, .dylib, .dll or whatever else
depending on the target platform)
2. "mod_spatialite" (.so, .dylib or .dll)

the first is a classic dynamic library, and is just intended
for directly linking into a C/C++ application.
the second is a pure loadable module, and is specifically
intended to be dynamically loaded as an extension to SQLite
by executing a "SELECT load_extension()" SQL statement.

so any attempt to dynamically load "libspatialite" will
now fail, exactly as any attempt to link "mod_spatialite"
will fail.
you are just required to change your load_extension()
call this way:

SELECT load_extension('mod_spatialite');

note: you can completely omit any .so or .dll suffix
because all recent versions of SQLite will automatically
add the appropriate platform extension.

for more technical infos:
https://www.gaia-gis.it/fossil/libspatialite/wiki?name=mod_spatialite

ciao Sandro

[cesare gerbino]

Ciao Sandro,

thank you for the quicky response! I can't try it now, I'll try your suggestion this evening at home. 

Just a little question more 

>>you are just required to change your load_extension() 
>>call this way: 
>>SELECT load_extension('mod_spatialite'); 

The same is for your PHP test code?

# loading SpatiaLite as an extension

$db->loadExtension('libspatialite.so');

# enabling Spatial Metadata

# using v.2.4.0 this automatically initializes SPATIAL_REF_SYS

# and GEOMETRY_COLUMNS

$db->exec("SELECT InitSpatialMetadata()");

# reporting some version info

$rs = $db->query('SELECT sqlite_version()');

while ($row = $rs->fetchArray())

$db->loadExtension('libspatialite.so'); becames $db->loadExtension('mod_spatialite'); ????

Thank you in advance!

[a.fu...@lqt.it]

On Thu, 3 Dec 2015 00:37:45 -0800 (PST), cesare gerbino wrote:
> Just a little question more 
>
>>>you are just required to change your load_extension() 
>>>call this way: 
>>>SELECT load_extension('mod_spatialite'); 
>
> The same is for your PHP test code?
>

> ----------- <snip> ---------------

>
> $db->loadExtension('libspatialite.so');
> becames $db->loadExtension('mod_spatialite'); ????
>

certainly yes: it fully depends on sqlite and spatialite
and it applies indifferently to all languages: Java, PHP,
C#, Python or whatever else.
when you load any recent version of Spatialite (4.2.0 or
later) as a dynamic extension to sqlite via the "load_extension"
mechanism the target binary module should always be
"mod_spatialite" and not "libspatialite"

bye Sandro

[cesare gerbino]

Hi Sandro!. I've done a little step now using your suggestion still not the complete path!.

When I'm in command line mode it seems that all works fine ... Here you are what happen when I try some general SELECT ..

SQLite version 3.9.2 2015-11-02 18:31:45

Enter ".help" for usage hints.

sqlite> SELECT load_extension('mod_spatialite');

Error: mod_spatialite.so: cannot open shared object file: No such file or directory

sqlite> SELECT load_extension('/usr/local/lib/mod_spatialite');

sqlite> SELECT sqlite_version();

3.9.2

sqlite> SELECT spatialite_version();

4.3.0a

sqlite> SELECT proj4_version();

Rel. 4.9.2, 08 September 2015

sqlite> SELECT geos_version();

3.5.0-CAPI-1.9.0 r4084

sqlite>

NOTE: I've to use the complete path at my mod_spatialite .....

When I try do do quite the same suing a simple php procedure like this one

<html>

  <head>

    <title>Testing SpatiaLite on PHP</title>

  </head>

  <body>

    <h1>testing SpatiaLite on PHP</h1>

<?php

# connecting some SQLite DB

# we'll actually use an IN-MEMORY DB

# so to avoid any further complexity;

# an IN-MEMORY DB simply is a temp-DB 

$db = new SQLite3(':memory:');

# loading SpatiaLite as an extension

$db->loadExtension('/usr/local/lib/mod_spatialite');

# enabling Spatial Metadata

# using v.2.4.0 this automatically initializes SPATIAL_REF_SYS

# and GEOMETRY_COLUMNS

$db->exec("SELECT InitSpatialMetadata()");

# reporting some version info

$rs = $db->query('SELECT sqlite_version()');

while ($row = $rs->fetchArray())

{

  print "<h3>SQLite version: $row[0]</h3>";

}

$rs = $db->query('SELECT spatialite_version()');

while ($row = $rs->fetchArray())

{

  print "<h3>SpatiaLite version: $row[0]</h3>";

}

?>

  </body>

</html>

the only thing printed is 

testing SpatiaLite on PHP

SQLite version: 3.9.2

In my php.ini I've this ....

[sqlite]

 http://php.net/sqlite.assoc-case

sqlite.assoc_case = 0

[sqlite3]

sqlite3.extension_dir = /var/www/html/cesarefortest/sqlite3_ext

and in sqlite3.extension_dir there is also a copy of mod_spatialite.so

I'm a really newbye in PHP (and not only for that ... :-) ....) ..... so sorry for the stupid question ..... 

Any suggestions?

 Thank you very much in advance!

[mj10777]

It has been a while since I have used this 

- so I am a bit fuzzy on the details

The goal of the script was to emulate a wms server

- to return either

-- spatialite geometries OR

-- rasterlite2 images

php.ini

- inform loadExtension where to look

[sqlite3]

sqlite3.extension_dir = /usr/local/lib

Main php-code to create database connection

- loads rasterlite2 first, if needed

-- then spatialite

--- with error controls on failure

 // -----------------------------------------------------------------------------------

 $db = new SQLite3($db_name);

 if (($wms_request != "getgeometry") && ($wms_request != "getfoto"))

 {

  $i_rc_raster=$db->loadExtension('mod_rasterlite2.so');

 }

 else

 { // We do not need rasterlite2 for 'getgeometry', but will avoid errors

  $i_rc_raster=1;

 }

 $i_rc_spatial=$db->loadExtension('mod_spatialite.so');

 if (($i_rc_raster != "1") || ($i_rc_spatial != "1"))

 {

  $s_xml_request="";

  if ($i_rc_raster != "1")

  {

   $s_xml_request.="-E-> mod_rasterlite2.so failed rc=$i_rc_raster\n";

  }

  if ($i_rc_spatial != "1")

  {

   $s_xml_request.="mod_spatialite.so rc=$i_rc_spatial\n";

  }

  header("Content-Type: $content_type_text");

  echo $s_xml_request;

  return 0;

 }

 // -----------------------------------------------------------------------------------

- loadExtension : does not use a path

-- being defined in php.ini

-- adding '.so' was needed to work correctly

Mark

[a.fu...@lqt.it]

Hi Cesare and Mark,

On Sun, 6 Dec 2015 18:20:37 -0800 (PST), mj10777 wrote:
> On Sunday, 6 December 2015 17:18:42 UTC+1, cesare gerbino wrote:

>> NOTE: I've to use the complete path at my _mod_spatialite ....._
>>

Cesare, this is simply because Debian (and Ubuntu too) will never
automatically search binary executables on /usr/local/lib
note: Fedora, CentOS and RHEL adopt an exactly opposite convention,
and /usr/local/lib is always included in the system search path.

on Debian (and on derived distro, such as Ubuntu or Mint) you are
required to explicitly set the LD_LIBRARY_PATH env variable:

export "LD_LIBRARY_PATH=/usr/local/lib"

after declaring the above env variable PHP CLI will be immediately
able to successfully execute "SELECT load_extension('mod_spatialite')"
(just declaring a relative path without any suffix)

> The goal of the script was to emulate a wms server
> - to return either
> -- spatialite geometries OR
> -- rasterlite2 images
>
> php.ini
> - inform loadExtension where to look
>
> [sqlite3]
> sqlite3.extension_dir = /usr/local/lib
>

Mark, setting "sqlite3.extension_dir=/usr/local/lib"
will certainly work, but could eventually open some
dangerous security breach, because this way PHP/sqlite3
will then be able to load any possible extension may
be including some dangerous one.

setting "sqlite3.extension_dir = /var/www/sqlite3_ext"
is a safer solution, because you'll then be required
to manually copy all authorized extensions one by one,
and this will probably help to fully preserve a stronger
web security.

> - loadExtension : does not use a path
> -- being defined in php.ini
> -- adding '.so' was needed to work correctly
>

yes, I confirm.
for some obscure reason explicitly adding a ".so"
suffix seems to be strictly required when loading
the mod_spatialite extension from the PHP own path.

bye Sandro