Overview

This tutorial shows how to load tropical storm tracking data into PostGIS that can be visualized using GeoServer and GoogleEarth.  It is meant to demonstrate how GeoServer can be used to filter a (relatively) large dataset with spatial and temporal attributes.  This tutorial assumes that you have already have a working PostgreSQL/PostGIS server, and that you are familiar with the tools used to work with PostgreSQL databases.

Table of Contents

PreReqs

• A working PostGIS Datastore
• Google Earth

Dataset Source

Tropical storm tracking data can be downloaded from the Unisys. While many datafiles are available on the site, this tutorial uses two 6-hour reanalysis datafiles available at:

• 2000-2006 Atlantic Tropical Storm Tracking
• 1990-1999 Atlantic Tropical Storm Tracking

Similar datasets for other time intervals can be accessed by links under the 'Hurricane Database reformatted by decade' section near the bottom of the Atlantic Tropical Storm Tracking by Year page.  Datasets for other regions of the earth can be found on the Hurricane/Tropical Data page.

Formatting the Data

The downloaded datasets are in fixed-width text format.  Converting the files to a comma delimited text files is useful for import into PostgreSQL.

Here are steps to convert from fixed-width to comma separated formats using the Open Office Calc 2.3 spreadsheet program.  (The same format conversion could probably be done with Excel or other programs.)

1. Save data files to: /tmp/unisys_hurricane_tracks/raw with a *.txt extension
2. Open data file in Open Office Calc as 'Text CSV'
   • Use a fixed with separator option
3. Save the formatted file as a 'Text CSV' in: /tmp/unisys_hurricane_tracks/formatted
   • set the export filter settings to UTF-8, comma field delimiter, double quote text delimiter

In OpenOffice.org Calc. 2.4.x it is easier to save the files with the extension .csv and drag them into OpenOffice.org Calc. The Text Import wizard will come up, you should then choose "Separated by" , check off "Space" and "Merge delimiters". Leave the others unchecked.

When you save, choose File type Text CSV, and check "Edit filter settings".

PostGIS Import

Importing Storm Observation Data

Now that we the storm track data in comma-delimited text files, we can now import it into PostGIS.  PostGIS is used for data storage because of it's ability to run spatial functions on the dataset, which we will use to create storm path segments (line geometry) from storm location observations (point geometry).

1. Create a PostGIS database named 'storm_track_sql'
   • choose UTF-8 encoding
2. Create a database user named 'geoserver' which will be used to access the PostGIS data.
3. Grant read permissions to the geoserver user for the PostGIS tables

   GRANT SELECT ON TABLE spatial_ref_sys TO geoserver;
   GRANT SELECT ON TABLE geometry_columns TO geoserver;

4. Create a temporary table (temp_import_data) for importing the comma-separated data file of raw storm observation data.  Note that this table does not contains location data in the latitude and longitude columns, and does not contain any PostGIS geometry data types.  Also, the temporal information is contained in several columns: obs_year, obs_month, obs_day, and obs_hour.

   CREATE TABLE temp_import_data
   (
     obs_year integer NOT NULL,
     obs_month integer NOT NULL,
     obs_day integer NOT NULL,
     obs_hour integer NOT NULL,
     storm_num integer, -- Storm number for that particular year (including subtropical storms)
     storm_name character varying(15) NOT NULL, -- Storms only given official names since 1950
     latitude numeric(4,1) NOT NULL,
     longitude numeric(4,1) NOT NULL,
     wind numeric(4,1) NOT NULL, -- Maximum sustained (1 minute) surface (10m) windspeed in knots (in general, these are to the nearest 5 knots).
     press numeric(4) NOT NULL -- Central surface pressure of storm in mb (if available).  Since1979, central pressures are given everytime even if a satellite estimation is needed.
   )
   WITH (OIDS=FALSE);
   ALTER TABLE temp_import_data OWNER TO postgres;
   COMMENT ON COLUMN temp_import_data.storm_num IS 'Storm number for that particular year (including subtropical storms)';
   COMMENT ON COLUMN temp_import_data.storm_name IS 'Storms only given official names since 1950';
   COMMENT ON COLUMN temp_import_data.wind IS 'Maximum sustained (1 minute) surface (10m) windspeed in knots (in general, these are to the nearest 5 knots).';
   COMMENT ON COLUMN temp_import_data.press IS 'Central surface pressure of storm in mb (if available).  Since1979, central pressures are given everytime even if a satellite estimation is needed.';

   Note: You may have to change ALTER TABLE temp_import_data OWNER TO postgres; to match your username.

