Getting started with Raster and PostGIS - I

Steps for loading a raster in PostGIS (PgSql 9.4/ PostGIS 2.2) and pull it from database to view in QGIS 2.14.17.

1. Generate SQL for raster [NOTE: Do not create PostGIS table name starts with underscore, it creates problem while adding raster to QGIS from PostGIS database using DB Manager]

        raster2pgsql -s 4326 -I -C -M -F -t 50x50 -N nan biodiv_ssolnw.tif > biodiv_ssolnw.sql

2. Import generated raster into PostGIS database [Make sure to enable postgis extension in db]

         psql -h localhost -U postgres -d ecolservicedb -f biodiv_ssolnw.sql

the output is:
Processing 1/1: ags_473038164.tif
BEGIN
CREATE TABLE

INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
…
…
….
CREATE INDEX
ANALYZE
NOTICE:  Adding SRID constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding scale-X constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding scale-Y constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding blocksize-X constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding blocksize-Y constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding alignment constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding number of bands constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding pixel type constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding nodata value constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding out-of-database constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
NOTICE:  Adding maximum extent constraint
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
 addrasterconstraints
----------------------

Read More

Register Database to ArcGIS server

There are two methods to register Database to ArcGIS Server
A) Register via ArcGIS Desktop

• Double click to ensure ArcGIS Server Instance is connected to ArcGIS Desktop
• Right Click to Publisher connection to ArcGIS Server
• Then Click ArcGIS Server Properties to open ArcGIS Server Properies
• Add Data Store in Registered Databases

ESRI Video on Registering database data with ArcGIS 10.1 for Server

B) Register via ArcGIS Server Manager Web Interface

SOURCE: http://gisdeveloper.blogspot.com/2014_04_01_archive.html ( Copied here for my future reference)
REGISTER DATABASE TO ARCGIS SERVER

While publishing data (from database) to ArcGIS server it gives you a warning that

24011: Data source is not registered with the server and data will be copied to the server

How to resolve this ??

Well there are different approaches, here is the one which i followed. But before doing anything Please Note:
Note: The client side libraries need to be copied to ArcGIS server's bin folder. What does this mean ??
Well you have to copy the libraries (obtained from esri's customer portal Or simply google it "PostgreSQLClientLibs922" Or PostgreSQL libraries for ArcGIS server)
Copy the correct version of libraries to
C:\Program Files\ArcGIS\Server\bin   

Note to see the correct version of libraries click here.

Now,

• Open ArcGIS server Manager from (prefer Internet Explorer)http://localhost:6080/arcgis/manager/site.html
• Sign in to your ArcGIS server account
• On home Page under Directories tab click on Data Store
• Click on Registered Database

Read More

Postgresql tips

Start to postgresql :  sudo -u postgres psql postgres

List all databases : 

• \list or \l: list all databases
• \dt: list all tables in the current database

Type \q and then press ENTER to quit psql
Connect to database : \connect database_name
Drop all tables from a database:  drop schema public cascade;

Read More

Export/Imoprt PostgreSQL database with PostGIS extension

#Run from windows command prompt
#Create dump into dump.sql

C:\Program Files\PostgreSQL\9.4\bin>pg_dump.exe -h localhost -p 5432 -U username dbnameToExport > D:\dump.sql

#Import dump

C:\Program Files\PostgreSQL\9.4\bin>psql.exe -h localhost -p 5432 -U postgres dbnameToImport < D:/dump.sql

Password for user postgres:

Only super user can create PostGIS extension in PostgreSQL.

Reproject Geometry from WGS84 to Web Mercator Aux

ALTER TABLE table_name
            ALTER COLUMN column_name TYPE geometry(MultiPolygon, 3857) USING                        ST_Transform(ST_SetSRID(column_name,4326),3857) ;

 Linux, import Postgresql dump with Postgresql database:
psql -h hostname -d databasename -U username -f dumpfile.sql

Read More

Automated Tools for Integrating Remote Sensing Data Into Spatial Epidemiology Research

Satellite remote sensing provides valuable information that can be used to map infectious diseases and forecast future health risks. However, amassing and managing the geographic information from diverse datasets is difficult and time-consuming. Therefore, there is a need for a geoinformatics system that integrates the acquisition, processing, management, and analysis of geospatial data sets from various sources.

Here, we present our software model for automated data capture and processing of satellite remote sensing data for public health applications.  The system incorporates land surface temperature and vegetation indices from MODIS precipitation data from TRMM, and a novel measurement of actual evapotranspiration.

Read More

Popular GIS Books

Books	Pro	Cons
	It provides solid guide to how geospatial analysis work, particularly with respect to GIS. The book emphasizes conceptual workflows and with basic math which is helpful for creating own code and also getting an understanding of what's happening under the hood in contemporary GIS.	It is better to have an update because lots of changes in GIS software over last five years.
	This book is for typical GIS user aspiring to design good maps. It is illustrating GIS map software and throughout with map samples in color which is especially useful for those who has little prior training or experience in map making.	This is acceptable book for beginners but very little information of advanced users. It hardly touches on advanced cartographic representations.
	This book explains the computational geometry and algorithms concisely and very readable. It emphasis on describing algorithms and data structures theoretically. It presents pseudo code with lots of figures that is very easy to understand and follow. It's also worth reading for all computer scientists and mathematicians who are working on geometry. This is good text/reference book for graduate course.	Focused on geometric computation and algorithm, very complicated for beginners, who does not have prior computer programming knowledge. The various algorithms and concepts often used in this book are triangulation, indexing, calculating intersection, shortest paths etc.
	The book illustrates the most common cartographic deceptions, and provides some excellent color guides. If you want to learn how to make influential maps for a cause, this is the book!. The reader can learn what to look for and how to avoid the inadvertent or unintentional 'lies'. Worth the effort!	Basically, the book as an introduction to the science of cartography and targeted for prospective cartographer or decision making authority.
	The book details the use of freely available open source mapping software and tools such as MapServer, GDAL, OpenEV, and PostGIS to create web gis and web maps. Mostly focused on UMN Mapserver for web mapping and building web gis.	Not much technical discussion on how GPS databases work, how to decode GIS information. The book is fairly shallow. It will give you a couple of basic examples of how to use some pieces of software, but for anything more complicated, you have to look elsewhere.

Read More

Spatial Developer must keep in your shelf : Book Review

One of my client’s requirements was to create and open source geospatial DBMS to hold utilities and city shape files. As looking through the internet, I found PostGIS is an open source software program that adds support for geographic objects to the PostgreSQL object-relational database.  As project reference, I choose PostGIS in Action, as project accomplished successfully I realized  my judgment was excellent.

PostGIS in Action is a very comprehensive introduction to PostGIS for developers of all levels of experience. Basically it is split into 3 parts:

Part 1- Nice and slow startup with PostGIS. You can skip this section if you have prior knowledge about any kinds of spatial databases and geometry data types.

Part 2- Put PostGIS in work. It contains technical details on proximity analysis, geocoding addresses, manipulating polygons and lines, and scaling and rotating geometries as well as efficient queries and how to tune your database and
selection of appropriate indexes and keys for your data.

Part 3- Use of PostGIS with other tools and frameworks. It describes manage raster datasets in PostGIS with PostGIS raster module, development of Web applications using PostGIS.

The Book will provide the much needed skills in building a spatial database, Application Server, Desktop GIS and Cartographic map renderer, Map tile server and Web map component using the following software: PostGIS/PostgreSQL, GeoServer, Apache/Tomcat, uDIG, Openjump, QGIS, GRASS GIS and Openlayers.

Read More

How to connect spatial database(PostGIS) with QGIS?

In this section, I am showing the 3 basic steps to connect PostGIS database with a widely used open source desktop based gis, QGIS. 

Step1: Install PostGIS with Spatial database support extension

i.                    If PostgreSQL is already installed è Launch ‘Application stack builder’ from startup menu in windows 7.

ii.                  Select the appropriate instance of PGSQL from dropdown list and Click NEXT.

iii.                Expand ‘Categories’è Expand ‘Spatial Extensions’ è Select appropriate PostGIS version(1.5)  for the already installed PGSQL version(8.4).

iv.                    Follow the instructions to install the PostGIS extension.

Read More

An Interactive Visualization using Geoserver & Openlayers

From last couples of week, I have been involving to develop a interactive web interface for West Nile virus incidence dissemination across the conterminous US from 1999-2011.

The image on the right shows one of my accomplishment using
Geoserver,Openlayers,jQuery, PostgreGIS,and Google API. The project is about to launch online, then I will post a complete link of the project to let you guys play around it.

Click to visit: Web application

I am willing to help any one who is stuck with web maps/visualization and also seek constructive criticism on web stuffs.

Read More