[mapserver-users] OGRVRT Data Sources and SQL server

[Robert Sanson]

Hi Chris

I am successfully using OGR with SQL-Server with SQL authentication. This is what I do:

Say I want to plot farm gates. I have a System DSN datasource set up in ODBC say called "MYDSN" with SQL Server authentication.

I create a farmgates.ovf file containing:

<OGRVRTDataSource>
        <OGRVRTLayer name="farmgates">
        <SrcDataSource>ODBC:user/passwd at MYDSN</SrcDataSource>
        <SrcLayer>vw_farmgates</SrcLayer>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>EPSG:2193</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="x_coord_nztm" y="y_coord_nztm"/>
        <FID>point_location_id</FID>
        </OGRVRTLayer>
</OGRVRTDataSource>

In my map file I have:

LAYER
  NAME "farmgates"
  GROUP "FMS"
  CONNECTION "./farmgates.ovf"
  CONNECTIONTYPE OGR
  DATA "farmgates"
  #FILTER "WHERE feature_type_id = 11 and farm_id = '%farmid%'"
  SIZEUNITS PIXELS
  STATUS ON
  DUMP TRUE
  # query templates for HTML-based queries
  # responds to OGC:WMS request=GetFeatureInfo......&info_format=text/html
  HEADER   ../templates/farmgates_query_header.html
  TEMPLATE ../templates/farmgates_query_body.html
  
  # fuzziness for querying
  TOLERANCE 5000
  TOLERANCEUNITS METERS
  TYPE POINT
  UNITS METERS
  # scale range
  MAXSCALE 250000
  MINSCALE 750
  LABELMAXSCALE 100000
  LABELMINSCALE 1
  LABELCACHE on
  LABELITEM 'reference_code'
  CLASS
    STYLE
      COLOR 138 43 226
      OUTLINECOLOR 0 0 0
      SIZE 13
      SYMBOL "triangle"
    END
    LABEL
      TYPE truetype
      FONT "arial"
      SIZE 10
      POSITION ur
      #ANGLE auto
      OUTLINECOLOR 235 235 235 #--Halo
      COLOR 0 0 0 
      #MINDISTANCE 50
      #MINFEATURESIZE 10
      BUFFER 2
      #SIZE small
    END #LABEL
  END #CLASS
    
  METADATA
    "wms_group_title" "FMS"
    "wms_name"    "farmgates" 
    "wms_title"    "farmgates"
    "wms_abstract"    "These are farmgates in NZTM"
    "wms_keywordlist"    "AgriBase"
    #"wms_server_version"    "1.1.1"
    "wms_srs"   "EPSG:2193"
    "wms_extent" "1114412.940 4793178.168 2089186.204 6190213.601"
    "wms_dataurl_format" "text/HTML"
    "gml_include_items"     "all"
    "gml_exclude_items"     ""
    "ows_include_items"     "all"
    "ows_exclude_items"     ""  
    "wms_exceptions_format" "application/vnd.ogc.se_xml"
  END
 END #farmgates

Regards,

Robert



>>> Chris Jackson <webturtles at gmail.com> 29/05/2012 3:01 a.m. >>>
Hi all

Has anyone got much experience of using OGRVRT Data Sources in Windows with
SQL server system DSNs?  I am having an issue with system DSN connection
type (windows authentication will work fine and proceses the table fine,
sql authentication - get not trusted connection (both authentication types
enabled in instance)) when testing via ogrinfo commandline.

Then if I take the working code forward then I can't render the layer in a
map output via say shp2img commandline or final map application. Map code
below:

  LAYER
    NAME "virtual"
    TYPE POINT
    CONNECTIONTYPE OGR
   CONNECTION
"<OGRVRTDataSource>

    <OGRVRTLayer
name='omreg1'>

        <SrcDataSource>ODBC:@omreg1</SrcDataSource>
        <SrcSQL>SELECT * FROM
dbo.Site</SrcSQL>

        <GeometryField encoding='PointFromColumns' x='SiteLong'
y='SiteLat'/>
        <GeometryType>wkbPoint</GeometryType>

</OGRVRTLayer>

</OGRVRTDataSource>" # the name and path to the virtual.ovf file, relative
to shapepath may work?
   DATA "omreg1"
     TEMPLATE void
     PROJECTION
       "init=epsg:4326"
      END
     METADATA
       "wms_srs"   "4326"
       "wms_title"   "virtual"
     END
        SIZEUNITS PIXELS
        STATUS ON
        TOLERANCE 0
        TOLERANCEUNITS PIXELS
        UNITS METERS
  CLASS
    NAME "Virtual"
    COLOR 255 0 0
    OUTLINECOLOR 255 0 0
    SYMBOL 'kreuz4'
    SIZE 6
  END  # Class
END

My version of mapserver is a little old (5.6.1).  I wonder whether I need
to set any extra SQL related env variables.....

Any assistance much appreciated!

Thanks
Chris


This email and any attachments are confidential and intended solely for the addressee(s). If you are not the intended recipient, please notify us immediately and then delete this email from your system.

This message has been scanned for Malware and Viruses by Websense Hosted Security.
www.websense.com