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@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. _______________________________________________ mapserver-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/mapserver-users
