Richard,
what I don't understand is how you manage to create with ogr2ogr a table
without explicit SRS with features whose coordinates are not compatible
of geographic coordinates. As mentioned previously, I do get a hard
error from ogr2ogr when trying that. Which GDAL version do you use?
The reason for which QGIS doesn't display features is likely it calls
OGR with SetSpatialFilter() , which the mySQL driver turns into a WHERE
MBRIntersects(ST_GeomFromText('POLYGON((.....))',
'axis-order=long-lat'), `geom_colum_name`) filter. The
'axis-order=long-lat' qualifier is because OGR (perhaps mistakenly?)
identifies the layer as with a geographic SRS, and if the extents of the
layer aren't compatible of geographic coordinates, that probably causes
the filter to fail.
Actually trying the following on a layer without explicit CRS, which
then causes a undefined geographic CRS to be assigned to the layer
$ ogrinfo
"mysql:test,user=root,password=passwd,port=33060,host=127.0.0.1" test
-spat -180 -90 180 90
I do get:
ERROR 1: MySQL error message:Longitude -180.000000 is out of range in
function st_geomfromtext. It must be within (-180.000000, 180.000000].
Description: SELECT `OGR_FID`, `SHAPE` `SHAPE`, `id` FROM `test` WHERE
MBRIntersects(ST_GeomFromText('POLYGON((-180 -90, 180 -90, 180 90, -180
90, -180 -90))', 32767, 'axis-order=long-lat'), `SHAPE`)
So MySQL is super picky on the range of values for spatial filters. OK,
fair enough, easy to fix by clamping values on the driver side.
But!!! I also discovered what is to me a MySQL non-sense. It seems
spatial predicates are totally broken when using geographic SRS.
select MBRIntersects(ST_GeomFromText('POLYGON((-90 -90, 90 -90, 90 90,
-90 90, -90 -90))', 4326), ST_GeomFromText('POINT(0 0)', 4326));
returns true as expected
But enlarging that extent to almost the whole world with
select MBRIntersects(ST_GeomFromText('POLYGON((-179 -89, 179 -89, 179
89, -179 89, -179 -89))', 4326, 'axis-order=long-lat'),
ST_GeomFromText('POINT(0 0)', 4326));
or (to test if winding order has some importance)
select MBRIntersects(ST_GeomFromText('POLYGON((-179 -89, -179 89, 179
89, 179 -89, -179 -89))', 4326, 'axis-order=long-lat'),
ST_GeomFromText('POINT(0 0)', 4326));
returns false !!!! (I've also verified with mysql:8.3.0 image)
And
select MBRIntersects(ST_GeomFromText('POLYGON((-179 -89, 179 -89, 179
89, -179 89, -179 -89))', 32631), ST_GeomFromText('POINT(0 0)', 32631));
returns true as expected
Consequence, it seems safer to disable spatial filtering on layers with
geographic coordinates with MySQL...
That's just what I've done in https://github.com/OSGeo/gdal/pull/9152 .
Hopefully that will fix those issues with geometries not appearing.
Obviously performance will not be optimal as spatial filtering will be
done on OGR client side...
Even
Le 28/01/2024 à 14:52, Richard Duivenvoorde a écrit :
On 1/23/24 23:21, Even Rouault wrote:
So all in all, no issues on my side...
You were off course right :-)
I found out how to fix the missing crs in mysql and wrote some info
(for future reference) in a comment:
https://github.com/qgis/QGIS/issues/55463#issuecomment-1913602140
One thing I'm curious for, is why QGIS even after setting the right
crs, is not able to show the geom (but IS able to show the
attributes). Apparently the features are retrieved. If you receive
features from a shp file without crs, after setting it on the layer,
all is fine. So myql is different?
Did a short try to debug this, but this is above my c++ skills :-)
Anyway thanks! And I hope the comment is helpful to mysql peeps
Regards,
Richard Duivenvoorde
--
http://www.spatialys.com
My software is free, but my time generally not.
_______________________________________________
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
