Space coordinates

This query is in 5.x format

This query finds invalid / space coordinates. The invalid coordinates need to be corrected.

SELECT 
geopoint.pointlocal_id, geopoint.pointno, geopoint.longitude, geopoint.latitude, geopoint.userenteredcoord
FROM
geopoint
INNER JOIN geospatialinfo ON geospatialinfo.geospatialinfo_guid = geopoint.geospatialinfo_guid
WHERE geopoint.longitude >  '180' OR 
geopoint.longitude <  '-180' OR
geopoint.latitude >  '90' OR 
geopoint.latitude <  '-90' OR 
geopoint.latitude IS NULL OR 
geopoint.longitude IS NULL OR 
geopoint.userenteredcoord IS NULL

If you get a result set from the query above the following queries will help you to identify to which object they belong.

Location

SELECT
fieldreport.fieldreport_localid,
geopoint.longitude,
geopoint.latitude,
locationinfoversion.location_localid
FROM
fieldreport
Inner Join geopoint
Inner Join locationinfoversion ON locationinfoversion.fieldreport_guid = fieldreport.fieldreport_guid
Inner Join locationinfoversion_has_geospatialinfo ON locationinfoversion_has_geospatialinfo.locationinfoversion_guid = locationinfoversion.locationinfoversion_guid AND locationinfoversion_has_geospatialinfo.geospatialinfo_guid = geopoint.geospatialinfo_guid
WHERE
geopoint.longitude >  '180' OR
geopoint.latitude >  '90' OR
geopoint.longitude <  '-180' OR
geopoint.latitude <  '-90'

Hazard

SELECT
fieldreport.fieldreport_localid,
geopoint.longitude,
geopoint.latitude,
hazardinfoversion.hazard_localid
FROM
fieldreport
Inner Join geopoint
Inner Join hazardinfoversion ON hazardinfoversion.fieldreport_guid = fieldreport.fieldreport_guid
Inner Join hazardinfoversion_has_geospatialinfo ON hazardinfoversion_has_geospatialinfo.geospatialinfo_guid = geopoint.geospatialinfo_guid AND hazardinfoversion_has_geospatialinfo.hazardinfoversion_guid = hazardinfoversion.hazardinfoversion_guid
WHERE
geopoint.longitude >  '80' OR
geopoint.latitude >  '90' OR
geopoint.longitude <  '-180' OR
geopoint.latitude <  '-90'

Hazard Reduction

SELECT
fieldreport.fieldreport_localid,
geopoint.longitude,
geopoint.latitude,
hazreducinfoversion.hazreduc_localid
FROM
fieldreport
Inner Join geopoint
Inner Join hazreducinfoversion ON hazreducinfoversion.fieldreport_guid = fieldreport.fieldreport_guid
Inner Join hazreducinfoversion_has_geospatialinfo ON hazreducinfoversion_has_geospatialinfo.hazreducinfoversion_guid = hazreducinfoversion.hazreducinfoversion_guid AND hazreducinfoversion_has_geospatialinfo.geospatialinfo_guid = geopoint.geospatialinfo_guid
WHERE
geopoint.longitude >  '180' OR
geopoint.latitude >  '90' OR
geopoint.longitude <  '-180' OR
geopoint.latitude <  '-90'

Accident

SELECT
accidentinfoversion.accident_localid,
fieldreport.fieldreport_localid,
geopoint.longitude,
geopoint.latitude
FROM
accidentinfoversion
Inner Join accidentinfoversion_has_geospatialinfo ON accidentinfoversion.accidentinfoversion_guid = accidentinfoversion_has_geospatialinfo.accidentinfoversion_guid
Inner Join fieldreport ON accidentinfoversion.fieldreport_guid = fieldreport.fieldreport_guid
Inner Join geopoint ON accidentinfoversion_has_geospatialinfo.geospatialinfo_guid = geopoint.geospatialinfo_guid
WHERE
geopoint.longitude >  '180' OR
geopoint.latitude >  '90' OR
geopoint.longitude <  '-180' OR
geopoint.latitude <  '-90'

