[ms4w-users] OGR VRT datasource with SrcSQL clause resulting in empty layer

[Andre van Atten]

Sam,

You will learn that some documented features do not work, and some
non-documented do!
I did not succeed in getting SrcSQL working in an .ovf file. Instead, I
use to call a view in the database. The view contains the SQL string
that you want to use.

So syntax will be:
<SrcDataSource>ODBC:@datasourcename,ViewName</SrcDataSource>
<SrcLayer>Viewname</SrcLayer>
<FID>StationID</FID>

Don't forget to use a FID (FeatureID), this has to be a column in the
view with an unique key to be able to search and find.
Be sure there are no spaces between datasourcename,ViewName. It won't
work

So this option with views works fine, but you need a view definition in
the database, and it is much more handsome to use a SQL string instead
of a view: no need to have DBA CREATE VIEW rights.  

As a work-around you can try to use a filter in the mapfile:

FILTER "WHERE NOT (Company IS NULL )"

Problem is that the filter is applied on the map only: You only see the
filtered points. Querying or searching the datasource will not use the
filter, so a lot of items are found in the database which could not be
found on the map. 

If I try to use the SQL clause in the DATA tag, I do get the right
layer, but searching results in a PHP crash:
DATA "SELECT * FROM TableName WHERE CompanyNo=23"

I also tried to use the PEAR definition in the Metadata with
XYLAYER_PROPERTIES, and use SQL (see @X>0) but did not get it working on
ODBC:

METADATA
  "DESCRIPTION"    "MyPointLayer"
  #|--- IN METADATA --" |--- PEAR DB definition string ----------||- DB
table - at - Filter -||coordinate fields  
  "XYLAYER_PROPERTIES"
"odbc://'':''/localhost@Ms4w_db||PointTable@X>0||X,Y"
END  

I didn't find a satisfying solution till now, so if anyone has a hint...
Please tell !
I am using Pmapper with MS4W 1.5.4, and I'm waiting for a new package
with the new 4.10 MapServer and MS4W 2.1 version with a correct PEAR
implementation. A new add-on package is available to upgrade ms4w 1.5.5
and 2.1 to use mapserver 4.10.0-beta2. Maybe then all functionality in
PEAR and OGR is linked correctly an can be used, I haven't been testing
yet.

A sample of what I would like to be working correctly is the use of
wkbLineString instead of wkbPoint: (Draw a layer directly from a
database view, with lines between two points, where the LINESTRING field
is for example: 
LINESTRING (X1 Y1 , X2 Y2)

<OGRVRTDataSource>
        <OGRVRTLayer name="LayerTopologyLINESTRING">
        <SrcDataSource>ODBC:@Datacsourcename</SrcDataSource>
        <SrcLayer>LayerTopologyLINESTRING</SrcLayer>
        <GeometryField encoding="WKB" field="LINESTRING"/>
        <GeometryType>wkbLineString</GeometryType>
        </OGRVRTLayer>
</OGRVRTDataSource>

So it seems the wkb-functionality is not always linked with all it's
rich functionality into MapServer. OGRINFO displays correct results then
however, but OGRINFO is not MapServer.
Because I am a typical user and not a developper I am not able to
pinpoint the problem in the code.  

Andre van Atten


-----Oorspronkelijk bericht-----
Van: Lawrance, Samuel (RTCA)
[mailto:Samuel.Lawrance at rtca.riotinto.com.au] 
Verzonden: donderdag 7 september 2006 1:55
Aan: Andre van Atten
Onderwerp: Re: [ms4w-users] OGR VRT datasource with SrcSQL clause
resulting in empty layer

Hi Andre,

Andre van Atten wrote:

>Hi list,
>
>I use MS4W 4.8.3 (using php_mapscript_48.dll ) on Windows200SP4 with
Apache and OGR, and experimenting with virtual Spatial data (point
layers on X,Y pairs in a SQL server 2000 database) I am confronted with
the following problem:
>If I use the SrcSQL option in a .OVF file and if I test it with OGRINFO
a get a nice result with about 500 points. As soon as I use the layer in
a mapfile this results in an empty layer: No error is reported, but no
symbols are shown and no points are selectable.
>
>I use the following OVF syntax:
>
><OGRVRTDataSource>
>        <OGRVRTLayer name="tb_StationDB">
>        <SrcDataSource>ODBC:@datasourcename</SrcDataSource>
>        <SrcSQL>SELECT * FROM tb_Station WHERE CompanyNo = 1</SrcSQL>
>        <GeometryField encoding="PointFromColumns" x="X" y="Y"/>
>        <FID>StationId</FID>
>        <GeometryType>wkbPoint</GeometryType>
>        </OGRVRTLayer>
></OGRVRTDataSource>
>
>As soon as I use the SrcLayer option instead (on the entire table)
there are no problems and all points are shown

I have the exact same problem with MS4W and a virtual spatial data
source using SQL Server/ODBC.  SrcLayer works fine; but SrcSQL only
works with ogrinfo - mapserver draws a blank layer.  Did you ever figure
out what the problem was?

Thanks
Sam