Wednesday, June 6, 2012

Using GeoServer to replace TerraServer DRG wms service


Another awe-inspiring moment provided by GeoServer.

Recently, MSRMAPS (used to be TerraServer), a WMS server for DRGs and DOQs is being dropped by Microsoft. Nobody really cares about the DOQs, but this was just about the only DRG WMS server out there.

Looking around, I found that the USGS has a DRG server:

http://raster.nationalmap.gov/ArcGIS/services/DRG/TNM_Digital_Raster_Graphics/MapServer/WMSServer?request=GetCapabilities&service=WMS

However it is split into UTM zones and different resolutions. I just wanted 4326 for the entire US, like Terraserver.

So, I created a GeoServer Amazon EC2 Micro (free for a year!) instance using the basic Amazon Linux AMI ami-e565ba8c, and put on just Tomcat and Geoserver 2.2 Beta 2:

sudo yum update
sudo yum install httpd httpd-devel tomcat7
wget http://sourceforge.net/projects/geoserver/files/GeoServer/2.2-beta2/geoserver-2.2-beta2-war.zip/download
unzip geoserver-2.2-beta2-war.zip
sudo chown tomcat:tomcat geoserver.war
sudo mv geoserver.war /var/lib/tomcat7/webapps/
sudo /sbin/service httpd start
sudo /sbin/service tomcat7 start

and then added a WMS Store and used the GetCapabilities above. It automatically adds all the layers and publishes them.

Then I put together a Layer Group with all the layers, the only change was to check the "Default Style" box, otherwise I would get a WMS error.
Set the coordinate system for the Layer Group to EPSG:4326, and it worked.

Thank you GeoServer team!

Roger

Wednesday, May 23, 2012

How to Backup and Restore an entire postgreSQL database that contains one or more PostGIS databases in two easy steps

How to Backup and Restore an entire postgreSQL database that contains one or more PostGIS databases in two easy steps


Problem: We have a PostgreSQL server on Windows Server 2008 that has 5 databases. One of which is a PostGIS spatial database. If you just do a complete server backup and restore, the PostGIS database doesn't restore correctly.


We wanted to upgrade to 64 bits, the latest V9 version of Postgresql from V8, so needed to do a complete backup and restore to the new server.


After a couple of days mucking around in the documentation, I found that the instructions here did not work, at least not for me.

PostGIS Hard Upgrade for Windows

http://trac.osgeo.org/postgis/wiki/UsersWikiWinUpgrade


Instead, I found a simple work around, just make sure the PostGIS database is created before the restore.

First. Do a Server backup from the old server using pgAdmin III

On the old server, the easiest way is to use PGAdmin III, right click on the server and choose “Backup Server...”


Easy.

Next, 

Transfer this file to the new server.

We will restore this file on the new machine. It includes all roles and databases. The restore is pretty ugly, but works.

However, before doing this restore, you have to create any PostGIS databases, and turn them into PostGIS databases. Otherwise, the restore won't work correctly for these.

Create the PostGIS database:
Right click on “Databases” in PGAdmin and select ” New Database”.

Create a new PostGIS database on the new database server in PgAdmin.



When this is done, highlight the OGI database and open an SQL Query window from pgAdmin. (the magnifying glass in the toolbar)

Run these commands:
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;



Also, open and execute the “legacy.sql” script in the SQL window. This is located here:
PostgreSQL913\share\contrib\postgis-2.0

From the start menu, run the PostGreSQL 9.1 | SQL Shell as Administrator

Log into the server, with Database OGI. Run the following commands:
\cd f:/ogi/backups This makes your current directory where the backup SQL file is
\i OGI_5-23-2012.sql This loads the backup SQL file into the OGI database.




You will get many errors and warnings, you can ignore them. For sanity, it is best to just minimize the SQL Shell window and wait for the process to complete.

When this has completed, you will have all the roles, tables etc that existed in the old server. This process will work for upgrading from any version of PostgreSQL/PostGIS to this one.