Copying an existing postgis db to use in Tilemill

Furthering my exploration in cartography, I’ve been experimenting with Tilemill lately, particularly using data from openstreetmap [that’s in a postgis db].

[The rest of this entry assumes you’re at least familiar with Tilemill and its terminology].

I started learning to make my own Tilemill stylesheets [written in carto]. As of now, there’s only 3-4 other stylesheets for maps on a street level. (we’re talking like 0.25mile=72 pixels, roughly; enough to see several city blocks on your computer monitor]. (I also plan on comparing these in a future post in the coming days).

One of the most prominent ones is open-streets, a stylesheet made by developmentseed/Mapbox, the dev’s of Tilemill.

Although open-streets has been invaluable for me learning tilemill, I was questioning how they structured their stylesheet and wondered if there were any other approaches.

I then found high-roads by Michal Migurski, whose work and mapping I’m beginning to admire.

So, for the past couple days, on and off, I was figuring how to try out high-roads without making a whole new postgis database from scratch (preparing OSM data, etc, etc) and instead copying an existing one and using that copied db to experiment with using high-roads.

Caveat: You could use the same database in another Tilemill project, but I wasn’t sure if using views.sql from high-road would alter the database; and wanted to be safe than sorry.

So, after some trial and error, this what I did to do that:

Found this helpful page by Ramesh Natarajan concisely explaining how to export a single mysql db:
did the following to back up the postgis database whose copy I would use for high-roads:
(obviously, user-name is the one that is the owner of the postgis db):

pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

pg_dump -U myusername thecleve -v -f theclevebackup.sql

Log into your postgres account:
sudo -u postgres -i

created the new database that you’ll be using for your new Tilemill project:
createdb thenameofyournewdb (in the following commands, it's named thecleve-hr)

Then run the couple commands to prepare your mysql for postgis:
createlang plpgsql thecleve-hr
psql -d thecleve-hr -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql -d thecleve-hr -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
echo "ALTER TABLE geometry_columns OWNER TO skorasaurus; ALTER TABLE spatial_ref_sys OWNER TO myusername;" | psql -d thecleve-hr

(I ran this last ‘echo..’ command to move the ownership back to my postgis user name.. I know that I didn’t create my postgis user name correctly, so I have add myself as the
owner again…)

Then, I was going to restore the database…
$ psql -U myusername -d thecleve-hr -f theclevebackup.sql
psql: FATAL: Ident authentication failed for user "myusername"
nope, that didn't work.

Next idea, log out of postgres, and do it as my normal user name…

So I did…
$ psql -U myusername -d thecleve-hr -f theclevebackup.sql
[received tons of errors after this].

So I figured, let’s see if the new db works anyways.
psql “dbname=thecleve-hr host=localhost user=myusername password=mypassword”
I can connect to the database by….
but when issuing \d:
no relations are made... that’s a bad sign !

but on a whim, I decided to remove my username and try:
$ psql -U -d thecleve-hr -f theclevebackup.sql

and now the data is there after issuing \d !

Then, I just went ahead and created my project in Tilemill =)

Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s