OVF Odbc connection identify erratic

[Frank Warmerdam]

Asif Ahmed wrote:
> Hi,
> 
> I am trying to implement dbox.
> 
> I am displaying some points through ovf odbc connection to a sql server
> database. The points are showing up fine but identify/ getfeatureinfo  seems
> to be returning only the first few rows in the database repeatedly no matter
> which point is clicked on.
> 
> Has anybody encountered this problem before?  I am using the latest map
> server and dbox.

Asif,

I imagine you are running into the classic "no unique identifier" problem.
The two pass query mechanism used for identify/getfeatureinfo only works
properly if features have a unique feature id.  But some OGR drivers just
set a feature id based on the order of returned records in a resultset.
When a filter is applied the subset are still just numbered 1,2,3 even though
they have different feature ids when no filter is applied.

The fix is to ensure OGR can assign a persistent feature id to records.
The OGR ODBC driver attempts to select a singular integer primary key as
the feature id if one is noted. But some ODBC drivers don't provide this
info properly.  Also, if you are using an SQL result for your query
instead of "plain table access" then this is defeated.  You can force
the OGR ODBC driver to use a particular column by setting the
ODBC_OGR_FID configuration variable but this is a blunt tool for the
job.

The other approach is to add an <FID>columnname</FID> element in your
OVF definition of the virtual layer.  This tells the OVF driver to use
a particular column for the fid.  I'd suggest you do this.

Note that MapServer queries through OVF/ODBC are somewhat fragile and
the performance can suck since it ends up doing fetches by feature
id which can be pretty slow.

In MapServer 5.2 we hope to alter the query mechanism such that it will
be one pass, resolving the fid requirement and performance issues.

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    | President OSGeo, http://osgeo.org