#loading states data using psql
//<start id="code_download_data_wget" />  
cd /gisdata
wget http://www2.census.gov/geo/tiger/TIGER2012/ROADS/*_72*     //<co id="co_code_download_data_wget_1" />
--no-parent --relative 
 --recursive --level=2 --accept=zip --mirror   //<co id="co_code_download_data_wget_2" /> 
//<end id="code_download_data_wget" />

#loading US states
//<start id="code_load_us_states" />
shp2pgsql -s 4269 -g geom_4269 -I -W "latin1" 
 "/path/to/tl_2012_us_state" staging.tl_2012_states 
 | psql -h localhost -p 5432 -d postgis_in_action -U postgres
// <end id="code_load_us_states" />

//<start id="code_generate_us_states_sql" />
shp2pgsql -s 4269 -g geom_4269 -I -W "latin1" "C:\GISData\tl_2012_states" 
  staging.tl_2012_states > C:\GISData\tl_2012_states.sql
//<end id="code_generate_us_states_sql" />

ogr2ogr --formats
//<start id="list_ogr2ogr_formats" />
"ESRI Shapefile" (read/write), "MapInfo File" (read/write),
"UK .NTF" (read-only), "SDTS" (read-only)
, "TIGER" (read/write), 
"S57" (read/write),"DGN" (read/write)
, "VRT" (read-only),"REC" (read-only)
,"Memory" (read/write)
, "BNA" (read/write),"CSV" (read/write)
 ,"NAS" (read-only), "GML" (read/write)
 , "GPX" (read/write),
, "OSM" (readonly)
"KML" (read/write), "GeoJSON" (read/write),
"Interlis 1" (read/write),"Interlis 2" (read/write),
"GMT" (read/write),"SQLite" (read/write)
,"ODBC" (read/write),
"PGeo" (readonly),"OGDI" (readonly)
, "MSSQLSpatial" (read/write)
, "PostgreSQL" (read/write),
"MySQL" (read/write),"XPlane" (readonly),
"AVCBin" (readonly),"AVCE00" (readonly),
"Geoconcept" (read/write),"GeoRSS" (read/write)
,"PDF" (read/write)
//<end id="list_ogr2ogr_formats" />

//<start id="code_unix_set_pg_variables" />
export PGCLIENTENCODING=latin1
export PG_USE_COPY=yes
//<end id="code_unix_set_pg_variables" />

//<start id="code_windows_set_pg_variables" />
set PGCLIENTENCODING=latin1
set PG_USE_COPY=yes
//<end id="code_windows_set_pg_variables" />


//<start id="ogrinfo_gpx" />
ogrinfo 468761.gpx //<co id="co_ogrinfo_gpx_1" />

Had to open data source read-only. //<co id="co_ogrinfo_gpx_2" />
INFO: Open of `468761.gpx'
      using driver `GPX' successful.
1: waypoints (Point)
2: routes (Line String)
3: tracks (Multi Line String)
4: route_points (Point)
5: track_points (Point)
//<end id="ogrinfo_gpx" />
#01 - command
#02 - results

//<start id="ogr2ogr_gpx_load" />
ogr2ogr -f "PostgreSQL"      //<co id="co_ogr2ogr_gpx_load_1" />
[CA]PG:"host=localhost user=postgres port=5432 
[CA]dbname=postgis_in_action password=mypassword" 468761.gpx -overwrite 
 [CA]-lco GEOMETRY_NAME=the_geom -nln "staging.aus_biketrip_narangba"

ogr2ogr -f "PostgreSQL"     //<co id="co_ogr2ogr_gpx_load_2" />
[CA] PG:"host=localhost user=postgres port=5432 
[CA] dbname=postgis_in_action password=mypassword"  
[CA] 468761.gpx -overwrite -lco GEOMETRY_NAME=the_geom 
[CA] -lco SCHEMA=staging tracks track_points
//<end id="ogr2ogr_gpx_load" />
#1 Single table load
#2 Multi table load

//<start id="code_ogrinfo_personal_geo" />
ogrinfo gadm_v0dot9.mdb -so -geom=YES gadm	    //<co id="co_ogrinfo_personal_geo_1" />

INFO: Open of 'gadm_v0dot9.mdb'                //<co id="co_ogrinfo_personal_geo_2" />
      using driver 'PGeo' successful.

