Vjylku

Is there any danger of my neighbor having my wife's signature?

How do I add a strong "onion flavor" to the biryani (in restaurant style)?

Does Plato's "Ring of Gyges" have a corrupting influence on its wearer?

Would water spill from a bowl in a Bag of Holding?

User input happy birthday program

Is this Article About Possible Mirrored Universe Junk Science?

Alternate timeline nomenclature

Can't figure out a htaccess rule

Why does a single AND gate need 60 transistors?

Linearity Assumption

Coworker is trying to get me to sign his petition to run for office. How to decline politely?

Is it possible to narrate a novel in a faux-historical style without alienating the reader?

Players preemptively rolling, even though their rolls are useless or are checking the wrong skills

Dealing with an internal ScriptKiddie

Is the Maximum Use License for Everybody (MULE) a FOSS license?

Expression for "unconsciously using words (or accents) used by a person you often talk with or listen to"?

In the Lost in Space intro why was Dr. Smith actor listed as a special guest star?

Is it possible to detect 100% of SQLi with a simple regex?

Can I benefit from a feat effect whilst polymorphed?

How do I fight with Heavy Armor as a Wizard with Tenser's Transformation?

What if I miss a connection and don't have money to book next flight?

1990s-2000s horror alien movie with slugs infecting people through the mouth

How long can the stop in a stop-and-go be?

Coworker asking me to not bring cakes due to self control issue. What should I do?

ST_ValueCount() returning multiple rows of same VALUE

PostGIS: Merge multiple table with same GIDST_MakeEnvelope returning too many geosPoor performance with storing large rasters in PostGIS and visualising in QGISCalculating Slope for the Whole World in PostGISST_Distance_Spheroid returning very large valueLoading raster to R from PostgreSQL via pgGetRast takes a lot of timeHow to return a polygon geometry from overlapping rows that share a same value in another columnWhat information is stored in a PostGIS geometry and how can I access it?Making UPDATE for coordinate does not return the same valueVery slow loading of PostGIS raster layer in qGIS

0

I want to calculate the proportion of each pixel value in a raster after clipping a much larger raster to my area of interest. However, after clipping and saving the raster from PostgreSQL database/PostGIS, the table created does not calculate the pixel number or proportion as expected.

Step 1: clip larger raster (Minnesota gridded soils) based on extent of "plotbounds" (polygon geometry table):

CREATE TABLE out.eonr_gaylord_2012_soils AS

SELECT ST_Clip(rast, p.geom)

FROM gssurgo_mn.mapunitraster_mn_10m, temp.eonr_gaylord_2012_plotbounds_data AS p 

WHERE ST_Intersects(p.geom, rast);

Step 2: Calculate the proportion of each pixel

SELECT DISTINCT pvc.VALUE, SUM(pvc.COUNT) AS tot_pix, 100 * SUM(pvc.COUNT) / SUM(SUM(pvc.COUNT)) OVER () AS prop

FROM out.eonr_gaylord_2012_soils, ST_ValueCount(rast, 1) AS pvc

GROUP BY pvc.VALUE, pvc.COUNT

ORDER BY pvc.VALUE

Output:

Why are there multiple rows (6) for each value?

I can load this raster into QGIS and it displays fine. I tried exporting to my local hard drive as a geotiff (from QGIS), then reloaded into PostgreSQL using raster2pgsql in the command prompt, and ran the same query to calculate pixel number and proportion (with the imported geotiff raster), and it calculates as expected:

