D 2019-05-14T14:47:33.326 L PROJ.6 P 7009c63e1a2d7cb655dd2e31173254f20da57875 U sandro W 44798 back

Introduction

PROJ is a well-known library for performing conversions between cartographic projections.
It's universally supported by almost all open source GIS-oriented applications and packages, so there is no need to waste time in futher presentations.
We just need a bit of history to fully understand the current state of the art of this library:

Past History

Timeline
Very few users and developers do really realize how ancient is PROJ, and how far in time it started moving its first steps:
Short conclusion: the fourth version of PROJ (aka PROJ.4) lasted for about two decades, a very uncommon situation.
And consequently a full generation of developers and users became sincerely convinced that PROJ.4 was the real name of the library.

The revolution comes

Timeline:
Note: the whole transition implies many relevant changes, so that a deeply revised API will be required. In other words, the old PROJ.4 and the new PROJ.7 will support two different APIs, thus abruptly breaking cross-version compatibility.
This is an umpleasant new, because it practically means that all software modules depending on PROJ (this including SpatiaLite) will require a not at all trivial rewrite in order to fulfill the new API requirements.
But when you consider that's the first time in its very long life that PROJ requires an extra effort in order to introduce so many useful innovations, this unexpected API breakage looks fully justified and absolutely reasonable.

More details about the API breakage:

What's new in PROJ.6

Fully supporting ISO 19162:2018 WKT

Old versions of PROJ (including PROJ.4) required to define each CRS (Coordinate Reference System) by a corresponding proj-string. The following table exemplifies the case of few CRSes:

SRIDCRS Nameproj-string (PROJ.4 style)proj-string (PROJ.6 style)
3003Monte Mario / Italy zone 1 +proj=tmerc +lat_0=0 +lon_0=9 +k=0.9996 +x_0=1500000 +y_0=0 \
+ellps=intl +towgs84=-104.1,-49.1,-9.9,0.971,-2.917,0.714,-11.68 +units=m +no_defs		+proj=tmerc +lat_0=0 +lon_0=9 +k=0.9996 +x_0=1500000 +y_0=0 \
+ellps=intl +units=m +no_defs +type=crs
4326WGS 84 +proj=longlat +datum=WGS84 +no_defs+proj=longlat +datum=WGS84 +no_defs +type=crs
32632WGS 84 / UTM zone 32N +proj=utm +zone=32 +datum=WGS84 +units=m +no_defs+proj=utm +zone=32 +datum=WGS84 +units=m +no_defs +type=crs

PROJ.6 still continues to support the old proj-strings, but it now adopts a slightly changed notation:
Althought new versions of PROJ (starting since PROJ.6) still continue to support the old proj-strings, this is no longer the preferred notation for defining a CRS.
Now the preferred notation is fully conformant to the ISO 19162:2018 international standard (OGC Abstract Specification Topic 2: “Referencing By Coordinates”).
The following table exemplifies the same CRSes as above in the more recent ISO WKT notation:

