Details

    • Task
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Incomplete
    • N/A
    • Tests
    • None

    Description

      Quoting Holyfoot's email:

      Related tasks in JIRA are:

      MDEV-4045 Missing OGC Spatial functions.
      MDEV-60 Support for Spatial Reference systems for the GIS data.
      MDEV-12 OpenGIS: create required tables: GeometryColumns, related views.

      Speaking shortly, the MariaDB GIS part is now OpenGIS compliant, and passes all the OpenGIS conformance tests

      To get that the next things were done:

      Missing functions were added:

      IsRing(ln) MDEV-7510
      The return type is Integer, with a return value of 1 for
      TRUE, 0 for FALSE, and –1 for UNKNOWN
      corresponding to a function invocation on NULL arguments;
      return TRUE if c is a ring, i.e., if c is closed and simple. A
      simple Curve does not pass through the same Point more than once.

      PointOnSurface(s) MDEV-7514
      The return type is POINT, returns a Point guaranteed to lie on the Surface.
      arguments: POLYGON or MYLTIPOLYGON.

      NumInteriorRings(p) MDEV-7525
      The return type is Integer, returns the number of interiorRings
      arguments: POLYGON.

      Relate(g1 Geometry, g2 Geometry, patternMatrix String) MDEV-7529
      The return type is Integer, with a return value of 1 for TRUE, 0 for
      FALSE, and –1 for UNKNOWN corresponding to a function invocation on NULL arguments;
      returns TRUE if the spatial relationship specified by the pattern Matrix holds.

      ConvexHull(g1 Geometry)
      The return is Geometry, returna a geometric object that is the convex hull of g1
      arguments: GEOMETRY.

      The information about the GEOMETRY column's reference system (SRID) is now stored in the
      field's metadata. Syntax of the CREATE and ALTER was modified to specify the SRID
      for the geometry columns.

      With this information some views required by OpenGIS, were added:

      GEOMETRY_COLUMNS, SPATIAL_REF_SYS:
      These are links to the INFORMATION_SCHEMA.GEOMETRY_COLUMNS and
      INFORMATION_SCHEMA.SPATIAL_REF_SYS respectively.

      INFORMATION_SCHEMA.GEOMETRY_COLUMNS:
      describes the available feature tables and their Geometry properties.
      Fields:

              F_TABLE_CATALOG CHARACTER VARYING(256) NOT NULL,
              F_TABLE_SCHEMA CHARACTER VARYING(256) NOT NULL,
              F_TABLE_NAME CHARACTER VARYING(256) NOT NULL,
                  the above 3 fields are the fully qualified name of the feature table
                  containing the geometry column;
              F_GEOMETRY_COLUMN CHARACTER VARYING(256) NOT NULL,
                  the name of the column in the feature table that is the Geometry Column.
              G_TABLE_CATALOG CHARACTER VARYING(256) NOT NULL,
              G_TABLE_SCHEMA CHARACTER VARYING(256) NOT NULL,
              G_TABLE_NAME CHARACTER VARYING(256) NOT NULL,
                  the above 3 fields are the name of the geometry table and its schema
                  and catalog. The geometry table implements the geometry column;
              STORAGE_TYPE INTEGER,
                  always 1 in MariaDB (means binary geometry implementation)
              GEOMETRY_TYPE INTEGER,
                  the type of geometry values stored in this column.
                  0 = GEOMETRY
                  1 = POINT
                  3 = LINESTRING
                  5 = POLYGON
                  7 = MULTIPOINT
                  9 = MULTILINESTRING
                  11 = MULTIPOLYGON
       
              COORD_DIMENSION INTEGER,
                  the number of dimensions in the spatial reference system.
                  Always 2 in MariaDB.
       
              MAX_PPR INTEGER,
                  Always 0 in MariaDB.
       
              SRID INTEGER
                  the ID of the Spatial Reference System used for the coordinate geometry
                  in this table. It is a foreign key reference to the SPATIAL_REF_SYS table.

      INFORMATION_SCHEMA.SPATIAL_REF_SYS:
      stores information on each spatial reference system used in the database.
      Fields:

              SRID INTEGER NOT NULL PRIMARY KEY,
                  an integer value that uniquely identifies each Spatial Reference System within a database.
              AUTH_NAME CHARACTER VARYING(256),
                  the name of the standard or standards body that is being cited for this reference system.
                  In most cases the value is "EPSG".
              AUTH_SRID INTEGER TEXT
                  the Well-known Text Representation of the Spatial Reference System.

      Required stored procedures added:

      AddGeometryColumn(FEATURE_TABLE_CATALOG, FEATURE_TABLE_SCHEMA,FEATURE_TABLE_NAME, GEOMETRY_COLUMN_NAME, SRID) MDEV-7515
      adds new column of spatial type to the specified table

      DropGeometryColumn(FEATURE_TABLE_CATALOG, FEATURE_TABLE_SCHEMA,FEATURE_TABLE_NAME, GEOMETRY_COLUMN_NAME) MDEV-7515
      removes the spatial column from the table.

      Attachments

        Issue Links

          Activity

            According to http://postgis.net/docs/manual-1.3/ch06.html

            We still don't have:

            (MDEV-7515) AddGeometryColumn(varchar, varchar, varchar, integer, varchar, integer)
            Syntax: AddGeometryColumn(<schema_name>, <table_name>, <column_name>, <srid>, <type>, <dimension>). Adds a geometry column to an existing table of attributes. The schema_name is the name of the table schema (unused for pre-schema PostgreSQL installations). The srid must be an integer value reference to an entry in the SPATIAL_REF_SYS table. The type must be an uppercase string corresponding to the geometry type, eg, 'POLYGON' or 'MULTILINESTRING'.

            (MDEV-7515) DropGeometryColumn(varchar, varchar, varchar)
            Syntax: DropGeometryColumn(<schema_name>, <table_name>, <column_name>). Remove a geometry column from a spatial table. Note that schema_name will need to match the f_schema_name field of the table's row in the geometry_columns table.

            Probe_Geometry_Columns()
            Scans all tables with PostGIS geometry constraints and adds them to the geometry_columns table if they are not already there. Also give stats on the number of inserts, already present, or possibly obsolete columns.

            ST_SetSRID(geometry, integer)
            Set the SRID on a geometry to a particular integer value. Useful in constructing bounding boxes for queries.

            ST_DWithin(geometry, geometry, float)
            Returns true if geometries are within the specified distance of one another. Uses indexes if available.

            ST_Shift_Longitude(geometry)
            Reads every point/vertex in every component of every feature in a geometry, and if the longitude coordinate is <0, adds 360 to it. The result would be a 0-360 version of the data to be plotted in a 180 centric map

            ST_NumInteriorRing(geometry)
            Synonym to NumInteriorRings(geometry).

            ST_Z(geometry)
            Return the Z coordinate of the point, or NULL if not available. Input must be a point.

            ST_MPointFromText(text,[<srid>])
            Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.

            ST_MLineFromText(text,[<srid>])
            Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.

            ST_MPolyFromText(text,[<srid>])
            Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.

            ST_MPointFromWKB(bytea,[<srid>])
            Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.

            ST_MLineFromWKB(bytea,[<srid>])
            Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.

            ST_MPolyFromWKB(bytea,[<srid>])
            Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.

            ST_BdPolyFromText(text WKT, integer SRID)
            Construct a Polygon given an arbitrary collection of closed linestrings as a MultiLineString text representation.

            ST_BdMPolyFromText(text WKT, integer SRID)
            Construct a MultiPolygon given an arbitrary collection of closed linestrings as a MultiLineString text representation.

            elenst Elena Stepanova added a comment - According to http://postgis.net/docs/manual-1.3/ch06.html We still don't have: ( MDEV-7515 ) AddGeometryColumn(varchar, varchar, varchar, integer, varchar, integer) Syntax: AddGeometryColumn(<schema_name>, <table_name>, <column_name>, <srid>, <type>, <dimension>). Adds a geometry column to an existing table of attributes. The schema_name is the name of the table schema (unused for pre-schema PostgreSQL installations). The srid must be an integer value reference to an entry in the SPATIAL_REF_SYS table. The type must be an uppercase string corresponding to the geometry type, eg, 'POLYGON' or 'MULTILINESTRING'. ( MDEV-7515 ) DropGeometryColumn(varchar, varchar, varchar) Syntax: DropGeometryColumn(<schema_name>, <table_name>, <column_name>). Remove a geometry column from a spatial table. Note that schema_name will need to match the f_schema_name field of the table's row in the geometry_columns table. Probe_Geometry_Columns() Scans all tables with PostGIS geometry constraints and adds them to the geometry_columns table if they are not already there. Also give stats on the number of inserts, already present, or possibly obsolete columns. ST_SetSRID(geometry, integer) Set the SRID on a geometry to a particular integer value. Useful in constructing bounding boxes for queries. ST_DWithin(geometry, geometry, float) Returns true if geometries are within the specified distance of one another. Uses indexes if available. ST_Shift_Longitude(geometry) Reads every point/vertex in every component of every feature in a geometry, and if the longitude coordinate is <0, adds 360 to it. The result would be a 0-360 version of the data to be plotted in a 180 centric map ST_NumInteriorRing(geometry) Synonym to NumInteriorRings(geometry). ST_Z(geometry) Return the Z coordinate of the point, or NULL if not available. Input must be a point. ST_MPointFromText(text, [<srid>] ) Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1. ST_MLineFromText(text, [<srid>] ) Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1. ST_MPolyFromText(text, [<srid>] ) Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1. ST_MPointFromWKB(bytea, [<srid>] ) Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1. ST_MLineFromWKB(bytea, [<srid>] ) Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1. ST_MPolyFromWKB(bytea, [<srid>] ) Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1. ST_BdPolyFromText(text WKT, integer SRID) Construct a Polygon given an arbitrary collection of closed linestrings as a MultiLineString text representation. ST_BdMPolyFromText(text WKT, integer SRID) Construct a MultiPolygon given an arbitrary collection of closed linestrings as a MultiLineString text representation.
            greenman Ian Gilfillan added a comment -

            Note that in the description of the SPATIAL_REF_SYS table, it looks like some text was chopped off. The text probably should read:

              SRID INTEGER NOT NULL PRIMARY KEY,
                        an integer value that uniquely identifies each Spatial Reference System within a database.
              AUTH_NAME CHARACTER VARYING(256),
                        the name of the standard or standards body that is being cited for this reference system.
                        In most cases the value is "EPSG".
              AUTH_SRID INTEGER 
                       the numeric ID of the coordinate system in the above authority's catalog.
              SRTEXT TEXT
                        the Well-known Text Representation of the Spatial Reference System.

            greenman Ian Gilfillan added a comment - Note that in the description of the SPATIAL_REF_SYS table, it looks like some text was chopped off. The text probably should read: SRID INTEGER NOT NULL PRIMARY KEY, an integer value that uniquely identifies each Spatial Reference System within a database. AUTH_NAME CHARACTER VARYING(256), the name of the standard or standards body that is being cited for this reference system. In most cases the value is "EPSG". AUTH_SRID INTEGER the numeric ID of the coordinate system in the above authority's catalog. SRTEXT TEXT the Well-known Text Representation of the Spatial Reference System.
            greenman Ian Gilfillan added a comment - See also https://mariadb.atlassian.net/browse/MDEV-7540

            People

              elenst Elena Stepanova
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.