Layer name: gadm
Geometry: Unknown (any)
Feature Count: 116996
Extent: (-180.000015, -90.000000) - (179.999999, 83.627419)
Layer SRS WKT:
GEOGCS["GCS_WGS_1984",
    DATUM["WGS_1984",
        SPHEROID["WGS_1984",6378137.0,298.257223563]],
    PRIMEM["Greenwich",0.0],
    UNIT["Degree",0.0174532925199433]]
OBJECTID: Integer (10.0)
ISO: String (255.0)
NAME_0: String (255.0)
NAME_1: String (255.0)
VARNAME_1: String (255.0)
NL_NAME_1: String (255.0)
:
:
ENGTYPE_5: String (255.0)
VALIDFR_5: String (255.0)
VALIDTO_5: String (255.0)
Shape_Length: Real (0.0)
Shape_Area: Real (0.0)
//<end id="code_ogrinfo_personal_geo" />
#1 command
#2 results

//<start id="code_ogr2ogr_personal_geo" />
ogr2ogr -f "PostgreSQL" PG:"host=localhost user=postgres port=5432 
[CA]dbname=postgis_in_action password=mypassword" gadm_v0dot9.mdb 
[CA]-lco GEOMETRY_NAME=the_geom -where "ISO='USA'" 
[CA]-t_srs "EPSG:2163" -nln "us.admin_boundaries"  gadm
//<end id="code_ogr2ogr_personal_geo" />

//<start id="code_ogr2ogr_load_mapinfo" />
ogr2ogr -f "PostgreSQL" PG:"host=localhost user=postgres port=5432 
dbname=postgis_in_action password=mypassword"  "/gisdata/canada" //<co id="co_code_ogr2ogr_load_mapinfo_1" /> 
-lco GEOMETRY_NAME=geom -lco SCHEMA=canada      //<co id="co_code_ogr2ogr_load_mapinfo_2" /> 
[CA] -a_srs "EPSG:4269"   //<co id="co_code_ogr2ogr_load_mapinfo_3" />
//<end id="code_ogr2ogr_load_mapinfo" />

//<start id="code_osm2pgsql_load_arctriumphe" />
osm2pgsql arctriump.osm -d postgis_in_action //<co id="co_code_load_arctriumphe_1" /> 
 [CA} -H localhost //<co id="co_code_load_arctriumphe_2" /> 
 [CA} -U postgres //<co id="co_code_load_arctriumphe_3" />  
 [CA} -P 5432 //<co id="co_code_load_arctriumphe_4" />  
 [CA} -S default.style --hstore //<co id="co_code_load_arctriumphe_5" /> 
//<end id="code_osm2pgsql_load_arctriumphe" />

//<start id="code_raster2pgsql_supported">
raster2pgsql -G           //<co id="co_code_raster2pgsql_supported_1" />

Supported GDAL raster formats: //<co id="co_code_raster2pgsql_supported_2" />
  Virtual Raster
  GeoTIFF
  National Imagery Transmission Format
  :
  Erdas Imagine Images (.img)
  :
  Arc/Info Binary Grid
  Arc/Info ASCII Grid
  GRASS ASCII Grid
  SDTS Raster
  DTED Elevation Raster
  Portable Network Graphics
  JPEG JFIF
  :
  Graphics Interchange Format (.gif)
  :
  SRTMHGT File Format
  :
  GRIdded Binary (.grb)
  :
  R Object Data Store
  :
  ASCII Gridded XYZ
  ://<end id="code_raster2pgsql_supported" />
  
raster2pgsql -s 4326 -C N48E086.hgt  staging.n48e086 | psql -h localhost -U postgres -p 5432  -d postgis_in_action  
//<start id="code_load_single_raster_file" />
raster2pgsql -s 4326 -C usgs_srtm\N48E086.hgt staging.n48e086 
 | psql -h localhost -U postgres -p 5432 -d postgis_in_action
//<end id="code_load_single_raster_file" />