SRID3003432632632
CRS NameMonte Mario / Italy zone 1WGS 84WGS 84 / UTM zone 32N
ISO-2018 WKT PROJCRS["Monte Mario / Italy zone 1", BASEGEODCRS["Monte Mario", DATUM["Monte Mario",
ELLIPSOID["International 1924",6378388,297, LENGTHUNIT["metre",1]]], PRIMEM["Greenwich",0, ANGLEUNIT["degree",0.0174532925199433]]], CONVERSION["Italy zone 1", METHOD["Transverse Mercator", ID["EPSG",9807]], PARAMETER["Latitude of natural origin",0, ANGLEUNIT["degree",0.0174532925199433], ID["EPSG",8801]], PARAMETER["Longitude of natural origin",9, ANGLEUNIT["degree",0.0174532925199433], ID["EPSG",8802]], PARAMETER["Scale factor at natural origin",0.9996, SCALEUNIT["unity",1], ID["EPSG",8805]], PARAMETER["False easting",1500000, LENGTHUNIT["metre",1], ID["EPSG",8806]], PARAMETER["False northing",0, LENGTHUNIT["metre",1], ID["EPSG",8807]]], CS[Cartesian,2], AXIS["easting (X)",east, ORDER[1], LENGTHUNIT["metre",1]], AXIS["northing (Y)",north, ORDER[2],
LENGTHUNIT["metre",1]], AREA["Italy - west of 12°E"], BBOX[36.53,5.94,47.04,12], ID["EPSG",3003]] 		GEODCRS["WGS 84", DATUM["World Geodetic System 1984", ELLIPSOID["WGS 84",6378137,298.257223563, LENGTHUNIT["metre",1]]], PRIMEM["Greenwich",0, ANGLEUNIT["degree",0.0174532925199433]], CS[ellipsoidal,2], AXIS["geodetic latitude (Lat)",north, ORDER[1], ANGLEUNIT["degree",0.0174532925199433]], AXIS["geodetic longitude (Lon)",east, ORDER[2], ANGLEUNIT["degree",0.0174532925199433]], AREA["World"], BBOX[-90,-180,90,180], ID["EPSG",4326]] PROJCRS["WGS 84 / UTM zone 32N", BASEGEODCRS["WGS 84", DATUM["World Geodetic System 1984", ELLIPSOID["WGS 84",6378137,298.257223563, LENGTHUNIT["metre",1]]], PRIMEM["Greenwich",0, ANGLEUNIT["degree",0.0174532925199433]]], CONVERSION["UTM zone 32N", METHOD["Transverse Mercator", ID["EPSG",9807]], PARAMETER["Latitude of natural origin",0, ANGLEUNIT["degree",0.0174532925199433], ID["EPSG",8801]], PARAMETER["Longitude of natural origin",9, ANGLEUNIT["degree",0.0174532925199433], ID["EPSG",8802]], PARAMETER["Scale factor at natural origin",0.9996, SCALEUNIT["unity",1], ID["EPSG",8805]], PARAMETER["False easting",500000, LENGTHUNIT["metre",1], ID["EPSG",8806]], PARAMETER["False northing",0, LENGTHUNIT["metre",1], ID["EPSG",8807]]], CS[Cartesian,2], AXIS["(E)",east, ORDER[1], LENGTHUNIT["metre",1]], AXIS["(N)",north, ORDER[2], LENGTHUNIT["metre",1]], AREA["World - N hemisphere - 6°E to 12°E - by country"], BBOX[0,6,84,12], ID["EPSG",32632]]

As you can easily notice, the two notations are profoundly different. The old proj-string notation is extremely concise and rough, whilst the new ISO-WKT is verbose but exhaustive, detailed and precise.
There is no possible match: ISO-WKT is clearly superior and more sophisticated under any possible aspect.
Not at all surprisingly, coordinate transformations based on ISO-WKT definitions (instead of proj-strings) are usually expected to be more accurate.

And that's not all; the old proj-strings weren't formally defined by any standard, and only PROJ, GDAL and few other FLOSS / GFOSS implementations could be expected to understand them.
At the opposite, the new ISO-WKT is formally defined by an international standard, and almost all free and proprietary implementations are expected to support it.
This is a very relevant change that ensures a robust compatibility between different applications.

Important notice: there are several dialects in the WKT notation describing CRSes.
All them share the same common core and mainly differ in few minor details.
PROJ.6 can understand the following dialects:
  1. ESRI WKT: a dialect widely adopted by ESRI proprietary software.
  2. GDAL WKT1: a dialect historically supported by the open source GDAL library.
  3. WKT2:2015: as defined by ISO 19162:2015 standard specification (now superseded).
  4. WKT2:2018: as defined by the more recent ISO 19162:2018 standard specification .

The private SQLite database supporting PROJ.6

Starting since PROJ.6 the PROJ library is supported by a private SQLite database containing all definitions about supported CRSes, Ellipsoids, Prime Meridians, Units of Measure, Conversions, Transformations and alike.
Note: many of the new advanced PROJ.6 features will fail if such database is not available at run time.

Direct transformations from CRS to CRS

Differently from all previous versions, PROJ.6 can now apply any transformation from a CRS to another without requiring to specify any proj-string or WKT expression qualifying each CRS.
This is because PROJ.6 is now capable to retrieve any CRS definition directly from its own private SQLite database, and consequently each CRS can be fully identified by simply specifying its symbolic name, as in:

Supporting specific areas of use

PROJ.6 is now able to make a distinction between different areas of use within a CRS, and can eventually apply the most appropriate transformation when the intended area-of-use is explicitly specified, thus leading to more precise results.

