GeoPHP, PHP SQLite to GeoJSON, PHP PostGIS to GeoJSON and Leaflet

[Noli Sicad]

Hi Bryan,

I think this is separate topic for discussion and decided take it out
from the other thread.

I am looking at GeoPHP and your SQLite to GeoJSON and PostGIS to
GeoJSON code, I could not decipher how to use these in order to
GeoJSON from Select statement i.e. a table.

Supposing I have contact table in PostGIS, How do I incorporate this
select statement?

Select name, street, phoneno, ST_asGeoJSON(the_geom) from contact
where street = 'Church St';

where: the_geom is point

I like to see an example code for PostGIS to GeoJSON and SQLite to
GeoJSON as well.

Do you have any Leaflet projects online or example that use these 2
PHP scripts (i.e. SQLite to GeoJSON and PostGIS to GeoJSON)?

Thanks in advance.

Noli

[Bryan McBride]

Noli,

These are PHP scripts that basically echo GeoJSON output from an SQL select statement build from URL parameters. They need to be run from a server with PHP installed and configured correctly. The PostGIS script requires PHP with php_pgsql enabled, while the SQLite script requires PHP with PDO_SQLITE enabled and a geoPHP include (since SQLite cannot has no native function to transform WKB to GeoJSON).

Example usage:

http://localhost/postgis_geojson.php?geotable=contact&geomfield=the_geom&fields=name,street,phoneno&parameters=street='Church Street'

If you ran this in your browser, it would return formatted GeoJSON, which could be consumed in Leaflet.

BRYAN

[Noli Sicad]

Bryan,

Thanks. I'm getting it now.

So, only reason why SQLite GeoJSON needs geoPHP is it does not have spatial.

It would be good to use Spatialite since it has now GeoJSON.
Spatialite 3.x has support for AsGeoJSON(geometry), not to mention WKB
already supported in previous Spatialite 2.x versions.

Here's Spatialite PHP
http://www.gaia-gis.it/spatialite-2.4.0-4/splite-php.html

Thanks again.

Noli

On 4/4/12, Bryan McBride <mcbrid...@gmail.com> wrote:
> Noli,
>
> These are PHP scripts that basically echo GeoJSON output from an SQL select
> statement build from URL parameters. They need to be run from a server with
> PHP installed and configured correctly. The

> PostGIS<https://gist.github.com/1913855>script requires PHP with php_pgsql
> enabled, while the
> SQLite <https://gist.github.com/1920796> script requires PHP with
> PDO_SQLITE enabled and a geoPHP <https://github.com/phayes/geoPHP> include

[Noli Sicad]

Bryan and others,

You might interested to try spatialite and needs data.

U.S. 2011 Census TIGER/Line files in Spatialite format.

From Spatialite GG.

"All the state, county, and municipality boundary shapefiles
for a project. Two versions are up - one pretty much as the Census Bureau
releases them, with one state per table/shapefile. The other is the
version we're actually using, with the state tables combined" - B Freed
stineconsulting.com.

Alex Mandel 7.z it here.

180MB for Combined and 169 for Boundaries
http://downloads.ice.ucdavis.edu/osgeo/data/

Thanks.

Noli

[Gery Herbozo]

