Re: [QGIS-Developer] Strange Mysql (spatial) behaviour: no points visible
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.00 is out of range in
function st_geomfromtext. It must be within (-180.00, 180.00].
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-develop
Re: [QGIS-Developer] Strange Mysql (spatial) behaviour: no points visible
But, my point: why can QGIS apparently show attributes, while not showing a geom.. Apparently the features were fully retrieved. When displaying the attribute table, QGIS doesn't apply a spatial filter. Whereas when displaying features on the canvas, it does. And as I found out, something's weird (broken?) on MySQL side regarding spatial filtering on layers without CRS (or with the implied undefined geographic SRS) -- 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
Re: [QGIS-Developer] Strange Mysql (spatial) behaviour: no points visible
On 1/28/24 19:04, Even Rouault wrote:
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?
I'm doing this on a Debian 12/Bookworm machine.
$ ogr2ogr --version
GDAL 3.6.2, released 2023/01/02
But the people who contacted me (today I found out, of
https://github.com/qgis/QGIS/issues/55463) actually create this table
theirselves, I think, without explicitly setting a crs on a column when
creating a table. Which if I understand from
https://ant.burnett.com.au/adding-spatial-column-to-existing-mysql-table/ is
the issue.
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.
But, my point: why can QGIS apparently show attributes, while not showing a
geom.. Apparently the features were fully retrieved.
I could even copy the features from the attribute-table (when not seeing them
on the MapCanvas), and copy them in another table (with made the geoms
visisble).
My hope is people, still using mysql, will learn from the comments to create
proper tables/columns.
Thanks for the fix!
Regards,
Richard Duivenvoorde
___
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
Re: [QGIS-Developer] Strange Mysql (spatial) behaviour: no points visible
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.00 is out of range in
function st_geomfromtext. It must be within (-180.00, 180.00].
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
Re: [QGIS-Developer] Strange Mysql (spatial) behaviour: no points visible
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 ___ 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
Re: [QGIS-Developer] Strange Mysql (spatial) behaviour: no points visible
Le 23/01/2024 à 20:43, Richard Duivenvoorde a écrit :
Ah, oo :-)
I can reproduce now in the docker!
After seeing
src/core/providers/ogr/qgsogrprovider.cpp:3236 : (crs) [0ms] no
spatial reference found
in the debug info of that other db, I thought to try to load the data
WITHOUT telling it EPSG:31370:
So instead of:
ogr2ogr -update
"mysql:test,user=root,password=passwd,port=33060,host=127.0.0.1"
test.csv -select id -nlt point -a_srs EPSG:31370
I removed the test table and did
ogr2ogr -update
"mysql:test,user=root,password=passwd,port=33060,host=127.0.0.1"
test.csv -select id -nlt point
This fails for me with:
ERROR 1: MySQL error message:Longitude 168937.186906 is out of range in
function st_geomfromtext. It must be within (-180.00, 180.00].
Description: INSERT INTO `test` (`SHAPE` , `id`) VALUES
(ST_GeomFromText('POINT (168937.186906043 175180.302889316)',32767,
'axis-order=long-lat') , '1')
ERROR 1: Unable to write feature 1 from layer test.
ERROR 1: Terminating translation prematurely after failed
translation of layer test (use -skipfailures to skip errors)
If you don't specify a SRS at layer creation time, the CreateLayer()
logic in the MySQL driver assigns to it a "Undefined_geographic_SRS",
and thus you must set longitude, latitude in range.
If trying with a point in range for geographic coordinates, ogr2ogr
works and QGIS also happily displays it (with unknown CRS as expected)
printf 'id,WKT\n1,"POINT(2 49)"\n' > test.csv
So all in all, no issues on my side...
--
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
Re: [QGIS-Developer] Strange Mysql (spatial) behaviour: no points visible
Ah, oo :-) I can reproduce now in the docker! After seeing src/core/providers/ogr/qgsogrprovider.cpp:3236 : (crs) [0ms] no spatial reference found in the debug info of that other db, I thought to try to load the data WITHOUT telling it EPSG:31370: So instead of: ogr2ogr -update "mysql:test,user=root,password=passwd,port=33060,host=127.0.0.1" test.csv -select id -nlt point -a_srs EPSG:31370 I removed the test table and did ogr2ogr -update "mysql:test,user=root,password=passwd,port=33060,host=127.0.0.1" test.csv -select id -nlt point THEN I see exact the behaviour you see in the db with the issue: - QGIS loads the layer (using 31370 project!), without crs (seeing the little globe with ? in it in the layer manager- - BUT if you do 'zoom to data' you zoom the the right place in the world - you can see the attributes in the attribute table - and even after assigning EPSG:31370 to the layer: NO point visible in Belgium! Note that loading the layer in a EPSG:4326 table, and assigning 31370 to the layer, QGIS does not zoom to Belgium... Is this helpfull? Or is this just a data issue? Regards, Richard Duivenvoorde On 1/23/24 13:40, Even Rouault wrote: Richard, I've failed to reproduce. Here's my testing procedure: docker run --name gdal-mysql1 -e MYSQL_ROOT_PASSWORD=passwd -e "MYSQL_ROOT_HOST=%" -p 33060:3306 -d mysql:8.0.34 mysqld --default-authentication-plugin=mysql_native_password docker exec gdal-mysql1 sh -c "echo 'CREATE DATABASE test; SELECT Version()' | mysql -uroot -ppasswd" printf 'id,WKT\n1,"POINT(168937.186906043 175180.302889316)"\n' > test.csv ogr2ogr -update "mysql:test,user=root,password=passwd,port=33060,host=127.0.0.1" test.csv -select id -nlt point -a_srs EPSG:31370 then opening it with QGIS displays the point on the canvas and the attribute table. I can also add other points. Even Le 16/01/2024 à 20:07, Richard Duivenvoorde via QGIS-Developer a écrit : No hints :-) ? I now have a live connection to the db, and can reproduce the issue here in master on Linux too. It's really strange: have a local db, I can even copy features from the attribute table to my local table, and THEN they show up ogrinfo shows exactly the same information of the db ( using -so). coordinates are fine too: select coord, ST_AsWKT(coord) from tblQgisProjecten tqp limit 1 coord |ST_AsWKT(coord) | -++ POINT (168937.186906043 175180.302889316)|POINT(168937.186906043 175180.302889316)| could it be in char encoding, or in a strange attribute value? We already tried to create a simple view with only coord and ID, but that did not show something either. As I cannot share the connection info, I'm happy to share my screen or so to show this. SELECT version(); version()| -+ 8.0.34-26| but is an upgraded db, not sure where it came from Regards, Richard Duivenvoorde On 1/12/24 15:06, Richard Duivenvoorde via QGIS-Developer wrote: Hi Devs, I was contacted by a company who after upgrading their db and QGIS did NOT see the points anymore. Note that 'all worked' when they used QGIS 3.10 (and an older mysql db) After a MySQL database upgrade, QGIS 3.10 was not able to connect anymore (apparently TLS issues), so they have to use 3.28 or higher. A small export loaded in my local db was OK in QGIS. So in a online meeting, sharing their screen I tried: - opening the db with ogrinfo: all data is visible (POINT(.)) showing correct coords and attributes - loading the table (25000 records) in QGIS: data: EPSG:31370 project EPSG:31370 : - attribute table shows all records - mapcanvas empty! - able to 'zoom to' records (Belgium), but NO points visible/selectable - creating a tiny table with only id and geom column of 3 records: mapcanvas emtpy - export the loaded (but invisible) layer to a geopackage: points AND attributes VISIBLE! - loading the data in dbeaver: all data is shown in the spatial tab There was an encoding issue when exporting to gpkg, but we tried to create a smaller table (very few columns): nothing. Anybody familiar with QGIS and Mysql has an idea what this not showing of any point/geom could be? Any hint appreciated, I plan to try to get a connection to the db myself, but hoping I just miss something obvious... Regards, Richard Duivenvoorde ___ 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 ___ 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 ___ QGIS-Developer mailing list QGIS-Developer@lists.osgeo.org
Re: [QGIS-Developer] Strange Mysql (spatial) behaviour: no points visible
I'll try to debug the current db, and I've asked the people to create a test db (which hopefully then has the same issue :-) ) I'm not familiar with MySQL but I guess there must be some dump & restore commands that could be used to exactly replicate a setup where the bug triggers? -- 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
Re: [QGIS-Developer] Strange Mysql (spatial) behaviour: no points visible
Thanks Even for checking Even! I replayed your test, and with me it was fine too My gut feeling is that it is something in the ogr - gdal bridge. OR there is a (?wkt) issue because of the move from an older mysql to a newer version? Is is actually exact the same issue as https://github.com/qgis/QGIS/issues/34170 : Ogr shows all, attributes shown ok, it is just QGIS that is not showing the points. I'll try to debug the current db, and I've asked the people to create a test db (which hopefully then has the same issue :-) ) Regards & Thanks, Richard On 1/23/24 13:40, Even Rouault wrote: Richard, I've failed to reproduce. Here's my testing procedure: docker run --name gdal-mysql1 -e MYSQL_ROOT_PASSWORD=passwd -e "MYSQL_ROOT_HOST=%" -p 33060:3306 -d mysql:8.0.34 mysqld --default-authentication-plugin=mysql_native_password docker exec gdal-mysql1 sh -c "echo 'CREATE DATABASE test; SELECT Version()' | mysql -uroot -ppasswd" printf 'id,WKT\n1,"POINT(168937.186906043 175180.302889316)"\n' > test.csv ogr2ogr -update "mysql:test,user=root,password=passwd,port=33060,host=127.0.0.1" test.csv -select id -nlt point -a_srs EPSG:31370 then opening it with QGIS displays the point on the canvas and the attribute table. I can also add other points. Even Le 16/01/2024 à 20:07, Richard Duivenvoorde via QGIS-Developer a écrit : No hints :-) ? I now have a live connection to the db, and can reproduce the issue here in master on Linux too. It's really strange: have a local db, I can even copy features from the attribute table to my local table, and THEN they show up ogrinfo shows exactly the same information of the db ( using -so). coordinates are fine too: select coord, ST_AsWKT(coord) from tblQgisProjecten tqp limit 1 coord |ST_AsWKT(coord) | -++ POINT (168937.186906043 175180.302889316)|POINT(168937.186906043 175180.302889316)| could it be in char encoding, or in a strange attribute value? We already tried to create a simple view with only coord and ID, but that did not show something either. As I cannot share the connection info, I'm happy to share my screen or so to show this. SELECT version(); version()| -+ 8.0.34-26| but is an upgraded db, not sure where it came from Regards, Richard Duivenvoorde On 1/12/24 15:06, Richard Duivenvoorde via QGIS-Developer wrote: Hi Devs, I was contacted by a company who after upgrading their db and QGIS did NOT see the points anymore. Note that 'all worked' when they used QGIS 3.10 (and an older mysql db) After a MySQL database upgrade, QGIS 3.10 was not able to connect anymore (apparently TLS issues), so they have to use 3.28 or higher. A small export loaded in my local db was OK in QGIS. So in a online meeting, sharing their screen I tried: - opening the db with ogrinfo: all data is visible (POINT(.)) showing correct coords and attributes - loading the table (25000 records) in QGIS: data: EPSG:31370 project EPSG:31370 : - attribute table shows all records - mapcanvas empty! - able to 'zoom to' records (Belgium), but NO points visible/selectable - creating a tiny table with only id and geom column of 3 records: mapcanvas emtpy - export the loaded (but invisible) layer to a geopackage: points AND attributes VISIBLE! - loading the data in dbeaver: all data is shown in the spatial tab There was an encoding issue when exporting to gpkg, but we tried to create a smaller table (very few columns): nothing. Anybody familiar with QGIS and Mysql has an idea what this not showing of any point/geom could be? Any hint appreciated, I plan to try to get a connection to the db myself, but hoping I just miss something obvious... Regards, Richard Duivenvoorde ___ 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 ___ 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 ___ 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
Re: [QGIS-Developer] Strange Mysql (spatial) behaviour: no points visible
Richard, I've failed to reproduce. Here's my testing procedure: docker run --name gdal-mysql1 -e MYSQL_ROOT_PASSWORD=passwd -e "MYSQL_ROOT_HOST=%" -p 33060:3306 -d mysql:8.0.34 mysqld --default-authentication-plugin=mysql_native_password docker exec gdal-mysql1 sh -c "echo 'CREATE DATABASE test; SELECT Version()' | mysql -uroot -ppasswd" printf 'id,WKT\n1,"POINT(168937.186906043 175180.302889316)"\n' > test.csv ogr2ogr -update "mysql:test,user=root,password=passwd,port=33060,host=127.0.0.1" test.csv -select id -nlt point -a_srs EPSG:31370 then opening it with QGIS displays the point on the canvas and the attribute table. I can also add other points. Even Le 16/01/2024 à 20:07, Richard Duivenvoorde via QGIS-Developer a écrit : No hints :-) ? I now have a live connection to the db, and can reproduce the issue here in master on Linux too. It's really strange: have a local db, I can even copy features from the attribute table to my local table, and THEN they show up ogrinfo shows exactly the same information of the db ( using -so). coordinates are fine too: select coord, ST_AsWKT(coord) from tblQgisProjecten tqp limit 1 coord |ST_AsWKT(coord) | -++ POINT (168937.186906043 175180.302889316)|POINT(168937.186906043 175180.302889316)| could it be in char encoding, or in a strange attribute value? We already tried to create a simple view with only coord and ID, but that did not show something either. As I cannot share the connection info, I'm happy to share my screen or so to show this. SELECT version(); version()| -+ 8.0.34-26| but is an upgraded db, not sure where it came from Regards, Richard Duivenvoorde On 1/12/24 15:06, Richard Duivenvoorde via QGIS-Developer wrote: Hi Devs, I was contacted by a company who after upgrading their db and QGIS did NOT see the points anymore. Note that 'all worked' when they used QGIS 3.10 (and an older mysql db) After a MySQL database upgrade, QGIS 3.10 was not able to connect anymore (apparently TLS issues), so they have to use 3.28 or higher. A small export loaded in my local db was OK in QGIS. So in a online meeting, sharing their screen I tried: - opening the db with ogrinfo: all data is visible (POINT(.)) showing correct coords and attributes - loading the table (25000 records) in QGIS: data: EPSG:31370 project EPSG:31370 : - attribute table shows all records - mapcanvas empty! - able to 'zoom to' records (Belgium), but NO points visible/selectable - creating a tiny table with only id and geom column of 3 records: mapcanvas emtpy - export the loaded (but invisible) layer to a geopackage: points AND attributes VISIBLE! - loading the data in dbeaver: all data is shown in the spatial tab There was an encoding issue when exporting to gpkg, but we tried to create a smaller table (very few columns): nothing. Anybody familiar with QGIS and Mysql has an idea what this not showing of any point/geom could be? Any hint appreciated, I plan to try to get a connection to the db myself, but hoping I just miss something obvious... Regards, Richard Duivenvoorde ___ 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 ___ 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 -- 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
Re: [QGIS-Developer] Strange Mysql (spatial) behaviour: no points visible
On Wed, 17 Jan 2024 at 06:15, Alessandro Pasotti via QGIS-Developer < qgis-developer@lists.osgeo.org> wrote: > Hi Richard, > > sorry, no hints except to check the logs and the QGIS dev tools query > logger: is there anything suspicious? > There's a LOT of open bugs relating to mysql data -- eg https://github.com/qgis/QGIS/issues/34170 . I suspect it's very rarely used, and there's either a bug in GDAL or some special handling which needs adding to QGIS to get this to work. Perhaps something we could focus on during the upcoming 3.36 bug fix sprint? Nyall > > On Tue, Jan 16, 2024 at 8:08 PM Richard Duivenvoorde via > QGIS-Developer wrote: > > > > No hints :-) ? > > > > I now have a live connection to the db, and can reproduce the issue here > in master on Linux too. > > > > It's really strange: have a local db, I can even copy features from the > attribute table to my local table, and THEN they show up > > > > ogrinfo shows exactly the same information of the db ( using -so). > > > > coordinates are fine too: > > > > select coord, ST_AsWKT(coord) from tblQgisProjecten tqp limit 1 > > > > coord|ST_AsWKT(coord) > | > > > -++ > > POINT (168937.186906043 175180.302889316)|POINT(168937.186906043 > 175180.302889316)| > > > > could it be in char encoding, or in a strange attribute value? We > already tried to create a simple view with only coord and ID, but that did > not show something either. > > > > As I cannot share the connection info, I'm happy to share my screen or > so to show this. > > > > SELECT version(); > > version()| > > -+ > > 8.0.34-26| > > > > but is an upgraded db, not sure where it came from > > > > Regards, > > > > Richard Duivenvoorde > > > > > > On 1/12/24 15:06, Richard Duivenvoorde via QGIS-Developer wrote: > > > Hi Devs, > > > > > > I was contacted by a company who after upgrading their db and QGIS did > NOT see the points anymore. > > > > > > Note that 'all worked' when they used QGIS 3.10 (and an older mysql db) > > > After a MySQL database upgrade, QGIS 3.10 was not able to connect > anymore (apparently TLS issues), so they have to use 3.28 or higher. > > > > > > A small export loaded in my local db was OK in QGIS. > > > > > > So in a online meeting, sharing their screen I tried: > > > > > > - opening the db with ogrinfo: all data is visible (POINT(.)) > showing correct coords and attributes > > > - loading the table (25000 records) in QGIS: data: EPSG:31370 project > EPSG:31370 : > > > - attribute table shows all records > > > - mapcanvas empty! > > > - able to 'zoom to' records (Belgium), but NO points > visible/selectable > > > - creating a tiny table with only id and geom column of 3 records: > mapcanvas emtpy > > > - export the loaded (but invisible) layer to a geopackage: points AND > attributes VISIBLE! > > > - loading the data in dbeaver: all data is shown in the spatial tab > > > > > > There was an encoding issue when exporting to gpkg, but we tried to > create a smaller table (very few columns): nothing. > > > > > > Anybody familiar with QGIS and Mysql has an idea what this not showing > of any point/geom could be? > > > > > > Any hint appreciated, I plan to try to get a connection to the db > myself, but hoping I just miss something obvious... > > > > > > Regards, > > > > > > Richard Duivenvoorde > > > ___ > > > 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 > > > > ___ > > 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 > > > > -- > Alessandro Pasotti > QCooperative: www.qcooperative.net > ItOpen: www.itopen.it > ___ > 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 > ___ 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
Re: [QGIS-Developer] Strange Mysql (spatial) behaviour: no points visible
Hi Richard, sorry, no hints except to check the logs and the QGIS dev tools query logger: is there anything suspicious? On Tue, Jan 16, 2024 at 8:08 PM Richard Duivenvoorde via QGIS-Developer wrote: > > No hints :-) ? > > I now have a live connection to the db, and can reproduce the issue here in > master on Linux too. > > It's really strange: have a local db, I can even copy features from the > attribute table to my local table, and THEN they show up > > ogrinfo shows exactly the same information of the db ( using -so). > > coordinates are fine too: > > select coord, ST_AsWKT(coord) from tblQgisProjecten tqp limit 1 > > coord|ST_AsWKT(coord) > | > -++ > POINT (168937.186906043 175180.302889316)|POINT(168937.186906043 > 175180.302889316)| > > could it be in char encoding, or in a strange attribute value? We already > tried to create a simple view with only coord and ID, but that did not show > something either. > > As I cannot share the connection info, I'm happy to share my screen or so to > show this. > > SELECT version(); > version()| > -+ > 8.0.34-26| > > but is an upgraded db, not sure where it came from > > Regards, > > Richard Duivenvoorde > > > On 1/12/24 15:06, Richard Duivenvoorde via QGIS-Developer wrote: > > Hi Devs, > > > > I was contacted by a company who after upgrading their db and QGIS did NOT > > see the points anymore. > > > > Note that 'all worked' when they used QGIS 3.10 (and an older mysql db) > > After a MySQL database upgrade, QGIS 3.10 was not able to connect anymore > > (apparently TLS issues), so they have to use 3.28 or higher. > > > > A small export loaded in my local db was OK in QGIS. > > > > So in a online meeting, sharing their screen I tried: > > > > - opening the db with ogrinfo: all data is visible (POINT(.)) showing > > correct coords and attributes > > - loading the table (25000 records) in QGIS: data: EPSG:31370 project > > EPSG:31370 : > > - attribute table shows all records > > - mapcanvas empty! > > - able to 'zoom to' records (Belgium), but NO points visible/selectable > > - creating a tiny table with only id and geom column of 3 records: > > mapcanvas emtpy > > - export the loaded (but invisible) layer to a geopackage: points AND > > attributes VISIBLE! > > - loading the data in dbeaver: all data is shown in the spatial tab > > > > There was an encoding issue when exporting to gpkg, but we tried to create > > a smaller table (very few columns): nothing. > > > > Anybody familiar with QGIS and Mysql has an idea what this not showing of > > any point/geom could be? > > > > Any hint appreciated, I plan to try to get a connection to the db myself, > > but hoping I just miss something obvious... > > > > Regards, > > > > Richard Duivenvoorde > > ___ > > 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 > > ___ > 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 -- Alessandro Pasotti QCooperative: www.qcooperative.net ItOpen: www.itopen.it ___ 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
Re: [QGIS-Developer] Strange Mysql (spatial) behaviour: no points visible
No hints :-) ? I now have a live connection to the db, and can reproduce the issue here in master on Linux too. It's really strange: have a local db, I can even copy features from the attribute table to my local table, and THEN they show up ogrinfo shows exactly the same information of the db ( using -so). coordinates are fine too: select coord, ST_AsWKT(coord) from tblQgisProjecten tqp limit 1 coord|ST_AsWKT(coord) | -++ POINT (168937.186906043 175180.302889316)|POINT(168937.186906043 175180.302889316)| could it be in char encoding, or in a strange attribute value? We already tried to create a simple view with only coord and ID, but that did not show something either. As I cannot share the connection info, I'm happy to share my screen or so to show this. SELECT version(); version()| -+ 8.0.34-26| but is an upgraded db, not sure where it came from Regards, Richard Duivenvoorde On 1/12/24 15:06, Richard Duivenvoorde via QGIS-Developer wrote: Hi Devs, I was contacted by a company who after upgrading their db and QGIS did NOT see the points anymore. Note that 'all worked' when they used QGIS 3.10 (and an older mysql db) After a MySQL database upgrade, QGIS 3.10 was not able to connect anymore (apparently TLS issues), so they have to use 3.28 or higher. A small export loaded in my local db was OK in QGIS. So in a online meeting, sharing their screen I tried: - opening the db with ogrinfo: all data is visible (POINT(.)) showing correct coords and attributes - loading the table (25000 records) in QGIS: data: EPSG:31370 project EPSG:31370 : - attribute table shows all records - mapcanvas empty! - able to 'zoom to' records (Belgium), but NO points visible/selectable - creating a tiny table with only id and geom column of 3 records: mapcanvas emtpy - export the loaded (but invisible) layer to a geopackage: points AND attributes VISIBLE! - loading the data in dbeaver: all data is shown in the spatial tab There was an encoding issue when exporting to gpkg, but we tried to create a smaller table (very few columns): nothing. Anybody familiar with QGIS and Mysql has an idea what this not showing of any point/geom could be? Any hint appreciated, I plan to try to get a connection to the db myself, but hoping I just miss something obvious... Regards, Richard Duivenvoorde ___ 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 ___ 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
[QGIS-Developer] Strange Mysql (spatial) behaviour: no points visible
Hi Devs, I was contacted by a company who after upgrading their db and QGIS did NOT see the points anymore. Note that 'all worked' when they used QGIS 3.10 (and an older mysql db) After a MySQL database upgrade, QGIS 3.10 was not able to connect anymore (apparently TLS issues), so they have to use 3.28 or higher. A small export loaded in my local db was OK in QGIS. So in a online meeting, sharing their screen I tried: - opening the db with ogrinfo: all data is visible (POINT(.)) showing correct coords and attributes - loading the table (25000 records) in QGIS: data: EPSG:31370 project EPSG:31370 : - attribute table shows all records - mapcanvas empty! - able to 'zoom to' records (Belgium), but NO points visible/selectable - creating a tiny table with only id and geom column of 3 records: mapcanvas emtpy - export the loaded (but invisible) layer to a geopackage: points AND attributes VISIBLE! - loading the data in dbeaver: all data is shown in the spatial tab There was an encoding issue when exporting to gpkg, but we tried to create a smaller table (very few columns): nothing. Anybody familiar with QGIS and Mysql has an idea what this not showing of any point/geom could be? Any hint appreciated, I plan to try to get a connection to the db myself, but hoping I just miss something obvious... Regards, Richard Duivenvoorde ___ 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
