[MDEV-30150] ST_GeomFromGeoJSON, 'geometry' before 'type: feature' error Created: 2022-12-02  Updated: 2023-01-09  Resolved: 2022-12-08

Status: Closed
Project: MariaDB Server
Component/s: GIS
Affects Version/s: 10.5.15, 10.3.37
Fix Version/s: 10.11.2, 10.3.38, 10.4.28, 10.5.19, 10.6.12, 10.7.8, 10.8.7, 10.9.5, 10.10.3

Type: Bug Priority: Minor
Reporter: Derick Magnusen Assignee: Daniel Black
Resolution: Fixed Votes: 0
Labels: GIS, ST_GeomFromGeoJSON, compat57, geometry
Environment:

Debian 11


 Description   

The following works in MySQL 8 but fails in MariaDB:

    SELECT
 
      lat,
 
      lon
 
    FROM
 
      my_table
 
    WHERE
 
      ST_CONTAINS(
 
        ST_GeomFromGeoJSON(
 
          '{"geometry":{"coordinates":[[[4.940319,51.603356],[4.89234,51.603196],[4.893885,51.576214],[4.940319,51.568532],[4.940319,51.603356]]],"type":"Polygon"},"properties":{},"type":"Feature"}',
 
          2, 0
 
        ),
 
        POINT(lon, lat)
 
      )

MariaDB requires the following:

    SELECT
 
      lat,
 
      lon
 
    FROM
 
      my_table
 
    WHERE
 
    ST_CONTAINS(
 
        ST_GeomFromGeoJSON(
 
        '{"coordinates":[[[4.940319,51.603356],[4.89234,51.603196],[4.893885,51.576214],[4.940319,51.568532],[4.940319,51.603356]]],"type":"Polygon"}',
 
          2, 0
 
        ),
 
        POINT(lon, lat)
 
       );

Yes, I recognize that only the "geometry" property in the feature is necessary and also works in MySQL, but it caused quite a bit of head scratching and if the function names are going to be the same, then they should work the same.

I don't personally have MariaDB setup, I'm just using this functionality in my OSS project and helping a user troubleshoot so I've provided as much info as I can about their environment.



 Comments   
Comment by Daniel Black [ 2022-12-03 ]

Quite right. I assume MySQL just ignores extra JSON fields. Also fails on 10.3 (without even a warning)

10.3

 
MariaDB [(none)]> select ST_GeomFromGeoJSON(           '{"geometry":{"coordinates":[[[4.940319,51.603356],[4.89234,51.603196],[4.893885,51.576214],[4.940319,51.568532],[4.940319,51.603356]]],"type":"Polygon"},"properties":{},"type":"Feature"}', 2, 0 ) as x;
 
+------+
 
| x    |
 
+------+
 
| NULL |
 
+------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [(none)]> show warnings;
 
Empty set (0.000 sec)
 
 
 
MariaDB [(none)]> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.3.38-MariaDB |
 
+-----------------+
 
1 row in set (0.000 sec)

10.5

 
MariaDB [(none)]> select ST_GeomFromGeoJSON(           '{"geometry":{"coordinates":[[[4.940319,51.603356],[4.89234,51.603196],[4.893885,51.576214],[4.940319,51.568532],[4.940319,51.603356]]],"type":"Polygon"},"properties":{},"type":"Feature"}', 2, 0 ) as x;
 
+------+
 
| x    |
 
+------+
 
| NULL |
 
+------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [(none)]> show warnings;
 
+---------+------+------------------------+
 
| Level   | Code | Message                |
 
+---------+------+------------------------+
 
| Warning | 1474 | Name '
 
' has become '' |
 
+---------+------+------------------------+
 
1 row in set (0.000 sec)

Comment by Daniel Black [ 2022-12-03 ]

Currently there's an assumption that "feature" comes before "geometry":

MariaDB [(none)]> SELECT st_astext(st_geomfromgeojson('{ "geometry": { "type": "Point", "coordinates": [102.0, 0.5] }, "type": "Feature" }'));
 
 
 
+----------------------------------------------------------------------------------------------------------------------+
 
| st_astext(st_geomfromgeojson('{ "geometry": { "type": "Point", "coordinates": [102.0, 0.5] }, "type": "Feature" }')) |
 
+----------------------------------------------------------------------------------------------------------------------+
 
| NULL                                                                                                                 |
 
+----------------------------------------------------------------------------------------------------------------------+
 
1 row in set (5.684 sec)
 
 
 
MariaDB [(none)]> SELECT st_astext(st_geomfromgeojson('{ "type": "Feature", "geometry": { "type": "Point", "coordinates": [102.0, 0.5] } }'));
 
+----------------------------------------------------------------------------------------------------------------------+
 
| st_astext(st_geomfromgeojson('{ "type": "Feature", "geometry": { "type": "Point", "coordinates": [102.0, 0.5] } }')) |
 
+----------------------------------------------------------------------------------------------------------------------+
 
| POINT(102 0.5)                                                                                                       |
 
+----------------------------------------------------------------------------------------------------------------------+

Thanks for the bug report turtlesocks.

Comment by Derick Magnusen [ 2022-12-03 ]

No problem, thanks for the quick replies!

Comment by Daniel Black [ 2022-12-03 ]

bar, can I get your review on https://github.com/MariaDB/server/pull/2354 please.

turtlesocks, out of interest, what is your project?

Comment by Alexey Botchkov [ 2022-12-08 ]

ok to push.

Generated at Thu Feb 08 10:14:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.