When looking at the Info from QGIS DB Manager, the original raster (the one that doesn't behave as expected) has a few differences from the imported raster (the one that works):

1. Pages: 3 vs 1


2. Rows: 128 vs 1


3. Spatial ref: [none] vs "WGS 84 / UTM zone 15N (32615)"


4. Constraints: [none] vs many (e.g., enforce_max_extent_rast, enforce_out_db_rast, etc.)

Version info:

SELECT version()

PostgreSQL 9.5.10, compiled by Visual C++ build 1800, 64-bit

SELECT PostGIS_full_version()

POSTGIS="2.3.5 r16110" GEOS="3.6.2-CAPI-1.10.2 4d2925d" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.2, released 2017/09/15" LIBXML="2.7.8" LIBJSON="0.12" RASTER

postgresql postgis-2.3

share

asked 2 mins ago

Tyler NTyler N

1316

add a comment | 

0

I want to calculate the proportion of each pixel value in a raster after clipping a much larger raster to my area of interest. However, after clipping and saving the raster from PostgreSQL database/PostGIS, the table created does not calculate the pixel number or proportion as expected.

Step 1: clip larger raster (Minnesota gridded soils) based on extent of "plotbounds" (polygon geometry table):

CREATE TABLE out.eonr_gaylord_2012_soils AS

SELECT ST_Clip(rast, p.geom)

FROM gssurgo_mn.mapunitraster_mn_10m, temp.eonr_gaylord_2012_plotbounds_data AS p 

WHERE ST_Intersects(p.geom, rast);

Step 2: Calculate the proportion of each pixel

SELECT DISTINCT pvc.VALUE, SUM(pvc.COUNT) AS tot_pix, 100 * SUM(pvc.COUNT) / SUM(SUM(pvc.COUNT)) OVER () AS prop

FROM out.eonr_gaylord_2012_soils, ST_ValueCount(rast, 1) AS pvc

GROUP BY pvc.VALUE, pvc.COUNT

ORDER BY pvc.VALUE

Output:

Why are there multiple rows (6) for each value?

I can load this raster into QGIS and it displays fine. I tried exporting to my local hard drive as a geotiff (from QGIS), then reloaded into PostgreSQL using raster2pgsql in the command prompt, and ran the same query to calculate pixel number and proportion (with the imported geotiff raster), and it calculates as expected:

When looking at the Info from QGIS DB Manager, the original raster (the one that doesn't behave as expected) has a few differences from the imported raster (the one that works):

1. Pages: 3 vs 1


2. Rows: 128 vs 1


3. Spatial ref: [none] vs "WGS 84 / UTM zone 15N (32615)"


4. Constraints: [none] vs many (e.g., enforce_max_extent_rast, enforce_out_db_rast, etc.)

Version info:

SELECT version()

PostgreSQL 9.5.10, compiled by Visual C++ build 1800, 64-bit

SELECT PostGIS_full_version()

POSTGIS="2.3.5 r16110" GEOS="3.6.2-CAPI-1.10.2 4d2925d" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.2, released 2017/09/15" LIBXML="2.7.8" LIBJSON="0.12" RASTER

postgresql postgis-2.3

share

asked 2 mins ago

Tyler NTyler N

1316

add a comment | 

I want to calculate the proportion of each pixel value in a raster after clipping a much larger raster to my area of interest. However, after clipping and saving the raster from PostgreSQL database/PostGIS, the table created does not calculate the pixel number or proportion as expected.

Step 1: clip larger raster (Minnesota gridded soils) based on extent of "plotbounds" (polygon geometry table):

CREATE TABLE out.eonr_gaylord_2012_soils AS

SELECT ST_Clip(rast, p.geom)

FROM gssurgo_mn.mapunitraster_mn_10m, temp.eonr_gaylord_2012_plotbounds_data AS p 

WHERE ST_Intersects(p.geom, rast);

Step 2: Calculate the proportion of each pixel

SELECT DISTINCT pvc.VALUE, SUM(pvc.COUNT) AS tot_pix, 100 * SUM(pvc.COUNT) / SUM(SUM(pvc.COUNT)) OVER () AS prop

FROM out.eonr_gaylord_2012_soils, ST_ValueCount(rast, 1) AS pvc

GROUP BY pvc.VALUE, pvc.COUNT

ORDER BY pvc.VALUE

Output:

Why are there multiple rows (6) for each value?

I can load this raster into QGIS and it displays fine. I tried exporting to my local hard drive as a geotiff (from QGIS), then reloaded into PostgreSQL using raster2pgsql in the command prompt, and ran the same query to calculate pixel number and proportion (with the imported geotiff raster), and it calculates as expected:

When looking at the Info from QGIS DB Manager, the original raster (the one that doesn't behave as expected) has a few differences from the imported raster (the one that works):

1. Pages: 3 vs 1


2. Rows: 128 vs 1


3. Spatial ref: [none] vs "WGS 84 / UTM zone 15N (32615)"


4. Constraints: [none] vs many (e.g., enforce_max_extent_rast, enforce_out_db_rast, etc.)

Version info:

SELECT version()

PostgreSQL 9.5.10, compiled by Visual C++ build 1800, 64-bit

SELECT PostGIS_full_version()

POSTGIS="2.3.5 r16110" GEOS="3.6.2-CAPI-1.10.2 4d2925d" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.2, released 2017/09/15" LIBXML="2.7.8" LIBJSON="0.12" RASTER

postgresql postgis-2.3

share

asked 2 mins ago

Tyler NTyler N

1316

CREATE TABLE out.eonr_gaylord_2012_soils AS

SELECT ST_Clip(rast, p.geom)

FROM gssurgo_mn.mapunitraster_mn_10m, temp.eonr_gaylord_2012_plotbounds_data AS p 

WHERE ST_Intersects(p.geom, rast);

SELECT DISTINCT pvc.VALUE, SUM(pvc.COUNT) AS tot_pix, 100 * SUM(pvc.COUNT) / SUM(SUM(pvc.COUNT)) OVER () AS prop

FROM out.eonr_gaylord_2012_soils, ST_ValueCount(rast, 1) AS pvc

GROUP BY pvc.VALUE, pvc.COUNT

ORDER BY pvc.VALUE

SELECT version()

SELECT PostGIS_full_version()

share

asked 2 mins ago

Tyler NTyler N

1316

share

asked 2 mins ago

Tyler NTyler N

1316

asked 2 mins ago

Tyler NTyler N

1316

asked 2 mins ago

Tyler NTyler N

1316