//<start id="code_load_multiple_raster_files" />
export PGPORT=5432    //<co id="co_code_load_multiple_raster_files_1" />
export PGHOST=localhost
export PGUSER=postgres
export PGPASSWORD=mypassword
export PGDATABASE=postgis_in_action
raster2pgsql -s 4326 -C -F //<co id="co_code_load_multiple_raster_files_2" /> 
 [CA] -t 100x100 //<co id="co_code_load_multiple_raster_files_3" />
 [CA] usgs_srtm/*.hgt //<co id="co_code_load_multiple_raster_files_4" />  
 [CA] staging.usgs_srtm //<co id="co_code_load_multiple_raster_files_5" />
 [CA] | psql //<co id="co_code_load_multiple_raster_files_6" />
//<end id="code_load_multiple_raster_files" />
#1 environment variables for psql
#2 set srid, constraints, file name
#3 tile size
#4 multiple files
#5 table to load in
#6 load into db

//<start id="code_pgsql2shp_export_table_nopwd" />
pgsql2shp -f /gisdata/cazips gisdb ca.zips
//<end id="code_pgsql2shp_export_table_nopwd" />

//<start id="code_pgsql2shp_export_table_pwd" />
pgsql2shp -f /gisdata/cazips -h localhost -u pguser
[CA] -P somepassword -p 5432 gisdb ca.zips
//<end id="code_pgsql2shp_export_table_pwd" />

//<start id="code_pgsql2shp_export_adhoc" />
pgsql2shp -f boszips -h localhost -u postgres gisdb 
[CA] "SELECT * FROM ma.zips WHERE city = 'Boston'"
//<end id="code_pgsql2shp_export_adhoc" />

//<start id="code_pgsql2shp_export_adhoc_complex" />
pgsql2shp -f boszips -h localhost -u postgres gisdb 
[CA] "SELECT zip5, ST_Transform(the_geom, 4326) As the_geom 
[CA] FROM ma.zips WHERE city = 'Boston'"
//<end id="code_pgsql2shp_export_adhoc_complex" />

//<start id="code_ogr2ogr_export_kml" />
ogr2ogr -f "KML" /gisdata/us_adminbd.kml  <co id="co_code_ogr2ogr_export_kml_1" />
[CA]PG:"host=localhost user=postgres port=5432 dbname=postgis_in_action 
[CA]password=mypassword" us.admin_boundaries -dsco NameField=name_2

ogr2ogr -f "KML"                //<co id="co_code_ogr2ogr_export_kml_2" />
[CA]/gisdata/biketrip.kml PG:"host=localhost user=postgres port=5432 
[CA]dbname=postgis_in_action password=mypassword" -dsco NameField=time 
[CA]-select "SELECT track_seg_point_id, ele, time" -where "time BETWEEN 
[CA]'2009-07-18 04:33-04' AND '2009-07-18 04:34-04'" 
[CA]staging.aus_biketrip_narangba

ogr2ogr -f "KML"               //<co id="co_code_ogr2ogr_export_kml_3" />
[CA]/gisdata/biketrail.kml PG:"host=localhost user=postgres port=5432 
[CA]dbname=postgis_in_action password=mypassword" -dsco NameField=time 
[CA]staging.track_points staging.tracks
//<end id="code_ogr2ogr_export_kml" />
#1 Simple export
#2 Export of filtered set 
#3 Export as multiple tables


//<start id="code_ogr2ogr_export_mapinfotab" />
ogr2ogr -f "MapInfo file"         //<co id="co_code_ogr2ogr_export_mapinfotab_1" />
[CA] /gisdata/us_boundaries.tab 
[CA] PG:"host=localhost user=postgres 
[CA] port=5432 dbname=postgis_in_action password=mypassword" 
[CA] -t_srs "EPSG:4326" us.admin_boundaries

ogr2ogr -f "MapInfo file"         //<co id="co_code_ogr2ogr_export_mapinfotab_2" />
[CA] /gisdata/biketrip.tab 
[CA] PG:"host=localhost user=postgres port=5432 
[CA] dbname=postgis_in_action password=mypassword" 
[CA] -select "SELECT track_seg_point_id, ele, time" 
[CA] -where "time BETWEEN '2009-07-18 04:33-04' 
  AND '2009-07-18 04:34-04'" staging.aus_biketrip_narangba

ogr2ogr -f "MapInfo file"       //<co id="co_code_ogr2ogr_export_mapinfotab_3" />
[CA] /gisdata/tab_files 
[CA] PG:"host=localhost user=postgres port=5432 
[CA] dbname=postgis_in_action password=mypassword" 
[CA] staging.track_points staging.tracks

//<end id="code_ogr2ogr_export_mapinfotab" />
#1 Export with transform
#2 Export with filter
#3 Export multifile

//<start id="code_gdalinfo" />
gdalinfo  "PG:host=localhost port=5432 dbname='postgis_in_action'   //<co id="co_code_gdalinfo_1" />
[CA] user='postgres' password='whatever' schema=staging table=n48e086"

Driver: PostGISRaster/PostGIS Raster driver //<co id="co_code_gdalinfo_2" />
Files: none associated
Size is 1201, 1201
Coordinate System is:
GEOGCS["WGS 84",
    DATUM["WGS_1984",
        SPHEROID["WGS 84",6378137,298.257223563,
            AUTHORITY["EPSG","7030"]],
        AUTHORITY["EPSG","6326"]],
    PRIMEM["Greenwich",0,
        AUTHORITY["EPSG","8901"]],
    UNIT["degree",0.0174532925199433,
        AUTHORITY["EPSG","9122"]],
    AUTHORITY["EPSG","4326"]]
Origin = (85.999583333333305,49.000416666666702)
Pixel Size = (0.000833333333333,-0.000833333333333)
Corner Coordinates:
Upper Left  (  85.9995833,  49.0004167) ( 85d59'58.50"E, 49d 0' 1.50"N)
Lower Left  (  85.9995833,  47.9995833) ( 85d59'58.50"E, 47d59'58.50"N)
Upper Right (  87.0004167,  49.0004167) ( 87d 0' 1.50"E, 49d 0' 1.50"N)
Lower Right (  87.0004167,  47.9995833) ( 87d 0' 1.50"E, 47d59'58.50"N)
Center      (  86.5000000,  48.5000000) ( 86d30' 0.00"E, 48d30' 0.00"N)
Band 1 Block=1201x1201 Type=Int16, ColorInterp=Gray
  NoData Value=-32768
//<end id="code_gdalinfo" />

//<start id="code_export_gdal_translate" />
gdal_translate -of GTiff -outsize 10% 10%   //<co id="co_code_export_gdal_translate_1" />
[CA] "PG:host=t port=5432 dbname='postgis_in_action' 
[CA] user='postgres' password='whatever' 
[CA] schema=staging table=usgs_srtm mode=2" elev_small.tif 

gdal_translate -of JPEG "PG:host=localhost //<co id="co_code_export_gdal_translate_2" />
[CA] dbname='postgis_in_action' port='5432' user='postgres' 
[CA] password='whatever' schema='staging' 
[CA] table='pele_chunked' column='rast' mode=2" -b 1 pele_grey.png

gdal_translate -of GTiff      //<co id="co_code_export_gdal_translate_3" />
[CA] "PG:host='localhost' port='5432' dbname='postgis_in_action'
[CA] user='postgres' password='whatever' schema='staging' 
[CA] table='usgs_srtm' 
[CA]where='rid BETWEEN 1 and 200' mode=2" subset.tif

gdal_translate -of GTiff    //<co id="co_code_export_gdal_translate_4" />
[CA] PG:"host='localhost' port='5432' 
[CA] dbname='postgis_in_action' user='postgres' 
[CA] password='whatever' schema='staging' table='usgs_srtm'
[CA] where='ST_Intersects(rast
[CA]  ,ST_MakeEnvelope(2.28568,48.8676,2.30371,48.87957,4326))' 
[CA] mode=2" arctriomphe.tif

gdal_translate -of USGSDEM   //<co id="co_code_export_gdal_translate_5" />
[CA] "PG:host=localhost port=5432 dbname='postgis_in_action' 
[CA] user='postgres' password='whatever' schema=staging table=usgs_srtm 
[CA] mode=2 where='filename=\'N48E086.hgt\'" N48E086.dem
//<end id="code_export_gdal_translate" />
#1 Export at 10% of original size
#2 Export specific raster column and band
#3 Export select rows
#4 Export geographic region
#5 reconstitute chunked file

//<start id="code_export_gdalwarp" />
gdalwarp -s_srs "EPSG:4326" //<co id="co_code_export_gdalwarp_1" /> 
[CA] -t_srs "EPSG:2163" //<co id="co_code_export_gdalwarp_2" />
[CA] PG:"host='localhost' port='5432' dbname='postgis_in_action' <co id="co_code_export_gdalwarp_3" />
[CA] user='postgres' password='whatever' 
[CA] schema='staging' table='usdem' //<co id="co_code_export_gdalwarp_4" />
[CA] where='ST_Intersects(rast, //<co id="co_code_export_gdalwarp_5" />
[CA] ST_MakeEnvelope(-115.60,32.54, -112.96, 26.03,4326))' 
[CA] mode='2'"  //<co id="co_code_export_gdalwarp_6" /> 
[CA] usdem_sub.tif //<co id="co_code_export_gdalwarp_7" />
//<end id="code_export_gdalwarp" />
#1 source spatial reference system
#2 target spatial reference system
#3 postgresql connection credentials
#4 table to query
#5 filter condition
#6 multiple rows
#7 output file


