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.
Lately I'm reminded that one person's feature is another person's frustration. I've been following Paul's PostGIS Apologia detail about why
things are done a certain way in PostGIS in response to Nathaniel Kelso's: A friendlier PostGIS? Top three areas for improvement.
I've also been following Henrik Ingo: comparing Open Source GIS Implementation to get
a MySQL user's perspective on PostGIS / PostgreSQL. Jo Cook has some interesting thoughts as well in her PostGIS for beginners amendment to Paul's comments.
I have to say that both Nathaniel, Henrik, Jo and commenters on those entries have overlapping frustrations with PostgreSQL and PostGIS. The number one frustration is caused by how bad a job we do
at pointing out avenues to get a friendly installation experience. I do plan to change this to at least document the most popular PostGIS package maintainers soon.
One of the things that Henrik mentioned was his frustration with trying to install PostGIS via Yum PostgreSQL repository and in fact not even knowing about the PostgreSQL Yum repository
that has current and bleeding versions of PostgreSQL. I was surprised he didn't know because as a long-time user of PostgreSQL, I dismissed this as common knowledge. This made me realize just how out of touch
I've become with my former newbie self and I consider this a very bad thing. I was also very surprised about another feature he complained about - CREATE EXTENSION did not work for him because he accidentally
installed the wrong version of PostGIS in his PostgreSQL 9.1. The main reason for his
frustration was something I thought was a neat feature of PostGIS. That is that PostGIS is not packaged into PostgreSQL core and you can in fact have various versions
of PostGIS installed in the same PostgreSQL cluster. This unlike the other OGC spatial offerings of other databases (SQL Server, Oracle, MySQL) allows the PostGIS dev group to work on their own time schedule largely apart
from PostgreSQL development group pressures.
It also means we can take advantage of breaking changes introduced in PostGIS 2.+ for example without impacting existing apps people have running 1.5 and also allow people
to take advantage of newer features even if they are running an earlier PostgreSQL version.
There has been a lot of talk lately about schemaless models touted by NoSQL groups and how PostgreSQL fits into this New world order.
Is PostgreSQL Object-Relational? Is it Multi-Model. We tend to think of PostgreSQL as type liberal and it's liberalness gets more liberal with each new release. PostgreSQL is fundamentally relational, but has little bias about what data types define each column of related tables. One of PostgreSQL great strengths is the ease with which different types can coexist in the same table and the flexible index plumbing and plan optimizer it provides that allows each type, regardless of how wild, to take full advantage of various index strategies and custom index bindings. Our 3 favorite custom non-built-in types we use in our workflow are
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.
PostgreSQL 9.2 beta3 got released this week and so we inch ever closer to final in another 2 months or so. One of the great
new features is the built-in JSON type and companion PLV8/PLCoffee languages that allow for easy processing of JSON objects.
One of the use cases we had in mind is to take as input a JSON search request that in turn returns a JSON dataset.
We'll use our table from PLV8 and PLCoffee Upserting.