Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30150

ST_GeomFromGeoJSON, 'geometry' before 'type: feature' error

Details

    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.

      Attachments

        Activity

          danblack Daniel Black added a comment -

          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)
          

          danblack Daniel Black added a comment - 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)
          danblack Daniel Black added a comment -

          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.

          danblack Daniel Black added a comment - 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 .

          No problem, thanks for the quick replies!

          turtlesocks Derick Magnusen added a comment - No problem, thanks for the quick replies!
          danblack Daniel Black added a comment - - edited

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

          turtlesocks, out of interest, what is your project?

          danblack Daniel Black added a comment - - edited bar , can I get your review on https://github.com/MariaDB/server/pull/2354 please. turtlesocks , out of interest, what is your project?

          ok to push.

          holyfoot Alexey Botchkov added a comment - ok to push.

          People

            danblack Daniel Black
            turtlesocks Derick Magnusen
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.