The side figure shows the situation for the Italian peninsula.
As you can notice there are two main areas (East and West of longitude 12E), but there are several smaller areas covering specific regions.
  • The two main areas correspond to EPSG:3003 Monte Mario / Italy zone 1 and EPSG:3004 Monte Mario / Italy zone 2.
  • The smallest areas correspond to the more specific areas of use now supported by PROJ.6

If your specific area of interest is contained within one of such sub-regions you can usefully inform PROJ.6 about the intended BBOX, and then PROJ.6 will attempt to use the most specific transformation parameters available.

We'll examine in more depth this topic on following sections. 		italy areas of use


Concepts to keep well in mind

  • forget the old proj-strings; they are no longer the best way for taking full profit from PROJ.6
  • the best way to make the most of PROJ.6 capabilities is by leaving the library fully free to choose the best possible transformation from the origin and the destination CRSes.
    but this strictly requires that the library will be backed up by its own private database.

Additional concept

The traditional spatial_ref_sys table contained within any SpatiaLite DB noticeably changes its intended scope in PROJ.6:
  • srid, auth_name, auth_srid and ref_sys_name columns are still required in order to define all supported CRSes and to support relations based on Primary and Foreign Keys.
  • but columns proj4text and srtext are now practically useless, because PROJ.6 is now capable to fully define any CRS by directly querying its own private database.
  • Future Forecasts: both proj4text and srtext will be probably removed in some future version of SpatiaLite.
    For now they continue to be supported mainly to the benefit of previous versions of SpatiaLite being based on the now superseded PROJ.4

Transformation pipelines

There is a last astonishing improvement supported by PROJ.6, that are transformation pipelines.
When using a pipeline you can freely define any complex geodetic transformation by chaining together many elementary steps such as conversion, transformation, projection, axis swap and so on.
A pipeline is conceptually similar to a UNIX shell script, with a dataflow regularly proceding forward from step to step until producing the final result.
The following is a practial example of a pipeline corresponding to a transformation from EPSG:4326 WGS 84 to EPSG:32632 WGS 84 / UTM zone 32N: +proj=pipeline +step +proj=axisswap +order=2,1 +step +proj=unitconvert +xy_in=deg +xy_out=rad +step +proj=utm +zone=32 +ellps=WGS84
Useful hint: PROJ.6 supports a new CLI tool (projinfo) for inspecting in full detail the internal definitions of CRSes, Transformations etc.
The above pipeline is simply the output of the following command: projinfo -s EPSG:4326 -t EPSG:32632 -o proj projinfo is a very precious resource; don't esitate to frequently use it in order to discover what's really happening behind the scenes.
It's the best and easiest way for fully understanting how PROJ.6 do really works.


In order to learn more about the many interesting cool new features supported by PROJ.6 please consult the original documentation

PROJ.6 support on SpatiaLite-5.0.0

Creating and populating the spatial_ref_sys metatable

There is no relevant change. As in all previous versions spatial_ref_sys will be automatically created and properly populate by calling the appropriate SQL function. SELECT InitSpatialMetaData(1); or SELECT InitSpatialMetaDataFull(1);
Please remeber that now in SpatiaLite-5.0.0 InitSpatialMetaDataFull represents the preferred method for creating all metadata tables required by this version.
InitSpatialMetaData is still maintained so to not break historical compatibility, but shouldn't be used any longer.

The most relevant change is that different versions of SpatiaLite/PROJ will now behave in radically different ways, as summarized in the following cross-version compatibility table.

SpatiaLite / PROJ versionConnected DBAction
SpatiaLite-5 built on PROJ.6 DB created by SpatiaLite-5 / PROJ.6 libspatialite-5 when built on PROJ.6 will always ignore both the proj-strings stored in column proj4text and the WKT definitions stored in column srtext.
It will instead leave PROJ.6 fully free to use the CRS definitions contained in its own private database.

Note that this applies indifferently to both legacy databases created by any previous version and most modern databases created by the latest version.
DB created by SpatiaLite-5 / PROJ.4
or
by any previous version of SpatiaLite (< 5.0.0)
SpatiaLite-5 built on PROJ.4
or
any previous version of SpatiaLite (< 5.0.0)		 DB created by SpatiaLite-5 / PROJ.6 When libspatialite (any version, including 5.0.0) is not supported by PROJ.6 then the traditional approach will be always applied, and each CRS will be consequently identified by its corresponding proj-string stored in column proj4text

