Open main menu
Changes
Space coordinates
6,908 bytes added
,
16:04, 23 June 2014
no edit summary
{{note| This query is in 5.x format}}
This query finds invalid / space coordinates
. The invalid coordinates need to be corrected.
<pre>
SELECT
geopoint.userenteredcoord IS NULL
</pre>
{{
note
Note
|
This
If you get a result set from the
query
is in 5
above the following queries will help you to identify to which object they belong
.
x format
}}
'''Location'''
<pre>
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'
</pre>
'''Hazard'''
<pre>
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'
</pre>
'''Hazard Reduction'''
<pre>
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'
</pre>
'''Accident'''
<pre>
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'
</pre>
'''Victim'''
<pre>
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'
</pre>
<pre>
'''MRE'''
</pre>
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'''
<pre>
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'
</pre>
'''Country Structure'''
<pre>
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'
</pre>
'''Organisation'''
<pre>
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'
</pre>
'''Place'''
<pre>
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'
</pre>
'''Task'''
<pre>
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'
</pre>
{{NavBox Upgrade}}
← Older edit
Newer edit →
Alnaucler
NoPublic,
bureaucrat
,
administrator
6,632
edits