Converting DMS to PostGIS Point Geometry

I love taking random spatial data and turning it into maps. Any location data can be put into PostGIS in a matter of minutes. Often when I’m working with data that humans collected, like historic locations or things that have not yet traditionally been done with computational data, I’ll find traditional Degrees, Minutes, Seconds (DMS) data. To get this into PostGIS and QGIS, you’ll need to convert this data to a different system for decimal degrees. There’s probably proprietary tools that will do this for you, but we can easily write our own code to do it. Let’s walk through a quick example today.

Let’s say I found myself with a list of coordinates, that look like this:

38°58′17″N 95°14′05″W

(this is the location of my town’s haunted hotel 👻)

This format of writing geographic coordinates is called DMS, Degrees, Minutes, Seconds (DMS). If you remember from 4th grade geography lessons, that is the latitude on the left there, representing N or S of the equator and longitude East or West of the Prime Meridian.

WKT & XY coordinates

PostGIS, and most computational spatial systems, work with a geographic system that is akin to an XY grid of the entire planet. Because it is XY, it is a longitude, latitude (X first) system.

PostGIS utilizes with two kinds of geometry values:

• WKT (Well-known text) where a point would look like this POINT(-126.4 45.32)
• WKB (Well-known binary) where a point would look like this 0101000000000000000000F03F000000000000F03

Most often you’ll see the binary used to represent stored data and you can use a function, st_astext, to view or query it as text.

Converting coordinates to decimal degrees

To convert our traditional coordinates into decimals or WKT, we can use decimal math like this:

({long_degree}+({long_minutes}/60)+({long_seconds}/3600)

So for our location:

-- starting location
38°58′17″N 95°14′05″W

-- formula
38+(58/60)+(17/3600), 95+(14/60)+(05/3600)

-- switch the order since this is X first
-- make the Western quad negative
-- getting this result

 -95.2472222, 38.9713888

Regex Function for Making PostGIS Points out of DMS

If you have one location like this, you probably have a lot, so we’ll need a more sophisticated solution for our whole data set. You know if you need something done right, you ask Paul Ramsey. Paul worked with me on getting this function written that will convert DMS to PostGIS friendly (binary geometry) point data.

CREATE OR REPLACE FUNCTION dms_to_postgis_point(dms_text TEXT)
    RETURNS geometry AS
    $$
    DECLARE
        dms TEXT[] := regexp_match(dms_text, '(\d+)\D+(\d+)\D+(\d+)\D+([NS])\D+(\d+)\D+(\d+)\D+(\d+)\D+([EW])');
        lat float8;
        lon float8;
    BEGIN
        lat := dms[1]::float8 + dms[2]::float8/60 + dms[3]::float8/3600;
        lon := dms[5]::float8 + dms[6]::float8/60 + dms[7]::float8/3600;
        IF upper(dms[4]) = 'S' THEN
            lat := -1 * lat;
        END IF;

        IF upper(dms[8]) = 'W' THEN
            lon := -1 * lon;
        END IF;

        RETURN ST_Point(lon, lat, 4326);
    END;
    $$
    LANGUAGE 'plpgsql'
    IMMUTABLE
    STRICT;

Let’s do a quick test with our original point:

SELECT st_astext(dms_to_postgis_point('38°58′17″N 95°14′05″W'));

                  st_astext
---------------------------------------------
 POINT(-95.23472222222222 38.97138888888889)
(1 row)

Great, that works.

Creating a new column with your geometry

Now we can use built-in PostGIS functions to add a new geom column and run the function on our old lat_long column.

ALTER TABLE my_table ADD COLUMN geom geometry(Point);

UPDATE my_table SET geom = dms_to_postgis_point(lat_long);

Conclusion

PostGIS is just packed with so many cool functions to make sure you can turn anything into maps. Hope this helps you get started if you’re using traditional lat long data.