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

[Even Rouault]

Update: actually, instead of completely disabling server side spatial 
filtering with geographic SRS, I just found a hack... One can use 
ST_SRID() to hack a "random" projected CRS, and then things work

So now the OGR mySQL driver will do things like:

select MBRIntersects(ST_GeomFromText('POLYGON((-179 -89, 179 -89, 179 
89, -179 89, -179 -89))', 32631), ST_SRID(geometry_column, 32631));

Not pretty but probably a bit better than just client side filtering.

On reflection, I suspect that in MySQL the geometry in geographic SRS 
are actually what is called a geography in PostGIS, that is a shape on 
the ellipsoid/sphere, where the smallest polygon is selected. And thus 
the following request actually returns true:

select MBRIntersects(ST_GeomFromText('POLYGON((-179 -89, 179 -89, 179 
89, -179 89, -179 -89))', 4326, 'axis-order=long-lat'), 
ST_GeomFromText('POINT(-179.5 0)', 4326, 'axis-order=long-lat'));


Le 28/01/2024 à 19:04, Even Rouault via QGIS-Developer a écrit :
> 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.