Tom,
I did some testing of your problem by setting up a layer to my
[similar] datasources using Jeff's suggestion of using the JOIN object:
LAYER
NAME 'test'
TYPE POINT
STATUS DEFAULT
CONNECTIONTYPE OGR
CONNECTION "PG:dbname=IMS host=localhost port=5432 user=postgres
password=mypassword"
DATA "SELECT * FROM (SELECT image_id, sum(length) FROM
feature_polygon GROUP BY image_id) AS new_table"
# ---- this doesn't work (syntax error at 'images.shp'):
# DATA "SELECT * FROM (SELECT image_id, sum(length) FROM
feature_polygon GROUP BY image_id) AS new_table LEFT JOIN
'images.shp'.images ON feature_polygon.image_id=images.ID"
JOIN
NAME "test"
# CONNECTIONTYPE DBF # no need?
TABLE "data/images.dbf"
FROM "image_id" # the attribute name from the LAYER's DATA object
TO "1"
TYPE ONE-TO-ONE
END # join
END # LAYER
While shp2img does not report any errors, I doubt if this config will
produce the desired result. The "interesting" thing in this scenario is
that the PostGIS table does not contain the geometry, the shapefile
does, but I expect the JOIN processing only looks at the DBF portion.
My log file shows many of the following:
msOGRFileNextShape: Rejecting feature (shapeid = 0, tileid=0) of
incompatible type for this layer (feature wkbType 0, layer type 0)
so I think this will be a problem.
One work-around would be to create a view in your database:
CREATE OR REPLACE VIEW my_view AS
SELECT country, sum(obligations) FROM foreign_assistance GROUP BY country);
Then use your shapefile as the datasource and join to the view (either
in the DATA object or in a JOIN object):
# Joining a shapefile with Postgres view:
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,my_vew'.my_view ON
ne_10m_admin_0_map_units.name=my_view.country"
Best Regards,
Brent Fraser
On 6/19/2014 7:21 AM, Tom wrote:
Hi Jeff,
Thanks for the reply. Yes, I did see that example, and tried a number
of different things to get it working for me, but to no avail. My log
gets a bunch of statements that look like this:
[Thu Jun 19 09:00:47 2014].144000 msPOSTGRESQLJoinClose() already
close or never opened.
Even if I did get that working, where would I put my GROUP BY sql
query? In the TABLE attribute?
Thanks!
Tom
Date: Wed, 18 Jun 2014 22:01:47 -0300
From: Jeff McKenna <jmcke...@gatewaygeomatics.com>
To: mapserver-users@lists.osgeo.org
Subject: Re: [mapserver-users] Grouping Postgres and join with
Shapefile
Message-ID: <53a2367b.2060...@gatewaygeomatics.com>
Content-Type: text/plain; charset=ISO-8859-1
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
_______________________________________________
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users
_______________________________________________
mapserver-users mailing list
mapserver-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users
