Check coordinates distance and bearing 6.0

These queries are for databases that have been upgraded to 6.0 and for which distance and bearing was not fixed during the upgrade process. This page contains checks to be run before and after upgrade to 6.0.

	The queries are SELECT queries that will help you identify if the database has any Distance and bearing issues. UPDATE queries that correct the problem is then needed to correct the database. A tool (e.g. Google Earth Pro) that could calculate longitude/latitude and/or distance/bearing between points might also be needed.

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

	longitude and latitude are the storage coordinates which are used for e.g. the sandbox / rendering in the Map Pane.

How should look like

Case 1 : Coordinates migrated from Legacy with Distance and Bearing as extra information

This worked well with 5.x but does 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.

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)?

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 1A

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

Run this query (DistBear Missing FromPoint Point1) to check if you have any points that fall 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

	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

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

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.

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 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

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

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 fall 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 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

	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. IMSMA^NG should not allow incorrect/incomplete files to be imported (ICR-376 Colombia).

Case 4 : Miscellaneous odd issues that need 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

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:

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.

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 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 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 fall 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

	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) = 0 too. NAA has UPDATE queries.

Case 4C : length(frompoint_guid) = 0

Difference between length = 0 and NULL

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.

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 need 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 fall 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 need to be investigated and updated manually.

Case 5 : Calculated MGRS contains spaces OR includes lower case

How to see if Case 5 exists

The data is entered with 6.0 and is relative points using MGRS. The calculated MGRS in user_entered_mgrs has spaces and should for consistency reasons and in order to avoid future issues be corrected when a new build is available (ICR-368). Run this query (DistBear Check Spaces) to check if you have any points that falls under Case 5. This query is NOT tested with MGRS.


SELECT
"public".geopoint.geopoint_guid,
"public".geopoint.pointlocal_id,
"public".geopoint.pointno,
"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_mgrs LIKE '% %'

SELECT
"public".geopoint.geopoint_guid,
"public".geopoint.pointlocal_id,
"public".geopoint.pointno,
"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
user_entered_mgrs <> UPPER(user_entered_mgrs)

	These rows needs to be have user_entered_mgrs updated. No updating query exists yet.

Template:NavBox Hub

Check coordinates distance and bearing 6.0

Contents

Which database columns should have values / How it should look like

Case 1 : Coordinates migrated from Legacy with Distance and Bearing as extra information

Case 1A

Case 1B

Case 2 : Coordinates entered with 5.x and upgraded to 6.0

Case 3 : Import from Excel to geo widget

Case 4 : Miscellaneous odd issues that need to be corrected

Case 4A : One/few points of a polygon are missing frompoint_guid and user_entered

Case 4B : Distance = 0

Case 4C : length(frompoint_guid) = 0

Case 4D : One of Distance / Bearing is null

Case 5 : Calculated MGRS contains spaces OR includes lower case

Navigation menu

Check coordinates distance and bearing 6.0

Which database columns should have values / How it should look like

Case 1 : Coordinates migrated from Legacy with Distance and Bearing as extra information

Case 1A

Case 1B

Case 2 : Coordinates entered with 5.x and upgraded to 6.0

Case 3 : Import from Excel to geo widget

Case 4 : Miscellaneous odd issues that need to be corrected

Case 4A : One/few points of a polygon are missing frompoint_guid and user_entered

Case 4B : Distance = 0

Case 4C : length(frompoint_guid) = 0

Case 4D : One of Distance / Bearing is null

Case 5 : Calculated MGRS contains spaces OR includes lower case

Navigation menu

Search