5. Import the comma-delimited observation data into the temporary database table

   COPY temp_import_data
       FROM '/tmp/unisys_hurricane_tracks/formatted/2000_data.csv'
       WITH CSV;
   COPY temp_import_data
       FROM '/tmp/unisys_hurricane_tracks/formatted/1990_data.csv'
       WITH CSV;

   Note 1:

   \copy temp_import_data FROM '/tmp/unisys_hurricane_tracks/formatted/1990_data.csv' WITH CSV

   Note 2:
   Depending on how you converted your data, you may get errors saying "ERROR: extra data after last expected column"> If this happens you should remove loose commas at the end of lines, and make sure that names like "SUBTROPIC 1" have not been split to "SUBTROPIC","1" (an extra column).

6. Create a table for the processed storm track observations.  Note that this table has a single column for the date/time information: obs_datetime.

   CREATE TABLE storm_obs
   (
     id serial NOT NULL,
     storm_num integer NOT NULL, -- Storm number for that particular year (including subtropical storms)
     storm_name character varying(15) NOT NULL, -- Storms only given official names since 1950
     wind numeric(4,1) NOT NULL, -- Maximum sustained (1 minute) surface (10m) windspeed in knots (in general, these are to the nearest 5 knots).
     press numeric(4) NOT NULL, -- Central surface pressure of storm in mb (if available).  Since1979, central pressures are given everytime even if a satellite estimation is needed.
     obs_datetime timestamp without time zone, -- date/time of the storm track observation
     CONSTRAINT pk_storm_obs PRIMARY KEY (id)
   )
   WITH (OIDS=FALSE);
   ALTER TABLE storm_obs OWNER TO postgres;
   GRANT ALL ON TABLE storm_obs TO postgres;
   GRANT SELECT ON TABLE storm_obs TO geoserver;
   COMMENT ON COLUMN storm_obs.storm_num IS 'Storm number for that particular year (including subtropical storms)';
   COMMENT ON COLUMN storm_obs.storm_name IS 'Storms only given official names since 1950';
   COMMENT ON COLUMN storm_obs.wind IS 'Maximum sustained (1 minute) surface (10m) windspeed in knots (in general, these are to the nearest 5 knots).';
   COMMENT ON COLUMN storm_obs.press IS 'Central surface pressure of storm in mb (if available).  Since1979, central pressures are given everytime even if a satellite estimation is needed.';
   COMMENT ON COLUMN storm_obs.obs_datetime IS 'date/time of the storm track observation';

7. Add a 2-D point geometry column to the storm_obs table:

   SELECT AddGeometryColumn( 'storm_obs', 'geom', 4326, 'POINT', 2 );

   Additional information on adding geometry columns can be found in the PostGIS docs.

