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



Combined zonal statistics for raster tiles as a single raster -

SELECT  (ST_SummaryStats (ST_Clip(ST_Union(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))


Function to Compute Zonal Statistics -
private String GetSqlQuery(String tableName,String polygonGeom){
        String pgSqlQuery =  "SELECT row_to_json(t) from ( SELECT (ST_SummaryStats (ST_Clip(ST_Union(rast), ST_GeomFromText('"+polygonGeom+"',4326),true))).*,"
                +" ST_Area(ST_GeomFromText('"+polygonGeom+"',4326))"
                +" FROM public."+tableName
                +" WHERE ST_Intersects(rast,ST_GeomFromText ('"+polygonGeom+"',4326)))t";

        return pgSqlQuery;
    }

I will make full post soon.

/* polygon 1 */
SELECT  (ST_SummaryStats (ST_Clip(ST_Union(rast), ST_GeomFromText('MULTIPOLYGON (((-117.603149 34.034453,-117.603149 34.079962,-117.526245 34.079962,-117.526245 34.034453,-117.603149 34.034453)))',4326),true))).*
FROM public.biodiv_ssolnw
WHERE ST_Intersects(rast,ST_GeomFromText('MULTIPOLYGON (((-117.603149 34.034453,-117.603149 34.079962,-117.526245 34.079962,-117.526245 34.034453,-117.603149 34.034453)))'  ,4326));

/* polygon 2 */
SELECT  (ST_SummaryStats (ST_Clip(ST_Union(rast), ST_GeomFromText('MULTIPOLYGON (((-119.274445 34.644507,-119.274445 34.663711,-119.251099 34.663711,-119.251099 34.644507,-119.274445 34.644507)))',4326),true))).*
FROM public.biodiv_ssolnw
WHERE ST_Intersects(rast,ST_GeomFromText('MULTIPOLYGON (((-119.274445 34.644507,-119.274445 34.663711,-119.251099 34.663711,-119.251099 34.644507,-119.274445 34.644507)))'  ,4326));

/* both */
SELECT  (ST_SummaryStats (ST_Clip(ST_Union(rast), ST_GeomFromText('MULTIPOLYGON (((-119.274445 34.644507,-119.274445 34.663711,-119.251099 34.663711,-119.251099 34.644507,-119.274445 34.644507)),((-117.603149 34.034453,-117.603149 34.079962,-117.526245 34.079962,-117.526245 34.034453,-117.603149 34.034453)))',4326),true))).*
FROM public.biodiv_ssolnw

WHERE ST_Intersects(rast,ST_GeomFromText('MULTIPOLYGON (((-119.274445 34.644507,-119.274445 34.663711,-119.251099 34.663711,-119.251099 34.644507,-119.274445 34.644507)),((-117.603149 34.034453,-117.603149 34.079962,-117.526245 34.079962,-117.526245 34.034453,-117.603149 34.034453)))'  ,4326));

Comments

Post a Comment

Popular posts from this blog

Prevent WPF Global Keyboard Hook from stops working after hitting keys a while C# .NET

Image
The article written by Dylan Currier,  Low Level Global Keyboard Hook / Sink in C# .NE T is one the simple, easily understandable, and and working post on Global Keyboard hook out there in internet. It worked in my WPF application and I added following line in the WPF app to refresh hook after each key press- IDK  if it is write or wrong, more likely to be a wrong practice to hook/unhook every time but it gets the job done. The primary reason to refresh hook is to prevent app hanging (not responding to the key press) after few clicks on the WPF button. void _listener_OnKeyPressed(object sender, KeyPressedArgs e)         {                       _listener.UnHookKeyboard();           _listener.HookKeyboard();         } Following is the video from  Dylan Currier   on  Low Level Keyboard Hook in C# / .NET"
Read more

Generate ArcGIS Token By URL Request From ArcGIS Portal, Federated Environment , ESRI JS API and Angular snippets

In my custom angular web application, I had to pull the data from different ArcGIS server environments, say Dev ArcGIS server and Test ArcGIS server environment, some of my items are directly referred from ArcGIS server services and some of them are from Enterprise portal referring the WebMapId and PortalId from both environment (Dev, Test). Servers are in federated environment. To pull the data from different ArcGIS server environment, user must login in each environment. In addition, user must login to get inside the custom application and Enterprise AD Group was set to authenticate users on the custom web application and ArcGIS server environments. So, there will be 3 login attempts (1 –application itself, 2- Dev server/portal, 3- Test server/portal) to use the application, which doesn’t provide good user experience. To improve the better application workflow, I decided to reduce the number of logins required in the application and use AD Username and Password captured du...
Read more

Solution: IntelliJ not recognizing a particular file correctly, instead its stuck as a text file

Image
I had mistakenly save my class as fileName.text file and tried to rename it with fileName .java but the IntelliJ couldn't recognize it and throws the error saying.. After spending long hours on internet, I found a solution and sharing/keeping here for future reference. Step 1: Click "File"==> "Settings" Step 2: Expand "Editor" & Click "File Types" Step 3: You will see all file types on Right. Navigate to the "Text Files" and Click it Step 4: You should able to see your file name on the bottom of Registered Patterns (lower box) Step 5: Remove your file from the Registered Patterns. The problem should solved and let you to rename with fileName.java Step 6: If not, delete the file from the project and create it again with name fileName
Read more