MS SQL SPATIAL- MAP SERVER FAILURE CONNECTION

Topics: Developer Forum, Project Management Forum, User Forum
May 16, 2007 at 10:40 PM
Hi, i'm carlos, and recently i start use MS SQL SPATIAL, before i always shapefiles as my primary source for mapserver, now I'm trying to generate a map using Mapserver and MS SQL Spatial... i try to make
a connection with ODBC Virtual Directory (.ovf)... i follow the instructions presented
in mapserver and mssqlspatial discussions and forums, but nothing seems to work properly
1. I create a system dsn, using Sql native cliente (SPATIALSQL)
2. Later i tested using ogrinfo

C: \> ogrinfo ODBC: spatialsql

That give a result a list of the all tables in the database...

Later i tested this:

C: \> ogrinfo ODBC: spatialsql parque_pt

the result is a list with all the data in the table...

3. I create an ovf file to make a call to database and put into a new folder in my ms4w installation
C:\ms4w\apps\metro\DBCONNECTION\virtual.ovf
virtual.ovf
##################
<OGRVRTDataSource>
<OGRVRTLayer name="parque">
<SrcDataSource>ODBC:@spatialsql</SrcDataSource>
<SrcLayer>parque_pt</SrcLayer>
<GeometryField encoding="WKB" field="the_geom"/>
</OGRVRTLayer>
</OGRVRTDataSource>
###############################

4. I run the ogrinfo to test the virtual.ovf
This is the result...

C:\> ogrinfo virtual.ovf
FAILURE:
Unable to open datasource `virtual.ovf' with the following drivers.
-> ESRI Shapefile
-> MapInfo File
-> UK .NTF
-> SDTS
-> TIGER
-> S57
-> DGN
-> VRT
-> AVCBin
-> REC
-> Memory
-> CSV
-> GML
-> KML
-> SQLite
-> ODBC
-> PGeo
-> PostgreSQL
-> MySQL

This is my .map....
MAP
NAME "METROTEST"
ANGLE 0
IMAGETYPE PNG
EXTENT -79.64 8.89 -79.29 9.17
UNITS METERS
SIZE 900 500
SHAPEPATH "../DBCONNECTION/virtual.ovf"
SYMBOLSET "../etc/symbols.sym"
FONTSET "../etc/fonts.txt"
IMAGECOLOR 222 254 251
TRANSPARENT Off
RESOLUTION 100

WEB
TEMPLATE 'C:/ms4w/apps/metro/template/main.html'
IMAGEPATH 'C:/ms4w/tmp/ms_tmp/'
IMAGEURL '/ms_tmp/'
METADATA
wms_title "Servidor de la Ciudad de Panamá"
wms_onlineresource "http://localhost/cgi-bin/mapserv.exe?map=/ms4w/apps/metro/map/metrotest.map&mode=map"
wms_srs "EPSG:4326 EPGS:32617"
wms_abstract "Este es un servicio de información geoespacial de la ciudad de Panamá. El mismo incluye cartografía base (calles, área urbana, lagos, zonas verdes, aeropuerto) para generar un mapa a escalas 1:1000k a 1:50k. Los datos fueron proporcionados por Geobis Internacional."
END
END

OUTPUTFORMAT
NAME png
DRIVER "GD/PNG"
MIMETYPE "image/png"
IMAGEMODE PC256
EXTENSION "png"
END

PROJECTION
"init=epsg:4326"
END

LEGEND
KEYSIZE 10 10
IMAGECOLOR 255 255 255
STATUS embed
POSTLABELCACHE TRUE
POSITION LL
LABEL
TYPE TRUETYPE
FONT ARIAL
SIZE 8
COLOR 0 0 89
END
END

LAYER
NAME "parques"
TYPE POINT
CONNECTIONTYPE OGR
CONNECTION "C:/ms4w/apps/metro/DBCONNECTION/virtual.ovf"
DATA "parque"
STATUS DEFAULT
UNITS METERS
SIZEUNITS PIXELS
TOLERANCE 0
TOLERANCEUNITS PIXELS
END
END

CLASS
NAME "parks"
STYLE
COLOR 255 0 0
SIZE 6
SYMBOL "star"
END
END
END

When i call from browser, mapserver give me this message...

msDrawMap(): Image handling error. Failed to draw layer named 'parques'.
msOGRFileOpen(): OGR error. Open failed for OGR connection `C:/ms4w/apps/metro/DBCONNECTION/virtual.ovf'. File not found or unsupported format.

I have look through several forums and list and seems that's a very common problem, altougth i haven't found a solution yet... so any help it's
welcome...
i'm working with MS Sql Server 2005 y MS Sql spatial, Apache and ms4w version 2.2.2

Thanxs a lot


Coordinator
May 17, 2007 at 3:40 AM
Did you try, instead of:
 <SrcDataSource>ODBC:@spatialsql</SrcDataSource>
...using:
<SrcDataSource>ODBC:username/password@spatialsql</SrcDataSource>
...?

Best regards,
Ricardo Stuven.
May 17, 2007 at 3:07 PM
Hi Ricardo!

Well actually i tried that solution, but it doesn't seems to work, maybe because i'm using windows authentication for my connection with the server (is in my own machine)... so, for me it's a problem that mapserver doesn't seem to know where the .ovf file it's... (C:\ms4w\apps\metro\DBCONNECTION\virtual.ovf)... i will continue trying and i tell you if found something that works...
Thanxs again

Carlos Gordon
Sep 12, 2007 at 9:54 PM
Hello guys!
(Ricardo pointed me to this thread)

What I did (and works now)

Step 1:
Install the SQL server NATIVE ODBC driver which can be found for your platform x86, 64bit at:
http://www.microsoft.com/downloads/details.aspx?FamilyId=50b97994-8453-4998-8226-fa42ec403d17&DisplayLang=en#snac
browse down and download.

Step 2: Create a ODBC System DSN under windows via START->Configuration->System->Datasources(ODBC). Test this driver to see if it can connect.

Step 3: enter <SrcDataSource>ODBC:TheNameOfMyNativeClientDriversDSNName</SrcDataSource>

And this time it will probably run!

More can be found at: http://lists.umn.edu/cgi-bin/wa?A2=ind0611&L=mapserver-users&P=48876

Good luck and post questions if you need more help.