[mapserver-users] Grouping Postgres and join with

[Brent Fraser]

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 at 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 <jmckenna at gatewaygeomatics.com>
> To: mapserver-users at lists.osgeo.org
> Subject: Re: [mapserver-users] Grouping Postgres and join with
> Shapefile
> Message-ID: <53A2367B.2060608 at 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 at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20140620/809bc94e/attachment-0001.html>