Geospatial Orphans
Jump to navigation
Jump to search
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. This query might take long time to run e.g. South Sudan almost 2 hours.
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 geopoint 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 geospatialinfo
This case creates errors when rebuilding the sandbox and updating items. In this example there are total 90 childless geospatialinfo.
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
Childless orphan geospatialinfo
In this example 68 of the childless geospatialinfo are also orphans i.e. not used in any xx_ has_geospatialinfo tables. Since they are not currently referenced from anywhere they should not cause any problems.
select * from geospatialinfo LEFT JOIN "public".geopoint ON "public".geopoint.geospatialinfo_guid = "public".geospatialinfo.geospatialinfo_guid where 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) AND "public".geopoint.geospatialinfo_guid IS NULL
Childless geospatialinfo with parents
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 LEFT JOIN "public".geopoint ON "public".geopoint.geospatialinfo_guid = "public".geospatialinfo.geospatialinfo_guid INNER JOIN "public".imsmaenum ON "public".imsmaenum.imsmaenum_guid = "public".geospatialinfo.shapeenum_guid WHERE "public".geospatialinfo.geospatialinfo_guid 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)) AND "public".geopoint.geospatialinfo_guid IS NULL ORDER BY 1 ASC
By checking item/infoversion by item/infoversion I found that 14 belongs to hazreduc_has_geospatialinfo and 14 belongs to hazreducinfoversion_has_geospatialinfo so the issue is consistent with the error message in the console log and isolated to Activities. What was furthermore positive in this example was that they were all single points (i.e. no polygons/polylines that could have had many turning points).
There are two options for correction:
- create dummy geopoint records for them with coordinates 0,0
- delete the rows in geospatialinfo, hazreduc_has_geospatialinfo and hazreducinfoversion_has_geospatialinfo plus update has_geodata
Taking in consideration cascade surprises and the risk of creating other problems (in a database that had more issues than this) and the few number of items for the country to double-check I have so far implemented option 1.
INSERT INTO geopoint (geopoint_guid, geospatialinfo_guid, pointlocal_id, pointno, pointtypeenum_guid, pointdescription, latitude, longitude, coordrefsys, userinputformat, coordformat, dataentrydate, dataenterer, user_entered_x, user_entered_y) SELECT uuid_generate_v4() AS geo_guid, "public".geospatialinfo.geospatialinfo_guid, "public".hazreducinfoversion.hazreduc_localid AS "PointId", '1' AS "PointNo", "ShapeType".imsmaenum_guid AS pointtypeenum_guid, 'Point is recreated with coordinates 0,0 and imported' AS "pointdescription", '0' AS latitude, '0' AS longitude, 'WGS 1984' AS coordrefsys, 'X and Y' AS userinputformat, 'Decimal Degrees' AS coordformat, LOCALTIMESTAMP AS entrydate, 'imsma' AS enterer, '0' AS user_entered_x, '0' AS user_entered_y FROM "public".geospatialinfo LEFT JOIN "public".geopoint ON "public".geopoint.geospatialinfo_guid = "public".geospatialinfo.geospatialinfo_guid LEFT JOIN "public".imsmaenum AS "ShapeType" ON "ShapeType".imsmaenum_guid = "public".geospatialinfo.shapeenum_guid LEFT JOIN "public".imsmaenum ON "public".geospatialinfo.poly_prop_enum_guid = "public".imsmaenum.imsmaenum_guid INNER JOIN "public".hazreducinfoversion_has_geospatialinfo ON "public".hazreducinfoversion_has_geospatialinfo.geospatialinfo_guid = "public".geospatialinfo.geospatialinfo_guid INNER JOIN "public".hazreducinfoversion ON "public".hazreducinfoversion_has_geospatialinfo.hazreducinfoversion_guid = "public".hazreducinfoversion.hazreducinfoversion_guid WHERE "public".geospatialinfo.geospatialinfo_guid IN (( select geospatialinfo_guid from hazreduc_has_geospatialinfo )) AND "public".geopoint.geospatialinfo_guid IS NULL
 |
This query requires that an extension to PostGreSQL is activated. Run this query in the database imsma
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" |