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

[Arnd Wippermann]

Hi,

i can confirm, that TOLERANCE and TOLERANCEUNITS 
changes the spatial filter MapServer 6.0.x applies.

MapServer 5.6.1 takes no notice of that.
BBOX and applied spatial filter are the same.

Because I have small datasets (~500-1000 rows), 
I haven't noticed the strange behaviour of Version 6.


Below some combinations I have tested to get an idea
how to configure ogr ODBC. 1-3 are OK, 4 fails.


1 #############################################
  
  CONNECTIONTYPE OGR                              
  CONNECTION 'ODBC:GEOOBJEKT,GEOPOINTS(POSITION)'
  DATA GEOPOINTS

  no table GEOMETRY_COLUMNS in MDB

2 #############################################

  CONNECTIONTYPE OGR                              
  CONNECTION 'ODBC:GEOOBJEKT,GEOPOINTS'
  #DATA GEOPOINTS

  table GEOMETRY_COLUMNS in MDB

3 #############################################

  CONNECTIONTYPE OGR                              
  CONNECTION 'ODBC:GEOOBJEKT,GEOPOINTS(POSITION)'
  DATA GEOPOINTS

  table GEOMETRY_COLUMNS in MDB

  1-3 correct results

4 ###############################################

  CONNECTIONTYPE OGR
  CONNECTION 'ODBC:GEOOBJEKT,GEOPOINTS(POSITION)'
  #DATA GEOPOINTS

  table GEOMETRY_COLUMNS in MDB

  !!! this fails :: OGR error. GetLayer(GEOPOINTS(POSITION)) failed ...

###############################################

To check, if the spatial filter is used to get the data
from the Access MDB, it's possible under Windows to log
the ODBC connection.

That are the interesting parts

SELECT f_table_name, f_geometry_column, geometry_type FROM geometry_columns
SELECT * FROM GEOPOINTS WHERE XMAX > 2571258.49071369 AND XMIN <
2586698.54053631 AND YMAX > 5673184.27196369 AND YMIN < 5687253.22803631

The spatial filter is used, if the fields XMIN, YMIN, XMAX and YMAX in the
queried table exists.


The structure of my access tables are the same as yours.
If your ODBC connection not working, it's possibly an issue
with setting the DNS.


Arnd
 

-----Ursprüngliche Nachricht-----
Von: Donald Kerr [mailto:donald.kerr at dkerr.co.uk] 
Gesendet: Freitag, 2. März 2012 19:55
An: 'Arnd Wippermann'
Cc: mapserver-users at lists.osgeo.org
Betreff: RE: [mapserver-users] Mapserver WFS - ODBC RDBMS - GEOMETRY_COLUMNS
andSPATIAL_REF_SYS Tables

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