Note that even when PROJ.6 is not supported any version of SpatiaLite (this including 5.0.0) can safely connect to any DB-file created by the most recent version with full PROJ.6 support.
DB created by SpatiaLite-5 / PROJ.4
or
by any previous version of SpatiaLite (< 5.0.0)


Existing SQL functions affected by PROJ.6

SQL FunctionExtended signatureBehavior under PROJ.6
ST_Transform() ST_Transform ( geom Geometry , newSrid Integer , area Geometry ) : Geometry
ST_Transform ( geom Geometry , newSrid Integer , area Geometry , proj_string_from Text ) : Geometry
ST_Transform ( geom Geometry , newSrid Integer , area Geometry , proj_string_from Text , proj_string_to Text ) : Geometry
Note: all these extended signatures are supported only when libspatialite-5.0 has been built on the top of PROJ.6
If the library has been built instead on earlier versions of PROJ any attempt to call these extended signatures will just return a wrong number of arguments to function ST_Transform() error.

Any invalid argument passed to ST_Transform() (extended signatures) will raise an SQL Exception.

  • the optional argument area may be NULL (default setting).
    Otherwise it's expected to contain a valid Geometry in SRID=4326 (long/lat). When an area of use is explicitly supplied, then PROJ.6 may eventually use the corresponding BBOX for finely tuning the more accurate transformation.
  • both the optional arguments proj_string_from and proj_string_to may be NULL (default setting).
    Otherwise they are exèpected to contain a valid string to be used for identifying the corresponding CRS.
    PROJ.6 accepts the following CRS strings:
    • traditional proj-strings used by all previous versions of PROJ, as e.g.
      • +proj=merc +a=6378137 +b=6378137 +lat_ts=0 +lon_0=0 +x_0=0 +y_0=0 +k=1 +units=m +nadgrids=@null +wktext +no_defs
      • +proj=utm +zone=32 +south +ellps=WGS72 +towgs84=0,0,4.5,0,0,0.554,0.2263 +units=m +no_defs
      • +proj=utm +zone=32 +ellps=intl +towgs84=-87,-98,-121,0,0,0,0 +units=m +no_defs
  • any WKT expression defining a CRS.
  • a CRS name as e.g.
    • WGS 84
    • WGS 84 / UTM zone 32N
    • IGM95 / UTM zone 33N
  • any canonical CRS referenc as e.g.
    • EPSG:4326
    • urn:ogc:def:crs:EPSG::3003

  • Special case: if proj_string_to is NULL whilst proj_string_from is explicitly set, then it will interpreted as an user defined transformation string (possibly, as a transformation pipeline) as e.g.
    • +proj=pipeline
      +step +proj=axisswap +order=2,1
      +step +proj=unitconvert +xy_in=deg +xy_out=rad
      +step +proj=push +v_3
      +step +proj=cart +ellps=WGS84
      +step +inv +proj=helmert +x=-104.1 +y=-49.1 +z=-9.9 +rx=0.971 +ry=-2.917 +rz=0.714 +s=-11.68 +convention=position_vector
      +step +inv +proj=cart +ellps=intl
      +step +proj=pop +v_3
      +step +proj=tmerc +lat_0=0 +lon_0=9 +k=0.9996 +x_0=1500000 +y_0=0 +ellps=intl

Notes

  • SpatiaLite does not constraints you in any way and fully respects users freedom carefully avoiding auto-magical assumptions.
    • The new extended signatures supported by ST_Transform closely mimic the most recent PROJ.6 API, so you are free to define your coordinates transformations in several different ways.
    • The default behavior of SpatiaLite when using PROJ.6 is to completely ignore the CRS definitions stored within the internal table spatial_ref_sys
      Any required CRS definition will be always retrieved from PROJ.6 itself that will simply receive a request in the form from EPSG:32632 to EPSG:3003.
    • If you have any good reason suggesting to do such a thing, you are anyway free to use any kind of proj-strings, WKT definitions or CRS names supported by PROJ.6.
      But this is under your full responsibility, and you must be well self-conscious of what are you doing.
  • SpatiaLite encourages and promotes the most creative uses of PROJ.6, this including using your own customized transformation pipelines.
    Correctly using custom transformations isn't necessarily simple and easy, but it's a big opportunity for all skilled and talented power users.



