[mapserver-users] Mapserver WFS - ODBC RDBMS - GEOMETRY_COLUMNS andSPATIAL_REF_SYS Tables
Donald Kerr
donald.kerr at dkerr.co.uk
Fri Mar 2 14:06:42 PST 2012
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.wippermann at web.de]
Sent: 02 March 2012 20:51
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,
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
-----Original Message-----
From: Arnd Wippermann [mailto:arnd.wippermann at web.de]
Sent: 02 March 2012 20:51
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,
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
More information about the MapServer-users
mailing list