从各种经纬度格式表中抽取满足标准格式，剔除错误格式的经纬度

--先选择所有正常格式经纬度

select  obstacle_id, obstacle_desc_id, no, name, type, bear, distance, elevation, has_light,

nvl2(latitude,‘N‘||replace(latitude,‘N‘),null) ,

nvl2(longitude,‘E‘||replace(longitude,‘E‘),null),

control_obstacle

from siniswift.OBSTACLE_TABLE@trans_cad

where regexp_like(nvl2(latitude,‘N‘||replace(latitude,‘N‘),null),‘^[NS]((([0-8]\d)([0-5]\d){2}(\.\d+)?)|(900000(\.0+)?))$‘) and

      regexp_like(nvl2(longitude,‘E‘||replace(longitude,‘E‘),null),‘^[EW]((((1[0-7]\d)|(0\d{2}))([0-5]\d){2}(\.\d+)?)|(1800000(\.0+)?))$‘)

union --补充选择经度只有两位的，并在经度前补0

select  obstacle_id, obstacle_desc_id, no, name, type, bear, distance, elevation, has_light,

nvl2(latitude,‘N‘||replace(latitude,‘N‘),null) ,

nvl2(longitude,‘E0‘||replace(longitude,‘E‘),null),

control_obstacle

from siniswift.OBSTACLE_TABLE@trans_cad

where regexp_like(nvl2(longitude,‘E‘||replace(longitude,‘E‘),null),‘^[EW]\d{2}([0-5]\d){2}(\.\d+)?$‘)

union --再补充没有经纬度或者经纬度格式不正确的，将其经纬度设为null

select  obstacle_id, obstacle_desc_id, no, name, type, bear, distance, elevation, has_light,null,null,control_obstacle

from siniswift.OBSTACLE_TABLE@trans_cad

where not regexp_like(nvl2(latitude,‘N‘||replace(latitude,‘N‘),null),‘^[NS]((([0-8]\d)([0-5]\d){2}(\.\d+)?)|(900000(\.0+)?))$‘) or

   (

      not regexp_like(nvl2(longitude,‘E‘||replace(longitude,‘E‘),null),‘^[EW]((((1[0-7]\d)|(0\d{2}))([0-5]\d){2}(\.\d+)?)|(1800000(\.0+)?))$‘) and

      not regexp_like(nvl2(longitude,‘E‘||replace(longitude,‘E‘),null),‘^[EW]\d{2}([0-5]\d){2}(\.\d+)?$‘)

   )

从各种经纬度格式表中抽取满足标准格式，剔除错误格式的经纬度

原文：http://www.cnblogs.com/mol1995/p/6292550.html