[mapserver-users] Mapserver WFS - ODBC RDBMS - GEOMETRY_COLUMNS and SPATIAL_REF_SYS Tables

[Donald Kerr]

Robert,

I wasn't aware of that so thanks for pointing it out - I had read somewhere
that this method was limited to point layers only.

The problem I can see with this method is (from the notes) "When the
GeometryField is "WKT" spatial filtering is applied after extracting all
rows from the source datasource. Essentially that means there is no fast
spatial filtering on WKT derived geometries."

I need the query to use the BBox on the data to limit the records returned
at the data access stage rather than after the whole recordset is returned.
I would prefer the direct access method that uses the BBox values in the
query for that reason. I also want to get the GEOMETRY_COLUMNS table working
as is described in the docs: http://www.gdal.org/ogr/drv_odbc.html.

Many thanks.

Regards,

Donald


-----Original Message-----
From: Robert Sanson [mailto:Robert.Sanson at asurequality.com] 
Sent: 02 March 2012 00:58
To: donald.kerr at dkerr.co.uk; mapserver-users at lists.osgeo.org
Subject: Re: [mapserver-users] Mapserver WFS - ODBC RDBMS - GEOMETRY_COLUMNS
and SPATIAL_REF_SYS Tables


OVF files can link to point, line and polygon data in WKT format. Just
specify wkbPoint, wkbLinestring, wkbPolygon see:

http://www.gdal.org/ogr/drv_vrt.html

Regards,
Robert

>>> "Donald Kerr"  03/02/12 11:54 AM >>>
Hi List,

I have a point layer set up for WFS requests that connects to a Microsoft
Access database with two tables structured as follows:

Table: GEOMETRY_COLUMNS

Fields:
F_TABLE_NAME
F_GEOMETRY_COLUMN
GEOMETRY_TYPE
SRID

Values:
MyData
geom
POINT
27700

Table: MyData (Contains about 8,000 records)

Fields:
UniqueID
NameStr
Geom
XMIN
YMIN
XMAX
YMAX

Values:
1
Smiths
POINT(265377 665516)
265377
665516
265377
665516

In my layer, I have:

CONNECTIONTYPE OGR
CONNECTION "ODBC:overlays,FullOIs(geom)"
DATA "FullOIs"

A GetFeature request works well and quite speedily so I think that the the
query is run against the database is using the XMIN, YMIN, XMAX and YMAX
columns to restrict the data returned. During debugging, the following query
appeared in the response as recorded by Firebug in Firefox but I can't
replicate that (if anyone can tell me how to confirm that the BBox is being
used as follows that would be appreciated):

SELECT * FROM MyData WHERE XMAX > 176077.07205091 AND XMIN < 340988.92794909
AND YMAX > 583498.46091313 AND YMIN < 748311.53908687

The problem that I have relates to the GEOMETRY_COLUMNS table which I know
is not being used.

If I remove the geometry column from the connection string, i.e. change
CONNECTION "ODBC:overlays,FullOIs(geom)" to CONNECTION
"ODBC:overlays,FullOIs" - remove the word "(geom), it does not find the
geometry column from the GEOMETRY_COLUMNS table. It would appear that this
table is being completely ignored.

I have tried all sorts of combinations and field names and have also created
a SPATIAL_REF_SYS table with values that I use in a working PostGIS database
to no avail.

I previously used an OVF file but I think that may be limited to point
layers whereas, when I get this working as I want to, I would like to extend
this type of connection to line and polygon layers.

Can anyone advise please?

Many thanks.

Regards,

Donald