[mapserver-users] non spatial MSSQL data in a Layer JOIN

[Brent Fraser]

Ken,

   A few years ago (with mapserv v5.6.5) I did something similar, doing 
a join on a shapefile and a MS Access database (via ODBC) using OGR:

     CONNECTIONTYPE OGR
     CONNECTION     'T:/GeoData/Parcels/Alberta/t_505033.shp'
     DATA "SELECT * FROM t_505033 LEFT JOIN 
'ODBC:Parcels_Rural,basic'.basic ON t_505033.PID=basic.PID"

Not quite the same as your problem, but it may give you a few ideas.

Best Regards,
Brent Fraser

On 1/8/2014 2:58 PM, KenHeer1 wrote:
> Daniel,
> First off, thank you for the quick response. Been working on this and I am
> now able to connect to database and query the table in question using OGR
> without it having spatial data. Using ogrinfo I am able to send a simple
> select statement and get the records I'm looking for so that works great.
>
> So then my thought was to add the data using the connection with JOIN in the
> layer definition in my map file, something like this:
>
> JOIN
> 	NAME "myjoin"
> 	CONNECTIONTYPE OGR
> 	CONNECTION
> "MSSQL:server=myserver\myinstance;database=dbname;UID=uid;PWD=uid;"
> 	TABLE "mytable"
> 	FROM "shapefilefilekey"
> 	TO "tablefieldkey"
> 	TYPE ONE-TO-ONE
> END
>
> but that didn't work. I'm assuming that this isn't working because JOIN only
> supports DBF/XBase,CSV,PostrgreSQL, and MySQL.
>
> So after reading through your reply again, I tried joining the datasources
> using OGR SQL. Thinking maybe instead of adding a shapefile layer and then
> joining the data in the mapfile, I could join the two datasources using OGR
> SQL and add that as a layer. Following through the links you provided and
> others I found, I can query the layer all day long, but I cannot access the
> shapefile, or just the dbf, through sql to perform the join.
>
> Thanks again for the earlier help, but did I miss something or maybe
> misinterpret your suggestions?
>
>
>
>
>
> --
> View this message in context: http://osgeo-org.1560.x6.nabble.com/non-spatial-MSSQL-data-in-a-Layer-JOIN-tp5096980p5097033.html
> Sent from the Mapserver - User mailing list archive at Nabble.com.
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>