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

[Daniel Morissette]

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