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.

I have two PostGIS tables: earthquakes and borders, which contain point data on earthquake locations, and polygons of countries in the world respectively.

I want to try and count the earthquakes within each country, and I've managed to do this using the following SQL:

SELECT ch01.borders.name, COUNT(ch01.earthquakes.geom) as count FROM ch01.borders, ch01.earthquakes
WHERE ST_Contains(ch01.borders.geom, ch01.earthquakes.geom)
GROUP BY ch01.borders.name ORDER BY count DESC;

This gives me a nice ordered table showing the number of earthquakes in each country. However, I'd like to produce a map of the world in QGIS, with each country shaded relative to the number of earthquakes occurring within its borders - but to do that, I need to get a geometry column back from the SQL query (it seems that I'll also need a primary key, but I'll cross that bridge later).

I tried simply selecting the geometry column in the SQL:

SELECT ch01.borders.geom, ch01.borders.name, COUNT(ch01.earthquakes.geom) as count FROM ch01.borders, ch01.earthquakes
WHERE ST_Contains(ch01.borders.geom, ch01.earthquakes.geom)
GROUP BY ch01.borders.name ORDER BY count DESC;

but that gives me an error saying:

ERROR: column "borders.geom" must appear in the GROUP BY clause or be used in an aggregate function

The problem is: I don't want to aggregate or group by the geometry, I just want to return the geometry for the rows that I've got. How can I do this?

share|improve this question
1  
Does each borders.name have a matching borders.geom? If so you can do a GROUP BY ch01.borders.name, borders.geom and it should return both name and geom. –  djq Feb 17 at 4:09
    
That seems like the simplest way to do it, but it doesn't seem to work for me. Adding the extra column to the group-by doesn't seem to change the results at all. Any idea what's going on? –  robintw Feb 17 at 8:38
    
@robintw Don't forget to add "geom" to your SELECT clause . If you try both solutions, I'd be interested to know the query times. –  Scro Feb 17 at 10:04

1 Answer 1

One way is to use a WITH query, also known as a Common Table Expression. You can use the first query you wrote (lightly modified) in the WITH query and then join it to your "borders" table. Assuming the PRIMARY KEY for "borders" is "gid", it could look like this:

WITH eqs_per_border AS (
  SELECT
    ch01.borders.gid AS gid,
    COUNT(*) AS eq_count
  FROM
    ch01.borders,
    ch01.earthquakes
  WHERE ST_Contains(ch01.borders.geom, ch01.earthquakes.geom)
  GROUP BY ch01.borders.name 
)
SELECT
  ch01.borders.gid AS "ROWID",
  ch01.borders.name AS border_name,
  eqs_per_border.eq_count AS eq_count,
  ch01.borders.geom AS geom
FROM 
  ch01.borders,
  eqs_per_border
WHERE ch01.borders.gid = eqs_per_border.gid
ORDER BY count DESC

A couple of notes: First, use a JOIN on the second query if that feels more natural. Second, it is not necessary to put the "ROWID" alias on the "gid" column. It's just a habit I developed from a trick to get QGIS to respect SpatiaLite views.

share|improve this answer

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.