By Rupert
GIS
Postgres PostGIS CheatSheet v2
Aug 29th
This is a quick-command list of Postres. If you want detailed instructions, please visit the Postgres Manual.
How do I Show all databases?
1. Using “psql -l”
2. Using
postgres=# \l List of databases Name | Owner | Encoding ------------------+----------+---------- postgis | postgres | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 template_postgis | postgres | UTF8 (5 rows)
Note: Do not drop template databases if not necessary.
How do I run a script from the prompt?
psql -d cybersoftbj -u user -f myfile.sql
Its very useful in reloading user-defined functions.
How do I create a user/role?
CREATE ROLE lbs WITH LOGIN PASSWORD 'mypassword' SUPERUSER INHERIT CREATEDB CREATEROLE;
How do I change the password for a user/role?
ALTER ROLE lbs PASSWORD 'mynewpassword';
How to provide/restrict access privileges to tables?
GRANT SELECT ON TABLE table TO user;
REVOKE SELECT ON TABLE table FROM user;
How to dump database in a text file?
pg_dump -U lbs -d cybersoftbjv1 -h 127.0.0.1 -W > cybersoftbjv1.sql
How to dump database cleanly?
pg_dump -c -d -E UTF8 -h 127.0.0.1 -U lbs -W platform_v1 > platform_v1.sql
How to rename a database?
ALTER DATABASE beijing_app RENAME TO beijing_app_20080801;
How to update using two tables?
UPDATE road_for_update u
SET the_geom = r.the_geom
FROM roads r
WHERE r.rd_id = u.rd_id;
How to change a column type with Cast?
ALTER TABLE roads ALTER COLUMN class_new TYPE integer USING class_new::integer;
How to add a geometry column to a table?
EXAMPLE: SELECT AddGeometryColumn(‘public’, ‘poi’, ‘the_geom’, 4326, ‘POINT’, 2)
Changing column names with spaces?
ALTER TABLE class_aroundme RENAME "level 1" TO level_1;
Setting kernel shmmax for postgres
sysctl -w kernel.shmmax=134217728
Note: For permanent changes see /etc/sysctl.cfg11. How to backup table(s) from pg_dump?
pg_dump poi_beijing -t class -t poi_class -f $BACKUPDIR/test_$MYDATE.sql
Using GDALWARP to reproject raster that will fit with Google Satellite
Jul 29th
Just a couple of notes to onself using gdal: Use gdalwarp to reproject your GeoTIFF files! I wanted to use my own satellite images acquired from GeoEye, however, on some areas I wanted to use google sat images as well since I don’t have the coverage. In order to do so, I need to reproject the sat images to 900913. Note you need to specify this in your epsg file in my previous post.
rupert:beijing_900913_satellite rupert$ gdalinfo Mosaic_RGB.tif Driver: GTiff/GeoTIFF Files: Mosaic_RGB.tif Size is 4248, 4553 Coordinate System is: GEOGCS["WGS 84", DATUM["WGS_1984", SPHEROID["WGS 84",6378137,298.2572235630016, AUTHORITY["EPSG","7030"]], AUTHORITY["EPSG","6326"]], PRIMEM["Greenwich",0], UNIT["degree",0.0174532925199433], AUTHORITY["EPSG","4326"]] Origin = (116.291476140000000,40.025198500000002) Pixel Size = (0.000046860000000,-0.000035970000000) Metadata: AREA_OR_POINT=Area TIFFTAG_XRESOLUTION=100 TIFFTAG_YRESOLUTION=100 Image Structure Metadata: INTERLEAVE=BAND Corner Coordinates: Upper Left ( 116.2914761, 40.0251985) (116d17'29.31"E, 40d 1'30.71"N) Lower Left ( 116.2914761, 39.8614271) (116d17'29.31"E, 39d51'41.14"N) Upper Right ( 116.4905374, 40.0251985) (116d29'25.93"E, 40d 1'30.71"N) Lower Right ( 116.4905374, 39.8614271) (116d29'25.93"E, 39d51'41.14"N) Center ( 116.3910068, 39.9433128) (116d23'27.62"E, 39d56'35.93"N) Band 1 Block=4248x1 Type=Byte, ColorInterp=Red Band 2 Block=4248x1 Type=Byte, ColorInterp=Green Band 3 Block=4248x1 Type=Byte, ColorInterp=Blue
rupert:beijing_900913_satellite rupert$ gdalwarp -s_srs epsg:4326 -t_srs epsg:900913 Mosaic_RGB.tif sat_4m_rgb.tif Creating output file that is 4245P x 4556L. Processing input file Mosaic_RGB.tif. 0...10...20...30...40...50...60...70...80...90...100 - done.
rupert:beijing_900913_satellite rupert$ gdalinfo sat_4m_rgb.tif Driver: GTiff/GeoTIFF Files: sat_4m_rgb.tif Size is 4245, 4556 Coordinate System is: PROJCS["Google Maps Global Mercator", GEOGCS["WGS 84", DATUM["WGS_1984", SPHEROID["WGS 84",6378137,298.2572235630016, AUTHORITY["EPSG","7030"]], AUTHORITY["EPSG","6326"]], PRIMEM["Greenwich",0], UNIT["degree",0.0174532925199433], AUTHORITY["EPSG","4326"]], PROJECTION["Mercator_1SP"], PARAMETER["central_meridian",0], PARAMETER["scale_factor",1], PARAMETER["false_easting",0], PARAMETER["false_northing",0], UNIT["metre",1, AUTHORITY["EPSG","9001"]]] Origin = (12945507.907502911984921,4869604.732793668285012) Pixel Size = (5.219801430503303,-5.219801430503303) Metadata: AREA_OR_POINT=Area Image Structure Metadata: INTERLEAVE=PIXEL Corner Coordinates: Upper Left (12945507.908, 4869604.733) (116d17'29.31"E, 40d12'53.10"N) Lower Left (12945507.908, 4845823.317) (116d17'29.31"E, 40d 3'2.78"N) Upper Right (12967665.965, 4869604.733) (116d29'25.89"E, 40d12'53.10"N) Lower Right (12967665.965, 4845823.317) (116d29'25.89"E, 40d 3'2.78"N) Center (12956586.936, 4857714.025) (116d23'27.60"E, 40d 7'58.12"N) Band 1 Block=4245x1 Type=Byte, ColorInterp=Red Band 2 Block=4245x1 Type=Byte, ColorInterp=Green Band 3 Block=4245x1 Type=Byte, ColorInterp=Blue
Installing Mapserver on MacOSX (by source)
Jul 26th
Just noticed that William of kyngchaos has updated the mapserver binary for MacOSX.
But right now, I need to tile these images bought from GeoEye, so I need TIFF support. Below is a summary of getting Mapserver installed by source. Note that I have the necessary GEOS, GDAL from kyngchaos as well from this ealier post.
1. Download the ff files:
-rw-r--r--@ 1 rupert admin 564313 Jul 26 10:32 agg-2.5.tar.gz
-rw-r--r--@ 1 rupert admin 1345700 Jul 26 10:22 gd-2.0.35.tar.gz
-rw-r--r--@ 1 rupert admin 613261 Jul 26 10:22 jpegsrc.v6b.tar.gz
-rw-r--r--@ 1 rupert admin 796551 Jul 26 10:22 libpng-1.2.29.tar.gz
-rw-r--r--@ 1 rupert admin 1948751 Jul 26 09:55 mapserver-5.2.0.tar.gz
-rw-r--r--@ 1 rupert admin 1336295 Jul 26 11:11 tiff-3.8.2.tar.gz
2. Install in the ff order:
- jpegsrc
- libpng
- gd (if you have trouble installing gd, then follow this pdf:installing_gd2_on_os_x_server)
- agg (make only)
- tiff
- mapserver
3. For mapserver, please install using the ff configure switches:
./configure \ --with-agg=/myhome/rupert/mapserver/agg-2.5 \ --with-jpeg \ --with-gd \ --with-freetype \ --with-png \ --with-ogr \ --with-proj \ --with-gd \ --with-httpd=/usr/local/apache2/bin/httpd \ --with-tiff \ --with-wfs \ --with-wcs \ --with-sos \ --with-wmsclient \ --with-wfsclient \ --with-tiff \ --with-gdal=/usr/local/bin/gdal/gdal-config \ --with-geos=/usr/local/bin/geos-config \ --with-postgis=/usr/local/pgsql/bin/pg_config
4. Mapserver output
rupert:mapserver-5.2.0 rupert$ ./mapserv -v MapServer version 5.2.0 OUTPUT=GIF OUTPUT=PNG OUTPUT=JPEG OUTPUT=WBMP OUTPUT=SVG SUPPORTS=PROJ SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=ICONV SUPPORTS=WMS_SERVER SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER SUPPORTS=GEOS INPUT=TIFF INPUT=EPPL7 INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE
Postgres PostGis PostInstall
Jun 7th
1. Edit pg_hba.conf
# "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 md5 host all all 192.168.1.0 255.255.255.0 md5 # IPv6 local connections: #host all all ::1/128 trust
2. Edit postgres.conf
55 56 listen_addresses = '*' # what IP address(es) to listen on; 57 # comma-separated list of addresses; 58 # defaults to 'localhost', '*' = all 59 # (change requires restart)
4. For CentOS5.1, create symbolic links:
ln -s /usr/local/lib/libproj.so.0 /usr/lib/libproj.so.0
ln -s /usr/local/lib/libgeos_c.so.1 /usr/lib/libgeos_c.so.1
ldconfig
/etc/init.d/postgresql stop
/etc/init.d/postgresql start
3. Postgis Post Install
createdb -E utf8 template_routing createlang plpgsql template_routing psql -d template_routing -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql psql -d template_routing -f /usr/share/postgresql-8.3-postgis/spatial_ref_sys.sql psql -d template_routing -f /usr/share/postlbs/routing_core.sql psql -d template_routing -f /usr/share/postlbs/routing_core_wrappers.sql
Installing Postgresql, Postgis, pgRouting on Debian
May 25th
Operating System: Debian sid
Versions:
- postgres 8.3.1
- postgis 1.3.3
- pgRouting1.02
1. Install base system and ssh
#vi /etc/apt/sources.list to include deb http://debian.cn99.com/debian etch main deb-src http://debian.cn99.com/debian etch main #apt-get update #apt-get upgrade libc6
2. Install the required packages for postgres8.3 and postgis1.3.3
#apt-get install sudo nmap telnet
#apt-get install python2.5 python2.5-dev python-setuptools
#apt-get install g++
#apt-get install build-essential cmake ibboost-graph-dev
#apt-get install libreadline5 libreadline5-dev
#apt-get install zlib-bin zlib1g-dev
#apt-get install libkrb5-dev
#apt-get install libcurl3
#apt-get install libssl-dev
#apt-get install postgresql-8.3
#apt-get install postgresql-8.3-postgis
#apt-get install postgresql-server-dev-8.3
3. Installing pgRouting
# tar -zxvf pgRouting-1.02.tgz # cmake . # make # make install
Comments