Few practical SQL examples: -- from EPSG:4326 to EPSG:3003 - default settings; ignoring spatialite_ref_sys and relying on PROJ.6 own private database SELECT AsEWKT ( ST_Transform( MakePoint( 11.878056 , 43.463056 , 4326 ) , 3003 )); ------------------------------------- SRID=3003;POINT(1732852.942716769 4816277.617690674) -- from EPSG:4326 to EPSG:3003 - exactly the same as above, but this time in an explicit form SELECT AsEWKT ( ST_Transform( MakePoint( 11.878056 , 43.463056, 4326 ) , 3003, NULL, 'EPSG:4326', 'EPSG:3003')); ------------------------------------- SRID=3003;POINT(1732852.942716769 4816277.617690674) -- from EPSG:4326 to EPSG:3003 - same as above, using the alternative notation SELECT AsEWKT ( ST_Transform( MakePoint( 11.878056 , 43.463056, 4326 ) , 3003, NULL, 'urn:ogc:def:crs:EPSG::4326', 'urn:ogc:def:crs:EPSG::3003')); ------------------------------------- SRID=3003;POINT(1732852.942716769 4816277.617690674) -- from EPSG:4326 to EPSG:3003 - explicitly passing CRS names SELECT AsEWKT ( ST_Transform( MakePoint( 11.878056 , 43.463056, 4326 ) , 3003, NULL, 'WGS 84', 'Monte Mario / Italy zone 1')); ------------------------------------- SRID=3003;POINT(1732852.942716769 4816277.617690674) -- from EPSG:4326 to EPSG:3003 - simulating the classic PROJ.4 approach based on proj-strings stored into spatial_ref_sys SELECT AsEWKT ( ST_Transform( MakePoint( 11.878056 , 43.463056, 4326 ) , 3003 , NULL , (SELECT proj4text FROM spatial_ref_sys WHERE srid = 4326), (SELECT proj4text FROM spatial_ref_sys WHERE srid = 3003))); ------------------------------------- SRID=3003;POINT(1732852.942716769 4816277.617690674) -- from EPSG:4326 to EPSG:3003 - fancy mode based on WKT expressions stored into spatial_ref_sys SELECT AsEWKT ( ST_Transform( MakePoint( 11.878056 , 43.463056, 4326 ) , 3003, NULL, (SELECT srtext FROM spatial_ref_sys WHERE srid = 4326), (SELECT srtext FROM spatial_ref_sys WHERE srid = 3003))); ------------------------------------- SRID=3003;POINT(1732852.942716769 4816277.617690674) This first test is based on a new SpatiaLite database created with full PROJ.6 support. There isn't anything worth to be noted, except that all methods return the same results.

-- from EPSG:4326 to EPSG:3003 - using a transformation pipeline SELECT AsEWKT ( ST_Transform( SwapCoords ( MakePoint( 11.878056 , 43.463056, 4326 ) ) , 3003 , NULL , '+proj=pipeline +step +proj=axisswap +order=2,1 +step +proj=unitconvert +xy_in=deg +xy_out=rad +step +proj=push +v_3 +step +proj=cart +ellps=WGS84 +step +inv +proj=helmert +x=-104.1 +y=-49.1 +z=-9.9 +rx=0.971 +ry=-2.917 +rz=0.714 +s=-11.68 +convention=position_vector +step +inv +proj=cart +ellps=intl +step +proj=pop +v_3 +step +proj=tmerc +lat_0=0 +lon_0=9 +k=0.9996 +x_0=1500000 +y_0=0 +ellps=intl' )); ------------------------------------- SRID=3003;POINT(1732852.942716769 4816277.617690674) -- from EPSG:4326 to EPSG:3003 - using a customized transformation pipeline SELECT AsEWKT ( ST_Transform( MakePoint( 11.878056 , 43.463056, 4326 ) , 3003 , NULL , '+proj=pipeline +step +proj=unitconvert +xy_in=deg +xy_out=rad +step +proj=push +v_3 +step +proj=cart +ellps=WGS84 +step +inv +proj=helmert +x=-104.1 +y=-49.1 +z=-9.9 +rx=0.971 +ry=-2.917 +rz=0.714 +s=-11.68 +convention=position_vector +step +inv +proj=cart +ellps=intl +step +proj=pop +v_3 +step +proj=tmerc +lat_0=0 +lon_0=9 +k=0.9996 +x_0=1500000 +y_0=0 +ellps=intl' )); ------------------------------------- SRID=3003;POINT(1732852.942716769 4816277.617690674) This second test is based on using transformation pipelines:

