[MDEV-7540] Information Schema SPATIAL_REF_SYS contents don't match the expected contents Created: 2015-02-04  Updated: 2015-12-21  Resolved: 2015-12-21

Status: Closed
Project: MariaDB Server
Component/s: GIS
Affects Version/s: 10.1.2
Fix Version/s: 10.1.10

Type: Bug Priority: Major
Reporter: Ian Gilfillan Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: None

Sprint: 10.1.6-2, 10.1.7-1, 10.1.7-2, 10.1.8-1, 10.1.8-3, 10.1.10

 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.

 Comments   
Comment by Elena Stepanova [ 2015-02-04 ]

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

Comment by Delveri Chick [ 2015-03-25 ]

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]>

Comment by Delveri Chick [ 2015-04-02 ]

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

Comment by Alexey Botchkov [ 2015-12-21 ]

Patch: http://lists.askmonty.org/pipermail/commits/2015-December/008775.html

Generated at Thu Feb 08 07:20:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.