Using AJAX to run PostGIS SQL statements and display results via GeoServer

Question

I am learning Web GIS, and I can't find a good example that gives a big picture of a web interface of running spatial analysis using PostGIS.

What I am looking for is a graphical/GIS version of pgAdmin running on the web. Given a SQL statement, the web interface calls PostGIS, possibly generating a result table, which then gets rendered in a graphical/cartographic form rather than in a tabular form as in pgAdmin.

I looked around, and most question/answers here is regarding how to display an existing table in PostGIS. But there is nothing that covers the complete loop. That is, start query, store the result in the database, display it, and maybe using the result table for a new query.

I have a little bit of familiarity with GeoServer and its RESTful API, and very basic knowledge of JavScript/jQuery/HTML5.

Can some suggest a viable route for achieving this query-store-display loop?

Note: I am less interested in the visualization part and getting GeoJson's because they cannot be directly used by PostGIS for further analysis.

---

From @John's answer, GeoJSON seems to be a very good option and I think there is good reason why GeoJSON is so popular. On the other hand, I was hoping to find a more "server-side" solution that relies primarily on PostGIS doing the spatial analysis and GeoServer doing the rendering/cartography of the PostGIS tables/views. Hopefully, this will involve minimal client side programming (other than issuing SQL commands and GeoServer API calls).

I can do this manually with pgAdmin together with the GeoServer administration interface or RESTful API curling. But I am not sure how to do the same programmatically from the Web with e.g. AJAX and REST APIs. I have tried to use WPS, but without much success. For example, I have tried to perform simple raster map algebra, but the WPS in GeoServer takes two input rasters from server and only gives you an output image for download, not another raster dataset for further analysis. Here, the "loop" is broken.

Besides, WPS functions vary from one package to another. If there is something wrong in a WPS, it's less easy to get it fixed unless the upstream team is really active.

So I am thinking to not rely on WPS, and instead rely on PostGIS to do all vector and raster analysis, as I think Postgres is more tested and the relational/map algebra therein is complete. And I was hoping to use AJAX as a "command center" to issue SQL statements to Postgres (maybe with some server side PHP proxy??), and to render some of the resulting tables to the web via GeoServer (or another map server) (e.g. by publishing/updating the result tables/views).

Answer 1

Your question is rather broad, but from what you say, the best format would be GeoJSON. OpenLayers, Leaflet and Google Maps all read and display GeoJSON, with a trivial amount of coding required. Here is the Open Layers 3 GeoJSON example. OpenLayers 2 and 3 also have (Geo)JSON writers, which allow you to serialize OpenLayers vector objects back to JSON for database storage.

As far as getting JSON from the server in the first place is concerned, you have a couple of options from GeoServer.

1. WFS. This allows reading and updating of data and will transmit data in a number of formats, including GeoJSON. See the GeoServer WFS docs for more information. Here is the OpenLayers 3 WFS example

2. WPS. This allows you to send remote queries to GeoServer and get the results back in your web client, the default being JSON, see the Geoserver docs. Here is an OpenLayers 2 WPS example. This approach actually allows you to do GIS/spatial operations on your geometries, buffer, union, etc, remotely and get the results back in the client.

There are other options too, such as rolling your own, which you will have to do if you are just using AJAX to make the request -- you will have to then code up the server side somehow. To get multiple features our of Postgres, along with their attributes, you need to create a FeatureCollection. This can be done using the JSON datatype and the row_to_json and array_to_json functions. A typical query, which comes from Regina Obe, author of Postgis in Action, on the Boston Gis website looks like this:

SELECT row_to_json(fc)
 FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As Features
  FROM (SELECT 'Feature' As type,
      ST_AsGeoJSON(lg.geog)::json As geometry,
      row_to_json((SELECT l FROM (SELECT loc_id, loc_name) As l)
    ) As properties
    FROM locations As lg ) 
   As f )  
As fc;

which is quite hard to look at when you first see it, but very powerful. This means you still have your geometries in a geometry field, so can do all the GIS analysis you want with, but still easily produce GeoJSON for display purposes.

There are also options using GeoDjango and the vectorformats library which will also produce GeoJSON with attributes directly from a Postgres table. You didn't specifically mention Python/Django, but as Python is so popular among GIS people, I thought this worth mentioning. Here is a longer blog article about the GeoDjango to GeoJSON approach.

Finally, it is not strictly true that you can not do analysis on JSON once it is in Postgres. Since Postgres 9.2 there has been a JSON datatype and in the upcoming Postgres 9.4 there will be a binary JSON format, known as JSONB which will allow indexing and search directly on JSON. I am not necessarily recommending this, given the rich array of functions available on separate geometry columns, but I mention if for completeness. I believe this is in some ways a response to the growing popularity of key/value stores in NoSQL databases, such as MongoDB, and will in no way displace Postgis's geometry column for GIS type queries.

There is also the WKT format, that can be used in a similar fashion, but it is much more of a pain to add attributes to it, and there is GML/XML, which allows for the encoding of complex structures, but is a pain to work with in Javascript (many would say, in general), whereas JSON is native, so perfect for "WebGIS".

Sorry, for such a long answer, but your question is rather broad :D

Answer 2

Sounds like you want to create a layer dynamically via the REST-API as a SQL View.

Another approach would be to create views in PostGis, update the store and publish the view as a layer.

The first one does not clutter the database with views and your dynamic work is gathered in GeoServer. The REST-API use Basic Authentication so it is easier if you proxy the calls adding the Authenticate header in the proxy. Also, the credentials are not readable to your users.