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

Read More

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

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

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

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

B. Google APIs

i.       Google Maps API

ii.      Google Charting API

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

Click here to visit the Application

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