SpatiaLite: SRID Inspection

SRID Inspection

Starting since version 4.3.0 SpatiaLite supports an extended set of SQL functions intended to make possible a complete and comprehensive qualification of all available SRIDs.
A further spatial_ref_sys_aux meta-table has been added which is complemented by a corresponding spatial_ref_sys_all view intended to simplify SQL queries.
Anyway the physical presence of this additional table isn't strictly required in order to support the new SQL functions, because they'll always attempt to apply a best effort strategy so to return the expected result.
Even when the spatial_ref_sys_aux table is not available (as e.g. it will happen in any DB-created by any previous version of the library) these SQL functions will possibly return some useful result, may be approximated or less accurate.

the spatial_ref_sys_aux table and the spatial_ref_sys_all view

CREATE TABLE spatial_ref_sys_aux (
	srid INTEGER NOT NULL PRIMARY KEY,
	is_geographic INTEGER,
	has_flipped_axes INTEGER,
	spheroid TEXT,
	prime_meridian TEXT,
	datum TEXT,
	projection TEXT,
	unit TEXT,
	axis_1_name TEXT,
	axis_1_orientation TEXT,
	axis_2_name TEXT,
	axis_2_orientation TEXT,
	CONSTRAINT fk_sprefsys FOREIGN KEY (srid)
 	REFERENCES spatial_ref_sys (srid));

CREATE VIEW spatial_ref_sys_all AS
SELECT a.srid AS srid, a.auth_name AS auth_name, a.auth_srid AS auth_srid,
       a.ref_sys_name AS ref_sys_name, b.is_geographic AS is_geographic,
       b.has_flipped_axes AS has_flipped_axes, b.spheroid AS spheroid,
       b.prime_meridian AS prime_meridian, b.datum AS datum,
       b.projection AS projection, b.unit AS unit,
       b.axis_1_name AS axis_1_name, b.axis_1_orientation AS axis_1_orientation,
       b.axis_2_name AS axis_2_name, b.axis_2_orientation AS axis_2_orientation,
       a.proj4text AS proj4text, a.srtext AS srtext
FROM spatial_ref_sys AS a
LEFT JOIN spatial_ref_sys_aux AS b ON (a.srid = b.srid);

Both the table and the view will be automatically created and populated when invoking the standard InitSpatialMetadata() SQL function.

SQL functions

SQL Function	Description
SridIsGeographic( SRID Integer ) : Boolean	Will inspect the SRID definitions checking if the SRID is of the Geographic type; will return 1 (i.e. TRUE) or 0 (i.e. FALSE). NULL will be returned on invalid argument or if the SRID is undefined.
SridIsProjected( SRID Integer ) : Boolean	Will inspect the SRID definitions checking if the SRID is of the Projected type; will return 1 (i.e. TRUE) or 0 (i.e. FALSE). NULL will be returned on invalid argument or if the SRID is undefined.
SridHasFlippedAxes( SRID Integer ) : Boolean	Will inspect the SRID definitions checking if the SRID requires a flipped Axes configuration: i.e. Y,X instead of the most usual X,Y; will return 1 (i.e. TRUE) or 0 (i.e. FALSE). NULL will be returned on invalid argument or if the SRID is undefined.
SridGetSpheroid( SRID Integer ) : Text SridGetEllipsoid( SRID Integer ) : Text	Will inspect the SRID definitions then returning the appropriate Spheroid name. NULL will be returned on invalid argument or if the SRID is undefined.
SridGetPrimeMeridian( SRID Integer ) : Text	Will inspect the SRID definitions then returning the appropriate Prime Meridian name. NULL will be returned on invalid argument or if the SRID is undefined.
SridGetDatum( SRID Integer ) : Text	Will inspect the SRID definitions then returning the appropriate Datum name. NULL will be returned on invalid argument or if the SRID is undefined.
SridGetUnit( SRID Integer ) : Text	Will inspect the SRID definitions then returning the appropriate Unit name. NULL will be returned on invalid argument or if the SRID is undefined.
SridGetProjection( SRID Integer ) : Text	Will inspect the SRID definitions then returning the appropriate Projection name. NULL will be returned on invalid argument or if the SRID is undefined.
SridGetAxis_1_Name( SRID Integer ) : Text	Will inspect the SRID definitions then returning the appropriate Name for its first axis. NULL will be returned on invalid argument or if the SRID is undefined.
SridGetAxis_1_Orientation( SRID Integer ) : Text	Will inspect the SRID definitions then returning the appropriate Orientation for its first axis. NULL will be returned on invalid argument or if the SRID is undefined.
SridGetAxis_2_Name( SRID Integer ) : Text	Will inspect the SRID definitions then returning the appropriate Name for its second axis. NULL will be returned on invalid argument or if the SRID is undefined.
SridGetAxis_2_Orientation( SRID Integer ) : Text	Will inspect the SRID definitions then returning the appropriate Orientation for its second axis. NULL will be returned on invalid argument or if the SRID is undefined.

few practical examples

SELECT SridIsGeographic(4326), SridIsProjected(4326), SridHasFlippedAxes(4326);
-----------
1    0    1


SELECT SridIsGeographic(32632), SridIsProjected(32632), SridHasFlippedAxes(32632);
-----------
0    1    0


SELECT SridGetEllipsoid(3068), SridGetDatum(3068), SridGetPrimeMeridian(3068), SridGetProjection(3068);
-----------
Bessel 1841    Deutsches_Hauptdreiecksnetz    Greenwich    Cassini_Soldner


SELECT SridGetEllipsoid(3003), SridGetDatum(3003), SridGetPrimeMeridian(3003), SridGetProjection(3003);
-----------
International 1924    Monte_Mario    Greenwich    Transverse_Mercator


SELECT SridGetAxis_1_Name(3003), SridGetAxis_1_Orientation(3003), SridGetAxis_2_Name(3003), SridGetAxis_2_Orientation(3003);
-----------
X    East    Y    North


SELECT SridGetAxis_1_Name(4326), SridGetAxis_1_Orientation(4326), SridGetAxis_2_Name(4326), SridGetAxis_2_Orientation(4326);
-----------
Latitude    North    Longitude    East

Technical Note

Starting since version 4.3.0 a brand new mechanism is adopted in order to create the internal predefined EPSG dataset.
If you are interested to learn more about this please read this file included within any source distribution: -/src/srsinit/epsg_update/README.txt
Alternatively you can read the file directly form the Fossil repository

back