Goal of the new tool would be:<br>
<ul>to allow a User to update an existing geometry field that contains a <b>Z</b> Value<br> by searching for the nearest POINTZ (or POINTZM) of a Dem-Database</ul>
<hr>
Preconditions:<br>
<ul>an existing Database (<b>Source</b>) Geometry field has been converted to a Dimension Z (or ZM)<br>
<ul>the TABLE <b>must</b> be a SpatialTable, since <b>ROWID</b> will be used to update</ul>
An Dem-Database (<b>Dem</b>) exists that contains a POINTZ or POINTZM Geometry<br>
The (<b>Dem</b>) contains a SpatialIndex<br>
Both Geometries contain a valid Srid</ul>
<hr>
Input Parameters:<br><br>
For both <b>Source</b> and <b>Dem</b>
<ul>Path to Database<br>Table<br>Geometry-Column</ul>
<br>Optional Parameter:<br>
<ul>resolution of Dem<ul>which by default can be calculated by
<ul>
<pre>
(extent_maxx_dem-extent_minx_dem)*(extent_maxy_dem-extent_miny_dem)/(double)rows_count_dem
</pre>
assuming that the <b>Dem</b> has been completely filled with a Geometry
</ul>
this parameter can be used to override this value if not correct.
</ul>
</ul>
<hr>
Commands:<br>
<ul><b>sniff</b>
 <ul>which would allow the User to prepare the command syntax<br>receiving a message that all preconditions are fulfilled.<br>
<b>Source</b> and <b>Dem</b>  can be done separately or together<br>
Goal is to ease the creation of the parameters and receiving information about what is to happen
 </ul>
</ul>
<br>
<ul><b>update</b>
 <ul>everything done during a <b>sniff</b>, will be done here<br>
if both <b>Source</b> and <b>Dem</b> are correct, the update will start<br>
All of the (not NULL) geometries will be selected
<ul>if the Srid of the two are different, a transformed geometry will also be created</ul>
A function called <b>gaiaUpdateZ</b> (based on gg_transform.c gaiaTransformCommon) is called with both geometries and the extent of <b>Dem</b>
<ul>only Geometries <b>completely</b> contained inside the <b>Dem</b> will be processed (returning NULL)</ul>
<ul>a Geometry will be returned when <b>any</b> of the Z-Values (that are not 0.0) have changed (otherwise returning NULL)</ul>
if the result <b>gaiaUpdateZ</b> returns NOT NULL, the Geometry will be updated
</ul>
</ul>
<hr>
<b>retrieve_dem_points</b>:<br>
is called during <b>gaiaUpdateZ</b> (4 times, when needed) to retrieve the nearest point from a sent array of x/y points
<ul><b>gaiaUpdateZ</b> will collect and send (when needed the transformed) points to
A query is called to the <b>ATTACH</b>ed Database, using the SpatialIndex<br>
<ul>The set resolution is used with <b>ST_Buffer</b> for the
Checking is done if the returned <b>z</b> (or possibly, yet unlikely, a <b>m</b>) value is different from the sent value.<br>
Counters exist in all functions to insure that update will only be done when needed
<ul>but also to inform the user the amount of geometries/points and how many of those have been changed</ul>
<hr>
At present the 'Help' looks like this:<br>
<pre>
usage: spatialite_updatez ARGLIST
==============================================================
-h or --help                    print this help message
========================== Parameters ========================
  -- -- ---------------- Dem-Data Database ---------------- --
-ddem or --dem-path  pathname to the SpatiaLite Dem DB
-tdem or --table-dem table_name [SpatialTable or SpatialView]
-gdem or --geometry-dem-column col_name the Geometry column
	 must be a POINT Z or a POINT ZM type
-rdem or --dem-resolution of the dem points while searching
	 the automatic resolution calculation is based on the row_count
	 within the extent, which may not be correct!
	 Use '-rdem' to set a realistic value

  -- -- ----------------- Source Database ----------------- --
-d or --db-path pathname to the SpatiaLite DB
-t or --table table_name,  must be a SpatialTable
-g or --geometry-column the Geometry column to update
	 must  be a Z or a ZM Dimension type
	 use CastToXYZ(geom) or CastToXYZM(geom) to convert
  -- -- --------------- General Parameters ---------------- --
