[mapserver-users] Grouping Postgres and join with Shapefile
Jeff McKenna
jmckenna at gatewaygeomatics.com
Wed Jun 18 18:01:47 PDT 2014
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
>
More information about the MapServer-users
mailing list