8. Create a function to process the data in the temp table (temp_import_data).  This function does the following things:
   • iterates through each of the records in the temp table
   • merges the spatial data columns into a single spatial data column (geom)
   • merges the temporal data columns into a single temporal data column (obs_datetime) _(Note: that the negative value of the longitude column was used for this datafile, but that may not be correct when processing the datafiles for other oceans.)_

     CREATE OR REPLACE FUNCTION f_populate_storm_obs()
       RETURNS integer AS
     $BODY$
     -- EXAMPLE USAGE:  SELECT f_populate_storm_obs();
     DECLARE
         iobs RECORD;
     BEGIN
     	FOR iobs IN
     		SELECT DISTINCT storm_num, storm_name, wind, press,
     		(obs_year||'-'||obs_month||'-'||obs_day||' '||obs_hour||':00')::timestamp AS obs_datetime,
     		ST_PointFromText('POINT(-'||longitude||' '||latitude||')',4326) AS geom
     		FROM temp_import_data
     	LOOP
     		EXECUTE 'INSERT INTO storm_obs(storm_num, storm_name, wind, press, obs_datetime, geom) ' ||
     		    'VALUES (' || iobs.storm_num
     		    || ',''' || iobs.storm_name || ''''
     		    || ',' || iobs.wind
     		    || ',' || iobs.press
     		    || ',''' || iobs.obs_datetime || ''''
     		    || ',''' || iobs.geom::text || ''''
     		    || ');';
     	END LOOP;
         RETURN 1;
     END;
     $BODY$
       LANGUAGE 'plpgsql' VOLATILE;
     ALTER FUNCTION f_populate_storm_obs() OWNER TO postgres;
     COMMENT ON FUNCTION f_populate_storm_obs() IS 'function to load storm observation data from a temp table to a table with geometry';

9. Run the function to import the data into the storm_obs table.  This creates 7,298 storm observations records.

   SELECT f_populate_storm_obs();

Importing State Boundaries

In order to facilitate demonstration of some geospatial functions applied to multiple datasets, we will import the standard 'States' dataset into PostGIS. The following example code can be used to import the States shapefile into PostGIS. Note that you will have to change the shapefile location and database connection parameters as appropriate for your local PostGIS system.

$ shp2pgsql /path-to_geoserver/data_dir/data/shapefiles/states.shp public.states -s 4326 | psql -h localhost -d storm_track_sql

Setting up Spatial Views

Visualizing the point locations of storm observations may be interesting by itself, but it is helpful to construct lines that connect consecutive observations. This section describes some PostGIS spatial queries that can be used to construct line geometries of from point geometries.

1. Spatial View: Storm Tracks

The first spatial view creates a single line geometry (the_route) for each unique storm (each storm has a unique year and storm_name).  The query also returns the starting time, ending time, and maximum wind speed for each storm.  This results in 222 storm track records for Atlantic storms between 1990 and 2006.

CREATE OR REPLACE VIEW v_storm_track AS
SELECT
	st.obs_year,
	st.storm_num,
	st.storm_name,
	min(st.obs_datetime) AS storm_start,
	max(st.obs_datetime) AS storm_end,
	max(st.wind) AS max_wind,
	makeline(st.geom) AS the_route
 FROM (
		SELECT
			storm_obs.storm_num,
			storm_obs.storm_name,
			storm_obs.wind,
			storm_obs.press,
			storm_obs.obs_datetime,
			date_part('year'::text, storm_obs.obs_datetime) AS obs_year,
			storm_obs.geom
		FROM storm_obs
		ORDER BY
			date_part('year'::text, storm_obs.obs_datetime),
			storm_obs.storm_num, storm_obs.obs_datetime
	) st
GROUP BY st.obs_year, st.storm_num, st.storm_name
ORDER BY st.obs_year, st.storm_num;

ALTER TABLE v_storm_track OWNER TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE v_storm_track TO postgres;
GRANT SELECT ON TABLE v_storm_track TO geoserver;
COMMENT ON VIEW v_storm_track IS 'This view creates a line geometry out of the point observations for each storm, and reports the storm start time, the storm end time, and the maximum wind speed.';

1. Spatial View: Storm Track Interval

The second spatial view creates a line geometry between consecutive storm observations.  The observations that are paired share the same storm name and are separated by 6 hours.  Each storm track interval contains information on the starting and ending wind speeds, and on the starting and ending pressures.

CREATE OR REPLACE VIEW v_storm_track_interval AS
SELECT
	date_part('year'::text, t1.obs_datetime) AS obs_year,
	t1.storm_num,
	t1.storm_name,
	t1.wind,
	t2.wind AS wind_end,
	t1.press,
	t2.press AS press_end,
	t1.obs_datetime,
	t2.obs_datetime AS obs_datetime_end,
	makeline(t1.geom, t2.geom) AS geom
  FROM
	storm_obs t1
   JOIN (
	SELECT
		storm_obs.id,
		storm_obs.storm_num,
		storm_obs.storm_name,
		storm_obs.wind,
		storm_obs.press,
		storm_obs.obs_datetime,
		storm_obs.geom
           FROM storm_obs
	) t2
		ON (t1.obs_datetime + '06:00:00'::interval) = t2.obs_datetime AND t1.storm_name::text = t2.storm_name::text
  ORDER BY date_part('year'::text, t1.obs_datetime), t1.storm_num, t1.obs_datetime;

ALTER TABLE v_storm_track_interval OWNER TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE v_storm_track_interval TO postgres;
GRANT SELECT ON TABLE v_storm_track_interval TO geoserver;
COMMENT ON VIEW v_storm_track_interval IS 'This view creates a line segment out of the point observations for each storm that are separated by 6 hours.';

1. Spatial View: Storms Intersecting States

A final spatial view can be setup to count then number of storms that intersected a state in a given year.

-- View: "v_storm_track_state_year"

-- DROP VIEW v_storm_track_state_year;

CREATE OR REPLACE VIEW v_storm_track_state_year AS
 SELECT states.state_name, subquery.obs_year, subquery.storm_count, states.the_geom
   FROM ( SELECT s.gid, t.obs_year, count(t.storm_num) AS storm_count
           FROM v_storm_track t, states s
          WHERE t.the_route && s.the_geom AND st_intersects(t.the_route, s.the_geom)
          GROUP BY s.gid, t.obs_year) subquery
   JOIN states ON subquery.gid = states.gid
  ORDER BY states.state_name, subquery.obs_year;

ALTER TABLE v_storm_track_state_year OWNER TO taericks;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE v_storm_track_state_year TO postgres;
GRANT SELECT ON TABLE v_storm_track_state_year TO geoserver;
COMMENT ON VIEW v_storm_track_state_year IS 'Counts the number of storms by year for each state';

GeoServer Configuration

Now that the data storage has be setup, GeoServer needs to be configured to use the PostGIS table and views. 

1. Setup a Namespace (Config -> Data -> Namespace)

This step is optional, but it helps with organizing GeoServer... this tutorial uses the namespace 'mtriDemo' but you can use whatever you want for the namespace name.

1. Setup a Datastore for the PostGIS database (Config -> Data -> Datastores)

1. Add Styles (Config -> Data -> Style)

Download the following files and add them as styles for GeoServer.

1. Add Feature Types (Config -> Data -> FeatureTypes)

1. Add a Placemark Template to each feature type

Placemark Templates are used to customize what is displayed in the feature description popups in Google Earth. The following code customizes the popups for the three FeatureTypes that were added. For more information on creating Placemark Templates, see the Placemark Templates page in the GeoServer documentation.

<h1>Tropical Storm Observation</h1>
<hr>
<b>Storm Name:</b> ${storm_name.value} (# ${storm_num.value})<br/>
<b>Observed At:</b> ${obs_datetime.value}<br/>
<b>Wind:</b> ${wind.value} knots<br/>
<b>Pressure:</b> ${press.value} mbar
<hr>
<div align="center">
<a href="http://geoserver.org"><img src="http://sigma.openplans.org/images/geos-90x33.jpg" width="90" height="33" border="0" alt="GeoServer Logo" /></a></div>

<h1>Tropical Storm Track</h1>
<b>Storm Name:</b> ${storm_name.value}<br/>
<b>Storm #:</b> ${storm_num.value}<br/>
<p>
<table BORDER=1>
<tr><th>First Observation:</th><td>${storm_start.value}</td></tr>
<tr><th>Last Observation:</th><td>${storm_end.value}</td></tr>
<tr><th>Maximum Wind:</th><td>${max_wind.value} knots</td></tr>
</table>
</p>
<div align="center">
<a href="http://geoserver.org"><img src="http://sigma.openplans.org/images/geos-90x33.jpg" width="90" height="33" border="0" alt="GeoServer Logo" /></a></div>

<h1>Tropical Storm Track Interval</h1>
<b>Storm Name:</b> ${storm_name.value}<br/>
<b>Storm #:</b> ${storm_num.value}<br/>
<p>
<table BORDER=1>
<tr><th></th><th>start</th><th>end</th></tr>
<tr><th>Time</th><td>${obs_datetime.value}</td><td>${obs_datetime_end.value}</td></tr>
<tr><th>Wind (knots)</th><td>${wind.value}</td><td>${wind_end.value}</td></tr>
<tr><th>Pressure (mbar)</th><td>${press.value}</td><td>${press_end.value}</td></tr>
</table>
</p>
<div align="center">
<a href="http://geoserver.org"><img src="http://sigma.openplans.org/images/geos-90x33.jpg" width="90" height="33" border="0" alt="GeoServer Logo" /></a></div>

1. Add a Time Template to each feature type

Time Templates are used to create time-related tags in the KML files produced by GeoServer. The following code shows how to create a time template for each of the three FeatureTypes. Note that the time format string works for the "timestamp without time zone" PostgreSQL data type. For further information on setting up time templates, see the Time Templates page.

${obs_datetime.value?datetime('MM/dd/yy hh:mm a')}

${storm_start.value?datetime('MM/dd/yy hh:mm a')}||${storm_end.value?datetime('MM/dd/yy hh:mm a')}

${obs_datetime.value?datetime('MM/dd/yy hh:mm a')}||${obs_datetime_end.value?datetime('MM/dd/yy hh:mm a')}

Google Earth Configuration

The final step is to configure Google Earth to communicate with GeoServer. Save this KML file, and open it up in Google Earth.

The following code block shows a portion of the KML, that loads up storm track data for the 2005 storm season. The key part to watch is the CQL_FILTER parameter in the WMS request, which limits the number of features that GeoServer returns.

<Folder>
		<name>2005 Season</name>
		<visibility>0</visibility>
		<open>1</open>
		<NetworkLink>
			<name>mtriDemo:storm_obs</name>
			<visibility>0</visibility>
			<LookAt id="geoserver_copy4">
				<longitude>179.050003051758</longitude>
				<latitude>39.80291230010882</latitude>
				<altitude>0</altitude>
				<range>6544656.591513608</range>
				<tilt>0</tilt>
				<heading>0</heading>
			</LookAt>
			<Url>
<href>http://localhost:8080/geoserver/wms?service=WMS&amp;request=GetMap&amp;format=application/vnd.google-earth.kmz+XML&amp;width=1024&amp;height=1024&amp;srs=EPSG:4326&amp;layers=mtriDemo:storm_obs&amp;styles=&amp;KMScore=60&amp;KMAttr=true&amp;legend=false&amp;CQL_FILTER=(obs_datetime&gt;&apos;3/1/2005&apos;)AND(obs_datetime&lt;&apos;3/1/2006&apos;)&amp;</href>
				<viewRefreshMode>onRequest</viewRefreshMode>
				<viewRefreshTime>1</viewRefreshTime>
			</Url>
		</NetworkLink>
		<NetworkLink>
			<name>mtriDemo:v_storm_track_interval</name>
			<visibility>0</visibility>
			<LookAt id="geoserver_copy3">
				<longitude>-180</longitude>
				<latitude>45</latitude>
				<altitude>0</altitude>
				<range>9009954.605878988</range>
				<tilt>0</tilt>
				<heading>0</heading>
			</LookAt>
			<Url>
<href>http://localhost:8080/geoserver/wms?service=WMS&amp;request=GetMap&amp;format=application/vnd.google-earth.kmz+XML&amp;width=1024&amp;height=1024&amp;srs=EPSG:4326&amp;layers=mtriDemo:v_storm_track_interval&amp;styles=&amp;KMScore=60&amp;KMAttr=true&amp;legend=true&amp;CQL_FILTER=(obs_datetime&gt;&apos;3/1/2005&apos;)AND(obs_datetime&lt;&apos;3/1/2006&apos;)&amp;</href>
				<viewRefreshMode>onRequest</viewRefreshMode>
				<viewRefreshTime>1</viewRefreshTime>
			</Url>
		</NetworkLink>
		<NetworkLink>
			<name>mtriDemo:v_storm_track</name>
			<visibility>0</visibility>
			<LookAt id="geoserver_copy0">
				<longitude>-179.05</longitude>
				<latitude>39.80291022101666</latitude>
				<altitude>0</altitude>
				<range>6544656.125839039</range>
				<tilt>0</tilt>
				<heading>0</heading>
			</LookAt>
			<Url>
<href>http://localhost:8080/geoserver/wms?service=WMS&amp;request=GetMap&amp;format=application/vnd.google-earth.kmz+XML&amp;width=1024&amp;height=1024&amp;srs=EPSG:4326&amp;layers=mtriDemo:v_storm_track&amp;styles=line&amp;KMScore=50&amp;KMAttr=true&amp;legend=false&amp;CQL_FILTER=(storm_start&gt;&apos;3/1/2005&apos;)AND(storm_start&lt;&apos;3/1/2006&apos;)&amp;</href>
				<viewRefreshMode>onRequest</viewRefreshMode>
				<viewRefreshTime>1</viewRefreshTime>
			</Url>
		</NetworkLink>
	</Folder>

Results

Now that all the components are configured, you now should be able to explore the storm track data in space and time. Static screenshots don't due justice to how you can use GoogleEarth to navigate the dataset in space and time...

Example screenshot, showing observations of Hurricane Katrina: 

Example screenshot, showing all the storm tracks from the 2005 Season

Screenshot showing all the storm tracks from 1990 through 2006

Video of storms in the Florida region, between 1990 and 2006.  OGG format (0.6 MB)  AVI format (4.3 MB)

Things still to do...

• Use an external graphic (hurricane icon) for the observations
• Optimize the PostGIS views to improve response time
• Add data for the Pacific and Indian oceans