-mdem or --copy-m [0=no, 1= yes [default] if exists]

  -- -- -------------------- Notes:  ---------------------- --
-I-> the Z value will be copied from the nearest point found
-I-> the Srid of the source Geometry and the Dem-POINT can be different

=========================== Commands ===========================
-sniff   [default] analyse settings without UPDATE of z-values
-update Perform UPDATE of z-values
</pre>
<hr>
Tests:<br>
<ul>Valgrind reports only the 2 well known GEOS leaks</ul>
<br>
<b>berlin_dhh92.db</b><br>
Was build from 279 .xyz files and combined and sorted,<br> each containing 4000.000 records (1.116 billion)
<ul>sort -k2 -n -k1 3902_581820.txt -o 3902_581820.dhhn92.txt</ul>
Was the imported into a sqlite3 Database
<pre>
.mode csv
.separator " "
.import berlin_dhh92_import
</pre>
A Spatialite script created a copy, preparing 2 Geometries.<br>
As of date only 114 million geometries, covering an area of 12*12 Km have been created.<br>
<br>
<b>berlin_street_segments</b><ul>contains 42716 LINESTRING's, many of which are outside the Dem area (that contain geometries)</ul>

<pre>
SQLite version: 3.17.0
SpatiaLite version: 4.5.0-devel

Source: srid 3068
Source: extent min x/y(3584.6332144,1452.2834924)
	       mix x/y(48941.5043681,37058.9517384)
Source: rows_count(soldner_segment) 42720
Source: geometry_type(1002) has_z[1]
Source: spatial_index_enabled[1]
Source 'berlin_admin_geometries.db'
will convert TABLE[berlin_street_segments] with GEOMETRY-Column[soldner_segment] Z-Value
	from nearest POINT found in
Source Database: has passed all checks.

Dem: srid 25833
Dem: extent min x/y(385000.0000000,5814000.0000000)
	    max x/y(397000.0000000,5826000.0000000)
Dem: rows_count(utm_point) 1116000000
Dem: resolution(utm_point) 0.1290323
Dem: geometry_type(1001) has_z[1]
Dem: spatial_index_enabled[1]
Source 'berlin_admin_geometries.db'
will convert TABLE[berlin_street_segments] or GEOMETRY-Column[soldner_segment] Z-Value
	from nearest POINT found in
Dem 'berlin_dhh92.db'
 TABLE[berlin_dhh92_2007] with GEOMETRY-Column[utm_point]
-W-> -rdem was  set. Using; resolution(0.9990000), overriding the calculated value: 0.1290323
Dem srid[25833]: is different from the Source srid[3068].
	 When searching for the nearest point, the Source points will be transformed to srid[25833].
Dem Database: has passed all checks.
	DB-file successfully converted !!!

-I-> geometries total[42716] changed[11808] ; points total[41010] changed z[41010] changed m[0]
</pre>

Which took about 30 minutes to complete.<br><br>
The results show: that of the 42716 geometries, 11808 were updated containing 41010 points - all of which were updated.<br><br>
A sample LINESTRING that was updated:
<pre>
SRID=3068;LINESTRING(24910.98653436587 21463.5093835775 <b>35.39999999999999</b>,24948.09927146823 21394.95263390163 <b>35.60999999999999</b>)
</pre>

shows correct results.
<br><br>
A second run took about 2 minutes:
<pre>
-I-> geometries total[42716] changed[0] ; points total[41010] changed z[0] changed m[0]
</pre>

showing that no updates were made, since nothing has changed.<br><br>
Although not yet tested, it should mean that if new points are added that may not contain reliable results
<ul>a (more or less) swift update can be made from the Dem-Database.</ul>
<hr>
Conclusion:<br>
To create an SQL-Command for the same task is unrealistic, since it would be very time consuming.<br><br>
So adding this as a new tool would be justified.<br><br>
It could also be adapted with a <b>-retrieve</b> or <b>-fetch</b> command,<ul> together with a config-file where the Dem information is stored,<br> could return the Z-value from a given srid,x,y position (or EWKT).</ul>
<br>
Considering the size of these Databases, a semi-automated use of them, would be a useful extra tool.