<i>Concept new toll 'spatialite_updatez'</i>.
by
anonymous
2017-09-13 06:17:17.
D 2017-09-13T06:17:17.424
J foundin development
J icomment Goal\sof\sthe\snew\stool\swould\sbe:<br>\r\n<ul>to\sallow\sa\sUser\sto\supdate\san\sexisting\sgeometry\sfield\sthat\scontains\sa\s<b>Z</b>\sValue<br>\sby\ssearching\sfor\sthe\snearest\sPOINTZ\s(or\sPOINTZM)\sof\sa\sDem-Database</ul>\r\n<hr>\r\nPreconditions:<br>\r\n<ul>an\sexisting\sDatabase\s(<b>Source</b>)\sGeometry\sfield\shas\sbeen\sconverted\sto\sa\sDimension\sZ\s(or\sZM)<br>\r\n<ul>the\sTABLE\s<b>must</b>\sbe\sa\sSpatialTable,\ssince\s<b>ROWID</b>\swill\sbe\sused\sto\supdate</ul>\r\nAn\sDem-Database\s(<b>Dem</b>)\sexists\sthat\scontains\sa\sPOINTZ\sor\sPOINTZM\sGeometry<br>\r\nThe\s(<b>Dem</b>)\scontains\sa\sSpatialIndex<br>\r\nBoth\sGeometries\scontain\sa\svalid\sSrid</ul>\r\n<hr>\r\nInput\sParameters:<br><br>\r\nFor\sboth\s<b>Source</b>\sand\s<b>Dem</b>\r\n<ul>Path\sto\sDatabase<br>Table<br>Geometry-Column</ul>\r\n<br>Optional\sParameter:<br>\r\n<ul>resolution\sof\sDem<ul>which\sby\sdefault\scan\sbe\scalculated\sby\r\n<ul>\r\n<pre>\r\n(extent_maxx_dem-extent_minx_dem)*(extent_maxy_dem-extent_miny_dem)/(double)rows_count_dem\r\n</pre>\r\nassuming\sthat\sthe\s<b>Dem</b>\shas\sbeen\scompletely\sfilled\swith\sa\sGeometry\r\n</ul>\r\nthis\sparameter\scan\sbe\sused\sto\soverride\sthis\svalue\sif\snot\scorrect.\r\n</ul>\r\n</ul>\r\n<hr>\r\nCommands:<br>\r\n<ul><b>sniff</b>\r\n\s<ul>which\swould\sallow\sthe\sUser\sto\sprepare\sthe\scommand\ssyntax<br>receiving\sa\smessage\sthat\sall\spreconditions\sare\sfulfilled.<br>\s\r\n<b>Source</b>\sand\s<b>Dem</b>\s\scan\sbe\sdone\sseparately\sor\stogether<br>\r\nGoal\sis\sto\sease\sthe\screation\sof\sthe\sparameters\sand\sreceiving\sinformation\sabout\swhat\sis\sto\shappen\r\n\s</ul>\r\n</ul>\r\n<br>\r\n<ul><b>update</b>\r\n\s<ul>everything\sdone\sduring\sa\s<b>sniff</b>,\swill\sbe\sdone\shere<br>\r\nif\sboth\s<b>Source</b>\sand\s<b>Dem</b>\sare\scorrect,\sthe\supdate\swill\sstart<br>\r\nAll\sof\sthe\s(not\sNULL)\sgeometries\swill\sbe\sselected\r\n<ul>if\sthe\sSrid\sof\sthe\stwo\sare\sdifferent,\sa\stransformed\sgeometry\swill\salso\sbe\screated</ul>\r\nA\sfunction\scalled\s<b>gaiaUpdateZ</b>\s(based\son\sgg_transform.c\sgaiaTransformCommon)\sis\scalled\swith\sboth\sgeometries\sand\sthe\sextent\sof\s<b>Dem</b>\r\n<ul>only\sGeometries\s<b>completely</b>\scontained\sinside\sthe\s<b>Dem</b>\swill\sbe\sprocessed\s(returning\sNULL)</ul>\r\n<ul>a\sGeometry\swill\sbe\sreturned\swhen\s<b>any</b>\sof\sthe\sZ-Values\s(that\sare\snot\s0.0)\shave\schanged\s(otherwise\sreturning\sNULL)</ul>\r\nif\sthe\sresult\s<b>gaiaUpdateZ</b>\sreturns\sNOT\sNULL,\sthe\sGeometry\swill\sbe\supdated\r\n</ul>\s\s\s\s\s\s\r\n</ul>\r\n<hr>\r\n<b>retrieve_dem_points</b>:<br>\r\nis\scalled\sduring\s<b>gaiaUpdateZ</b>\s(4\stimes,\swhen\sneeded)\sto\sretrieve\sthe\snearest\spoint\sfrom\sa\ssent\sarray\sof\sx/y\spoints\r\n<ul><b>gaiaUpdateZ</b>\swill\scollect\sand\ssend\s(when\sneeded\sthe\stransformed)\spoints\sto\s<b>retrieve_dem_points</b></ul>\r\nA\squery\sis\scalled\sto\sthe\s<b>ATTACH</b>ed\sDatabase,\susing\sthe\sSpatialIndex<br>\r\n<ul>The\sset\sresolution\sis\sused\swith\s<b>ST_Buffer</b>\sfor\sthe\s</b>search_frame</b></ul>\r\nChecking\sis\sdone\sif\sthe\sreturned\s<b>z</b>\s(or\spossibly,\syet\sunlikely,\sa\s<b>m</b>)\svalue\sis\sdifferent\sfrom\sthe\ssent\svalue.<br>\r\nCounters\sexist\sin\sall\sfunctions\sto\sinsure\sthat\supdate\swill\sonly\sbe\sdone\swhen\sneeded\r\n<ul>but\salso\sto\sinform\sthe\suser\sthe\samount\sof\sgeometries/points\sand\show\smany\sof\sthose\shave\sbeen\schanged</ul>\r\n<hr>\r\nAt\spresent\sthe\s'Help'\slooks\slike\sthis:<br>\r\n<pre>\r\nusage:\sspatialite_updatez\sARGLIST\r\n==============================================================\r\n-h\sor\s--help\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\s\sprint\sthis\shelp\smessage\r\n==========================\sParameters\s========================\r\n\s\s--\s--\s----------------\sDem-Data\sDatabase\s----------------\s--\r\n-ddem\sor\s--dem-path\s\spathname\sto\sthe\sSpatiaLite\sDem\sDB\s\r\n-tdem\sor\s--table-dem\stable_name\s[SpatialTable\sor\sSpatialView]\r\n-gdem\sor\s--geometry-dem-column\scol_name\sthe\sGeometry\scolumn\r\n\t\smust\sbe\sa\sPOINT\sZ\sor\sa\sPOINT\sZM\stype\r\n-rdem\sor\s--dem-resolution\sof\sthe\sdem\spoints\swhile\ssearching\r\n\t\sthe\sautomatic\sresolution\scalculation\sis\sbased\son\sthe\srow_count\r\n\t\swithin\sthe\sextent,\swhich\smay\snot\sbe\scorrect!\r\n\t\sUse\s'-rdem'\sto\sset\sa\srealistic\svalue\r\n\r\n\s\s--\s--\s-----------------\sSource\sDatabase\s-----------------\s--\r\n-d\sor\s--db-path\spathname\sto\sthe\sSpatiaLite\sDB\r\n-t\sor\s--table\stable_name,\s\smust\sbe\sa\sSpatialTable\r\n-g\sor\s--geometry-column\sthe\sGeometry\scolumn\sto\supdate\r\n\t\smust\s\sbe\sa\sZ\sor\sa\sZM\sDimension\stype\r\n\t\suse\sCastToXYZ(geom)\sor\sCastToXYZM(geom)\sto\sconvert\s\r\n\s\s--\s--\s---------------\sGeneral\sParameters\s----------------\s--\r\n-mdem\sor\s--copy-m\s[0=no,\s1=\syes\s[default]\sif\sexists]\r\n\r\n\s\s--\s--\s--------------------\sNotes:\s\s----------------------\s--\r\n-I->\sthe\sZ\svalue\swill\sbe\scopied\sfrom\sthe\snearest\spoint\sfound\r\n-I->\sthe\sSrid\sof\sthe\ssource\sGeometry\sand\sthe\sDem-POINT\scan\sbe\sdifferent\r\n\r\n===========================\sCommands\s===========================\r\n-sniff\s\s\s[default]\sanalyse\ssettings\swithout\sUPDATE\sof\sz-values\s\r\n-update\sPerform\sUPDATE\sof\sz-values\s\r\n</pre>\r\n<hr>\r\nTests:<br>\r\n<ul>Valgrind\sreports\sonly\sthe\s2\swell\sknown\sGEOS\sleaks</ul>\r\n<br>\r\n<b>berlin_dhh92.db</b><br>\r\nWas\sbuild\sfrom\s279\s.xyz\sfiles\sand\scombined\sand\ssorted,<br>\seach\scontaining\s4000.000\srecords\s(1.116\sbillion)\r\n<ul>sort\s-k2\s-n\s-k1\s3902_581820.txt\s-o\s3902_581820.dhhn92.txt</ul>\r\nWas\sthe\simported\sinto\sa\ssqlite3\sDatabase\r\n<pre>\r\n.mode\scsv\r\n.separator\s"\s"\r\n.import\s2007.berlin.dhh92.xyz\sberlin_dhh92_import\r\n</pre>\r\nA\sSpatialite\sscript\screated\sa\scopy,\spreparing\s2\sGeometries.<br>\r\nAs\sof\sdate\sonly\s114\smillion\sgeometries,\scovering\san\sarea\sof\s12*12\sKm\shave\sbeen\screated.<br>\r\n<br>\r\n<b>berlin_street_segments</b><ul>contains\s42716\sLINESTRING's,\smany\sof\swhich\sare\soutside\sthe\sDem\sarea\s(that\scontain\sgeometries)</ul>\r\n\r\n<pre>\r\nSQLite\sversion:\s3.17.0\r\nSpatiaLite\sversion:\s4.5.0-devel\r\n\r\nSource:\ssrid\s3068\r\nSource:\sextent\smin\sx/y(3584.6332144,1452.2834924)\r\n\t\s\s\s\s\s\s\smix\sx/y(48941.5043681,37058.9517384)\r\nSource:\srows_count(soldner_segment)\s42720\r\nSource:\sgeometry_type(1002)\shas_z[1]\r\nSource:\sspatial_index_enabled[1]\r\nSource\s'berlin_admin_geometries.db'\r\nwill\sconvert\sTABLE[berlin_street_segments]\swith\sGEOMETRY-Column[soldner_segment]\sZ-Value\r\n\tfrom\snearest\sPOINT\sfound\sin\r\nSource\sDatabase:\shas\spassed\sall\schecks.\r\n\r\nDem:\ssrid\s25833\r\nDem:\sextent\smin\sx/y(385000.0000000,5814000.0000000)\r\n\t\s\s\s\smax\sx/y(397000.0000000,5826000.0000000)\r\nDem:\srows_count(utm_point)\s1116000000\r\nDem:\sresolution(utm_point)\s0.1290323\r\nDem:\sgeometry_type(1001)\shas_z[1]\r\nDem:\sspatial_index_enabled[1]\r\nSource\s'berlin_admin_geometries.db'\r\nwill\sconvert\sTABLE[berlin_street_segments]\sor\sGEOMETRY-Column[soldner_segment]\sZ-Value\r\n\tfrom\snearest\sPOINT\sfound\sin\r\nDem\s'berlin_dhh92.db'\r\n\sTABLE[berlin_dhh92_2007]\swith\sGEOMETRY-Column[utm_point]\r\n-W->\s-rdem\swas\s\sset.\sUsing;\sresolution(0.9990000),\soverriding\sthe\scalculated\svalue:\s0.1290323\r\nDem\ssrid[25833]:\sis\sdifferent\sfrom\sthe\sSource\ssrid[3068].\r\n\t\sWhen\ssearching\sfor\sthe\snearest\spoint,\sthe\sSource\spoints\swill\sbe\stransformed\sto\ssrid[25833].\r\nDem\sDatabase:\shas\spassed\sall\schecks.\r\n\tDB-file\ssuccessfully\sconverted\s!!!\r\n\r\n-I->\sgeometries\stotal[42716]\schanged[11808]\s;\spoints\stotal[41010]\schanged\sz[41010]\schanged\sm[0]\r\n</pre>\r\n\r\nWhich\stook\sabout\s30\sminutes\sto\scomplete.<br><br>\r\nThe\sresults\sshow:\sthat\sof\sthe\s42716\sgeometries,\s11808\swere\supdated\scontaining\s41010\spoints\s-\sall\sof\swhich\swere\supdated.<br><br>\r\nA\ssample\sLINESTRING\sthat\swas\supdated:\r\n<pre>\r\nSRID=3068;LINESTRING(24910.98653436587\s21463.5093835775\s<b>35.39999999999999</b>,24948.09927146823\s21394.95263390163\s<b>35.60999999999999</b>)\r\n</pre>\r\n\r\nshows\scorrect\sresults.\r\n<br><br>\r\nA\ssecond\srun\stook\sabout\s2\sminutes:\r\n<pre>\r\n-I->\sgeometries\stotal[42716]\schanged[0]\s;\spoints\stotal[41010]\schanged\sz[0]\schanged\sm[0]\r\n</pre>\r\n\r\nshowing\sthat\sno\supdates\swere\smade,\ssince\snothing\shas\schanged.<br><br>\r\nAlthough\snot\syet\stested,\sit\sshould\smean\sthat\sif\snew\spoints\sare\sadded\sthat\smay\snot\scontain\sreliable\sresults\r\n<ul>a\s(more\sor\sless)\sswift\supdate\scan\sbe\smade\sfrom\sthe\sDem-Database.</ul>\r\n<hr>\r\nConclusion:<br>\r\nTo\screate\san\sSQL-Command\sfor\sthe\ssame\stask\sis\sunrealistic,\ssince\sit\swould\sbe\svery\stime\sconsuming.<br><br>\r\nSo\sadding\sthis\sas\sa\snew\stool\swould\sbe\sjustified.<br><br>\r\nIt\scould\salso\sbe\sadapted\swith\sa\s<b>-retrieve</b>\sor\s<b>-fetch</b>\scommand,<ul>\stogether\swith\sa\sconfig-file\swhere\sthe\sDem\sinformation\sis\sstored,<br>\scould\sreturn\sthe\sZ-value\sfrom\sa\sgiven\ssrid,x,y\sposition\s(or\sEWKT).</ul>\r\n<br>\r\nConsidering\sthe\ssize\sof\sthese\sDatabases,\sa\ssemi-automated\suse\sof\sthem,\swould\sbe\sa\suseful\sextra\stool.
J login anonymous
J mimetype text/html
J private_contact a3ab3a740c17d1a955665bbfeda76d4f8b6739bf
J severity Minor
J status Open
J title Concept\snew\stoll\s'spatialite_updatez'
J type Feature_Request
K 3743a244cf30c0e424e660af3034bbfbdd4febac
U anonymous
Z a009f44852c8e5ff6c2657324a08a0b9