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

[Daniel Morissette]

Oh, and BTW, assuming your MSSQL connection string is 
"MSSQL:server=myserver\myinstance;database=dbname;UID=uid;PWD=uid;" and 
the layer name (as seen by ogrinfo) is "mytable" then the layer defn and 
SQL statement should look like this:

CONNECTIONTYPE OGR
CONNECTION "/path/to/myshapefile.shp"
DATA "SELECT myshapefile.*, mytable.* FROM myshapefile
         LEFT JOIN 
'MSSQL:server=myserver\myinstance;database=dbname;UID=uid;PWD=uid;'.mytable 
mytable
         ON myshapefile.key=mytable.key"


Daniel


On 14-01-09 9:49 AM, Daniel Morissette wrote:
> Errr... Please forgive the poor wording of my last response, my brain
> isn't finished booting up yet.
>
>
>
> On 14-01-09 9:47 AM, Daniel Morissette wrote:
>> 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