[mapserver-users] Re: ODBC OGR MS SQL CONNECTION

[Frank Warmerdam]

Robert Sanson wrote:
> Hi Frank
>  
> I have an OpenLayers / Mapserver system, where some of the data comes 
> from a SQL-Server database via OGR. Trying to understand where a FILTER 
> condition is applied. Here is a snippet from my Mapfile:
>  
> LAYER
>   # name of layer
>   NAME "apiariestm"
>   TYPE POINT
>   UNITS METERS
>   # actual data pointer
>   CONNECTION "./apiariestm.ovf"
>   CONNECTIONTYPE OGR
>   DATA "apiariestm"
>   FILTER "WHERE apiary_id = '%apiaryid%'"
>   STATUS ON
>  
> Here is the OGR .ovf file:
>  
> <OGRVRTDataSource>
>         <OGRVRTLayer name="apiariestm">
>         <SrcDataSource>ODBC:user/passwd at APIARY</SrcDataSource>
>         <SrcLayer>vw_ogr_apiary_layer</SrcLayer>
>         <GeometryType>wkbPoint</GeometryType>
>         <LayerSRS>EPSG:2193</LayerSRS>
>         <GeometryField encoding="PointFromColumns" x="x_nztm" y="y_nztm"/>
>         <FID>apiary_id</FID>
>         </OGRVRTLayer>
> </OGRVRTDataSource>
>  
> The SrcLayer is a view.
>  
> Does the filter get applied at the Mapserver end, i.e. all the points in 
> the map window get retrieved, and then the filter is applied, or does 
> Mapserver send the filter through to OGR?

Robert,

A few (1-4) years ago we modified the OGR connection type to push the filter
into OGR if the filter starts with "WHERE ".  So in your case it should get
pushed all the way through to SQL Server which is where you would want it.

If you can run your map via "shp2img" you could do the following:

set CPL_DEBUG=ON
shp2img -m yourmap -o out.png

and you should see a bunch GDAL/OGR debug output in the terminal, including
(hopefully) the actual SQL statement issued against SQL Server.

Best regards,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent