[mapserver-users] Best way to join nonspatial attribute data to spatial PostGIS data

[Kenny Heer]

Hello,
                Currently I have a shapefile that I am joining to some
non-spatial MS SQL data via the DATA parameter of my Layer as so:

                CONNECTIONTYPE OGR
                CONNECTION "C:/PATH/TO/MY /SHAPEFILE.shp"
                DATA "SELECT shapefile.*, postgistable.commonid,
postgistable.field2, postgistable.field3, postgistable.field4 FROM
shapefile LEFT JOIN
'MSSQL:server=SQLServer\instancename;database=mydbase;UID=myuid;PWD=mypw;Tables=postgistable'.postgistable
ON shapefile.commonid = postgistable.commonid"

                This works great but we want to move the data shapefile
data into postgresql. I’ve tried adapting the above by replacing the parts
referencing the shapefile to a standard PostGIS connection but that results
in an error. I can successfully add the non-spatial MS SQL data to the
layer via a JOIN, but I’m using the MS SQL data as the label for the layer
and this doesn’t work with the Join.

                Is anyone else doing something similar or have any thoughts
on how I could do this differently?

Thanks in advance for any help,

Kenneth Heer
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20160824/5bcf8786/attachment.html>