PostGIS- GROUP BY for continuous data values?

Question

In the sample code for the PostGIS ST_ConvexHull function, multiple polygons would be created based upon different, discrete values found in the field "disease_type".

--Get estimate of infected area based on point observations
SELECT d.disease_type,
    ST_ConvexHull(ST_Collect(d.the_geom)) As the_geom
    FROM disease_obs As d
    GROUP BY d.disease_type;

Lets say there was a numeric field (lets call it "myfield") with continuous values from 0-5. How would you produce similar results as GROUP BY but defining your own breakpoints (for example: 0-1.25, 1.25-3.5, 3.5-5)?

Answer 1

You can use CASE to create classes. Something like that:

SELECT 
d.disease_type,
ST_ConvexHull(ST_Collect(d.the_geom)) As the_geom
FROM 
    (
    SELECT
    diseaseobs.the_geom,
    (CASE diseaseobs.number WHEN number BETWEEN 0 AND 1.5 THEN 'type one'
         WHEN number BETWEEN 1.6 AND 3 THEN 'type two'       
         ELSE 'other'
    END ) as disease_type
    FROM schema.diseaseobs

    ) AS d

GROUP BY d.disease_type;

Answer 2

Hallo

What I use to do in those cases is creating a table with the classes and joining against them or use a subquery to create a list of classes.

I think that gives a more intuitive approach and I think it is more flexible since you can update the classes by just updating a table.

Another benefit is that you can get the empty classes too.

the subquery approach could look something like:

SELECT
d.disease_type,
ST_ConvexHull(ST_Collect(d.the_geom)) AS the_geom FROM disease_obs
RIGHT JOIN
(SELECT 0::float AS classbottom, 1.25::float AS classtop, 1::int AS classid
UNION ALL    
SELECT 1.25::float AS classbottom, 3.5::float AS classtop, 2::int AS classid
UNION ALL    
SELECT 3.5::float AS classbottom, 5::float AS classtop, 3::int AS classid) AS classes
ON disease_obs.continuous_value>=classes.classbottom AND disease_obs.continuous_value<classes.classbottom
GROUP BY classes.id;

Here I used a right join to also get the empty classes which can be handy sometimes. If you don't want the empty ones just change to an inner join.

If you move the classes to a table and put the rest of the query in a view you can change the classes without touching the query.

HTH /Nicklas