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

Tuesday, August 22, 2017

Solved: Cannot connect Postgre in VM from host

Used vagrant for setting up the VM, so set private network and port forward in vagrant file as below -

migration.vm.network "private_network", ip: "192.168.33.10"
migration.vm.network "forwarded_port",guest:5432 , host:15432

1. Use telnet localhost 15432 to see if the port is open (Host)

Still can not connect to PostgresSql running in VM  :(

Then

2. Edit postgresql.conf (Guest)
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'

3.  Add following in pg_hba.conf

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all              all            0.0.0.0/0               md5

4. Finally, able to connect to Postgres via localhost:15432