sql.gpkg_geometry_columns Maven / Gradle / Ivy

Go to download

Show more of this group Show more artifacts with this name
Show all versions of geopackage-core Show documentation

Core functionality for GeoPackage implementations

There is a newer version: 6.6.7

CREATE TABLE gpkg_geometry_columns (
  table_name TEXT NOT NULL,
  column_name TEXT NOT NULL,
  geometry_type_name TEXT NOT NULL,
  srs_id INTEGER NOT NULL,
  z TINYINT NOT NULL,
  m TINYINT NOT NULL,
  CONSTRAINT pk_geom_cols PRIMARY KEY (table_name, column_name),
  CONSTRAINT uk_gc_table_name UNIQUE (table_name),
  CONSTRAINT fk_gc_tn FOREIGN KEY (table_name) REFERENCES gpkg_contents(table_name),
  CONSTRAINT fk_gc_srs FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys (srs_id)
);

CREATE VIEW st_geometry_columns AS
  SELECT
    table_name,
    column_name,
    "ST_" || geometry_type_name AS geometry_type_name,
    g.srs_id,
    srs_name
  FROM gpkg_geometry_columns as g JOIN gpkg_spatial_ref_sys AS s
  WHERE g.srs_id = s.srs_id;
  
CREATE VIEW geometry_columns AS
  SELECT
    table_name AS f_table_name,
    column_name AS f_geometry_column,
    (CASE geometry_type_name 
    	WHEN 'GEOMETRY' THEN 0 
    	WHEN 'POINT' THEN 1 
    	WHEN 'LINESTRING' THEN 2 
    	WHEN 'POLYGON' THEN 3 
    	WHEN 'MULTIPOINT' THEN 4 
    	WHEN 'MULTILINESTRING' THEN 5 
    	WHEN 'MULTIPOLYGON' THEN 6 
    	WHEN 'GEOMETRYCOLLECTION' THEN 7 
    	WHEN 'CIRCULARSTRING' THEN 8 
    	WHEN 'COMPOUNDCURVE' THEN 9 
    	WHEN 'CURVEPOLYGON' THEN 10 
    	WHEN 'MULTICURVE' THEN 11 
    	WHEN 'MULTISURFACE' THEN 12 
    	WHEN 'CURVE' THEN 13 
    	WHEN 'SURFACE' THEN 14 
    	WHEN 'POLYHEDRALSURFACE' THEN 15 
    	WHEN 'TIN' THEN 16 
    	WHEN 'TRIANGLE' THEN 17 
    	ELSE 0 END) AS geometry_type,
    2 + (CASE z WHEN 1 THEN 1 WHEN 2 THEN 1 ELSE 0 END) + (CASE m WHEN 1 THEN 1 WHEN 2 THEN 1 ELSE 0 END) AS coord_dimension,
    srs_id AS srid
  FROM gpkg_geometry_columns