Tech Note: using projinfo for discovering pipelines and areas of use

projinfo -s EPSG:4326 -t EPSG:3003 -o proj --spatial-test intersect Candidate operations found: 3 ------------------------------------- Operation n°1: unknown id, Inverse of Monte Mario to WGS 84 (4) + Italy zone 1, 4 m, Italy - mainland PROJ string: +proj=pipeline +step +proj=axisswap +order=2,1 +step +proj=unitconvert +xy_in=deg +xy_out=rad +step +proj=push +v_3 +step +proj=cart +ellps=WGS84 +step +inv +proj=helmert +x=-104.1 +y=-49.1 +z=-9.9 +rx=0.971 +ry=-2.917 +rz=0.714 +s=-11.68 +convention=position_vector +step +inv +proj=cart +ellps=intl +step +proj=pop +v_3 +step +proj=tmerc +lat_0=0 +lon_0=9 +k=0.9996 +x_0=1500000 +y_0=0 +ellps=intl ------------------------------------- Operation n°2: unknown id, Inverse of Monte Mario to WGS 84 (2) + Italy zone 1, 4 m, Italy - Sardinia onshore PROJ string: +proj=pipeline +step +proj=axisswap +order=2,1 +step +proj=unitconvert +xy_in=deg +xy_out=rad +step +proj=push +v_3 +step +proj=cart +ellps=WGS84 +step +inv +proj=helmert +x=-168.6 +y=-34 +z=38.6 +rx=-0.374 +ry=-0.679 +rz=-1.379 +s=-9.48 +convention=position_vector +step +inv +proj=cart +ellps=intl +step +proj=pop +v_3 +step +proj=tmerc +lat_0=0 +lon_0=9 +k=0.9996 +x_0=1500000 +y_0=0 +ellps=intl ------------------------------------- Operation n°3: unknown id, Inverse of Monte Mario to WGS 84 (11) + Italy zone 1, 10 m, Italy - Sicily Strait west of 13°E PROJ string: +proj=pipeline +step +proj=axisswap +order=2,1 +step +proj=unitconvert +xy_in=deg +xy_out=rad +step +proj=push +v_3 +step +proj=cart +ellps=WGS84 +step +proj=helmert +x=230.47 +y=56.08 +z=-22.43 +step +inv +proj=cart +ellps=intl +step +proj=pop +v_3 +step +proj=tmerc +lat_0=0 +lon_0=9 +k=0.9996 +x_0=1500000 +y_0=0 +ellps=intl

-- to EPSG:3003 - default settings, NULL area of use, - ignoring spatialite_ref_sys and using the PROJ.6 own private database SELECT AsEWKT ( ST_Transform( MakePoint( 9.169464, 39.478275, 4326 ), 3003 )); ------------------------------------- SRID=3003;POINT(1514600.134321862 4369874.489269957) -- to EPSG:3003 - same as above, but explicitly defining a specific area of use SELECT AsEWKT ( ST_Transform( MakePoint( 9.169464, 39.478275, 4326 ), 3003 , BuildMBR(9.1 , 39.4 , 9.2 , 39.5 , 4326 ))); ------------------------------------- SRID=3003;POINT(1514605.982762248 4369873.004226943) Third and last example. In some cases explicitly defining a specific area of use may enable PROJ.6 to select an optimized transformation leading to more precise results.
In this example we've used a Point located in Sardinia, and PROJ.6 supports several flavors of EPSG:3003 depending on the specific area of use.
Italy - Sardinia onshore is one between them, so after specifying an explicit area of use a more precise transformation pipeline was selected.

