SQL-Server and MapServer

[Van Ulden, Joost]

Hi Stefanie,

Microsoft SQL-Server does not have a spatial extension.  However, you can
access point data quite easily using OGR.

Here are some code snippets that you can experiment with:

############################################################################
############
#
# CONNECTION SNIPPETS
#
# Usage: Replace all [ ] elements with your connection information.
#
############################################################################
############

#---------------------------------------------------------------------------
------------
# Microsoft SQL Server
#---------------------------------------------------------------------------
------------

 LAYER
  NAME [insert your layer name without spaces (eg. my_layer)]
  TYPE POINT
  CONNECTIONTYPE OGR
  CONNECTION 
  
  '<OGRVRTDataSource>
       <OGRVRTLayer name="[insert your layer name]">
        <SrcDataSource>ODBC:[insert user_name]/[insert password]@[insert dsn
name],[insert table name]</SrcDataSource> 
         <SrcLayer>[insert table name]</SrcLayer> # OR <SrcSQL>[insert your
sql statement (eg. SELECT Longitude,Latitude,... FROM MyTable)]</SrcSQL>
        <GeometryType>wkbPoint</GeometryType> 
        <LayerSRS>WGS84</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="[insert field name
(eg. Longitude)]" y="[insert field name (eg. Latitude)]"/> 
      </OGRVRTLayer>
  </OGRVRTDataSource>'

  DUMP TRUE # for WFS only
  DATA "[insert your layer name]"
  STATUS default
  CLASS
    SYMBOL 'circle'
    SIZE 25
    COLOR 255 0 0
  END
 END

#---------------------------------------------------------------------------
------------
# FileMaker
#---------------------------------------------------------------------------
------------

 LAYER
  NAME [insert your layer name without spaces (eg. my_layer)]
  TYPE POINT
  CONNECTIONTYPE OGR
  CONNECTION 
  
  '<OGRVRTDataSource>
     <OGRVRTLayer name="[insert your layer name]">
       <SrcDataSource>ODBC:[insert your dsn name],[insert your database
name]</SrcDataSource> 
       <GeometryType>wkbPoint</GeometryType> 
       <SrcSQL>[insert your sql statement (eg. SELECT Longitude,Latitude,...
FROM Earthquake)]</SrcSQL>
       <GeometryField encoding="PointFromColumns" x="[insert field name (eg.
Longitude)]" y="[insert field name (eg. Latitude)]"/> 
     </OGRVRTLayer>
  </OGRVRTDataSource>'

  DUMP TRUE # for WFS only
  DATA "[insert your layer name]"
  STATUS default
  CLASS
    SYMBOL 'circle'
    SIZE 25
    COLOR 255 0 0
  END
 END

#---------------------------------------------------------------------------
------------
# Microsoft Access
#---------------------------------------------------------------------------
------------

 LAYER
  NAME [insert your layer name without spaces (eg. my_layer)]
  TYPE POINT
  CONNECTIONTYPE OGR
  CONNECTION 
  
  '<OGRVRTDataSource>
       <OGRVRTLayer name="[insert your layer name]">
        <SrcDataSource>ODBC:[insert your dsn name],[insert your table
name]</SrcDataSource> 
        <SrcLayer><insert your table name></SrcLayer> 
        <GeometryType>wkbPoint</GeometryType> 
        <GeometryField encoding="PointFromColumns" x="[insert field name
(eg. Longitude)]" y="[insert field name (eg. Latitude)]"/> 
      </OGRVRTLayer>
  </OGRVRTDataSource>'

  DUMP TRUE # for WFS only
  DATA "[insert your layer name]"
  STATUS default
  CLASS
    SYMBOL 'circle'
    SIZE 25
    COLOR 255 0 0
  END
 END

#---------------------------------------------------------------------------
------------
# GML
#---------------------------------------------------------------------------
------------

 LAYER
  NAME [insert your layer name without spaces (eg. my_layer)]
  TYPE POINT
  CONNECTIONTYPE OGR
  CONNECTION '[insert your directory path here]\[insert your filename
here].gml'
  
  DUMP TRUE # for WFS only
  DATA "[insert the featureType here (eg. point | line | polygon)]"
  STATUS default
  CLASS
    SYMBOL 'circle'
    SIZE 25
    COLOR 255 0 0
  END
 END
 

-----Original Message-----
From: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU] On
Behalf Of Stefanie Weykam
Sent: Wednesday, November 02, 2005 10:55 AM
To: MAPSERVER-USERS at LISTS.UMN.EDU
Subject: [UMN_MAPSERVER-USERS] SQL-Server and MapServer

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