<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">Tom,<br>
<br>
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:<br>
<br>
<tt> LAYER</tt><tt><br>
</tt><tt> NAME 'test'</tt><tt><br>
</tt><tt> TYPE POINT</tt><tt><br>
</tt><tt> STATUS DEFAULT</tt><tt><br>
</tt><tt> </tt><tt><br>
</tt><tt> CONNECTIONTYPE OGR</tt><tt><br>
</tt><tt> CONNECTION "PG:dbname=IMS host=localhost port=5432
user=postgres password=mypassword"</tt><tt><br>
</tt><tt> DATA "SELECT * FROM (SELECT image_id, sum(length)
FROM feature_polygon GROUP BY image_id) AS new_table"</tt><tt><br>
</tt><tt> </tt><tt><br>
</tt><tt># ---- this doesn't work (syntax error at 'images.shp'):</tt><tt><br>
</tt><tt># 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"</tt><tt><br>
</tt><tt> </tt><tt><br>
</tt><tt> JOIN</tt><tt><br>
</tt><tt> NAME "test"</tt><tt><br>
</tt><tt># CONNECTIONTYPE DBF # no need?</tt><tt><br>
</tt><tt> TABLE "data/images.dbf"</tt><tt><br>
</tt><tt> FROM "image_id" # the attribute name from the
LAYER's DATA object </tt><tt><br>
</tt><tt> TO "1"</tt><tt><br>
</tt><tt> TYPE ONE-TO-ONE</tt><tt><br>
</tt><tt> END # join</tt><tt><br>
</tt><tt><br>
</tt><tt> END # LAYER</tt><br>
<br>
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:<br>
<br>
<tt>msOGRFileNextShape: Rejecting feature (shapeid = 0, tileid=0)
of incompatible type for this layer (feature wkbType 0, layer
type 0)</tt><br>
<br>
so I think this will be a problem.<br>
<br>
<br>
One work-around would be to create a view in your database:<br>
<br>
<tt>CREATE OR REPLACE VIEW my_view AS </tt><tt></tt><tt><br>
</tt><tt> </tt><tt>SELECT country, sum(obligations) FROM
foreign_assistance GROUP BY country)</tt><tt>;</tt><br>
<br>
Then use your shapefile as the datasource and join to the view
(either in the DATA object or in a JOIN object):<br>
<br>
<pre wrap=""># 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" </pre>
<br>
<br>
<pre class="moz-signature" cols="72">Best Regards,
Brent Fraser</pre>
On 6/19/2014 7:21 AM, Tom wrote:<br>
</div>
<blockquote cite="mid:000301cf8bc1$6f7fa8e0$4e7efaa0$@net"
type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=ISO-8859-1">
<meta name="Generator" content="Microsoft Word 12 (filtered
medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Helvetica;
panose-1:2 11 6 4 2 2 2 2 2 4;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0in;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<div
style="mso-element:para-border-div;border:none;border-bottom:solid
windowtext 1.0pt;padding:0in 0in 1.0pt 0in">
<p class="MsoNormal" style="border:none;padding:0in">Hi Jeff,<o:p></o:p></p>
<p class="MsoNormal" style="border:none;padding:0in"><o:p> </o:p></p>
<p class="MsoNormal" style="border:none;padding:0in">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:<o:p></o:p></p>
<p class="MsoNormal" style="border:none;padding:0in"><o:p> </o:p></p>
<p class="MsoNormal" style="border:none;padding:0in">[Thu Jun
19 09:00:47 2014].144000 msPOSTGRESQLJoinClose() already
close or never opened.<o:p></o:p></p>
<p class="MsoNormal" style="border:none;padding:0in"><o:p> </o:p></p>
<p class="MsoNormal" style="border:none;padding:0in">Even if I
did get that working, where would I put my GROUP BY sql
query? In the TABLE attribute?<o:p></o:p></p>
<p class="MsoNormal" style="border:none;padding:0in"><o:p> </o:p></p>
<p class="MsoNormal" style="border:none;padding:0in">Thanks!<o:p></o:p></p>
<p class="MsoNormal" style="border:none;padding:0in"><o:p> </o:p></p>
<p class="MsoNormal" style="border:none;padding:0in">Tom<o:p></o:p></p>
<p class="MsoNormal" style="border:none;padding:0in"><o:p> </o:p></p>
<p class="MsoNormal" style="border:none;padding:0in"><o:p> </o:p></p>
<p class="MsoNormal" style="border:none;padding:0in"><o:p> </o:p></p>
</div>
<p class="MsoNormal"><span
style="font-size:9.0pt;font-family:"Helvetica","sans-serif";color:#333333"><br>
Date: Wed, 18 Jun 2014 22:01:47 -0300<br>
From: Jeff McKenna <a class="moz-txt-link-rfc2396E" href="mailto:jmckenna@gatewaygeomatics.com"><jmckenna@gatewaygeomatics.com></a><br>
To: <a class="moz-txt-link-abbreviated" href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a><br>
Subject: Re: [mapserver-users] Grouping Postgres and join
with<br>
Shapefile<br>
Message-ID: <a class="moz-txt-link-rfc2396E" href="mailto:53A2367B.2060608@gatewaygeomatics.com"><53A2367B.2060608@gatewaygeomatics.com></a><br>
Content-Type: text/plain; charset=ISO-8859-1<br>
<br>
Are you aware of the JOIN docs, with an example of how to
join shp to<br>
Postgres?<br>
<a class="moz-txt-link-freetext" href="http://www.mapserver.org/mapfile/join.html#example-2-join-from-shape-dataset-to-postgresql-table">http://www.mapserver.org/mapfile/join.html#example-2-join-from-shape-dataset-to-postgresql-table</a><br>
I created that example years ago but it is still valid.<br>
<br>
-jeff</span><o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
mapserver-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/mapserver-users">http://lists.osgeo.org/mailman/listinfo/mapserver-users</a></pre>
</blockquote>
<br>
</body>
</html>