[mapserver-users] Mapserver WFS - ODBC RDBMS - GEOMETRY_COLUMNS andSPATIAL_REF_SYS Tables

Fri Mar 2 13:55:23 EST 2012

Arnd,

I haven't been testing on 5.6.1 but have finally got around to upgrading to
6.0.2 where I noticed some issues with a point layer WFS using ovf files to
connect to a Microsoft Access database. I too noticed the spatial filter
being much larger than that requested which is the reason why I started
looking at connecting in a different way. No matter what I did, all 8,000
records in my database were returned. I have tested both connection types
again and the logs show that the correct spacial filter is being applied. I
was tearing my hair out with this one but I know how I sorted it: That
particular layer had "TOLERANCE 20". I changed that to "TOLERANCE 0" and the
correct spacial filter was applied:

"TOLERANCE 0" - "Setting spatial filter to 258416.000000 665829.000000
258665.000000 666035.000000"
"TOLERANCE 20" - "Setting spatial filter to 176141.752508 583554.752508
340939.247492 748309.247492"

Changing the "TOLERANCE" affects the spatial filter as expected but ...

I have "TOLERANCEUNITS pixels" so the difference in the two spacial filters
above should be minimal. The actual difference is 82275 metres (51 miles or
44 nautical miles approx) added on both the x and y axis.

TOLERANCE UNITS can be
[pixels|feet|inches|kilometers|meters|miles|nauticalmiles|dd]

"feet" works approximately
"inches" works pretty accurately
"kilometers" accurately
"meters" works accurately
"miles" works accurately
I never checked "nauticalmiles" or "dd" (don't know what that is!)

Could this be a Mapserver bug with "pixels"?

Anyway, back to the subject ...

You wrote:
-------
Omitting the geometry column 
CONNECTION 'ODBC:GEO,GEOPOINTS'
and renaming the table GEOMETRY_COLUMNS in the MDB,
-------

I'm not clear on whether or not you had the GEOMETRY_COLUMNS table working
prior to you renaming it. If you have an access database with a working
GEOMETRY_COLUMNS table then I would be very grateful if you could detail the
structure for me. I have never had this working in 6.0.2 and I have never
used this type of connection with previous versions. That's the reason why I
am not sure if it's something I'm doing or maybe a buggy bit of code in
either Mapserver or GDAL ODBC RDBMS.

There's another issue with this type of connection: If I create indexes on
the XMIN, YMIN, XMAX, YMAX columns, as would be reasonable given that
they're being searched, I get an empty response as follows but no error in
the error log other than "Returning MS_DONE (no more shapes)":

<gml:boundedBy>
	<gml:Null>missing</gml:Null>
</gml:boundedBy>

Many thanks.

Regards,

Donald

-----Original Message-----
From: Arnd Wippermann [mailto:arnd.wippermann at web.de] 
Sent: 02 March 2012 17:09
To: 'Donald Kerr'
Cc: mapserver-users at lists.osgeo.org
Subject: AW: [mapserver-users] Mapserver WFS - ODBC RDBMS - GEOMETRY_COLUMNS
andSPATIAL_REF_SYS Tables

Hi,

It seems, that the table GEOMETRY_COLUMNS is not used,
if the geometry column is declared like
CONNECTION 'ODBC:GEO,GEOPOINTS(theGeom)'

Omitting the geometry column 
CONNECTION 'ODBC:GEO,GEOPOINTS'

and renaming the table GEOMETRY_COLUMNS in the MDB, 
gets a different response for MapServer 5.6.1 and 6.0.1.

MapServer 5.6.1 raises an error
OGR error. Pointer 'hGeom' is NULL in 'OGR_G_GetGeometryType'.

MapServer 6.0.1 loops through all features
Rejecting feature (shapeid = 511, tileid=0) of incompatible type for this
layer and returns an empty result

To get the MapServer request use DEBUG in the mapfile

CONFIG "MS_ERRORFILE" "GEOPOINTS.log"
DEBUG 5 #on map and layer level

Then you should get a likewise entry
[Fri Mar 02 17:20:31 2012].156000 msOGRFileWhichShapes: Setting spatial
filter to 2577984.190625 5679829.870625 2579058.409375 5680904.089375

Funny thing with MapServer 6.0.x
spatial filter used to request the features in the logfile 
is much larger (5843.071787m on every side) than the BBOX 
from the request, what results also in a larger feature set.

MapServer 5.6.1 uses a spatial filter the same 
as the BBOX from the request and the response shows
only features for this extent.

Arnd

-----Ursprüngliche Nachricht-----
Von: mapserver-users-bounces at lists.osgeo.org
[mailto:mapserver-users-bounces at lists.osgeo.org] Im Auftrag von Donald Kerr
Gesendet: Donnerstag, 1. März 2012 23:49
An: mapserver-users at lists.osgeo.org
Betreff: [mapserver-users] Mapserver WFS - ODBC RDBMS - GEOMETRY_COLUMNS
andSPATIAL_REF_SYS Tables

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

_______________________________________________
mapserver-users mailing list
mapserver-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/mapserver-users