Arnd, Aha! Combination 2 works for me too and this is exactly what I'm looking for. :)
CONNECTIONTYPE OGR CONNECTION 'ODBC:GEOOBJEKT,GEOPOINTS' #DATA GEOPOINTS If I comment out the DATA keyword it uses the GEOMETRY_COLUMNS table correctly. I must have tried 999,999 combinations over the last two days and the 1,000,000th one worked! Thank you very much for your help and time. Regards, Donald -----Original Message----- From: Arnd Wippermann [mailto:arnd.wipperm...@web.de] Sent: 02 March 2012 20:51 To: 'Donald Kerr' Cc: mapserver-users@lists.osgeo.org Subject: AW: [mapserver-users] Mapserver WFS - ODBC RDBMS - GEOMETRY_COLUMNS andSPATIAL_REF_SYS Tables 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.k...@dkerr.co.uk] Gesendet: Freitag, 2. März 2012 19:55 An: 'Arnd Wippermann' Cc: mapserver-users@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 -----Original Message----- From: Arnd Wippermann [mailto:arnd.wipperm...@web.de] Sent: 02 March 2012 20:51 To: 'Donald Kerr' Cc: mapserver-users@lists.osgeo.org Subject: AW: [mapserver-users] Mapserver WFS - ODBC RDBMS - GEOMETRY_COLUMNS andSPATIAL_REF_SYS Tables 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.k...@dkerr.co.uk] Gesendet: Freitag, 2. März 2012 19:55 An: 'Arnd Wippermann' Cc: mapserver-users@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 _______________________________________________ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users