Lesson learned:
  • The best mode for taking full profit from PROJ.6 is by using method=auth_name:auth_srid (that is the default setting).
    This way you'll be absolutely sure that the most recent and fully detailed definitions will be always retrieved from the PROJ.6 own database completely ignoring any definition (may be incomplete or obsolete) eventually stored into the spatial_ref_sys table. This is a particulary useful option when processing older databases being created by earlier versions of SpatiaLite not yet supporting PROJ.6
  • SpatiaLite does not constraints you in any way and fully respects users freedom carefully avoiding auto-magical assumptions.
    If you have any good reason suggesting to do such a thing you are anyway free to use the proj-strings (method=proj4text) or the WKT definitions (method=srtext) stored into the spatial_ref_sys table.
    But this is under your full responsibility, and you must be well sure of what are you doing.
  • SpatiaLite encourages and promotes the most creative uses of PROJ.6, this including transformation pipelines.
    Correctly using method=proj_string for computing custom transformations isn't necessarily simple and easy, but it's a big opportunity for all skilled and talented power users.

New auxiliary SQL functions specifically supporting PROJ.6

SQL FunctionSupported argumentsDescription
HasProj6( void ) : Boolean None Will return 1 (TRUE) if the library has been built on PROJ.6 (or any later version), otherwise 0 (FALSE).
PROJ_GetLastErrorMsg( void ) : String None Will return the most recent error message returned by PROJ (if any).
NULL will be returned if there is no curently pending PROJ error.
PROJ_GetDatabasePath( void : String) None Will return the currently set pathname leading to the private PROJ's SQLite database.
NULL will be returned if there is no private PROJ's SQLite database currently connected.
PROJ_SetDatabasePath ( new_path String ) : String
  • new_path: a relative or absolute pathname leading to a valid PROJ's SQLite database.
 Will change the currently set pathname leading to the private PROJ's SQLite database.
NULL will be returned if the passed path is invalid, otherwise the path of the currently set private PROJ's SQLite database will be returned.
PROJ_AsWKT ( auth_name String , auth_srid Integer ) : String
PROJ_AsWKT ( auth_name String , auth_srid Integer , wkt_style String ) : String
PROJ_AsWKT ( auth_name String , auth_srid Integer , wkt_style String , indented Boolean ) : String
PROJ_AsWKT ( auth_name String , auth_srid Integer , wkt_style String , indented Boolean , indentation Integer ) : String
  • auth_name and auth_srid identify the intended CRS.
    auth_name can be NULL, and in this case EPSG will be assumed.
  • the optional argument style determines which specific WKT format should be adopted, and must be one between GDAL, ESRI, ISO-2015 or ISO-2018 (this latter being the default setting).
  • the optional argument indented if set to TRUE will nicely format a multiline WKT expression, otherwise a single monolithic line lacking any white-space or new-line will be printed (the default is TRUE).
  • the optional argument indentation determines how many white-spaces are to be used for indenting (only meaningful if indented=TRUE; the default values is 4).
 Will return the WKT expression corresponding to a given CRS; the definitions will be taken directly from the private PROJ's own database.
NULL will be returned on failure or on invalid arguments.
PROJ_GuessSridFromWKT ( wkt_expr String ) : Integer
  • wkt_expr: the WKT expression to be evaluated.
 Will possibly return the SRID value corresponding to a given WKT expression defining a CRS.
-1 will be returned if no CRS supported by PROJ.6 matches the WKT expression.
NULL will be returned on invalid argument.
PROJ_GuessSridFromSHP ( filename String ) : Integer
  • filename: the absolute or relative path leading to some Shapefile.
    Note: exactley as required by ImportSHP() filename must omit any .shp, .shx, .dbf or .prj suffix.
 Will possibly return the SRID value corresponding to the CRS defined by the .PRJ member of the Shapefile.
-1 will be returned if no CRS supported by PROJ.6 matches PRJ member of the Shapefile.
NULL will be returned on invalid argument.
Please note well: this SQL function opend the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.

Note: all the above SQL Functions will be available only when Spatialite-5.0.0 has been built against PROJ.6 (or any subsequent version).
If SpatiaLite-5.0.0 has been built instead against any previosus version (as e.g. PROJ.4) any attempt to call one of these SQL Functions will simply return a no such function SQL error.

