You're right, using ST_Intersection
slows down your query noticeable.
Instead of using ST_Intersection
it is better to clip (ST_Clip
) your raster with the polygons (your fields) and dump the result as polygons (ST_DumpAsPolygons
). So every raster cell will be converted into a little polygon rectangle with distinct values.
For receiving min, max or mean from the dumps you can use the same statements.
This query should do the trick:
SELECT
toid,
Min((gv).val) As MinElevation,
Max((gv).val) As MaxElevation,
Sum(ST_Area((gv).geom) * (gv).val) / Sum(ST_Area((gv).geom)) as MeanElevation
FROM (
SELECT
toid,
ST_DumpAsPolygons(ST_Clip(rast, 1, geom, true)) AS gv
FROM topo_area_su_region,dem
WHERE ST_Intersects(rast, geom)) AS foo
GROUP BY toid
ORDER BY toid;
In the statement ST_Clip
you define the raster, the raster band (=1), the polygon and if the crop should be TRUE or FALSE.
Besides you can use avg((gv).val)
to calculate the mean value.
EDIT
The result of your approach is the more exact, but the slower one. The results of the combination of ST_Clip
and ST_DumpAsPolygons
are ignoring the raster cells that are intersecting with less than 50% (or 51%) of their size.
These two screen shots from a CORINE Land Use intersection show the difference. First picture with ST_Intersection
, second one with ST_Clip
and ST_DumpAsPolygons
.
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.
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
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
Best Answer
I believe you're trying to ask too much out of that hardware... running a GeoServer requires a "server" (a laptop PC will do, even a few years old), the Raspberry does not have enough computational power.
I believe that running a full fledged distro like Ubuntu MATE is not helping, but the real issue is the hardware.