All data for this chapter can be loaded using ch10_data.sql
psql -d postgis_in_action -f ch10_data.sql

Downloaded from http://efele.net/maps/tz/world/tz_world.zip 2013-05-29 loaded into ch10.tz_world as geog (4326) single polygons.

Downloaded from MassGIS 2013-05-18
http://www.mass.gov/anf/research-and-tech/it-serv-and-support/application-serv/office-of-geographic-information-massgis/datalayers/ftpl3parcels.html

file: http://wsgw.mass.gov/data/gispub/shape/l3parcels/L3_SHP_M049_CAMBRIDGE.zip
Loaded the M049assess.dbf as staging.assess
M049Taxpar as staging.parcels using srid 26986.
M049UC_LUT.db as staging.lut 

Cambridge roads from: http://www.mass.gov/anf/research-and-tech/it-serv-and-support/application-serv/office-of-geographic-information-massgis/datalayers/ftpeotroads.html
file: http://wsgw.mass.gov/data/gispub/shape/eotroads/eotroads_49.zip
loaded into staging.eotroads srid:26986

-- for simplicity we created a denormalized joining two tables to form one

DROP TABLE IF EXISTS ch10.road;
SELECT gid, street_nam As road_name, geom
INTO ch10.road
FROM staging.eotroads
WHERE street_nam > '';

DROP TABLE IF EXISTS ch10.land ;
SELECT a.prop_id AS pid, a.bldg_val, a.land_val, a.other_val, a.total_val, a.addr_num, a.full_str
	, CASE WHEN  lut1.use_desc ILIKE '%apartment%' THEN 'apartment' WHEN  lut1.use_desc ILIKE '%office%' THEN 'office'
		WHEN  lut1.use_desc ILIKE '%bank%' THEN 'bank' 
		WHEN  lut1.use_desc ILIKE '%auto%' THEN 'auto'
		WHEN  lut1.use_desc ILIKE '%shopping%' THEN 'shopping'
		WHEN  lut1.use_desc ILIKE '%mixed%' THEN 'mixed use'
		WHEN  lut1.use_desc ILIKE '%church%' THEN 'church'
		WHEN  lut1.use_desc ILIKE '%residential%' THEN 'residential'
		WHEN  lut1.use_desc ILIKE '%vacant%' THEN 'vacant'
		WHEN  lut1.use_desc ILIKE '%commercial%' THEN 'commercial'
		WHEN  lut1.use_desc ILIKE '%education%' THEN 'education'
		ELSE 'other' END::varchar(100) As land_type, a.units, p.geom
INTO ch10.land
FROM staging.assess AS a INNER JOIN staging.parcels As p ON a.loc_id = p.loc_id
	INNER JOIN staging.lut AS lut1 ON lut1.use_code = a.use_code;
	
ALTER TABLE ch10.land ADD CONSTRAINT pk_land PRIMARY KEY (pid);
ALTER TABLE ch10.road ADD CONSTRAINT pk_road PRIMARY KEY (gid);
CREATE INDEX idx_road_geom_gist ON ch10.road USING gist (geom);
CREATE INDEX idx_land_geom_gist ON ch10.land USING gist (geom);
CREATE INDEX idx_land_land_type ON ch10.land USING btree(land_type);


-- airports data --
http://www.ourairports.com/data/

CREATE TABLE ch10.airports(id integer, ident varchar(20)  primary key, type varchar(100), name varchar(200)
 , lat double precision, lon double precision, elevation_ft integer, continent varchar(2), iso_country varchar(3), iso_region varchar(20), municipality varchar(150)
 ,  scheduled_service varchar(3), gps_code varchar(10), iata_code varchar(10), local_code varchar(10), home_link varchar(200), wikipedia varchar(300), keywords varchar(300));
 
In psql:
\encoding UTF8
\copy ch10.airports FROM airports.csv CSV HEADER DELIMITER AS ',' QUOTE AS '"' ;
ALTER TABLE ch10.airports ADD COLUMN geog geography(POINT,4326);
UPDATE ch10.airports SET geog = ST_Point(lon,lat)::geography;

CREATE INDEX idx_airports_geog_gist ON ch10.airports USING gist (geog);
CREATE INDEX idx_airports_geom_gist_cast ON ch10.airports USING gist (geometry(geog));
ALTER TABLE ch10.airports ALTER COLUMN scheduled_service TYPE boolean USING CAST(scheduled_service AS boolean);

DROP TABLE IF EXISTS ch10.navaids;
CREATE TABLE ch10.navaids(id integer primary key, filename varchar(150), ident varchar(20),  name varchar(200), type varchar(100)
 , freq_khz integer
 , lat double precision, lon double precision, elevation_ft integer, iso_country varchar(3), dme_freq_khz integer, dme_channel varchar(20)
 , dme_lat double precision, dme_lon double precision, dme_elevation_ft integer
 , slaved_variation_deg numeric(12,5), magnetic_var_deg numeric(12,5)
 , usage_type varchar(20), power varchar(20), associated_airport varchar(20) );

In psql:
\encoding UTF8
\copy ch10.navaids FROM navaids.csv CSV HEADER DELIMITER AS ',' QUOTE AS '"' ;
ALTER TABLE ch10.navaids ADD COLUMN geog geography(POINT,4326);
UPDATE ch10.navaids SET geog = ST_Point(lon,lat)::geography;
CREATE INDEX idx_navaids_geog_gist ON ch10.navaids USING gist (geog);
