Are you aware of the JOIN docs, with an example of how to join shp to Postgres? http://www.mapserver.org/mapfile/join.html#example-2-join-from-shape-dataset-to-postgresql-table I created that example years ago but it is still valid.
-jeff -- Jeff McKenna MapServer Consulting and Training Services http://www.gatewaygeomatics.com/ On 2014-06-18, 9:57 PM, Tom Lee wrote: > 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 > _______________________________________________ mapserver-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/mapserver-users