Victim

SELECT
fieldreport.fieldreport_localid,
geopoint.longitude,
geopoint.latitude,
victiminfoversion.victim_localid
FROM
fieldreport
Inner Join geopoint
Inner Join victiminfoversion ON victiminfoversion.fieldreport_guid = fieldreport.fieldreport_guid
Inner Join victiminfoversion_has_geospatialinfo ON victiminfoversion_has_geospatialinfo.victiminfoversion_guid = victiminfoversion.victiminfoversion_guid AND victiminfoversion_has_geospatialinfo.geospatialinfo_guid = geopoint.geospatialinfo_guid
WHERE
geopoint.longitude >  '180' OR
geopoint.latitude >  '90' OR
geopoint.longitude <  '-180' OR
geopoint.latitude <  '-90'

MRE

SELECT
fieldreport.fieldreport_localid,
geopoint.longitude,
geopoint.latitude,
mreinfoversion.mre_localid
FROM
fieldreport
Inner Join geopoint
Inner Join mreinfoversion ON mreinfoversion.fieldreport_guid = fieldreport.fieldreport_guid
Inner Join mreinfoversion_has_geospatialinfo ON mreinfoversion.mreinfoversion_guid = mreinfoversion_has_geospatialinfo.mreinfoversion_guid AND mreinfoversion_has_geospatialinfo.geospatialinfo_guid = geopoint.geospatialinfo_guid
WHERE
geopoint.longitude >  '180' OR
geopoint.latitude >  '90' OR
geopoint.longitude <  '-180' OR
geopoint.latitude <  '-90'

QM

SELECT
fieldreport.fieldreport_localid,
geopoint.longitude,
geopoint.latitude,
qainfoversion.qa_localid
FROM
fieldreport
Inner Join geopoint
Inner Join qainfoversion ON fieldreport.fieldreport_guid = qainfoversion.fieldreport_guid
Inner Join qainfoversion_has_geospatialinfo ON qainfoversion.qainfoversion_guid = qainfoversion_has_geospatialinfo.qainfoversion_guid AND qainfoversion_has_geospatialinfo.geospatialinfo_guid = geopoint.geospatialinfo_guid
WHERE
geopoint.longitude >  '180' OR
geopoint.latitude >  '90' OR
geopoint.longitude <  '-180' OR
geopoint.latitude <  '-90'

Country Structure

SELECT
gazetteer.gazetteername,
geopoint.pointno,
geopoint.longitude,
geopoint.latitude
FROM
geopoint
Inner Join geospatialinfo ON geospatialinfo.geospatialinfo_guid = geopoint.geospatialinfo_guid
Inner Join gazetteer_has_geospatialinfo ON geospatialinfo.geospatialinfo_guid = gazetteer_has_geospatialinfo.geospatialinfo_guid
Inner Join gazetteer ON gazetteer.gazetteer_guid = gazetteer_has_geospatialinfo.gazetteer_guid
WHERE
geopoint.longitude >  '180' OR
geopoint.longitude <  '-180' OR
geopoint.latitude >  '90' OR
geopoint.latitude <  '-90'

Organisation

SELECT
organisation.orgname,
geopoint.pointno,
geopoint.longitude,
geopoint.latitude
FROM
geopoint
Inner Join geospatialinfo ON geospatialinfo.geospatialinfo_guid = geopoint.geospatialinfo_guid
Inner Join organisation_has_geospatialinfo ON organisation_has_geospatialinfo.geospatialinfo_guid = geospatialinfo.geospatialinfo_guid
Inner Join organisation ON organisation.org_guid = organisation_has_geospatialinfo.org_guid
WHERE
geopoint.longitude >  '180' OR
geopoint.longitude <  '-180' OR
geopoint.latitude >  '90' OR
geopoint.latitude <  '-90'