Practical examples: SELECT PROJ_GetDatabasePath(); -- retrieving the currently set PROJ's own database ------------------------------ /usr/local/share/proj/proj.db SELECT PROJ_SetDatabasePath('/home/sandro/not_existing_proj.db'); -- non existing database ------------------------------ NULL SELECT PROJ_GetLastErrorMsg(); -- retrieving the failure cause ------------------------------ proj_context_set_database_path: Open of /home/sandro/not_existing_proj.db failed SELECT PROJ_SetDatabasePath('/home/sandro/valid_proj.db'); -- valid database ------------------------------ /home/sandro/valid_proj.db SELECT PROJ_AsWKT('EPSG', 4326); -- default settings: ISO-2018 style, indented with indentation 4 ------------------------------ GEODCRS["WGS 84", DATUM["World Geodetic System 1984", ELLIPSOID["WGS 84",6378137,298.257223563, LENGTHUNIT["metre",1]]], PRIMEM["Greenwich",0, ANGLEUNIT["degree",0.0174532925199433]], CS[ellipsoidal,2], AXIS["geodetic latitude (Lat)",north, ORDER[1], ANGLEUNIT["degree",0.0174532925199433]], AXIS["geodetic longitude (Lon)",east, ORDER[2], ANGLEUNIT["degree",0.0174532925199433]], AREA["World"], BBOX[-90,-180,90,180], ID["EPSG",4326]] SELECT PROJ_AsWKT('EPSG', 4326, 'ESRI', 0, 0); -- ESRI style, monolithic line ------------------------------ GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]] SELECT PROJ_GuessSridFromWKT('GEOGCS["GCS_Monte_Mario_Rome",DATUM["D_Monte_Mario",SPHEROID["International_1924",6378388.0,297.0]],PRIMEM["Rome",12.4523333333333],UNIT["Degree",0.0174532925199433]]'); ------------------------------ 4806 SELECT PROJ_GuessSridFromSHP('/home/sandro/tuscany_shp/prov2011'); -- remember: requires to explicitly set SPATIALITE_SECURITY=relaxed ------------------------------ 3003

Important notice for Windows users

PROJ.6 critically depends on its own private SQLite database (proj.db) containing all relevant definitions about Ellipsoids, Prime Meridians, CRSes, Transformations and alike.
If PROJ.6 is unable to correctly establish a connection to this database it will be severely limited and will not be able to correctly behave as expected.

  • Short conclusion: installing the software alone is not enough.
    The PROJ.6 private database proj.db must be properly installed as well, in order to ensure that anything runs smoothly.

This isn't usually a big issue on Linux and Unix-like platforms, where a rational and very clear filesystem layout exists.
On these operating systems the package manager (or make install) will automatically take care to install proj.db on the most appropriate directory (usually as /usr/share/proj/proj.db or as /usr/local/share/proj/proj.db) and that's all.

Things are unhappily a little bit more difficults on Windows platforms. The user itself is responsible for properly installing proj.db So it becomes critical understanding the basic rules adopted by libspatialite in order to properly locate proj.db on Windows.

Where proj.db is expected to be found on Windows

  1. the first place where proj.db will be searched is the same folder form where the EXE binary was initially loaded.
    Example: assuming that you are currently executing C:\myprogs\spatialite\bin\spatialite.exe
    • then an attempt will be made to connect C:\myprogs\spatialite\bin\proj.db
  2. a second attempt will be made on behalf of the Public folder.
    Example: an attempt will be made to connect C:\Users\Public\spatialite\proj\proj.db
  3. a third (and last) attempt will be made on behalf of the User folder.
    Example: an attempt will be made to connect C:\Users\sandro\spatialite\proj\proj.db

An useful diagnostic check

SELECT PROJ_GetDatabasePath();
  • if NULL is returned, then PROJ.6 is definitely unable to connect to its own private SQLite database proj.db
    You have to carefully verifiy that it's really installed on one of the expected standard locations.
  • otherwise the path leading to the currently connected proj.db will be returned.



Using the environment variable SPATIALITE_PROJ_DB_PATH

If the environment variable SPATIALITE_PROJ_DB_PATH is set, then libspatialite will simply attempt to connect the database pointed by this variable.
Such a capability is supported not only on Windows but also on Linux (and on any other platform).

Linux example:
  • export "SPATIALITE_PROJ_DB_PATH=/home/sandro/aux/proj.db"
    echo $SPATIALITE_PROJ_DB_PATH
    spatialite


Windows example:
  • SET SPATIALITE_PROJ_DB_PATH=C:\Users\sandro\aux\proj.db
    echo %SPATIALITE_PROJ_DB_PATH%
    spatialite_gui




back Z ad460d934eef48a7d63bfcc2415eea08