Coordinate display rules

This query shows the different combination of coordrefsys, coordformat and userinputformat used in the database.

1. Verify that all combinations are correct, if not they need to be corrected.
2. Verify that you have prj files for all used non-standard coordinate systems e.g. Kandawala.

SELECT
geopoint.coordrefsys,
geopoint.coordformat,
geopoint.userinputformat,
Count(geopoint.geopoint_guid)
FROM
geopoint
GROUP BY
geopoint.coordrefsys,
geopoint.coordformat,
geopoint.userinputformat
ORDER BY
geopoint.coordrefsys ASC,
geopoint.coordformat ASC,
geopoint.userinputformat ASC

This query identifies coordinates where the userenteredcoord contains : when coordformat is Decimal Degrees

SELECT
geopoint.longitude,
geopoint.latitude,
geopoint.coordrefsys,
geopoint.coordformat,
geopoint.userinputformat,
geopoint.userenteredcoord
FROM
geopoint
WHERE
userenteredcoord LIKE '%:%'
AND coordformat = 'Decimal Degrees'

This query identifies coordinates where the userenteredcoord does not contains 4 : when coordformat is Degrees:Minutes:Seconds

SELECT
geopoint.coordrefsys,
geopoint.userinputformat,
geopoint.coordformat,
geopoint.userenteredcoord,
LENGTH(userenteredcoord) - LENGTH(REPLACE( userenteredcoord, ':', '')) AS occ

FROM
geopoint
WHERE
geopoint.userenteredcoord LIKE  '%:%' and
LENGTH(userenteredcoord) - LENGTH(REPLACE( userenteredcoord, ':', '')) <> 4
and coordformat = 'Degrees:Minutes:Seconds'

This query identifies coordinates where the userenteredcoord does not contains 2 : when coordformat is Degrees:Decimal Minutes

SELECT
geopoint.coordrefsys,
geopoint.userinputformat,
geopoint.coordformat,
geopoint.userenteredcoord,
LENGTH(userenteredcoord) - LENGTH(REPLACE( userenteredcoord, ':', '')) AS occ

FROM
geopoint
WHERE
geopoint.userenteredcoord LIKE  '%:%' and
LENGTH(userenteredcoord) - LENGTH(REPLACE( userenteredcoord, ':', '')) <> 2
and coordformat = 'Degrees:Decimal Minutes'

This query identifies coordinates where distance and bearing has been migrated from Legacy as extra information

SELECT
geopoint_guid,
longitude,
latitude,
bearing,
distance,
frompoint_guid,
coordrefsys,
userinputformat,
coordformat,
poly.enumvalue
FROM
geopoint
INNER JOIN geospatialinfo ON geospatialinfo.geospatialinfo_guid = geopoint.geospatialinfo_guid
INNER JOIN imsmaenum AS poly ON geospatialinfo.shapeenum_guid = poly.imsmaenum_guid
WHERE
(distance IS NOT NULL AND
bearing IS NOT NULL) AND
(frompoint_guid IS NULL OR length(frompoint_guid) = 0 )
ORDER BY
geopoint.geospatialinfo_guid ASC,
pointno ASC

{{#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:coordinate display rules

|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:coordinate display rules

|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:coordinate display rules

|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:coordinate display rules

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

}}}}}}