Place

SELECT
place.placename,
geopoint.pointno,
geopoint.longitude,
geopoint.latitude
FROM
geopoint
Inner Join geospatialinfo ON geospatialinfo.geospatialinfo_guid = geopoint.geospatialinfo_guid
Inner Join place_has_geospatialinfo ON place_has_geospatialinfo.geospatialinfo_guid = geospatialinfo.geospatialinfo_guid
Inner Join place ON place.place_guid = place_has_geospatialinfo.place_guid
WHERE
geopoint.longitude >  '180' OR
geopoint.longitude <  '-180' OR
geopoint.latitude >  '90' OR
geopoint.latitude <  '-90'

Task

SELECT
task.name,
geopoint.pointno,
geopoint.longitude,
geopoint.latitude
FROM
geopoint
Inner Join geospatialinfo ON geospatialinfo.geospatialinfo_guid = geopoint.geospatialinfo_guid
Inner Join task_has_geospatialinfo ON geospatialinfo.geospatialinfo_guid = task_has_geospatialinfo.geospatialinfo_guid
Inner Join task ON task_has_geospatialinfo.task_guid = task.guid
WHERE
geopoint.longitude >  '180' OR
geopoint.longitude <  '-180' OR
geopoint.latitude >  '90' OR
geopoint.latitude <  '-90'

{{#switch:|subgroup|child=|none=|#default=

}}{{#if:|}}{{#if:Upgrade Process|<td style="text-align:left;border-left-width:2px;border-left-style:solid;|{{#if:|}}}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:space coordinates

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}{{#if:|{{{group2}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list2}}}

}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:space coordinates

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}{{#if:|{{{group3}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list3}}}

}}{{#if:|{{#if:|{{{group4}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list4}}}

}}{{#if:|{{#if:|{{{group5}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list5}}}

}}{{#if:|{{#if:|{{{group6}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list6}}}

}}{{#if:|{{#if:|{{{group7}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list7}}}

}}{{#if:|{{#if:|{{{group8}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list8}}}

}}{{#if:|{{#if:|{{{group9}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list9}}}

}}{{#if:|{{#if:|{{{group10}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list10}}}

}}{{#if:|{{#if:|{{{group11}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list11}}}

}}{{#if:|{{#if:|{{{group12}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list12}}}

}}{{#if:|{{#if:|{{{group13}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list13}}}

}}{{#if:|{{#if:|{{{group14}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list14}}}

}}{{#if:|{{#if:|{{{group15}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list15}}}

}}{{#if:|{{#if:|{{{group16}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list16}}}

}}{{#if:|{{#if:|{{{group17}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list17}}}

}}{{#if:|{{#if:|{{{group18}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list18}}}

}}{{#if:|{{#if:|{{{group19}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list19}}}

}}{{#if:|{{#if:|{{{group20}}}<td style="text-align:left;border-left-width:2px;border-left-style:solid;|

{{{list20}}}

}}{{#if:|{{#if:IMSMA Hub{{#switch:{{#if:|{{{border}}}|child}}|subgroup|child=|none=|#default=

}}{{#ifeq:|Template|{{#ifeq:{{#if:|{{{border}}}|child}}|child||{{#ifeq:{{#if:|{{{border}}}|child}}|subgroup||{{#switch:space coordinates

|doc
|sandbox
|testcases =
|#default = {{#switch:
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}|}}

{{{below}}}

}}{{#switch:|subgroup|child=

|none=|#default=}}{{#ifeq:|Template|{{#ifeq:|child||{{#ifeq:|subgroup||{{#switch:space coordinates

|doc
|sandbox
|testcases =
|#default = {{#switch:hlist
 |plainlist
 |hlist
 |hlist hnum
 |hlist vcard
 |vcard hlist = 
 |#default = 
 }}
}}

}}}}}}