Geospatial Orphans - Revision history

Alnaucler at 21:27, 4 June 2017

2017-06-04T21:27:21Z

← Older revision		Revision as of 23:27, 4 June 2017
Line 276:		Line 276:

	There are two options for correction:		There are two options for correction:
	# create dummy geopoint records for them with coordinates 0,0)		# 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		# delete the rows in geospatialinfo, hazreduc_has_geospatialinfo and hazreducinfoversion_has_geospatialinfo plus update has_geodata

Alnaucler at 13:07, 15 April 2017

2017-04-15T13:07:41Z

← Older revision		Revision as of 15:07, 15 April 2017
Line 1:		Line 1:
	{{TOC right}}		{{TOC right}}
	==Case geospatialinfo orphans ==__NOEDITSECTION__		==Case geospatialinfo orphans ==__NOEDITSECTION__
	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 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.
	<pre>		<pre>
	SELECT		SELECT

Alnaucler at 09:35, 15 April 2017

2017-04-15T09:35:05Z

← Older revision		Revision as of 11:35, 15 April 2017
Line 57:		Line 57:
	</pre>		</pre>

	==Case ~~gepoint~~ orphans ==__NOEDITSECTION__		==Case geopoint orphans ==__NOEDITSECTION__
	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.		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.
	<pre>		<pre>

Alnaucler at 09:24, 15 April 2017

2017-04-15T09:24:47Z

← Older revision		Revision as of 11:24, 15 April 2017
Line 132:		Line 132:
	These cases '''require''' actions.		These cases '''require''' actions.

	=== ~~geopoint~~ orphans===__NOEDITSECTION__		=== Geopoint orphans===__NOEDITSECTION__
	I have not found any database with these.		I have not found any database with these.
	<pre>		<pre>

Alnaucler at 09:22, 15 April 2017

2017-04-15T09:22:18Z

← Older revision		Revision as of 11:22, 15 April 2017
Line 327:		Line 327:
	"public".geopoint.geospatialinfo_guid IS NULL		"public".geopoint.geospatialinfo_guid IS NULL
	</pre>		</pre>
	{{Note \| This query requires that an extension to PostGreSQL is activated. Run this query in the database '''~~postgres~~'''		{{Note \| This query requires that an extension to PostGreSQL is activated. Run this query in the database '''imsma'''
	CREATE EXTENSION IF NOT EXISTS "uuid-ossp"}}		CREATE EXTENSION IF NOT EXISTS "uuid-ossp"}}
	[[Category:NoPublic]]		[[Category:NoPublic]]
	[[Category:SQL Queries]]		[[Category:SQL Queries]]
	{{NavBox Hub}}		{{NavBox Hub}}

Alnaucler at 00:52, 10 November 2016

2016-11-10T00:52:31Z

Show changes

Alnaucler at 23:01, 9 November 2016

2016-11-09T23:01:50Z

← Older revision		Revision as of 01:01, 10 November 2016
Line 130:		Line 130:

	==Case orphans between geopoint and geospatial ==__NOEDITSECTION__		==Case orphans between geopoint and geospatial ==__NOEDITSECTION__
			These cases '''require''' actions.

	=== geopoint orphans===__NOEDITSECTION__		=== geopoint orphans===__NOEDITSECTION__
			I have not found any database with these.
	<pre>		<pre>
	SELECT		SELECT
Line 143:		Line 145:
	</pre>		</pre>

	~~I have not found any database with these.~~		=== Childless geospatial ===__NOEDITSECTION__

	=== geospatial ~~orphans~~===__NOEDITSECTION__
	This case creates errors when rebuilding the sandbox and updating items.		This case creates errors when rebuilding the sandbox and updating items.
			<pre>
	2016-04-24 19:23:45,903 [features.AddToMapHelper] line:116 - com.fgm.imsma.gis.GeoException: Error creating Domain Object marker		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		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		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		com.fgm.imsma.gis.GeoException: Error creating Domain Object marker
			</pre>
	<pre>		<pre>
	SELECT		SELECT
Line 163:		Line 164:
	</pre>		</pre>

	=== Subsection ===__NOEDITSECTION__		==== Subsection ====__NOEDITSECTION__

			==== Subsection ====__NOEDITSECTION__

			[[Category:NoPublic]]
			[[Category:SQL Queries]]
			{{NavBox Hub}}

Alnaucler: Created page with "{{TOC right}} ==Case geospatialinfo orphans ==NOEDITSECTION This query tests if there are any rows in table ''geospatialinfo'' do not exist in any xx_ has_geospatialinfo..."

2016-11-09T22:51:24Z

Created page with "{{TOC right}} ==Case geospatialinfo orphans ==__NOEDITSECTION__ This query tests if there are any rows in table ''geospatialinfo'' do not exist in any xx_ has_geospatialinfo..."

New page

{{TOC right}}
==Case geospatialinfo orphans ==__NOEDITSECTION__
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.
<pre>
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))
</pre>

==Case gepoint orphans ==__NOEDITSECTION__
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.
<pre>
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))
</pre>

==Case orphans between geopoint and geospatial ==__NOEDITSECTION__

=== geopoint orphans===__NOEDITSECTION__
<pre>
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
</pre>

I have not found any database with these.

=== geospatial orphans===__NOEDITSECTION__
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

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

=== Subsection ===__NOEDITSECTION__