In this last post (after which there will be a video explaining how to bring all this together in Power view) I cover how to convert geo coordinates from 38° 53′ 55.133″ N to Decimal Coordinate system 38.898648. You can find the previous posts here.
The calculations is fairly simple the sql for it is mentioned below.
with cte as (
select * , latitude = case when latitudenpeers ='S' then (cast(LatitudeDegree as float)+(cast(LatitudeMinute as float)/60) + (cast(LatitudeSecond as float)/3600))*-1
else (cast(LatitudeDegree as float)+(cast(LatitudeMinute as float)/60) + (cast(LatitudeSecond as float)/3600))*1
end ,
logitude =case when LongitudeEperW ='W' then (cast(LongitudeDegree as float)+(cast(LongitudeMinute as float)/60) + (cast(LongitudeSeconds as float)/3600))*-1
else (cast(LongitudeDegree as float)+(cast(LongitudeMinute as float)/60) + (cast(LongitudeSeconds as float)/3600))*1
end
from Airports)
select AirportCode as 'Code',geography::STGeomFromText('POINT('+cast(logitude as varchar(128))+' '+cast(latitude as varchar(128))+')',4326) as GeoCordinates from cte
order by latitude
To begin
We add one more DFT to the package which will fetch the recently uploaded data and then calculate the decimal representation of the Latitude and Longitude to store within a new column for Geography datatype.
Double click the DFT (I have named it Convert geo Coordinated to Point Spatial data types in SQL Server”) and add a OLEDB Source and a OLDE Command task as shown
I have configured the OLEDB command as shown below
Note there are only two columns Airport Code and the spatial coordinates
The OLEDB Command is configured as shown below
Note I am using the same Connection manager as the previous posts and just updating a geography column in a new table. You may simply add a new column to the exiting table if needed.
Next we define the values for the input parameters (the questions marks within the OLEDB Command where the update statement is being run.)
Parameters are position sensitive so the first question mark represents the first field within the update statement and thus need to be mapped to the second column which is the output of our OLEDB Source i.e Coordinates.
Please Consider Subscribing
