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

Information Schema SPATIAL_REF_SYS contents don't match the expected contents

        Details

        • Type: Bug
        • Status: In Progress
        • Priority: Major
        • Resolution: Unresolved
        • Affects Version/s: 10.1.2
        • Fix Version/s: 10.1
        • Component/s: GIS
        • Labels:
          None
        • Sprint:
          10.1.6-2, 10.1.7-1, 10.1.7-2, 10.1.8-1, 10.1.8-3

          Description

          Querying the table in 10.1.2, the following is returned

           SELECT * FROM INFORMATION_SCHEMA.SPATIAL_REF_SYS;
+------+-----------------+-----------+--------+
| SRID | AUTH_NAME       | AUTH_SRID | SRTEXT |
+------+-----------------+-----------+--------+
|   -1 | Not defined     |        -1 |        |
|    0 | Cartesian plane |         0 |        |
+------+-----------------+-----------+--------+

          According the specs, the table should contain:

          srid: The numeric SRID. This should be the table's primary key.
          auth_name: An authority name as a string. The documentation email suggested that this should be "EPSG" in most cases.
          auth_srid: The numeric ID of the coordinate system in the above authority's catalog.
          srtext: The Well-Known-Text (WKT) representation of the coordinate system.

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                PostGIS has 3911 rows in the table. Examples:

                select * from spatial_ref_sys limit 2;

 3819 | EPSG      |      3819 | GEOGCS["HD1909",DATUM["Hungarian_Datum_1909",SPHEROID["Bessel 1841",6377397.155,299.1528128,AUTHORITY["EPSG","7004"]],TOWGS84[595.48,121.69,515.35,4.115,-2.9383,0.853,-3.408],AUTHORITY["EPSG","1024"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","3819"]] | +proj=longlat +ellps=bessel +towgs84=595.48,121.69,515.35,4.115,-2.9383,0.853,-3.408 +no_defs 
 3821 | EPSG      |      3821 | GEOGCS["TWD67",DATUM["Taiwan_Datum_1967",SPHEROID["GRS 1967 Modified",6378160,298.25,AUTHORITY["EPSG","7050"]],AUTHORITY["EPSG","1025"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","3821"]]                                                                 | +proj=longlat +ellps=aust_SA +no_defs
                Show
                elenst Elena Stepanova added a comment - PostGIS has 3911 rows in the table. Examples: select * from spatial_ref_sys limit 2; 3819 | EPSG | 3819 | GEOGCS["HD1909",DATUM["Hungarian_Datum_1909",SPHEROID["Bessel 1841",6377397.155,299.1528128,AUTHORITY["EPSG","7004"]],TOWGS84[595.48,121.69,515.35,4.115,-2.9383,0.853,-3.408],AUTHORITY["EPSG","1024"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","3819"]] | +proj=longlat +ellps=bessel +towgs84=595.48,121.69,515.35,4.115,-2.9383,0.853,-3.408 +no_defs 3821 | EPSG | 3821 | GEOGCS["TWD67",DATUM["Taiwan_Datum_1967",SPHEROID["GRS 1967 Modified",6378160,298.25,AUTHORITY["EPSG","7050"]],AUTHORITY["EPSG","1025"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","3821"]] | +proj=longlat +ellps=aust_SA +no_defs
                Hide
                Permalink
                chidelmun Delveri Chick added a comment - - edited

                Hi
                Im Chick and I have dropped a proposal on melange for the GSoC GIS enhancement task mentored by holyfoot. However I wish to work on this related bug to add value to my proposal. I build mariadb from a github clone but when I run the bug query, SELECT * FROM INFORMATION_SCHEMA.SPATIAL_REF_SYS; I rather get something different from the results mentioned above.
                Here what I get.

                MariaDB [information_schema]> SELECT * FROM INFORMATION_SCHEMA.SPATIAL_REF_SYS;

                ERROR 1109 (42S02): Unknown table 'SPATIAL_REF_SYS' in information_schema

                MariaDB [information_schema]>

                Show
                chidelmun Delveri Chick added a comment - - edited Hi Im Chick and I have dropped a proposal on melange for the GSoC GIS enhancement task mentored by holyfoot. However I wish to work on this related bug to add value to my proposal. I build mariadb from a github clone but when I run the bug query, SELECT * FROM INFORMATION_SCHEMA.SPATIAL_REF_SYS; I rather get something different from the results mentioned above. Here what I get. MariaDB [information_schema] > SELECT * FROM INFORMATION_SCHEMA.SPATIAL_REF_SYS; ERROR 1109 (42S02): Unknown table 'SPATIAL_REF_SYS' in information_schema MariaDB [information_schema] >
                Hide
                Permalink
                chidelmun Delveri Chick added a comment -

                fill_spatial_ref_sys() in sql/sql_spatial.cc does not fill the table.
                Need to populate this with the references

                Show
                chidelmun Delveri Chick added a comment - fill_spatial_ref_sys() in sql/sql_spatial.cc does not fill the table. Need to populate this with the references

                  People

                  • Assignee:
                    holyfoot Alexey Botchkov
                    Reporter:
                    greenman Ian Gilfillan
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    4 Start watching this issue

                    Dates

                    • Created:
                      Updated:

                      Agile