How to convert coordinates from degrees, minutes, seconds to decimal format


Here’s how to do it alone.
Conversion from degrees, minutes, seconds to decimal format:
Conversion is by adding degrees minutes divided by 60, plus seconds divided to 3600.

If we have  42° 41′ 48.6528″ 23° 19′ 14.9298″
42+(41/60)+(48.6528/3600) => 42.696848
23+(19/60)+(14.9298/3600) => 23.320813


Conversion from decimal format to degrees, minutes, seconds:

The number before the decimal point are degrees. Take only the decimal part and multiply by 60. The resulting number to the decimal point are minute. Again taking only the decimal part and multiply by 60. Receiving seconds.

42.696848 =>
42 degrees
0.696848 * 60 = 41.81088 => 41 minutes
0.81088 * 60 = 48.6528 => 48.6528 seconds
This equal to 42° 41′ 48.6528″

Convert, Reproject, and Load Spatial Data to SQL Server with OGR2OGR

I used OGR2OGR to join a set of shapefiles together prior to loading them into SQL Server using Shape2SQL. But OGR2OGR can do much more than simply appending shapefiles – it can convert data into different formats, transform coordinates into different spatial references systems, read and write a whole variety of spatial datasources, and (with a bit of fiddling) load them into SQL Server 2008.

To demonstrate, I thought I’d repeat the objective of my previous post, but instead of simply appending the Ordnance Survey open data shapefiles together, I would reproject them into a different SRID, and import them into SQL Server too using OGR2OGR alone, without using Shape2SQL or any other tools.

Use OGR2OGR to create a CSV file containing WKT

OGR2OGR can’t insert spatial data directly into SQL Server 2008, but it can create CSV files that can be read into SQL Server. To create an output file in CSV format, set the –f CSV output option. You can also manually set layer creation options to dictate the field delimiter and line return character of the CSV file, using the LINEFORMAT and SEPARATOR layer creation options.

By default, the CSV file created only contains columns containing the attributes associated with each shape (i.e the data in the .dbf file associated with a shapefile), but it doesn’t include the shape itself. To include the shape of the geometry in Well-Known Text format, you also need to state the –lco “GEOMETRY=AS_WKT” layer creation option.

But that’s not all – OGR2OGR can also reproject data between coordinate systems as it converts it. To convert to another spatial reference system, include the -t_srs parameter, together with either the full WKT definition (hard to escape properly,i.e. GEOGCS(ELLIPSOID(“WGS 84”, ……) or the EPSG code (much easier, i.e. EPSG:4326 for WGS84).

Putting this all together, you can use the following command to load a shapefile (in this case, the Ordnance Survey OS Vector Map settlement_area polygon shapefile), reproject the data into SRID 4326, and then save the result as a CSV file containing a column with the WKT of each resulting polygon:
[php]ogr2ogr -f "CSV" "Settlement_Area" "Settlement_Area.shp" -t_srs "EPSG:4326" -lco "GEOMETRY=AS_WKT" -lco "LINEFORMAT=CRLF" -lco "SEPARATOR=SEMICOLON" [/php]
NOTE – I’ve noticed some problems with the quote/speechmark characters in this blog being changed to “smartquotes”, even when I use the code-formatting option. OGR2OGR isn’t happy about this, so I recommend that you don’t try and copy and paste the line above into a CMD window – if you get an error about “Couldn’t fetch request layer uT” or something similar then be sure to retype using normal speech marks.

More information on CSV options for OGR2OGR can be found here:

Create a Format File

To import the CSV into SQL Server, we have a range of options – you could use SSIS, or you could use the Tasks –> Import Data wizard. Or you could use BCP or BULK INSERT. However, I’m going to use OPENROWSET. This will allow me to write a single query to access the CSV file directly from T-SQL as if it were an existing table.

In order to do this, we first need to define a format file which specifies the datatypes of each of the columns in the input csv file, and how they should be mapped to datatypes of columns in a sql table. More information on format files can be found here.

The following shows the XML format file required for the OS VectorMaps Settlement Area shapefile just converted:

[php]<?xml version="1.0"?>
<BCPFORMAT xmlns="" xmlns:xsi="">
<FIELD ID="0" xsi:type="CharTerm" TERMINATOR="""/>
<FIELD ID="1" xsi:type="CharTerm" MAX_LENGTH="2147483647" TERMINATOR="";"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"/>
Query the CSV from SQL Server using the Format File

Finally, you can write a SELECT query in SQL Server to query the CSV file using  OPENROWSET in conjunction with the format file above. Since the CSV contains column headers, I’ve included the FIRSTROW=2 parameter to skip the header row. I’ve also used the geometry::Parse() method in the SELECT to dynamically create the geometry data from the Well-Known Text representation contained in the WKT column of the supplied CSV file.
BULK ‘C:Settlement_Area.csv’,
) AS CSV;[/php]
And here’s the results showing my Ordnance Survey data, originally provided as a shapefile using projected coordinates in SRID 27700, now loaded as latitude/longitude coordinates (EPSG:4326) using the geography datatype:


Janne Aukia’s World of Adjectives

Janne Aukia's world of Google

Links to typographic maps of one sort or another — and it turns out that there is more than one sort — continue to come out of the woodwork, in numbers sufficient to warrant their own category. The latest comes from Janne Aukia, who writes with links to two word maps of the world he made a couple of years ago. Each is a map of the world made up of phrases describing cities, with colours and font sizes matching population size. One is “a map with Google search matches that are of the format ‘is * for its,’ such as ‘Helsinki is * for its’” (above); the other is of adjectives describing cities on Wikitravel. Janne describes the maps on his blog, here and here (in Finnish, but Google Translate isn’t bad).