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

Subsection

Template:NavBox Hub