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

[Daniel Morissette]

Actually,

The example that Brent provided here is exactly the way I was suggesting 
you to do.

Essentially you would setup your layer with CONNECTIONTYPE OGR, set the 
primary CONNECTION string to point to your shapefile, and use the DATA 
statement to provide the SQL statement built with "ogrinfo" that joins 
the shapefile and the MSSQL data.

Your SQL statement should look like the one in the example provided by 
Brent, except that you'll replace the ODBC connection string with your 
MSSQL connection string. Once again, testing the SQL / JOIN statement 
with ogrinfo will make your life much easier.

Makes sense?

Daniel


On 14-01-08 5:49 PM, Brent Fraser wrote:
> 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
>>
>
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users


-- 
Daniel Morissette
http://www.mapgears.com/
Provider of Professional MapServer Support since 2000