SQL-Server and MapServer

[listuser HH]

Stefanie Weykam wrote:

>Hi list members,
>Does SQL-Server have a spatial extension? How does it perform?? (..compared
>to PostGIS?)
>
>I have talked with a couple of biologists who do not use GIS (yet) but
>maintain a nice database with a lot of interesting spatial data which they
>would like to serve as maps on Internet. The database will be very
>frequently updated and the application will include spatial queries.  
>
>These guys have come from Access and ADO to SQL-Server using Visual Basic
>and .net and seem to be quite happy with it. So, -as far as  possible- I
>would like to avoid making them change their familiar environment. Although,
>their database design is still under development, so this might as well be
>the chance. 
>
>After a quick look at the archive, google and the SQL-Server home page, I
>haven't found many references (some on TerraLib and little else). 
>I suppose the few hits indicate that there is nothing for SQL-Server that
>comes close to PostGIS. Could somebody provide me with some good pro's and
>con's on SQL-Server, please?
>
>Thanks in advance,
>Stefanie
>
>
>  
>
Hi,

if you use the OVRT:

http://www.gdal.org/ogr/drv_vrt.html

like Joost suggested you can also use line and polygon geometry. This is 
done with WKT. You will need a column for geometries which can use a 
varchar datatype. I don't know SQL Server but the string is the limit 
for points in one feature so take care of the length (number of points). 
WKT look like e.g. for a line LINESTRING('x1 y1,x2 y2,x3 y3,...xn yn'). 
You now need a function which creates that string form all the points in 
a geometry to fill the column.

You can create a .ovf file:

<OGRVRTDataSource>
    <OGRVRTLayer name="mylayer">
        <SrcDataSource>ODBC:**user**/**pwd**@**DNS name**,**table**</SrcDataSource>
 	<SrcSQL>select **geom_column** from **table**</SrcSQL>
	<GeometryType>wkbLineString</GeometryType>
	<LayerSRS>EPSG:**epsg_code**</LayerSRS>
	<GeometryField encoding="WKT" field="**geom_column**"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

and in the mapfile:

LAYER
    NAME         "WKT_line_layer"
    CONNECTION "/usr/local/umn_mapserver_dir/mapfiles/test_ora2.ovf"
    CONNECTIONTYPE OGR
    DATA "mylayer"
...

About the performance etc. I can't give you a hint, sorry - but you get the geometry diplayed. I think very important is the amount of features which you will display. I only used it for some single features and this worked good. Perhaps Frank Warmerdam can tell a bit more also for the con's on the usage of WKT in OGR.

Regards,
Norbert