Finding distance from a point in Spain to the Sea

So, a friend asked me if there was a list of distances-to-the-sea for worldwide cities. I wasn’t aware of one, but thought it should be relatively quick to build one — then actually thought a bit more, and actually did it, and found out instead of the ‘a while’ estimate, it was actually closer to about 10 minutes. So I thought I’d write up what I did and get a critique on it.

This approach only works for the area of interest, Spain.

So, first I grabbed the world_borders file from — we’re looking for a gross estimate, so this is a reasonable dataset to use. I unzipped the data, and loaded it into postgres:

$ createdb world
$ psql -f $POSTGIS/postgis.sql world
# Crap, forgot the plpgsql
$ createlang plpgsql world
$ psql -f $POSTGIS/postgis.sql world
$ ogr2ogr -f PostgreSQL PG:dbname=world world_borders.shp

Then assembled a geometry of France, Spain, and Portugal, using ST_Union:

select ST_Union(wkb_geometry) 
   FROM world_borders 
   WHERE cntry_name IN ('Spain', 'France', 'Portugal');

Once I did that, I grabbed the point for Madrid, and calculated the distance, after converting that shape to a boundary:

select ST_Distance_Sphere(ST_GeomFromText('POINT(-3.683333 40.4)'), 
    FROM world_borders 
    WHERE cntry_name IN ('Spain', 'France', 'Portugal');

Which gave me the ballpark answer I was expecting, of 303.2km.

Dependencies: PostgreSQL, PostGIS, OGR installed on your machine (easy on a mac — just grab the KyngChaos libraries — and also easy on Debian derivatives).

Comments are closed.