{"id":461,"date":"2011-05-15T08:44:05","date_gmt":"2011-05-15T13:44:05","guid":{"rendered":"http:\/\/crschmidt.net\/blog\/?p=461"},"modified":"2011-05-15T08:44:05","modified_gmt":"2011-05-15T13:44:05","slug":"finding-distance-from-a-point-in-spain-to-the-sea","status":"publish","type":"post","link":"https:\/\/crschmidt.net\/blog\/archives\/461\/finding-distance-from-a-point-in-spain-to-the-sea\/","title":{"rendered":"Finding distance from a point in Spain to the Sea"},"content":{"rendered":"<p>So, a friend asked me if there was a list of distances-to-the-sea for worldwide cities. I wasn&#8217;t aware of one, but thought it should be relatively quick to build one &#8212; then actually thought a bit more, and actually did it, and found out instead of the &#8216;a while&#8217; estimate, it was actually closer to about 10 minutes. So I thought I&#8217;d write up what I did and get a critique on it. <\/p>\n<p>This approach only works for the area of interest, Spain.<\/p>\n<p>So, first I grabbed the world_borders file from http:\/\/www.mappinghacks.com\/data\/world_borders.zip &#8212; we&#8217;re looking for a gross estimate, so this is a reasonable dataset to use. I unzipped the data, and loaded it into postgres:<\/p>\n<pre>$ createdb world\n$ psql -f $POSTGIS\/postgis.sql world\n# Crap, forgot the plpgsql\n$ createlang plpgsql world\n$ psql -f $POSTGIS\/postgis.sql world\n$ ogr2ogr -f PostgreSQL PG:dbname=world world_borders.shp<\/pre>\n<p>Then assembled a geometry of France, Spain, and Portugal, using ST_Union:<\/p>\n<pre>select ST_Union(wkb_geometry) \n   FROM world_borders \n   WHERE cntry_name IN ('Spain', 'France', 'Portugal');<\/pre>\n<p>Once I did that, I grabbed the point for Madrid, and calculated the distance, after converting that shape to a boundary:<\/p>\n<pre>select ST_Distance_Sphere(ST_GeomFromText('POINT(-3.683333 40.4)'), \n    Boundary(ST_Union(wkb_geometry))) \n    FROM world_borders \n    WHERE cntry_name IN ('Spain', 'France', 'Portugal');<\/pre>\n<p>Which gave me the ballpark answer I was expecting, of 303.2km.<\/p>\n<p>Dependencies: PostgreSQL, PostGIS, OGR installed on your machine (easy on a mac &#8212; just grab the KyngChaos libraries &#8212; and also easy on Debian derivatives).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>So, a friend asked me if there was a list of distances-to-the-sea for worldwide cities. I wasn&#8217;t aware of one, but thought it should be relatively quick to build one &#8212; then actually thought a bit more, and actually did it, and found out instead of the &#8216;a while&#8217; estimate, it was actually closer to [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-461","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/crschmidt.net\/blog\/wp-json\/wp\/v2\/posts\/461","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/crschmidt.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/crschmidt.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/crschmidt.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/crschmidt.net\/blog\/wp-json\/wp\/v2\/comments?post=461"}],"version-history":[{"count":0,"href":"https:\/\/crschmidt.net\/blog\/wp-json\/wp\/v2\/posts\/461\/revisions"}],"wp:attachment":[{"href":"https:\/\/crschmidt.net\/blog\/wp-json\/wp\/v2\/media?parent=461"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/crschmidt.net\/blog\/wp-json\/wp\/v2\/categories?post=461"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/crschmidt.net\/blog\/wp-json\/wp\/v2\/tags?post=461"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}