[QGIS-Developer] Strange Mysql (spatial) behaviour: no points visible

[Even Rouault]

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.