Geospatial Orphans
Case geospatialinfo orphans
This query tests if there are any rows in table geospatialinfo do not exist in any xx_ has_geospatialinfo tables. If you have a result here it should be seen as an indication that there are issues with the geodata. I do not think that having orphans in this direction creates issues so if you do not have a high number of records you do not need to do anything.
SELECT "public".geospatialinfo.geospatialinfo_guid, "public".geospatialinfo.shapeenum_guid, "public".geospatialinfo.shape_id, "public".geospatialinfo.isactive, "public".geospatialinfo.dataentrydate, "public".geospatialinfo.dataenterer, "public".geospatialinfo.poly_prop_enum_guid, "public".imsmaenum.enumvalue FROM "public".geospatialinfo INNER JOIN "public".imsmaenum ON "public".imsmaenum.imsmaenum_guid = "public".geospatialinfo.shapeenum_guid WHERE "public".geospatialinfo.geospatialinfo_guid NOT IN ((select geospatialinfo_guid from location_has_geospatialinfo union select geospatialinfo_guid from hazard_has_geospatialinfo union select geospatialinfo_guid from hazreduc_has_geospatialinfo union select geospatialinfo_guid from mre_has_geospatialinfo union select geospatialinfo_guid from qa_has_geospatialinfo union select geospatialinfo_guid from accident_has_geospatialinfo union select geospatialinfo_guid from victim_has_geospatialinfo union select geospatialinfo_guid from locationinfoversion_has_geospatialinfo union select geospatialinfo_guid from hazardinfoversion_has_geospatialinfo union select geospatialinfo_guid from hazreducinfoversion_has_geospatialinfo union select geospatialinfo_guid from mreinfoversion_has_geospatialinfo union select geospatialinfo_guid from qainfoversion_has_geospatialinfo union select geospatialinfo_guid from accidentinfoversion_has_geospatialinfo union select geospatialinfo_guid from victiminfoversion_has_geospatialinfo union select geospatialinfo_guid from task_has_geospatialinfo union select geospatialinfo_guid from gazetteer_has_geospatialinfo union select geospatialinfo_guid from organisation_has_geospatialinfo union select geospatialinfo_guid from victim_assistance_has_geospatialinfo union select geospatialinfo_guid from victim_assistance_version_has_geospatialinfo union select geospatialinfo_guid from place_has_geospatialinfo))
Case gepoint orphans
This query tests if there are any rows in table geopoint do not exist in any xx_ has_geospatialinfo tables. If you have a result here it should be seen as an indication that there are issues with the geodata. I do not think that having orphans in this direction creates issues so if you do not have a high number of records you do not need to do anything.
SELECT "public".geopoint.geopoint_guid, "public".geopoint.geospatialinfo_guid, "public".geopoint.pointlocal_id, "public".geopoint.pointno, "public".geopoint.pointtypeenum_guid, "public".geopoint.pointdescription, "public".geopoint.latitude, "public".geopoint.longitude, "public".geopoint.coordrefsys, "public".geopoint.fixedby_guid, "public".geopoint.bearing, "public".geopoint.distance, "public".geopoint.frompoint_guid, "public".geopoint.frompointinput, "public".geopoint.userinputformat, "public".geopoint.coordformat, "public".geopoint.dataentrydate, "public".geopoint.dataenterer, "public".geopoint.elevation, "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.geospatialinfo_guid NOT IN ((select geospatialinfo_guid from location_has_geospatialinfo union select geospatialinfo_guid from hazard_has_geospatialinfo union select geospatialinfo_guid from hazreduc_has_geospatialinfo union select geospatialinfo_guid from mre_has_geospatialinfo union select geospatialinfo_guid from qa_has_geospatialinfo union select geospatialinfo_guid from accident_has_geospatialinfo union select geospatialinfo_guid from victim_has_geospatialinfo union select geospatialinfo_guid from locationinfoversion_has_geospatialinfo union select geospatialinfo_guid from hazardinfoversion_has_geospatialinfo union select geospatialinfo_guid from hazreducinfoversion_has_geospatialinfo union select geospatialinfo_guid from mreinfoversion_has_geospatialinfo union select geospatialinfo_guid from qainfoversion_has_geospatialinfo union select geospatialinfo_guid from accidentinfoversion_has_geospatialinfo union select geospatialinfo_guid from victiminfoversion_has_geospatialinfo union select geospatialinfo_guid from task_has_geospatialinfo union select geospatialinfo_guid from gazetteer_has_geospatialinfo union select geospatialinfo_guid from organisation_has_geospatialinfo union select geospatialinfo_guid from victim_assistance_has_geospatialinfo union select geospatialinfo_guid from victim_assistance_version_has_geospatialinfo union select geospatialinfo_guid from place_has_geospatialinfo))
Case orphans between geopoint and geospatial
These cases require actions.
geopoint orphans
I have not found any database with these.
SELECT "public".geospatialinfo.geospatialinfo_guid, "public".geopoint.geospatialinfo_guid FROM "public".geopoint LEFT JOIN "public".geospatialinfo ON "public".geopoint.geospatialinfo_guid = "public".geospatialinfo.geospatialinfo_guid WHERE "public".geospatialinfo.geospatialinfo_guid IS NULL
Childless geospatial
This case creates errors when rebuilding the sandbox and updating items.
2016-04-24 19:23:45,903 [features.AddToMapHelper] line:116 - com.fgm.imsma.gis.GeoException: Error creating Domain Object marker com.fgm.imsma.gis.GeoException: Error creating Domain Object marker ERROR 2016-04-24 19:23:45,907 [changelog.ChangeLogHandler] line:366 - Error synchronizing geospatial data cache with Activity item c0a8-d801-1347e9c9047-67b41d0f-91-e3e com.fgm.imsma.gis.GeoException: Error creating Domain Object marker
SELECT "public".geospatialinfo.geospatialinfo_guid, "public".geopoint.geospatialinfo_guid FROM "public".geopoint RIGHT JOIN "public".geospatialinfo ON "public".geopoint.geospatialinfo_guid = "public".geospatialinfo.geospatialinfo_guid WHERE "public".geopoint.geospatialinfo_guid IS NULL