From 3ac670b992dccb074630f93bdd0475b2dfa9a0c4 Mon Sep 17 00:00:00 2001 From: Bborie Park Date: Tue, 23 Aug 2011 22:41:22 +0000 Subject: [PATCH] Refactored ST_ValueCount and ST_ValuePercent to be a C function rather than a plpgsql function. git-svn-id: http://svn.osgeo.org/postgis/trunk@7778 b70326c6-7e19-0410-871a-916f4a2858ee --- raster/rt_core/rt_api.c | 14 +- raster/rt_core/rt_api.h | 11 +- raster/rt_pg/rt_pg.c | 468 ++++++++++++++++++++++++++++++-- raster/rt_pg/rtpostgis.sql.in.c | 173 ++++-------- raster/test/core/testapi.c | 18 +- 5 files changed, 522 insertions(+), 162 deletions(-) diff --git a/raster/rt_core/rt_api.c b/raster/rt_core/rt_api.c index c4cb470cd..a23df57ff 100644 --- a/raster/rt_core/rt_api.c +++ b/raster/rt_core/rt_api.c @@ -1827,7 +1827,7 @@ rt_band_get_summary_stats(rt_band band, int exclude_nodata_value, double sample, rt_histogram rt_band_get_histogram(rt_bandstats stats, int bin_count, double *bin_width, int bin_width_count, - int right, double min, double max, int *rtn_count) { + int right, double min, double max, uint32_t *rtn_count) { rt_histogram bins = NULL; int init_width = 0; int i; @@ -2082,7 +2082,7 @@ rt_band_get_histogram(rt_bandstats stats, */ rt_quantile rt_band_get_quantiles(rt_bandstats stats, - double *quantiles, int quantiles_count, int *rtn_count) { + double *quantiles, int quantiles_count, uint32_t *rtn_count) { rt_quantile rtn; int init_quantiles = 0; int i = 0; @@ -2326,9 +2326,9 @@ rt_quantile rt_band_get_quantiles_stream(rt_band band, int exclude_nodata_value, double sample, uint64_t cov_count, - struct quantile_llist **qlls, int *qlls_count, + struct quantile_llist **qlls, uint32_t *qlls_count, double *quantiles, int quantiles_count, - int *rtn_count) { + uint32_t *rtn_count) { rt_quantile rtn = NULL; int init_quantiles = 0; @@ -2840,6 +2840,7 @@ rt_band_get_quantiles_stream(rt_band band, * @param search_values: array of values to count * @param search_values_count: the number of search values * @param roundto: the decimal place to round the values to + * @param rtn_total: the number of pixels examined in the band * @param rtn_count: the number of value counts being returned * * @return the number of times the provide value(s) occur @@ -2847,7 +2848,7 @@ rt_band_get_quantiles_stream(rt_band band, rt_valuecount rt_band_get_value_count(rt_band band, int exclude_nodata_value, double *search_values, uint32_t search_values_count, double roundto, - int *rtn_count) { + uint32_t *rtn_total, uint32_t *rtn_count) { rt_valuecount vcnts = NULL; rt_pixtype pixtype = PT_END; uint8_t *data = NULL; @@ -2990,6 +2991,7 @@ rt_band_get_value_count(rt_band band, int exclude_nodata_value, continue; vcnts[i].count = band->width * band->height; + if (NULL != rtn_total) *rtn_total = vcnts[i].count; vcnts->percent = 1.0; } @@ -3006,6 +3008,7 @@ rt_band_get_value_count(rt_band band, int exclude_nodata_value, vcnts->value = nodata; vcnts->count = band->width * band->height; + if (NULL != rtn_total) *rtn_total = vcnts[i].count; vcnts->percent = 1.0; *rtn_count = 1; @@ -3085,6 +3088,7 @@ rt_band_get_value_count(rt_band band, int exclude_nodata_value, } RASTER_DEBUG(3, "done"); + if (NULL != rtn_total) *rtn_total = total; *rtn_count = vcnts_count; return vcnts; } diff --git a/raster/rt_core/rt_api.h b/raster/rt_core/rt_api.h index a5b5c10e3..0d12c0aca 100644 --- a/raster/rt_core/rt_api.h +++ b/raster/rt_core/rt_api.h @@ -644,7 +644,7 @@ rt_bandstats rt_band_get_summary_stats(rt_band band, int exclude_nodata_value, */ rt_histogram rt_band_get_histogram(rt_bandstats stats, int bin_count, double *bin_widths, int bin_widths_count, - int right, double min, double max, int *rtn_count); + int right, double min, double max, uint32_t *rtn_count); /** * Compute the default set of or requested quantiles for a set of data @@ -658,7 +658,7 @@ rt_histogram rt_band_get_histogram(rt_bandstats stats, * @return the default set of or requested quantiles for a band */ rt_quantile rt_band_get_quantiles(rt_bandstats stats, - double *quantiles, int quantiles_count, int *rtn_count); + double *quantiles, int quantiles_count, uint32_t *rtn_count); int quantile_llist_destroy(struct quantile_llist **list, uint32_t list_count); @@ -693,9 +693,9 @@ int quantile_llist_destroy(struct quantile_llist **list, rt_quantile rt_band_get_quantiles_stream(rt_band band, int exclude_nodata_value, double sample, uint64_t cov_count, - struct quantile_llist **qlls, int *qlls_count, + struct quantile_llist **qlls, uint32_t *qlls_count, double *quantiles, int quantiles_count, - int *rtn_count); + uint32_t *rtn_count); /** * Count the number of times provided value(s) occur in @@ -706,13 +706,14 @@ rt_quantile rt_band_get_quantiles_stream(rt_band band, * @param search_values: array of values to count * @param search_values_count: the number of search values * @param roundto: the decimal place to round the values to + * @param rtn_total: the number of pixels examined in the band * @param rtn_count: the number of value counts being returned * * @return the number of times the provide value(s) occur */ rt_valuecount rt_band_get_value_count(rt_band band, int exclude_nodata_value, double *search_values, uint32_t search_values_count, - double roundto, int *rtn_count); + double roundto, uint32_t *rtn_total, uint32_t *rtn_count); /** * Returns new band with values reclassified diff --git a/raster/rt_pg/rt_pg.c b/raster/rt_pg/rt_pg.c index 3e20e08f6..2514bf840 100644 --- a/raster/rt_pg/rt_pg.c +++ b/raster/rt_pg/rt_pg.c @@ -217,6 +217,7 @@ Datum RASTER_quantileCoverage(PG_FUNCTION_ARGS); /* get counts of values */ Datum RASTER_valueCount(PG_FUNCTION_ARGS); +Datum RASTER_valueCountCoverage(PG_FUNCTION_ARGS); /* reclassify specified bands of a raster */ Datum RASTER_reclass(PG_FUNCTION_ARGS); @@ -3194,8 +3195,8 @@ Datum RASTER_summaryStats(PG_FUNCTION_ARGS) TupleDesc tupdesc; int i = 0; bool *nulls = NULL; - Datum values[6]; int values_length = 6; + Datum values[values_length]; HeapTuple tuple; Datum result; @@ -3596,7 +3597,6 @@ Datum RASTER_histogram(PG_FUNCTION_ARGS) TupleDesc tupdesc; int i; - int count; rt_histogram hist; rt_histogram hist2; int call_cntr; @@ -3613,14 +3613,15 @@ Datum RASTER_histogram(PG_FUNCTION_ARGS) int num_bands = 0; bool exclude_nodata_value = TRUE; double sample = 0; - int bin_count = 0; + uint32_t bin_count = 0; double *bin_width = NULL; - int bin_width_count = 0; + uint32_t bin_width_count = 0; double width = 0; bool right = FALSE; double min = 0; double max = 0; rt_bandstats stats = NULL; + uint32_t count; int j; int n; @@ -3860,7 +3861,6 @@ Datum RASTER_histogramCoverage(PG_FUNCTION_ARGS) TupleDesc tupdesc; int i; - int count; rt_histogram covhist = NULL; rt_histogram covhist2; int call_cntr; @@ -3879,11 +3879,12 @@ Datum RASTER_histogramCoverage(PG_FUNCTION_ARGS) int32_t bandindex = 1; bool exclude_nodata_value = TRUE; double sample = 0; - int bin_count = 0; + uint32_t bin_count = 0; double *bin_width = NULL; - int bin_width_count = 0; + uint32_t bin_width_count = 0; double width = 0; bool right = FALSE; + uint32_t count; int len = 0; char *sql = NULL; @@ -4373,7 +4374,6 @@ Datum RASTER_quantile(PG_FUNCTION_ARGS) TupleDesc tupdesc; int i; - int count; rt_quantile quant; rt_quantile quant2; int call_cntr; @@ -4391,9 +4391,10 @@ Datum RASTER_quantile(PG_FUNCTION_ARGS) bool exclude_nodata_value = TRUE; double sample = 0; double *quantiles = NULL; - int quantiles_count = 0; + uint32_t quantiles_count = 0; double quantile = 0; rt_bandstats stats = NULL; + uint32_t count; int j; int n; @@ -4613,7 +4614,6 @@ Datum RASTER_quantileCoverage(PG_FUNCTION_ARGS) TupleDesc tupdesc; int i; - int count; rt_quantile covquant = NULL; rt_quantile covquant2; int call_cntr; @@ -4633,8 +4633,9 @@ Datum RASTER_quantileCoverage(PG_FUNCTION_ARGS) bool exclude_nodata_value = TRUE; double sample = 0; double *quantiles = NULL; - int quantiles_count = 0; + uint32_t quantiles_count = 0; double quantile = 0; + uint32_t count; int len = 0; char *sql = NULL; @@ -4652,7 +4653,7 @@ Datum RASTER_quantileCoverage(PG_FUNCTION_ARGS) rt_band band = NULL; int num_bands = 0; struct quantile_llist *qlls = NULL; - int qlls_count; + uint32_t qlls_count; int j; int n; @@ -4797,7 +4798,7 @@ Datum RASTER_quantileCoverage(PG_FUNCTION_ARGS) /* get stats */ snprintf(sql, len, "SELECT count FROM _st_summarystats('%s','%s',%d,%d::boolean,%f)", tablename, colname, bandindex, (exclude_nodata_value ? 1 : 0), sample); - POSTGIS_RT_DEBUGF(3, "RASTER_quantileCoverage: %s", sql); + POSTGIS_RT_DEBUGF(3, "stats sql: %s", sql); spi_result = SPI_execute(sql, TRUE, 0); pfree(sql); if (spi_result != SPI_OK_SELECT || SPI_tuptable == NULL || SPI_processed != 1) { @@ -4823,7 +4824,7 @@ Datum RASTER_quantileCoverage(PG_FUNCTION_ARGS) SRF_RETURN_DONE(funcctx); } cov_count = strtol(tmp, NULL, 10); - POSTGIS_RT_DEBUGF(3, "RASTER_quantileCoverage: covcount = %d", cov_count); + POSTGIS_RT_DEBUGF(3, "covcount = %d", cov_count); pfree(tmp); /* iterate through rasters of coverage */ @@ -4841,7 +4842,7 @@ Datum RASTER_quantileCoverage(PG_FUNCTION_ARGS) /* get cursor */ snprintf(sql, len, "SELECT \"%s\" FROM \"%s\" WHERE \"%s\" IS NOT NULL", colname, tablename, colname); - POSTGIS_RT_DEBUGF(3, "RASTER_quantileCoverage: %s", sql); + POSTGIS_RT_DEBUGF(3, "coverage sql: %s", sql); portal = SPI_cursor_open_with_args( "coverage", sql, @@ -4948,6 +4949,12 @@ Datum RASTER_quantileCoverage(PG_FUNCTION_ARGS) quantile_llist_destroy(&qlls, qlls_count); if (quantiles_count) pfree(quantiles); + /* + dustymugs 2011-08-23 + The following block seems to remove a strange memory + issue only in OSX (tested on 64-bit only) where + covquant is corrupted in subsequent calls of SRF + */ covquant2 = palloc(sizeof(struct rt_quantile_t) * count); for (i = 0; i < count; i++) { covquant2[i].quantile = covquant[i].quantile; @@ -5031,7 +5038,6 @@ Datum RASTER_valueCount(PG_FUNCTION_ARGS) { TupleDesc tupdesc; int i; - int count; rt_valuecount vcnts; rt_valuecount vcnts2; int call_cntr; @@ -5048,8 +5054,9 @@ Datum RASTER_valueCount(PG_FUNCTION_ARGS) { int num_bands = 0; bool exclude_nodata_value = TRUE; double *search_values = NULL; - int search_values_count = 0; + uint32_t search_values_count = 0; double roundto = 0; + uint32_t count; int j; int n; @@ -5158,7 +5165,7 @@ Datum RASTER_valueCount(PG_FUNCTION_ARGS) { } /* get counts of values */ - vcnts = rt_band_get_value_count(band, (int) exclude_nodata_value, search_values, search_values_count, roundto, &count); + vcnts = rt_band_get_value_count(band, (int) exclude_nodata_value, search_values, search_values_count, roundto, NULL, &count); rt_band_destroy(band); rt_raster_destroy(raster); PG_FREE_IF_COPY(pgraster, 0); @@ -5235,6 +5242,431 @@ Datum RASTER_valueCount(PG_FUNCTION_ARGS) { } } +/* get counts of values for a coverage */ +PG_FUNCTION_INFO_V1(RASTER_valueCountCoverage); +Datum RASTER_valueCountCoverage(PG_FUNCTION_ARGS) { + FuncCallContext *funcctx; + TupleDesc tupdesc; + + int i; + uint64_t covcount = 0; + uint64_t covtotal = 0; + rt_valuecount covvcnts = NULL; + rt_valuecount covvcnts2; + int call_cntr; + int max_calls; + + POSTGIS_RT_DEBUG(3, "RASTER_valueCountCoverage: Starting"); + + /* first call of function */ + if (SRF_IS_FIRSTCALL()) { + MemoryContext oldcontext; + + text *tablenametext = NULL; + char *tablename = NULL; + text *colnametext = NULL; + char *colname = NULL; + int32_t bandindex = 1; + bool exclude_nodata_value = TRUE; + double *search_values = NULL; + uint32_t search_values_count = 0; + double roundto = 0; + + int len = 0; + char *sql = NULL; + int spi_result; + Portal portal; + SPITupleTable *tuptable = NULL; + HeapTuple tuple; + Datum datum; + bool isNull = FALSE; + rt_pgraster *pgraster = NULL; + rt_raster raster = NULL; + rt_band band = NULL; + int num_bands = 0; + uint32_t count; + uint32_t total; + rt_valuecount vcnts = NULL; + int exists = 0; + + int j; + int n; + + ArrayType *array; + Oid etype; + Datum *e; + bool *nulls; + int16 typlen; + bool typbyval; + char typalign; + int ndims = 1; + int *dims; + int *lbs; + + /* create a function context for cross-call persistence */ + funcctx = SRF_FIRSTCALL_INIT(); + + /* switch to memory context appropriate for multiple function calls */ + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + /* tablename is null, return null */ + if (PG_ARGISNULL(0)) { + elog(NOTICE, "Table name must be provided"); + SRF_RETURN_DONE(funcctx); + } + tablenametext = PG_GETARG_TEXT_P(0); + tablename = text_to_cstring(tablenametext); + if (!strlen(tablename)) { + elog(NOTICE, "Table name must be provided"); + SRF_RETURN_DONE(funcctx); + } + POSTGIS_RT_DEBUGF(3, "tablename = %s", tablename); + + /* column name is null, return null */ + if (PG_ARGISNULL(1)) { + elog(NOTICE, "Column name must be provided"); + PG_RETURN_NULL(); + } + colnametext = PG_GETARG_TEXT_P(1); + colname = text_to_cstring(colnametext); + if (!strlen(colname)) { + elog(NOTICE, "Column name must be provided"); + SRF_RETURN_DONE(funcctx); + } + POSTGIS_RT_DEBUGF(3, "colname = %s", colname); + + /* band index is 1-based */ + if (!PG_ARGISNULL(2)) + bandindex = PG_GETARG_INT32(2); + + /* exclude_nodata_value flag */ + if (!PG_ARGISNULL(3)) + exclude_nodata_value = PG_GETARG_BOOL(3); + + /* search values */ + if (!PG_ARGISNULL(4)) { + array = PG_GETARG_ARRAYTYPE_P(4); + etype = ARR_ELEMTYPE(array); + get_typlenbyvalalign(etype, &typlen, &typbyval, &typalign); + + switch (etype) { + case FLOAT4OID: + case FLOAT8OID: + break; + default: + elog(ERROR, "RASTER_valueCountCoverage: Invalid data type for values"); + rt_raster_destroy(raster); + PG_RETURN_NULL(); + break; + } + + ndims = ARR_NDIM(array); + dims = ARR_DIMS(array); + lbs = ARR_LBOUND(array); + + deconstruct_array(array, etype, typlen, typbyval, typalign, &e, + &nulls, &n); + + search_values = palloc(sizeof(double) * n); + for (i = 0, j = 0; i < n; i++) { + if (nulls[i]) continue; + + switch (etype) { + case FLOAT4OID: + search_values[j] = (double) DatumGetFloat4(e[i]); + break; + case FLOAT8OID: + search_values[j] = (double) DatumGetFloat8(e[i]); + break; + } + + POSTGIS_RT_DEBUGF(5, "search_values[%d] = %f", j, search_values[j]); + j++; + } + search_values_count = j; + + if (j < 1) { + pfree(search_values); + search_values = NULL; + } + } + + /* roundto */ + if (!PG_ARGISNULL(5)) { + roundto = PG_GETARG_FLOAT8(5); + if (roundto < 0.) roundto = 0; + } + + /* iterate through rasters of coverage */ + /* create sql */ + len = sizeof(char) * (strlen("SELECT \"\" FROM \"\" WHERE \"\" IS NOT NULL") + (strlen(colname) * 2) + strlen(tablename) + 1); + sql = (char *) palloc(len); + if (NULL == sql) { + elog(ERROR, "RASTER_valueCountCoverage: Unable to allocate memory for sql\n"); + + if (search_values_count) pfree(search_values); + SRF_RETURN_DONE(funcctx); + } + + /* connect to database */ + spi_result = SPI_connect(); + if (spi_result != SPI_OK_CONNECT) { + elog(ERROR, "RASTER_valueCountCoverage: Could not connect to database using SPI\n"); + + pfree(sql); + if (search_values_count) pfree(search_values); + + SRF_RETURN_DONE(funcctx); + } + + /* get cursor */ + snprintf(sql, len, "SELECT \"%s\" FROM \"%s\" WHERE \"%s\" IS NOT NULL", colname, tablename, colname); + POSTGIS_RT_DEBUGF(3, "RASTER_valueCountCoverage: %s", sql); + portal = SPI_cursor_open_with_args( + "coverage", + sql, + 0, NULL, + NULL, NULL, + TRUE, 0 + ); + pfree(sql); + + /* process resultset */ + SPI_cursor_fetch(portal, TRUE, 1); + while (SPI_processed == 1 && SPI_tuptable != NULL) { + tupdesc = SPI_tuptable->tupdesc; + tuptable = SPI_tuptable; + tuple = tuptable->vals[0]; + + datum = SPI_getbinval(tuple, tupdesc, 1, &isNull); + if (SPI_result == SPI_ERROR_NOATTRIBUTE) { + elog(ERROR, "RASTER_valueCountCoverage: Unable to get raster of coverage\n"); + + if (SPI_tuptable) SPI_freetuptable(tuptable); + SPI_cursor_close(portal); + SPI_finish(); + + if (NULL != covvcnts) free(covvcnts); + if (search_values_count) pfree(search_values); + + SRF_RETURN_DONE(funcctx); + } + else if (isNull) { + SPI_cursor_fetch(portal, TRUE, 1); + continue; + } + + pgraster = (rt_pgraster *) PG_DETOAST_DATUM(datum); + + raster = rt_raster_deserialize(pgraster, FALSE); + if (!raster) { + elog(ERROR, "RASTER_valueCountCoverage: Could not deserialize raster"); + + if (SPI_tuptable) SPI_freetuptable(tuptable); + SPI_cursor_close(portal); + SPI_finish(); + + if (NULL != covvcnts) free(covvcnts); + if (search_values_count) pfree(search_values); + + SRF_RETURN_DONE(funcctx); + } + + /* inspect number of bands*/ + num_bands = rt_raster_get_num_bands(raster); + if (bandindex < 1 || bandindex > num_bands) { + elog(NOTICE, "Invalid band index (must use 1-based). Returning NULL"); + + if (SPI_tuptable) SPI_freetuptable(tuptable); + SPI_cursor_close(portal); + SPI_finish(); + + rt_raster_destroy(raster); + if (NULL != covvcnts) free(covvcnts); + if (search_values_count) pfree(search_values); + + SRF_RETURN_DONE(funcctx); + } + + /* get band */ + band = rt_raster_get_band(raster, bandindex - 1); + if (!band) { + elog(NOTICE, "Could not find raster band of index %d. Returning NULL", bandindex); + + if (SPI_tuptable) SPI_freetuptable(tuptable); + SPI_cursor_close(portal); + SPI_finish(); + + rt_raster_destroy(raster); + if (NULL != covvcnts) free(covvcnts); + if (search_values_count) pfree(search_values); + + SRF_RETURN_DONE(funcctx); + } + + /* get counts of values */ + vcnts = rt_band_get_value_count(band, (int) exclude_nodata_value, search_values, search_values_count, roundto, &total, &count); + rt_band_destroy(band); + rt_raster_destroy(raster); + if (NULL == vcnts || !count) { + elog(NOTICE, "Could not count the values of raster band of index %d", bandindex); + SRF_RETURN_DONE(funcctx); + } + + POSTGIS_RT_DEBUGF(3, "%d value counts returned", count); + + if (NULL == covvcnts) { + /* + dustymugs 2011-08-23 + covvcnts is initialized using malloc instead of palloc due to the strange + memory issue only seen in OSX (tested on 64-bit only) where + covvcnts is corrupted in subsequent calls of SRF + */ + covvcnts = (rt_valuecount) malloc(sizeof(struct rt_valuecount_t) * count); + if (NULL == covvcnts) { + elog(ERROR, "RASTER_valueCountCoverage: Unable to allocate memory for coverage value counts"); + + if (SPI_tuptable) SPI_freetuptable(tuptable); + SPI_cursor_close(portal); + SPI_finish(); + + if (search_values_count) pfree(search_values); + pfree(vcnts); + + SRF_RETURN_DONE(funcctx); + } + + for (i = 0; i < count; i++) { + covvcnts[i].value = vcnts[i].value; + covvcnts[i].count = vcnts[i].count; + covvcnts[i].percent = -1; + } + + covcount = count; + } + else { + for (i = 0; i < count; i++) { + exists = 0; + + for (j = 0; j < covcount; j++) { + if (FLT_EQ(vcnts[i].value, covvcnts[j].value)) { + exists = 1; + break; + } + } + + if (exists) { + covvcnts[j].count += vcnts[i].count; + } + else { + covcount++; + covvcnts = realloc(covvcnts, sizeof(struct rt_valuecount_t) * covcount); + if (NULL == covvcnts) { + elog(ERROR, "RASTER_valueCountCoverage: Unable to change allocated memory for coverage value counts"); + + if (SPI_tuptable) SPI_freetuptable(tuptable); + SPI_cursor_close(portal); + SPI_finish(); + + if (search_values_count) pfree(search_values); + if (NULL != covvcnts) free(covvcnts); + pfree(vcnts); + + SRF_RETURN_DONE(funcctx); + } + + covvcnts[covcount - 1].value = vcnts[i].value; + covvcnts[covcount - 1].count = vcnts[i].count; + covvcnts[covcount - 1].percent = -1; + } + } + } + + covtotal += total; + + pfree(vcnts); + + /* next record */ + SPI_cursor_fetch(portal, TRUE, 1); + } + + if (SPI_tuptable) SPI_freetuptable(tuptable); + SPI_cursor_close(portal); + SPI_finish(); + + if (search_values_count) pfree(search_values); + + /* compute percentages */ + for (i = 0; i < covcount; i++) { + covvcnts[i].percent = (double) covvcnts[i].count / covtotal; + } + + /* Store needed information */ + funcctx->user_fctx = covvcnts; + + /* total number of tuples to be returned */ + funcctx->max_calls = covcount; + + /* Build a tuple descriptor for our result type */ + if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) { + ereport(ERROR, ( + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg( + "function returning record called in context " + "that cannot accept type record" + ) + )); + } + + BlessTupleDesc(tupdesc); + funcctx->tuple_desc = tupdesc; + + MemoryContextSwitchTo(oldcontext); + } + + /* stuff done on every call of the function */ + funcctx = SRF_PERCALL_SETUP(); + + call_cntr = funcctx->call_cntr; + max_calls = funcctx->max_calls; + tupdesc = funcctx->tuple_desc; + covvcnts2 = funcctx->user_fctx; + + /* do when there is more left to send */ + if (call_cntr < max_calls) { + int values_length = 3; + Datum values[values_length]; + bool *nulls = NULL; + HeapTuple tuple; + Datum result; + + POSTGIS_RT_DEBUGF(3, "Result %d", call_cntr); + + nulls = palloc(sizeof(bool) * values_length); + for (i = 0; i < values_length; i++) nulls[i] = FALSE; + + values[0] = Float8GetDatum(covvcnts2[call_cntr].value); + values[1] = UInt32GetDatum(covvcnts2[call_cntr].count); + values[2] = Float8GetDatum(covvcnts2[call_cntr].percent); + + /* build a tuple */ + tuple = heap_form_tuple(tupdesc, values, nulls); + + /* make the tuple into a datum */ + result = HeapTupleGetDatum(tuple); + + /* clean up */ + pfree(nulls); + + SRF_RETURN_NEXT(funcctx, result); + } + /* do when there is no more left */ + else { + free(covvcnts2); + SRF_RETURN_DONE(funcctx); + } +} + /** * Reclassify the specified bands of the raster */ diff --git a/raster/rt_pg/rtpostgis.sql.in.c b/raster/rt_pg/rtpostgis.sql.in.c index 48e63c35f..8d66a3ec1 100644 --- a/raster/rt_pg/rtpostgis.sql.in.c +++ b/raster/rt_pg/rtpostgis.sql.in.c @@ -840,115 +840,19 @@ CREATE OR REPLACE FUNCTION st_valuecount(rast raster, searchvalues double precis CREATE OR REPLACE FUNCTION st_valuecount(rast raster, nband integer, exclude_nodata_value boolean, searchvalue double precision, roundto double precision DEFAULT 0) RETURNS integer - AS $$ SELECT count FROM _st_valuecount($1, $2, $3, ARRAY[$4]::double precision[], $5) $$ + AS $$ SELECT (_st_valuecount($1, $2, $3, ARRAY[$4]::double precision[], $5)).count $$ LANGUAGE 'sql' IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION st_valuecount(rast raster, nband integer, searchvalue double precision, roundto double precision DEFAULT 0) RETURNS integer - AS $$ SELECT count FROM _st_valuecount($1, $2, TRUE, ARRAY[$3]::double precision[], $4) $$ + AS $$ SELECT (_st_valuecount($1, $2, TRUE, ARRAY[$3]::double precision[], $4)).count $$ LANGUAGE 'sql' IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION st_valuecount(rast raster, searchvalue double precision, roundto double precision DEFAULT 0) RETURNS integer - AS $$ SELECT count FROM _st_valuecount($1, 1, TRUE, ARRAY[$2]::double precision[], $3) $$ + AS $$ SELECT (_st_valuecount($1, 1, TRUE, ARRAY[$2]::double precision[], $3)).count $$ LANGUAGE 'sql' IMMUTABLE STRICT; -CREATE OR REPLACE FUNCTION _st_valuecount(rastertable text, rastercolumn text, nband integer DEFAULT 1, exclude_nodata_value boolean DEFAULT TRUE, searchvalues double precision[] DEFAULT NULL, roundto double precision DEFAULT 0) - RETURNS SETOF valuecount - AS $$ - DECLARE - curs refcursor; - - ctable text; - ccolumn text; - rast raster; - - vcnts valuecount; - BEGIN - -- nband - IF nband < 1 THEN - RAISE WARNING 'Invalid band index (must use 1-based). Returning NULL'; - RETURN; - END IF; - - -- rastertable and rastercolumn - IF rastertable IS NULL THEN - RAISE WARNING 'rastertable cannot be NULL. Returning NULL'; - RETURN; - ELSEIF rastercolumn IS NULL THEN - RAISE WARNING 'rastercolumn cannot be NULL. Returning NULL'; - RETURN; - END IF; - - -- clean rastertable and rastercolumn - ctable := quote_ident(rastertable); - ccolumn := quote_ident(rastercolumn); - - BEGIN - OPEN curs FOR EXECUTE 'SELECT ' - || ccolumn - || ' FROM ' - || ctable - || ' WHERE ' - || ccolumn - || ' IS NOT NULL'; - EXCEPTION - WHEN OTHERS THEN - RAISE WARNING 'Invalid table or column name. Returning NULL'; - RETURN; - END; - - LOOP - FETCH curs INTO rast; - EXIT WHEN NOT FOUND; - - FOR vcnts IN SELECT * FROM _st_valuecount(rast, nband, exclude_nodata_value, searchvalues, roundto) LOOP - IF vcnts IS NULL THEN - CONTINUE; - END IF; - --RAISE NOTICE 'vcnts = %', vcnts; - - RETURN NEXT vcnts; - END LOOP; - - END LOOP; - - CLOSE curs; - - RETURN; - END; - $$ LANGUAGE 'plpgsql' STABLE; - -CREATE OR REPLACE FUNCTION st_valuecount(rastertable text, rastercolumn text, nband integer DEFAULT 1, exclude_nodata_value boolean DEFAULT TRUE, searchvalues double precision[] DEFAULT NULL, roundto double precision DEFAULT 0, OUT value double precision, OUT count bigint) - RETURNS SETOF record - AS $$ SELECT value, sum(count) AS count FROM _st_valuecount($1, $2, $3, $4, $5, $6) GROUP BY 1 ORDER BY 1 $$ - LANGUAGE 'sql' STABLE; - -CREATE OR REPLACE FUNCTION st_valuecount(rastertable text, rastercolumn text, nband integer, searchvalues double precision[], roundto double precision DEFAULT 0, OUT value double precision, OUT count bigint) - RETURNS SETOF record - AS $$ SELECT value, count FROM st_valuecount($1, $2, $3, TRUE, $4, $5) $$ - LANGUAGE 'sql' STABLE; - -CREATE OR REPLACE FUNCTION st_valuecount(rastertable text, rastercolumn text, searchvalues double precision[], roundto double precision DEFAULT 0, OUT value double precision, OUT count bigint) - RETURNS SETOF record - AS $$ SELECT value, count FROM st_valuecount($1, $2, 1, TRUE, $3, $4) $$ - LANGUAGE 'sql' STABLE; - -CREATE OR REPLACE FUNCTION st_valuecount(rastertable text, rastercolumn text, nband integer, exclude_nodata_value boolean, searchvalue double precision, roundto double precision DEFAULT 0) - RETURNS bigint - AS $$ SELECT count FROM st_valuecount($1, $2, $3, $4, ARRAY[$5]::double precision[], $6) $$ - LANGUAGE 'sql' STABLE STRICT; - -CREATE OR REPLACE FUNCTION st_valuecount(rastertable text, rastercolumn text, nband integer, searchvalue double precision, roundto double precision DEFAULT 0) - RETURNS bigint - AS $$ SELECT count FROM st_valuecount($1, $2, $3, TRUE, ARRAY[$4]::double precision[], $5) $$ - LANGUAGE 'sql' STABLE STRICT; - -CREATE OR REPLACE FUNCTION st_valuecount(rastertable text, rastercolumn text, searchvalue double precision, roundto double precision DEFAULT 0) - RETURNS bigint - AS $$ SELECT count FROM st_valuecount($1, $2, 1, TRUE, ARRAY[$3]::double precision[], $4) $$ - LANGUAGE 'sql' STABLE STRICT; - CREATE OR REPLACE FUNCTION st_valuepercent(rast raster, nband integer DEFAULT 1, exclude_nodata_value boolean DEFAULT TRUE, searchvalues double precision[] DEFAULT NULL, roundto double precision DEFAULT 0, OUT value double precision, OUT percent double precision) RETURNS SETOF record AS $$ SELECT value, percent FROM _st_valuecount($1, $2, $3, $4, $5) $$ @@ -966,63 +870,82 @@ CREATE OR REPLACE FUNCTION st_valuepercent(rast raster, searchvalues double prec CREATE OR REPLACE FUNCTION st_valuepercent(rast raster, nband integer, exclude_nodata_value boolean, searchvalue double precision, roundto double precision DEFAULT 0) RETURNS double precision - AS $$ SELECT percent FROM _st_valuecount($1, $2, $3, ARRAY[$4]::double precision[], $5) $$ + AS $$ SELECT (_st_valuecount($1, $2, $3, ARRAY[$4]::double precision[], $5)).percent $$ LANGUAGE 'sql' IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION st_valuepercent(rast raster, nband integer, searchvalue double precision, roundto double precision DEFAULT 0) RETURNS double precision - AS $$ SELECT percent FROM _st_valuecount($1, $2, TRUE, ARRAY[$3]::double precision[], $4) $$ + AS $$ SELECT (_st_valuecount($1, $2, TRUE, ARRAY[$3]::double precision[], $4)).percent $$ LANGUAGE 'sql' IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION st_valuepercent(rast raster, searchvalue double precision, roundto double precision DEFAULT 0) RETURNS double precision - AS $$ SELECT percent FROM _st_valuecount($1, 1, TRUE, ARRAY[$2]::double precision[], $3) $$ + AS $$ SELECT (_st_valuecount($1, 1, TRUE, ARRAY[$2]::double precision[], $3)).percent $$ LANGUAGE 'sql' IMMUTABLE STRICT; +CREATE OR REPLACE FUNCTION _st_valuecount(rastertable text, rastercolumn text, nband integer DEFAULT 1, exclude_nodata_value boolean DEFAULT TRUE, searchvalues double precision[] DEFAULT NULL, roundto double precision DEFAULT 0) + RETURNS SETOF valuecount + AS 'MODULE_PATHNAME', 'RASTER_valueCountCoverage' + LANGUAGE 'C' STABLE; + +CREATE OR REPLACE FUNCTION st_valuecount(rastertable text, rastercolumn text, nband integer DEFAULT 1, exclude_nodata_value boolean DEFAULT TRUE, searchvalues double precision[] DEFAULT NULL, roundto double precision DEFAULT 0, OUT value double precision, OUT count integer) + RETURNS SETOF record + AS $$ SELECT value, count FROM _st_valuecount($1, $2, $3, $4, $5, $6) $$ + LANGUAGE 'sql' STABLE; + +CREATE OR REPLACE FUNCTION st_valuecount(rastertable text, rastercolumn text, nband integer, searchvalues double precision[], roundto double precision DEFAULT 0, OUT value double precision, OUT count integer) + RETURNS SETOF record + AS $$ SELECT value, count FROM _st_valuecount($1, $2, $3, TRUE, $4, $5) $$ + LANGUAGE 'sql' STABLE; + +CREATE OR REPLACE FUNCTION st_valuecount(rastertable text, rastercolumn text, searchvalues double precision[], roundto double precision DEFAULT 0, OUT value double precision, OUT count integer) + RETURNS SETOF record + AS $$ SELECT value, count FROM _st_valuecount($1, $2, 1, TRUE, $3, $4) $$ + LANGUAGE 'sql' STABLE; + +CREATE OR REPLACE FUNCTION st_valuecount(rastertable text, rastercolumn text, nband integer, exclude_nodata_value boolean, searchvalue double precision, roundto double precision DEFAULT 0) + RETURNS integer + AS $$ SELECT (_st_valuecount($1, $2, $3, $4, ARRAY[$5]::double precision[], $6)).count $$ + LANGUAGE 'sql' STABLE STRICT; + +CREATE OR REPLACE FUNCTION st_valuecount(rastertable text, rastercolumn text, nband integer, searchvalue double precision, roundto double precision DEFAULT 0) + RETURNS integer + AS $$ SELECT (_st_valuecount($1, $2, $3, TRUE, ARRAY[$4]::double precision[], $5)).count $$ + LANGUAGE 'sql' STABLE STRICT; + +CREATE OR REPLACE FUNCTION st_valuecount(rastertable text, rastercolumn text, searchvalue double precision, roundto double precision DEFAULT 0) + RETURNS integer + AS $$ SELECT (_st_valuecount($1, $2, 1, TRUE, ARRAY[$3]::double precision[], $4)).count $$ + LANGUAGE 'sql' STABLE STRICT; + CREATE OR REPLACE FUNCTION st_valuepercent(rastertable text, rastercolumn text, nband integer DEFAULT 1, exclude_nodata_value boolean DEFAULT TRUE, searchvalues double precision[] DEFAULT NULL, roundto double precision DEFAULT 0, OUT value double precision, OUT percent double precision) RETURNS SETOF record - AS $$ - SELECT - value, - CASE - WHEN sum(count) != 0 - THEN sum(count) / sum( - CASE - WHEN percent != 0 - THEN (count / percent) - ELSE 0 - END - ) - ELSE 0 - END AS percent - FROM _st_valuecount($1, $2, $3, $4, $5, $6) - GROUP BY 1 - ORDER BY 1 - $$ LANGUAGE 'sql' STABLE; + AS $$ SELECT value, percent FROM _st_valuecount($1, $2, $3, $4, $5, $6) $$ + LANGUAGE 'sql' STABLE; CREATE OR REPLACE FUNCTION st_valuepercent(rastertable text, rastercolumn text, nband integer, searchvalues double precision[], roundto double precision DEFAULT 0, OUT value double precision, OUT percent double precision) RETURNS SETOF record - AS $$ SELECT value, percent FROM st_valuepercent($1, $2, $3, TRUE, $4, $5) $$ + AS $$ SELECT value, percent FROM _st_valuecount($1, $2, $3, TRUE, $4, $5) $$ LANGUAGE 'sql' STABLE; CREATE OR REPLACE FUNCTION st_valuepercent(rastertable text, rastercolumn text, searchvalues double precision[], roundto double precision DEFAULT 0, OUT value double precision, OUT percent double precision) RETURNS SETOF record - AS $$ SELECT value, percent FROM st_valuepercent($1, $2, 1, TRUE, $3, $4) $$ + AS $$ SELECT value, percent FROM _st_valuecount($1, $2, 1, TRUE, $3, $4) $$ LANGUAGE 'sql' STABLE; CREATE OR REPLACE FUNCTION st_valuepercent(rastertable text, rastercolumn text, nband integer, exclude_nodata_value boolean, searchvalue double precision, roundto double precision DEFAULT 0) RETURNS double precision - AS $$ SELECT percent FROM st_valuepercent($1, $2, $3, $4, ARRAY[$5]::double precision[], $6) $$ + AS $$ SELECT (_st_valuecount($1, $2, $3, $4, ARRAY[$5]::double precision[], $6)).percent $$ LANGUAGE 'sql' STABLE STRICT; CREATE OR REPLACE FUNCTION st_valuepercent(rastertable text, rastercolumn text, nband integer, searchvalue double precision, roundto double precision DEFAULT 0) RETURNS double precision - AS $$ SELECT percent FROM st_valuepercent($1, $2, $3, TRUE, ARRAY[$4]::double precision[], $5) $$ + AS $$ SELECT (_st_valuecount($1, $2, $3, TRUE, ARRAY[$4]::double precision[], $5)).percent $$ LANGUAGE 'sql' STABLE STRICT; CREATE OR REPLACE FUNCTION st_valuepercent(rastertable text, rastercolumn text, searchvalue double precision, roundto double precision DEFAULT 0) RETURNS double precision - AS $$ SELECT percent FROM st_valuepercent($1, $2, 1, TRUE, ARRAY[$3]::double precision[], $4) $$ + AS $$ SELECT (_st_valuecount($1, $2, 1, TRUE, ARRAY[$3]::double precision[], $4)).percent $$ LANGUAGE 'sql' STABLE STRICT; ----------------------------------------------------------------------- diff --git a/raster/test/core/testapi.c b/raster/test/core/testapi.c index 8b98f023f..7918b81b2 100644 --- a/raster/test/core/testapi.c +++ b/raster/test/core/testapi.c @@ -995,7 +995,7 @@ static void testBandStats() { double quantiles[] = {0.1, 0.3, 0.5, 0.7, 0.9}; double quantiles2[] = {0.66666667}; rt_quantile quantile = NULL; - int count = 0; + uint32_t count = 0; rt_raster raster; rt_band band; @@ -1008,7 +1008,7 @@ static void testBandStats() { uint32_t values[] = {0, 91, 55, 86, 76, 41, 36, 97, 25, 63, 68, 2, 78, 15, 82, 47}; struct quantile_llist *qlls = NULL; - int qlls_count; + uint32_t qlls_count; raster = rt_raster_new(xmax, ymax); assert(raster); /* or we're out of virtual memory */ @@ -1387,7 +1387,7 @@ static void testValueCount() { uint32_t xmax = 100; uint32_t y; uint32_t ymax = 100; - int rtn = 0; + uint32_t rtn = 0; double count[] = {3, 4, 5}; @@ -1403,32 +1403,32 @@ static void testValueCount() { CHECK((rtn != -1)); } } - vcnts = rt_band_get_value_count(band, 1, NULL, 0, 0, &rtn); + vcnts = rt_band_get_value_count(band, 1, NULL, 0, 0, NULL, &rtn); CHECK(vcnts); CHECK((rtn > 0)); rtdealloc(vcnts); - vcnts = rt_band_get_value_count(band, 1, NULL, 0, 0.01, &rtn); + vcnts = rt_band_get_value_count(band, 1, NULL, 0, 0.01, NULL, &rtn); CHECK(vcnts); CHECK((rtn > 0)); rtdealloc(vcnts); - vcnts = rt_band_get_value_count(band, 1, NULL, 0, 0.1, &rtn); + vcnts = rt_band_get_value_count(band, 1, NULL, 0, 0.1, NULL, &rtn); CHECK(vcnts); CHECK((rtn > 0)); rtdealloc(vcnts); - vcnts = rt_band_get_value_count(band, 1, NULL, 0, 1, &rtn); + vcnts = rt_band_get_value_count(band, 1, NULL, 0, 1, NULL, &rtn); CHECK(vcnts); CHECK((rtn > 0)); rtdealloc(vcnts); - vcnts = rt_band_get_value_count(band, 1, NULL, 0, 10, &rtn); + vcnts = rt_band_get_value_count(band, 1, NULL, 0, 10, NULL, &rtn); CHECK(vcnts); CHECK((rtn > 0)); rtdealloc(vcnts); - vcnts = rt_band_get_value_count(band, 1, count, 3, 1, &rtn); + vcnts = rt_band_get_value_count(band, 1, count, 3, 1, NULL, &rtn); CHECK(vcnts); CHECK((rtn > 0)); rtdealloc(vcnts); -- 2.50.1