1

I've pulled together a series of maps in R based on Nathan Yau's excellent Great Circles tutorial and a nice zipcode package The maps show how many people from many zip codes descended on a single zip code. Here's one example:

sample map

I'd like to reproduce the maps in CartoDB (which uses PostGIS, I believe), but I'm not sure where to start.

My read of what I was doing in R was generating a matrix called "inter" that was full of lat/long points, drawing the line connecting those points and then starting over. So one solution that occurs to me is to have R store each line as a map line and take that series of lines to CartoDB. But I'm wondering if there's a reasonable way to just generate the maps in CartoDB directly, given an end point and a series of starting points.

1 Answer 1

1

CartoDB allows you to submit custom SQL queries so you can use the same commands as suggested in this post to create a great circle directly in CartoDB. For this demo I created two tables, with one point each (source and destination). Then I created a third empty table to hold the line string. How exactly you do this depends on your particular situation. Note that you have to also insert the CRS into the spatial_ref_sys table as explained in the same post.

enter image description here

http://cdb.io/1dEXBh9

Update: The actual query is:

UPDATE experimental.airroutes
SET the_geom =
(SELECT ST_Transform(ST_Segmentize(ST_MakeLine(
       ST_Transform(a.the_geom, 953027),
       ST_Transform(b.the_geom, 953027)
     ), 100000 ), 4326 )
 FROM experimental.airports a, experimental.airports b
 WHERE a.id = airroutes.source_id 
   AND b.id = airroutes.dest_id
);

and you'll need to have srid 953027 defined, so try:

SELECT * FROM spatial_ref_sys WHERE srid = 953027

And if it isn't set, set it with:

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 953027, 'esri', 53027, '+proj=eqdc +lat_0=0 +lon_0=0 +lat_1=60 +lat_2=60 +x_0=0 +y_0=0 +a=6371000 +b=6371000 +units=m +no_defs ', 'PROJCS["Sphere_Equidistant_Conic",GEOGCS["GCS_Sphere",DATUM["Not_specified_based_on_Authalic_Sphere",SPHEROID["Sphere",6371000,0]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Equidistant_Conic"],PARAMETER["False_Easting",0],PARAMETER["False_Northing",0],PARAMETER["Central_Meridian",0],PARAMETER["Standard_Parallel_1",60],PARAMETER["Standard_Parallel_2",60],PARAMETER["Latitude_Of_Origin",0],UNIT["Meter",1],AUTHORITY["EPSG","53027"]]');
4
  • Will try this. And CartoDB's Andrew Hill sent this my way: bl.ocks.org/andrewxhill/raw/8406976
    – Amanda
    Jan 27, 2014 at 15:40
  • Where does "INSERT INTO spatial_ref_sys" do it's magic? I don't have my own spatial_ref_sys table. Am I altering CartoDB's own? That seems wonky.
    – Amanda
    Jan 27, 2014 at 22:21
  • @Amanda You are altering the spatial_ref_sys table that comes with your own database.
    – cengel
    Jan 28, 2014 at 3:54
  • Accepting because I did ask "where to start" and this was the start. Added new questions as I got stuck inside the query.
    – Amanda
    Jan 28, 2014 at 13:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.