Check coordinates distance and bearing 6.0
These queries are for databases that has been upgraded to 6.0 and during upgrade process distance and bearing was not fixed.
Case 1 Coordinates migrated from Legacy with Distance and bearing as extra information
This worked well with 5.x but especially xml export/import does not work well with 6.0. Extra information refers to that the points are defined as an absolute point plus has distance and bearing information but does not have FromPointGUID.
This case has to be fixed in two steps:
- point 1 of the polygon is referring to a single point i.e. not to any point that is part of the polygon
- point 1 of the polygon is the absolute point
Case 1A
Run this query (DistBear Missing FromPoint Point1) to check if you have any points that falls under Case 1A. This query is NOT tested with MGRS.
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.coordrefsys,
"public".geopoint.userinputformat,
"public".geopoint.coordformat,
poly.enumvalue
FROM
"public".geopoint
INNER JOIN "public".geospatialinfo ON "public".geospatialinfo.geospatialinfo_guid = "public".geopoint.geospatialinfo_guid
INNER 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) 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
poly.enumvalue LIKE 'Poly%'
ORDER BY
"public".geopoint.geospatialinfo_guid ASC,
"public".geopoint.pointno ASC
Case 1B
Run this query (DistBear Missing FromPoint Point2) to check if you have any points that falls under Case 1B. This query is NOT tested with MGRS.
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_id
FROM
"public".geopoint
INNER JOIN "public".geospatialinfo ON "public".geospatialinfo.geospatialinfo_guid = "public".geopoint.geospatialinfo_guid
INNER JOIN "public".imsmaenum ON "public".imsmaenum.imsmaenum_guid = "public".geospatialinfo.shapeenum_guid
WHERE
("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
 |
These rows needs to get FromPointGUID populated and userinputformat updated. NAA has UPDATE queries. |
Case 2 Coordinates entered with 5.x and upgraded to 6.0
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 funtionality does not set user_entered_x and user_entered_y or user_entered_mgrs for records with Bearing and Distance as userinputformat. This causes problems with 6.0.
Run this query (DistBear Check user_entered1) to check if you have any points that falls under Case 2. This query is NOT tested with MGRS.
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_mgrs
FROM
"public".geopoint
WHERE
(("public".geopoint.user_entered_x IS NULL OR
length("public".geopoint.user_entered_x) = 0 OR
"public".geopoint.user_entered_y IS NULL OR
length("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 OR
length("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
|
These rows needs to get user_entered_x and user_entered_y or user_entered_mgrs populated. NAA has UPDATE queries. |
Case 4 Misc odd issues that needs to be corrected
These errors may have been caused by
- user mistakes
- import
- SQL update of the database
- user updating DEFs with Replace action when Point1 of the polygon was referring to a single point.
Case 4A One/few points of a polygon are missing frompoint_guid and user_entered
Run this query (DistBear Check user_entered2) to check if you have any points that falls under Case 4A. This query is NOT tested with MGRS.
SELECT
"public".geopoint.longitude,
"public".geopoint.latitude,
"public".geopoint.coordrefsys,
"public".geopoint.distance,
"public".geopoint.bearing,
"public".geopoint.frompoint_guid,
"public".geopoint.userinputformat,
"public".geopoint.coordformat,
"public".geopoint.user_entered_x,
"public".geopoint.user_entered_y,
"public".geopoint.user_entered_mgrs
FROM
"public".geopoint
WHERE
("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))
|
These rows needs 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 there might be necessary to change to absolute coordinates for a few cases. |
Case 4B Distance = 0
To have Distance = 0 may cause calculation errors. Run this query (DistBear Check dist 0) to check if you have any points that falls under Case 4B. This query is NOT tested with MGRS.
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 FROM "public".geopoint INNER JOIN "public".imsmaenum ON "public".geopoint.pointtypeenum_guid = "public".imsmaenum.imsmaenum_guid WHERE "public".geopoint.distance = 0
|
{{{1}}} |
Case 4C length(frompoint_guid) = 0
These records are likely to be the same as found for Case 4B and could as well be orphans. Run this query (DistBear Check length FromPointGUID) to check if you have any points that falls under Case 4C. This query is NOT tested with MGRS.
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
|
These rows needs to be investigated manually. NAA has UPDATE queries. |
Case 4D One of Distance / Bearing is null
Run this query (DistBear Check bear dist NULL) to check if you have any points that falls under Case 4D. This query is NOT tested with MGRS.
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 NULL AND "public".geopoint.distance IS NOT NULL) OR
("public".geopoint.bearing IS NOT NULL AND "public".geopoint.distance IS NULL)
|
These rows needs to be investigated and updated manually. |