NoPublic, bureaucrat, administrator
6,632
edits
Changes
no edit summary
{{TOC right}}{{Rewrite| This page in under re-write/construction. Now this This page is a mix of contain '''NOW''' PGSQL syntax and what needs to should be checked done [[Coordinate display rules | '''before''' ]] upgrade, and [[Check coordinates distance and bearing| '''afterduring''' ]] upgrade and for countries that have used 6is not split up to 2 different pages yet.0.<br/>}}{{Warning | This page describes what needs to be checked for BEFORE and DURING a database is upgraded to 6.0 .If you suspect that the coordinates were not checked before and after upgrade to 6.0 then this [[Check coordinates distance and bearing 6.0 | '''everyPGSQL page''' country]] that is upgrade to 6what you are looking.0.}}There are five different cases of found issues with Distance and Bearing# migrated data Note that errors may not have FromPointGUID set# upgraded data from version 5 that will not have user_entered coordinates# a few cases where one point also occur after upgrade due to import of a polygon where missing FromPointGUID and/or user_entered coordinates# distance = 0 and length(frompoint_guid)=0# wrong format of MGRS (ICR-368)NAA has found case 1-4 in Sri Lanka and have select + update queries for them.}}
==Which database columns should have values / How it should look like==Depending on which coordinate system is used, different combination of values are possible in these columns:* coordformat* userinputformat Depending on whether absolute or relative coordinates are used, different combination of values are possible in these columns:* user_entered_x* user_entered_y* user_entered_mgrs {{Note | ''longitude'' and ''latitude'' are the '''storage''' coordinates which are used for e.g. the sandbox / rendering in the Map Pane.}} [[Image:DistBear Correct 5x.png|900px|center]]<div align="center">'' How should look like ''</div>==Case 1 : Coordinates migrated from Legacy with Distance and Bearing as extra information==This worked well with 5.x but will not work well with 6.0 especially for xml export/import. Extra information refers to the fact that the points are defined as absolute points but also have distance and bearing information. but they do '''not''' have frompoint_guid. [[Image:DistBear Case1.png|400px|center]]<div align="center">'' Is the first point of the polygon recorded as a relative point (Distance/Bearing) to a single point OR with as a absolute point (X,Y)? ''</div>This case can be separated in two sub cases:# point 1 of the polygon is referring to a single point i.e. not to any point that is part of the polygon (case 1A)# point 1 of the polygon is the absolute point (case 1B) === Case 2 First 1A (should be corrected before the upgrade/migration scripts are run )===[[Image:DistBear Case1A wrong 5x.png|800px|center]]<div align="center">'' How to see if Case 1A exists i.e. when Distance/Bearing are provided they should be marked with yellow and From Point should be filled in''</div>Run this query (''DistBear Missing FromPoint Point1'') to see check if there is you have any coordinates points that use fall under Case 1A. This query is '''Distance and bearingNOT''' and do not have user_entered coordinatestested with MGRS.
<pre>
SELECT
"public".geopoint.geopoint_guid,"public".geopoint.coordrefsyslongitude,"public".geopoint.latitude,"public".geopoint.bearing,"public".geopoint.distance,"public".geopoint.frompoint_guid,"public".geopoint.user_entered_x,"public".geopoint.user_entered_y,"public".geopoint.user_entered_mgrs,"public".geopoint.coordformatcoordrefsys,"public".geopoint.userinputformat,Count("public".geopoint.geopoint_guid)coordformat,poly.enumvalue
FROM
"public".geopointINNER JOIN "public".geospatialinfo ON "public".geospatialinfo.geospatialinfo_guid = "public".geopoint.geospatialinfo_guidINNER JOIN "public".imsmaenum AS poly ON "public".geospatialinfo.shapeenum_guid = poly.imsmaenum_guid
WHERE
("public".geopoint.distance IS NOT NULL AND"public".geopoint.bearing IS NOT NULL ) ANDdistance ("public".geopoint.frompoint_guid IS NOT NULL OR length("public".geopoint.frompoint_guid) = 0 )AND"public".geopoint.user_entered_x IS NOT NULL AND"public".geopoint.user_entered_y IS NOT NULLANDGROUP BYgeopoint"public".coordrefsys,geopoint.coordformat,pointno = 1 ANDgeopointpoly.userinputformatenumvalue LIKE 'Poly%'
ORDER BY
"public".geopoint.geospatialinfo_guid ASC,"public".geopoint.pointno ASC</pre> {{Note | These records needs to get ''frompoint_guid'' updated with ''geopoint_guid'' from a single point and ''userinputformat'' should be updated. NAA has UPDATE queries.}} === Case 1B (should be corrected before the upgrade/migration scripts are run)===[[Image:DistBear Case1B wrong 5x.png|800px|center]]<div align="center">'' How to see if Case 1B exists i.e. when Distance/Bearing are provided they should be marked with yellow and From Point should be filled in''</div>Run this query (''DistBear Missing FromPoint Point2'') to check if you have any points that fall under Case 1B. This query is '''NOT''' tested with MGRS.<pre>SELECT"public".geopoint.geopoint_guid,"public".geopoint.longitude,"public".geopoint.latitude,"public".geopoint.bearing,"public".geopoint.distance,"public".geopoint.frompoint_guid,"public".geopoint.user_entered_x,"public".geopoint.user_entered_y,"public".geopoint.user_entered_mgrs,"public".geopoint.pointno,"public".geopoint.userinputformat,"public".geopoint.coordformat,"public".geopoint.coordrefsys ,"public".imsmaenum.enumvalue,"public".geopoint.pointlocal_idFROM"public".geopointINNER JOIN "public".geospatialinfo ON "public".geospatialinfo.geospatialinfo_guid = "public".geopoint.geospatialinfo_guidINNER JOIN "public".imsmaenum ON "public".imsmaenum.imsmaenum_guid = "public".geospatialinfo.shapeenum_guidWHERE("public".geopoint.distance IS NOT NULL AND"public".geopoint.bearing IS NOT NULL) AND("public".geopoint.frompoint_guid IS NULL OR length("public".geopoint.frompoint_guid) = 0) AND"public".geopoint.user_entered_x IS NOT NULL AND"public".geopoint.user_entered_y IS NOT NULL AND"public".geopoint.pointno <> 1 AND"public".imsmaenum.enumvalue LIKE 'Poly%'ORDER BY"public".geopoint.geospatialinfo_guid ASC,"public".geopoint.pointno ASC</pre> {{Note | These rows need to get ''frompoint_guid'' populated with ''geopoint_guid'' of point number -1 '''and''' ''userinputformat'' updated. Double check also that ''coordformat '' is correct. NAA has UPDATE queries.}} ==Case 2 : Coordinates entered with 5.x and upgraded to 6.0 (should be corrected after the upgrade/migration scripts are run)=={{Rewrite| This section should remain here}} [[Image:DistBear Case2 wrong.png|800px|center]]<div align="center">''How to see if Case 2 exists: It is not possible to open Geospatial tab in the Summary window and the DEF looks like this ''</div>These records have been entered into the database with 5.x and upgraded to 6.0 by the upgrade functionality provided by Novetta. But the upgrade functionality does '''not''' set user_entered_x and user_entered_y or user_entered_mgrs for records with '''Bearing and Distance''' as userinputformat. This will cause problems with 6.0. Run this query (''DistBear Check user_entered1'') to check if you have any points that fall under Case 2. This query is '''NOT''' tested with MGRS.<pre>SELECT"public".geopoint.longitude,"public".geopoint.latitude,"public".geopoint.coordrefsys,"public".geopoint.distance,"public".geopoint.bearing,"public".geopoint.frompoint_guid,"public".geopoint.coordformat,"public".geopoint.userinputformat,"public".geopoint.user_entered_x,"public".geopoint.user_entered_y,"public".geopoint.user_entered_mgrsFROM"public".geopointWHERE(("public".geopoint.user_entered_x IS NULL ORlength("public".geopoint.user_entered_x) = 0 OR"public".geopoint.user_entered_y IS NULL ORlength("public".geopoint.user_entered_y) = 0 ) and("public".geopoint.distance IS NOT NULL or "public".geopoint.bearing is NOT NULL)AND ("public".geopoint.frompoint_guid IS NOT NULL))OR(("public".geopoint.user_entered_mgrs IS NULL ORlength("public".geopoint.user_entered_mgrs) = 0) AND "public".geopoint.coordformat = 'MGRS' and("public".geopoint.distance IS NOT NULL or "public".geopoint.bearing is NOT NULL)AND ("public".geopoint.frompoint_guid IS NOT NULL))ORDER BY"public".geopoint.userinputformat ASC</pre> {{Note | These rows need to get user_entered_x and user_entered_y or user_entered_mgrs populated. NAA has UPDATE queries.}} ==Case 3 : Import from Excel to geo widget=={{Warning | New variation of Case 1 where the issues are created by import of Excel file where the columns are not filled in according to the rules. {{IMSMANG}} should not allow incorrect/incomplete files to be imported but this will not be fixed for 5.08.04.}} ==Case 4 : Miscellaneous odd issues that need to be corrected (should be corrected before the upgrade/migration scripts are run)==These errors may have been caused by * user mistakes* import* SQL update of the database* user updating DEFs with ''Replace'' action when Point 1 of the polygon was referring to a single point. === Case 4A : One/few points of a polygon are missing frompoint_guid and user_entered (should be corrected before the upgrade/migration scripts are run)===[[Image:DistBear Case4A wrong 5x.png|600px|center]]<div align="center">'' How to see if Case 4A exists? It is not possible to open Geospatial tab in the Summary window and the DEF looks like this: ''</div>Run this query (''DistBear Check user_entered2'') to check if you have any points that fall under Case 4A. This query is '''NOT''' tested with MGRS.<pre>SELECT"public".geopoint.longitude,"public".geopoint.latitude,"public".geopoint.coordrefsys,"public".geopoint.distance,"public".geopoint.bearing,"public".geopoint.frompoint_guid,"public".geopoint.userinputformat ASC,"public".geopoint.coordformat,"public".geopoint.user_entered_x,"public".geopoint.user_entered_y,"public".geopoint.user_entered_mgrsFROM"public".geopointWHERE("public".geopoint.user_entered_x IS NULL AND"public".geopoint.user_entered_y IS NULL AND("public".geopoint.distance IS NOT NULL or "public".geopoint.bearing is NOT NULL)AND ("public".geopoint.frompoint_guid IS NULL or length("public".geopoint.frompoint_guid) = 0)) OR("public".geopoint.user_entered_mgrs IS NULL AND"public".geopoint.coordformat = 'MGRS' AND("public".geopoint.distance IS NOT NULL or "public".geopoint.bearing is NOT NULL)AND ("public".geopoint.frompoint_guid IS NULL or length("public".geopoint.frompoint_guid) = 0))
</pre>
{{Note | These rows need to get user_entered_x and user_entered_y or user_entered_mgrs '''and''' frompoint_guid populated. NAA has UPDATE queries which may be applied to some of the above records but it might be necessary to change to absolute coordinates for a few cases.}} === Case 2 If there 4B : Distance = 0 (should be corrected before the upgrade/migration scripts are run)===To have Distance = 0 may cause calculation errors. Run this query (''DistBear Check dist 0'') to check if you have any with userinputformat points that fall under Case 4B. This query is '''Bearing and DistanceNOT''' then you MUST update themtested with MGRS.
<pre>
{{Note | These rows need to be investigated manually but most likely both distance and bearing should be deleted. Most likely they also have length(frompoint_guid) <nowiki>= 0</nowiki> too. NAA has UPDATE queries.}}
=== Case 4C : length(frompoint_guid) = 0 (should be corrected before the upgrade/migration scripts are run)===
[[Image:DistBear Case4C wrong 5x.png|800px|center]]
<div align="center">
'' Difference between length = 0 and NULL''
</div>
These records are likely to be the same as found for Case 4B and could be orphans as well.
Run this query (''DistBear Check length FromPointGUID'') to check if you have any points that fall under Case 4C. This query is '''NOT''' tested with MGRS.
<pre>
SELECT
"public".geopoint.geopoint_guid,
"public".geopoint.geospatialinfo_guid,
"public".geopoint.coordformat,
"public".geopoint.longitude,
"public".geopoint.latitude,
"public".geopoint.bearing,
"public".geopoint.distance,
"public".geopoint.frompoint_guid,
"public".geopoint.userinputformat,
"public".geopoint.coordrefsys,
"public".geopoint.user_entered_x,
"public".geopoint.user_entered_y,
"public".geopoint.user_entered_mgrs,
"public".imsmaenum.enumvalue,
"public".geopoint.pointlocal_id,
"public".geopoint.pointno,
"public".geopoint.pointdescription,
"public".geopoint.pointtypeenum_guid,
"public".geopoint.fixedby_guid,
"public".geopoint.frompointinput,
"public".geopoint.dataentrydate,
"public".geopoint.dataenterer
FROM
"public".geopoint
INNER JOIN "public".imsmaenum ON "public".geopoint.pointtypeenum_guid = "public".imsmaenum.imsmaenum_guid
WHERE
length(frompoint_guid) = 0
</pre>
{{Note | These rows need to be investigated manually. NAA has UPDATE queries.}}
=== Case 4D : One of Distance / Bearing is null (should be corrected before the upgrade/migration scripts are run)===
Run this query (''DistBear Check bear dist NULL'') to check if you have any points that fall under Case 4D. This query is '''NOT''' tested with MGRS.
<pre>
SELECT
"public".geopoint.geopoint_guid,
"public".geopoint.geospatialinfo_guid,
"public".geopoint.longitude,
"public".geopoint.latitude,
"public".geopoint.bearing,
"public".geopoint.distance,
"public".geopoint.userinputformat,
"public".geopoint.user_entered_x,
"public".geopoint.user_entered_y,
"public".geopoint.user_entered_mgrs
FROM
"public".geopoint
WHERE
("public".geopoint.bearing IS NOT NULL AND"public".geopoint.distance IS NOT NULL AND) OR user_entered_x ("public".geopoint.bearing IS NOT NULL ANDuser_entered_y "public".geopoint.distance IS NULL)
</pre>
{{Note | These rows need to be investigated and updated manually. }}
==Case 5 : Calculated MGRS contains spaces OR includes lower case (should be corrected before the upgrade/migration scripts are run)==
This you should have tested for in [[MGRS coordinates | previous step]].
{{NavBox Upgrade}}
[[Category:NoPublic]]
[[Category:SQL Queries]]
[[Category:NAA]]