Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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)?

share|improve this question
    
Not really a GIS question but since it has already been answered. –  underdark Jun 2 '11 at 16:54

2 Answers 2

up vote 8 down vote accepted

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;
share|improve this answer
    
Thank you, Pablo. This is great... I need to test it out myself but I am hopeful that this is the answer I was looking for. –  RyanDalton Jun 2 '11 at 17:28

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

share|improve this answer
    
Thanks for the suggestion. I considered going this route, but I would ultimately like to put this into an app where the user can choose their own break points, which makes this solution not quite as conducive. –  RyanDalton Jun 7 '11 at 18:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.