[mapserver-users] Grouping Postgres and join with Shapefile

[Tom Lee]

Hi folks,

I'm struggling with something that I *think* should be possible, but running into a lot of dead ends.  What I'm trying to do is join some data from a Postgres database with shapefile data, while grouping the Postgres data by an aggregate function.  So far I can do those two things separately, but not together.  For example:

# Joining a shapefile with Postgres data:

CONNECTIONTYPE OGR 
CONNECTION     'data/ne_10m_admin_0_map_units.shp' 
DATA "SELECT * FROM ne_10m_admin_0_map_units LEFT JOIN 'ODBC:postgres at Postgres,foreign_assistance'.foreign_assistance ON ne_10m_admin_0_map_units.name=foreign_assistance.country" 

# Querying the Postgres data and grouping it:

CONNECTIONTYPE OGR 
CONNECTION     'PG:dbname=data host=localhost port=5432 user=postgres password=password' 
DATA "SELECT * FROM (SELECT country, sum(obligations) FROM foreign_assistance GROUP BY country) AS new_table"

What I'd really like to do is something like this:

DATA "SELECT * FROM (SELECT country, sum(obligations) FROM foreign_assistance GROUP BY country) AS new_table LEFT JOIN  'data/ne_10m_admin_0_map_units.shp' ON foreign_assistance.country=ne_10m_admin_0_map_units.name"

The problem with this is that it mixes native Postgres SQL and OGRSQL, and treats the shapefile as a datasource without it being loaded.

Any thoughts?  Thanks.