[mapserver-users] Grouping Postgres and join with Shapefile

[Jeff McKenna]

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 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.
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>