SpatiaLite with PHP on Windows

[Jim O'Leary]

Is it possible to use Spatialite with PHP on Windows? I have searched for this topic and cannot find any conclusive answer.

Thanks

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

Not really sure about nothing, when PHP is someway involved.
And on Windows it could probably be much more confused than
on Linux.

I've reported my last experience (on Debian) here:
http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/php.html

Hoping this could be someway useful to you.

bye Sandro


--
Il messaggio e' stato analizzato alla ricerca di virus o
contenuti pericolosi da MailScanner, ed e'
risultato non infetto.

[Jim O'Leary]

I think that bringing PHP support to SpatiaLite would greatly increase SpatiaLite's popularity and usage. Most web hosts offer PHP with MySQL, which as you know has limited spatial functionality. At the same time, most web hosts do not support PostGreSQL with PostGIS because of its complexity. I think that being able to use SpatiaLite with PHP would be a bit hit.

[Noli Sicad]

Try my PHP Spatialite to GeoJSON
https://gist.github.com/2296532

This is not tested.

Setting Spatialite and PHP
http://www.gaia-gis.it/spatialite-2.4.0-4/splite-php.html

https://groups.google.com/forum/?fromgroups#!topic/spatialite-users/0mY8ZEgpyQA

Noli

> --
> You received this message because you are subscribed to the Google Groups
> "SpatiaLite Users" group.
> To post to this group, send email to spatiali...@googlegroups.com.
> To unsubscribe from this group, send email to
> spatialite-use...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/spatialite-users?hl=en.
>
>

[Noli Sicad]

Just like to add this blog, not spatialite but PHP sqlite search and
json i.e. Ubersearch

http://fuzzytolerance.info/screencast-15-ubersearch/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+FuzzyTolerance+%28Fuzzy+Tolerance%29

Noli

[Jim O'Leary]

I think you have compiled Spatialite into Linux, correct? My question is about Spatialite with PHP on Windows. It appears that you can't add the DLL or call the load_extension to add it at runtime.

On Saturday, July 28, 2012 8:50:36 AM UTC-7, Jim O'Leary wrote:

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

> on Windows. It appears that you can't add
> the DLL or call the load_extension to add it at runtime.
>

Hi Jim,

it simply depends by packagers/maintainers choices. please see:
http://www.gaia-gis.it/gaia-sins/spatialite-arch-2.3.1.html#trap

once the maintainers of any pre-built PHP distribution decide (for
some very disputable reason) to disable dynamic extensions, there is
absolutely nothing you can do in order to by-pass the problem; and
there is absolutely nothing the spatialite developers can do.

except (may be) one thing: you can post a message to your Windows PHP
packagers/maintainers explaining the serious inconvenient they are
causing to you (and possibly to many other users).
and urgently ask them to change their security policies in more
reasonable way, allowing dynamic extensions to be loaded.

[Chuck Young]

The usage of Spatialite on Windows with PHP could be possible depending on the setting.  I don't know if the driver is available for PHP but you might be able to get some help from this link: http://sqlite.awardspace.info/syntax/sqliteodbc.htm.  I don't know if they have worked on a driver for PHP.

If you want to use sqlite / spatialite with MS Access to test, you an download it here.
http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe.  
That is a link will download a driver that works with MS Access

I went looking for what you need and found several things that point to a solution.  I don't have time right now to experimant but I found this that looks really promising.  It says that you can access Sqlite directly with this library.
 http://devpaks.org/details.php?devpak=120 

Also there is a lot of mention of the idea that PHP already has built in support for Sqlite / Spatialite.  See this link for their instructions on how to enable it.

http://www.wikihow.com/Enable-SQLite-for-PHP-5.X-in-Windows

Please let us know what you find.
Chuck

On Sat, Jul 28, 2012 at 8:50 AM, Jim O'Leary <joleary...@gmail.com> wrote:

Is it possible to use Spatialite with PHP on Windows? I have searched for this topic and cannot find any conclusive answer.

Thanks

--

You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.

To view this discussion on the web visit https://groups.google.com/d/msg/spatialite-users/-/_lHo9bbnk8oJ.

[Chuck Young]

Also found this link:

http://php.net/manual/en/sqlite.installation.php

[Jim O'Leary]

There is definitely a DLL for Sqlite in PHP. To load it, you uncomment these lines in your php.ini:

[PHP_PDO_SQLITE]

extension=php_pdo_sqlite.dll

[PHP_SQLITE]

extension=php_sqlite.dll

Then, after restarting Apache, these lines will work on a Spatialite database that you create with spatialite_gui:

$conn = new PDO('sqlite:census_tract.sqlite');

 /*** set the error reporting attribute ***/

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

$query = "select ctuid from census_tract_lat_lng";

foreach ($conn->query($query) as $row)

    {

        list($ctuid) = $row;

        print "$ctuid<br>";

    }

However, you cannot load the spatialite dll. This line:

$stat = $conn->query('SELECT load_extension("/Program Files (x86)/PHP/ext/libspatialite-2.dll");');

...generates this message:

SQLSTATE[HY000]: General error: 1 not authorized 

Thus this line:

$query .= " where st_within(

                geometry,

                ST_GeomFromText

                (

                   'POLYGON((-123.1086760644531 49.22191988838464, -123.1086760644531 49.24299345671403,-123.06267081542967 49.24299345671403 ,-123.06267081542967 49.22191988838464, -123.1086760644531 49.22191988838464 ))'

                )

            )";

...generates this message.

SQLSTATE[HY000]: General error: 1 no such function: ST_GeomFromText 

I have tried other ways, including loading the  libspatialite-2.dll in php.ini, but all methods fail. It seems that PHP has to create its own "official" dll to support spatialite on Windows.

On Sunday, July 29, 2012 12:29:27 PM UTC-7, chuck wrote:

Also found this link:

http://php.net/manual/en/sqlite.installation.php

On Sun, Jul 29, 2012 at 12:24 PM, Chuck Young <wyli...@gmail.com> wrote:

The usage of Spatialite on Windows with PHP could be possible depending on the setting.  I don't know if the driver is available for PHP but you might be able to get some help from this link: http://sqlite.awardspace.info/syntax/sqliteodbc.htm.  I don't know if they have worked on a driver for PHP.

If you want to use sqlite / spatialite with MS Access to test, you an download it here.
http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe.  
That is a link will download a driver that works with MS Access

I went looking for what you need and found several things that point to a solution.  I don't have time right now to experimant but I found this that looks really promising.  It says that you can access Sqlite directly with this library.
 http://devpaks.org/details.php?devpak=120 

Also there is a lot of mention of the idea that PHP already has built in support for Sqlite / Spatialite.  See this link for their instructions on how to enable it.

http://www.wikihow.com/Enable-SQLite-for-PHP-5.X-in-Windows

Please let us know what you find.
Chuck

On Sat, Jul 28, 2012 at 8:50 AM, Jim O'Leary <joleary...@gmail.com> wrote:

Is it possible to use Spatialite with PHP on Windows? I have searched for this topic and cannot find any conclusive answer.

Thanks

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To view this discussion on the web visit https://groups.google.com/d/msg/spatialite-users/-/_lHo9bbnk8oJ.

To post to this group, send email to spatialite-users@googlegroups.com.
To unsubscribe from this group, send email to spatialite-users+unsubscribe@googlegroups.com.

[Noli Sicad]

updated php.ini:
[sqlite3]
sqlite3.extension_dir = /Program Files (x86)/PHP/ext/

Place in libspatialite-2.dll in that dir (i.e. /Program Files (x86)/PHP/ext/)

###################
Probably better:

[sqlite3]
sqlite3.extension_dir = /var/www/sqlite3_ext

then place the dll in that directory.

########

Do this to loading the dll

# loading SpatiaLite as an extension
$db->loadExtension('libspatialite-2.dll');


Noli

[Jim O'Leary]

I have tried that and get:

Warning: SQLite3::loadExtension(): Not supported in multithreaded Web servers  

Have tried other ways as well. Again, it seems that PHP has to create an "official" Spatialite DLL.

On Saturday, July 28, 2012 8:50:36 AM UTC-7, Jim O'Leary wrote: