## Wednesday, September 13, 2017

### Compute zonal statistics on area of interest polygon on fly

Draft to compute zonal statistics on area of interest polygon or shape draw on leaflet using PostgreSql and Java -

Geometry extracted from leaflet as GeoJson.

Polygon-
{"type":"Feature","properties":{"desc":null,"image":null},"geometry":{"type":"Polygon","coordinates":[[[-117.103271484375,34.264026473152875],[-117.1142578125,34.14818102254435],[-117.03186035156251,34.10498222546687],[-116.91925048828124,34.14363482031264],[-116.94946289062499,34.25494631082515],[-117.0867919921875,34.252676117101515],[-117.103271484375,34.264026473152875]]]}}

{"type":"Feature","properties":{"desc":null,"image":null},"geometry":{"type":"Polygon","coordinates":[[[-116.86981201171875,34.11407854333859],[-116.86981201171875,34.24132422972854],[-116.71874999999999,34.24132422972854],[-116.71874999999999,34.11407854333859],[-116.86981201171875,34.11407854333859]]]}}

Point-
{"type":"Feature","properties":{"desc":null,"image":null},"geometry":{"type":"Point","coordinates":[-116.9879150390625,34.048108084909835]}}

Area of GeoJson
Select (ST_Area(ST_GeomFromText
('POLYGON ((-117.16918945312501 34.27083595165,-117.1307373046875 34.166363384737892,-117.00988769531251 34.161818161230386,-116.93298339843751 34.239053668516412,-116.98516845703126 34.27083595165,-117.16918945312501 34.27083595165))',4326)))

Zonal statistics for each raster tile -

SELECT rid, (ST_SummaryStats (ST_Clip(rast,ST_GeomFromText('POLYGON ((-117.16918945312501 34.27083595165,-117.1307373046875 34.166363384737892,-117.00988769531251 34.161818161230386,-116.93298339843751 34.239053668516412,-116.98516845703126 34.27083595165,-117.16918945312501 34.27083595165))',4326),true))).*
FROM public.biodiv_ssolnw_wgs84
WHERE ST_Intersects
(rast,ST_GeomFromText
('POLYGON ((-117.16918945312501 34.27083595165,-117.1307373046875 34.166363384737892,-117.00988769531251 34.161818161230386,-116.93298339843751 34.239053668516412,-116.98516845703126 34.27083595165,-117.16918945312501 34.27083595165))',4326))
Group By rid

## Thursday, August 17, 2017

### 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
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
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
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
CONTEXT:  PL/pgSQL function addrasterconstraints(name,name,name,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean,boolean) line 53 at RETURN
----------------------

## Monday, December 12, 2016

### 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
• Click on Registered Database

## Monday, December 5, 2016

### 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;

## Thursday, September 22, 2016

### 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

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

## Saturday, September 12, 2015

### FIXED: Shape file import error in Postgis AddGeometry column doesn't exist

Last week I have installed PostgreSql/PostGis and PgAdmin. Once I have tired to import shape file from PgAdmin using "PostGIS Shape file and DBF Loader 2.1" as below.

But it throws an error complaining "File import error in PostGis AddGeometry column doesn't exist" as shown in figure below

Then, I found out that the spatial extension is not enabled in my database. The following image shows the steps to enabled the spatial extension in the database.

Open new extension and name it postgis.

Then import the shape file again... Voila it works

## Sunday, January 13, 2013

### 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.

## Tuesday, January 8, 2013

### 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.

## Tuesday, June 5, 2012

### Interactive West Nile virus incidence mapping using OpenGeo tools, Google APIs, & HTML5

The Geographic Information Science Center of Excellence at South Dakota State University developed a web based West Nile virus (WNV) mapping application using Geosever, Openlayers, PostGIS,GeoWebCache, Jquery, Google visualization APIs, and, other open source technologies. This application allows users to pan, and zoom to visualize historical WNV patterns while using a slider bar to navigate through time. Users can click on counties to see -county level time-series graphs, case counts, and total population. The application also supports changing choropleth transparencies, choropleth classifications, and map backgrounds.

Methods:
A. OpenGeo tools:

i.     Open layers
ii.    Geoserver
iii.   WebGeoCache
iv.   PostGIS

C.      HTML5

These are frequently refereed to as HTML5 technologies.
Javascript
HTML Canvas
WebGL
CSS3
Other CSS improvements such as flex boxes
Offline Storage
Geolocation
New events such as touch and orientation
Ajax
Websockets

## Wednesday, April 18, 2012

### 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.