I'd like to ask something about it, I'm using this script (update https://gist.github.com/3763701) and it works nicely (congrats to the author), but the 'parameters' doesn't work with my tables, for instance this call:

http://localhost/postgis_geojson.php?geotable=seafloor_thermometry_point_wgs84&geomfield=geom&parameters=source='GEOMAR'

gives:

There was an error parsing the JSON document. The document may not be well-formed.

Document contents:

<br /> <b>Warning</b>: pg_query() [<a href='function.pg-query'>function.pg-query</a>]: Query failed: ERROR: syntax error at or near &quot;GEOMAR&quot; at character 110 in <b>/var/www/html/postgis_geojson.php</b> on line <b>85</b><br /> An SQL error occured.

however, in postgis this call:

select id,fid,source from seafloor_thermometry_point_wgs84 where source='GEOMAR';

gives:

 id | fid | source
----+-----+--------
  1 |   1 | GEOMAR
  2 |   2 | GEOMAR
  3 |   3 | GEOMAR
  4 |   4 | GEOMAR
...etc

where could the problem be?

thanks for any hint about this,

Gery

[Bryan McBride]

Try  &parameters=source+=+'GEOMAR' 

BRYAN

[Gery Herbozo]

thanks Bryan for the suggestion but still it gives an error:

There was an error parsing the JSON document. The document may not be well-formed.

Document contents:

<br /> <b>Warning</b>: pg_query() [<a href='function.pg-query'>function.pg-query</a>]: Query failed: ERROR: syntax error at or near &quot;GEOMAR&quot; at character 112 in <b>/var/www/html/postgis_geojson.php</b> on line <b>85</b><br /> An SQL error occured.

this is the call:

http://localhost/postgis_geojson.php?geotable=seafloor_thermometry_point_wgs84&geomfield=geom&parameters=source+=+'GEOMAR'

I noticed that after copying this link, this "%27" replaces the single quotes ('), is that normal? without the parameters option, that call gives:

{

• "type": "FeatureCollection",
• "features": [
  • {
    • "type": "Feature",
    • "geometry": {
      • "type": "Point",
      • "coordinates": [
      • ... etc, etc ....
        

btw my postgis table has PK in id, I'm using postgis 1.5, postgresql 8.4.8, php-5.1.6-27.el5, php-pgsql-5.1.6-27.el5, apache 2 (httpd-2.2.3-43.el5) and Firefox 6.0.2 in rhel5 32-bits,

thanks for the support,

[Gery Herbozo]

any idea what could be the problem?

[Bryan McBride]

Looks like an issue with escaping your single quotes in the URL... On line 70, try changing pg_escape_string($parameters) to just $parameters. Or you can try hard coding your where clause.

BRYAN

[Gery Herbozo]

cool Bryan, changing that line solved the problem, it works now. I was playing with another table but using "ilike", it works even though I assigned a blank space in between:

http://localhost/postgis_geojson.php?geotable=submarine_dive_logs_point_wgs84&geomfield=geom&parameters=source ilike 'PSU'

the funny thing is that it doesn't work when I use the word "ballena":

http://localhost/postgis_geojson.php?geotable=submarine_dive_logs_point_wgs84&geomfield=geom&parameters=comments ilike '%ballena%'

I received:

There was an error parsing the JSON document. The document may not be well-formed.

Document contents:

<br /> <b>Warning</b>: pg_query() [<a href='function.pg-query'>function.pg-query</a>]: Query failed: ERROR: invalid byte sequence for encoding &quot;UTF8&quot;: 0xba HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by &quot;client_encoding&quot;. in <b>/var/www/html/postgis_geojson.php</b> on line <b>86</b><br /> An SQL error occured.

but if I use "allena"  (because "ballen" gives also the above error):

http://localhost/postgis_geojson.php?geotable=submarine_dive_logs_point_wgs84&geomfield=geom&parameters=comments ilike '%allena%'

it works perfectly, funny isn't?.

Thanks again,

[Arnie Shore]

Folks, first a big-time thank you to the folks who've done the
examples and explanations. Truly appreciated, for an awesome library.

Now what will seem a trivial complaint to the more experienced here:
This noob will eventually figger out the references, but when a given
identifier - 'map' in most cases - is used in more than a single
context, it can generate some confusion and frustration until the
clouds part.

So a simple request: Don't multiplex identifiers. Like div_map and
obj_map would be SO clear.

Thanks, all.

[Bryan McBride]

Gery,

I've revisited/updated my PHP GeoJSON scripts and given them their own GitHub repo at https://github.com/bmcbride/PHP-Database-GeoJSON. I have tried to simplify things and ended up removing much of the URL parameter junk used to build the select statement. It's much safer to hard code the SQL and that should solve many of the issues you've been having.

BRYAN