If you do a lot of web-based GIS applications, a common desire is to allow a user to
draw out an area on the map and then do searches against that area and return back a FeatureCollection
where each feature is composed of a geometry and attributes about that feature. In the past the format
was GML or KML, but the world seems to be moving to prefer JSON/GeoJSON. Normally you'd throw
a mapping server that talks Web Feature Service
, do more or less with a webscripting glue, or use a Webservice
such as CartoDb that lets you pass along raw SQL.
In this article we'll demonstrate how to build GeoJSON feature collections that can be consumed by web mapping apps.
the built in JSON functions in PostgreSQL 9.2 and some PostGIS hugging.
Even if you
don't use PostGIS, we hope you'll come away with some techniques for working with
PostgreSQL extended types and also how to morph relational data into JSON buckets.
In our last article, PL/V8JS and PL/Coffee JSON search requests
PHP and JQuery. The PHP part is fairly minimalistic just involving a call to the database and return a single row back. Normally we use a database abstraction layer such as ADODB or PearDB, but this is so
simple that we are just going to use the raw PHP PostgreSQL connection library directly. This example requires PHP 5.1+ since it uses the pg_query_param function introduced in PHP 5.1.
Most of the work is happening in the JQuery client side tier and the database part we already saw. That said the PHP part is fairly trivial to swap out with something like
ASP.NET and most other web server side languages.