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

centroid not even close to polygon in some cases

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.3.27, 10.5.9
    • Fix Version/s: 10.3, 10.4, 10.5
    • Component/s: GIS
    • Labels:
    • Environment:
      Ububtu 20.04, debian 9

      Description

      In some not so rare cases, when trying to get a centroid of a polygon, the point it returns is not even close to the polygon example:

      SELECT AsText(ST_Centroid(ST_GeomFromText('POLYGON((141.473127 -31.949235,141.47322 -31.949187,141.473232 -31.949181,141.473238 -31.949186,141.473244 -31.94919,141.473254 -31.949198,141.473269 -31.94921,141.473311 -31.949243,141.47322 -31.949235,141.473231 -31.949199,141.473127 -31.949235))')))
      

      Have tested this on MariaDB ver: 10.3.27 (what i'm using) and latest stable (10.5.9)

      it shows a point ~600m away from the polygon. trying the exact same on MySQL ver: 5.7.20 & 8.0.22, the point is where you would expect it.

      here is some GeoJSON to demonstrate the issue (use on GeoJSON.io):

      {
        "type": "FeatureCollection",
        "features": [
          {
            "type": "Feature",
            "properties": {"name": "centroid", "version": "MariaDB 10.3.27"},
            "geometry": {
              "type": "Point",
              "coordinates": [141.4804016259371, -31.950833470712737]
            }
          },
          {
            "type": "Feature",
            "properties": {"name": "centroid", "version": "MariaDB 10.5.9"},
            "geometry": {
              "type": "Point",
              "coordinates": [141.4804016259371, -31.950833470712737]
            }
          },
          {
            "type": "Feature",
            "properties": {"name": "centroid", "version": "MySQL 5.7.20"},
            "geometry": {
              "type": "Point",
              "coordinates": [141.47323905138515, -31.949215928358655]
            }
          },
          {
            "type": "Feature",
            "properties": {"name": "polygon"},
            "geometry": {
              "type": "Polygon",
              "coordinates": [[[141.473127, -31.949235], [141.47322, -31.949187], [141.473232, -31.949181], [141.473238, -31.949186], [141.473244, -31.94919], [141.473254, -31.949198], [141.473269, -31.94921], [141.473311, -31.949243], [141.47322, -31.949235], [141.473231, -31.949199], [141.473127, -31.949235]]]}
          }
        ]
      }
      

      It seems to be random, alter the polygon a little and it will work. its not just a freak accident as it has been brought up to me 8-10 times is the few months we have been using MariaDB over MySQL

      Results:

      Server Type Server Version Centroid
      MariaDB 10.3.27 POINT(141.4804016259371 -31.950833470712737)
      MariaDB 10.5.9 POINT(141.4804016259371 -31.950833470712737)
      MySQL 5.7.20 POINT(141.47323905138515 -31.949215928358655)
      MySQL 8.0.22 POINT(141.47323905138515 -31.949215928358655)

        Attachments

          Activity

            People

            Assignee:
            holyfoot Alexey Botchkov
            Reporter:
            mike16889 Michael Richards
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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