Changes

Geospatial Orphans

6,781 bytes added, 02:52, 10 November 2016

no edit summary

</pre>

=== Childless ~~geospatial~~ geospatialinfo ===__NOEDITSECTION__ This case creates errors when rebuilding the sandbox and updating items. In this example there are total 90 childless geospatialinfo.

<pre>

2016-04-24 19:23:45,903 [features.AddToMapHelper] line:116 - com.fgm.imsma.gis.GeoException: Error creating Domain Object marker

</pre>

==== ~~Subsection~~ Childless orphan geospatialinfo====__NOEDITSECTION__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.<pre>select * from geospatialinfo LEFT JOIN "public".geopoint ON "public".geopoint.geospatialinfo_guid = "public".geospatialinfo.geospatialinfo_guidwhere geospatialinfo.geospatialinfo_guid not in (select geospatialinfo_guid from location_has_geospatialinfounionselect geospatialinfo_guid from hazard_has_geospatialinfounionselect geospatialinfo_guid from hazreduc_has_geospatialinfounionselect geospatialinfo_guid from mre_has_geospatialinfounionselect geospatialinfo_guid from qa_has_geospatialinfounionselect geospatialinfo_guid from accident_has_geospatialinfounionselect geospatialinfo_guid from victim_has_geospatialinfounionselect geospatialinfo_guid from locationinfoversion_has_geospatialinfounionselect geospatialinfo_guid from hazardinfoversion_has_geospatialinfounionselect geospatialinfo_guid from hazreducinfoversion_has_geospatialinfounionselect geospatialinfo_guid from mreinfoversion_has_geospatialinfounionselect geospatialinfo_guid from qainfoversion_has_geospatialinfounionselect geospatialinfo_guid from accidentinfoversion_has_geospatialinfounionselect geospatialinfo_guid from victiminfoversion_has_geospatialinfounionselect geospatialinfo_guid from task_has_geospatialinfounionselect geospatialinfo_guid from gazetteer_has_geospatialinfounionselect geospatialinfo_guid from organisation_has_geospatialinfounionselect geospatialinfo_guid from victim_assistance_has_geospatialinfounionselect geospatialinfo_guid from victim_assistance_version_has_geospatialinfounionselect geospatialinfo_guid from place_has_geospatialinfo)AND"public".geopoint.geospatialinfo_guid IS NULL</pre> ==== Childless geospatialinfo with parents====__NOEDITSECTION__<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.enumvalueFROM"public".geospatialinfoLEFT JOIN "public".geopoint ON "public".geopoint.geospatialinfo_guid = "public".geospatialinfo.geospatialinfo_guidINNER JOIN "public".imsmaenum ON "public".imsmaenum.imsmaenum_guid = "public".geospatialinfo.shapeenum_guidWHERE"public".geospatialinfo.geospatialinfo_guid IN ((select geospatialinfo_guid from location_has_geospatialinfounionselect geospatialinfo_guid from hazard_has_geospatialinfounionselect geospatialinfo_guid from hazreduc_has_geospatialinfounionselect geospatialinfo_guid from mre_has_geospatialinfounionselect geospatialinfo_guid from qa_has_geospatialinfounionselect geospatialinfo_guid from accident_has_geospatialinfounionselect geospatialinfo_guid from victim_has_geospatialinfounionselect geospatialinfo_guid from locationinfoversion_has_geospatialinfounionselect geospatialinfo_guid from hazardinfoversion_has_geospatialinfounionselect geospatialinfo_guid from hazreducinfoversion_has_geospatialinfounionselect geospatialinfo_guid from mreinfoversion_has_geospatialinfounionselect geospatialinfo_guid from qainfoversion_has_geospatialinfounionselect geospatialinfo_guid from accidentinfoversion_has_geospatialinfounionselect geospatialinfo_guid from victiminfoversion_has_geospatialinfounionselect geospatialinfo_guid from task_has_geospatialinfounionselect geospatialinfo_guid from gazetteer_has_geospatialinfounionselect geospatialinfo_guid from organisation_has_geospatialinfounionselect geospatialinfo_guid from victim_assistance_has_geospatialinfounionselect geospatialinfo_guid from victim_assistance_version_has_geospatialinfounionselect geospatialinfo_guid from place_has_geospatialinfo)) AND"public".geopoint.geospatialinfo_guid IS NULLORDER BY1 ASC</pre>

~~==== Subsection ====__NOEDITSECTION__~~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.

<pre>

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

</pre>

{{Note | This query requires that an extension to PostGreSQL is activated. Run this query in the database '''postgres'''

CREATE EXTENSION IF NOT EXISTS "uuid-ossp"}}

[[Category:NoPublic]]

[[Category:SQL Queries]]

Alnaucler

NoPublic, bureaucrat, administrator

6,632

edits