void ows_layers_storage_fill(ows * o) { PGresult *res, *res_g; ows_layer_node *ln; bool filled; buffer *sql; int i, end; assert(o); assert(o->layers); sql = buffer_init(); buffer_add_str(sql, "SELECT DISTINCT f_table_schema, f_table_name FROM geometry_columns"); res = ows_psql_exec(o, sql->buf); buffer_empty(sql); buffer_add_str(sql, "SELECT DISTINCT f_table_schema, f_table_name FROM geography_columns"); res_g = ows_psql_exec(o, sql->buf); buffer_free(sql); for (ln = o->layers->first ; ln ; ln = ln->next) { filled = false; for (i = 0, end = PQntuples(res); i < end; i++) { if ( buffer_cmp(ln->layer->storage->schema, (char *) PQgetvalue(res, i, 0)) && buffer_cmp(ln->layer->storage->table, (char *) PQgetvalue(res, i, 1))) { ows_layer_storage_fill(o, ln->layer, true); filled = true; } } for (i = 0, end = PQntuples(res_g); i < end; i++) { if ( buffer_cmp(ln->layer->storage->schema, (char *) PQgetvalue(res_g, i, 0)) && buffer_cmp(ln->layer->storage->table, (char *) PQgetvalue(res_g, i, 1))) { ows_layer_storage_fill(o, ln->layer, false); filled = true; } } if (!filled) { if (ln->layer->storage) ows_layer_storage_free(ln->layer->storage); ln->layer->storage = NULL; } } PQclear(res); PQclear(res_g); }
/* * Return the column's name matching the specified number from table * (Only use in specific FE position function, so not directly inside * storage handle mechanism) */ buffer *ows_psql_column_name(ows * o, buffer * layer_name, int number) { buffer *sql; PGresult *res; buffer *column; assert(o); assert(layer_name); sql = buffer_init(); column = buffer_init(); buffer_add_str(sql, "SELECT a.attname FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname ='"); buffer_copy(sql, layer_name); buffer_add_str(sql, "' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid AND a.attnum = "); buffer_add_int(sql, number); res = ows_psql_exec(o, sql->buf); buffer_free(sql); if (PQresultStatus(res) != PGRES_TUPLES_OK || PQntuples(res) != 1) { PQclear(res); return column; } buffer_add_str(column, PQgetvalue(res, 0, 0)); PQclear(res); return column; }
/* * Returns the constraint name for a table column. * Used to return enumeration constraints in describe feature type request. */ buffer *ows_psql_column_constraint_name(ows * o, buffer * column_name, buffer * table_name) { buffer *sql; PGresult *res; buffer *constraint_name; constraint_name = buffer_init(); assert(o); assert(column_name); assert(table_name); sql = buffer_init(); buffer_add_str(sql, "SELECT constraint_name FROM information_schema.constraint_column_usage WHERE table_name = '"); buffer_add_str(sql, table_name->buf); buffer_add_str(sql, "' AND column_name='"); buffer_add_str(sql, column_name->buf); buffer_add_str(sql, "'"); res = ows_psql_exec(o, sql->buf); if (PQresultStatus(res) != PGRES_TUPLES_OK || PQntuples(res) != 1) { PQclear(res); return constraint_name; } buffer_add_str(constraint_name, PQgetvalue(res, 0, 0)); PQclear(res); return constraint_name; }
/* * Returns the column character_maximum_length value from the database * information schema. * Used to return maxLenght constraint in describe feature type request. */ buffer *ows_psql_column_character_maximum_length(ows * o, buffer * column_name, buffer * table_name) { buffer *sql; PGresult *res; buffer *character_maximum_length; character_maximum_length = buffer_init(); assert(o); assert(column_name); assert(table_name); sql = buffer_init(); buffer_add_str(sql, "SELECT character_maximum_length FROM information_schema.columns WHERE table_name = '"); buffer_add_str(sql, table_name->buf); buffer_add_str(sql, "' and column_name = '"); buffer_add_str(sql, column_name->buf); buffer_add_str(sql, "'"); res = ows_psql_exec(o, sql->buf); buffer_free(sql); if (PQresultStatus(res) != PGRES_TUPLES_OK || PQntuples(res) != 1) { PQclear(res); return character_maximum_length; } buffer_add_str(character_maximum_length, PQgetvalue(res, 0, 0)); PQclear(res); return character_maximum_length; }
/* * Retrieve an ows_version, related to current PostGIS version. */ ows_version * ows_psql_postgis_version(ows *o) { list *l; PGresult * res; ows_version * v = NULL; res = ows_psql_exec(o, "SELECT substr(postgis_full_version(), 10, 5)"); if (PQresultStatus(res) != PGRES_TUPLES_OK || PQntuples(res) != 1) { PQclear(res); return NULL; } l = list_explode_str('.', (char *) PQgetvalue(res, 0, 0)); if ( l->size == 3 && check_regexp(l->first->value->buf, "^[0-9]+$") && check_regexp(l->first->next->value->buf, "^[0-9]+$") && check_regexp(l->last->value->buf, "^[0-9]+$") ) { v = ows_version_init(); v->major = atoi(l->first->value->buf); v->minor = atoi(l->first->next->value->buf); v->release = atoi(l->last->value->buf); } list_free(l); PQclear(res); return v; }
/* * Retrieve a srs from a srid */ buffer *ows_srs_get_from_a_srid(ows * o, int srid) { buffer *b; buffer *sql; PGresult *res; assert(o); sql = buffer_init(); buffer_add_str(sql, "SELECT auth_name||':'||auth_srid AS srs "); buffer_add_str(sql, "FROM spatial_ref_sys "); buffer_add_str(sql, "WHERE srid="); buffer_add_int(sql, srid); res = ows_psql_exec(o, sql->buf); buffer_free(sql); b = buffer_init(); if (PQresultStatus(res) != PGRES_TUPLES_OK || PQntuples(res) != 1) { PQclear(res); return b; } buffer_add_str(b, PQgetvalue(res, 0, 0)); PQclear(res); return b; }
/* * Transform a bbox from initial srid to another srid passed in parameter */ bool ows_bbox_transform(ows * o, ows_bbox * bb, int srid) { buffer *sql; PGresult *res; assert(o); assert(bb); sql = buffer_init(); buffer_add_str(sql, "SELECT xmin(g), ymin(g), xmax(g), ymax(g) FROM (SELECT ST_Transform("); ows_bbox_to_query(o, bb, sql); buffer_add_str(sql, ")) AS g ) AS foo"); res = ows_psql_exec(o, sql->buf); buffer_free(sql); if (PQresultStatus(res) != PGRES_TUPLES_OK) { PQclear(res); return false; } bb->xmin = strtod(PQgetvalue(res, 0, 0), NULL); bb->ymin = strtod(PQgetvalue(res, 0, 1), NULL); bb->xmax = strtod(PQgetvalue(res, 0, 2), NULL); bb->ymax = strtod(PQgetvalue(res, 0, 3), NULL); /* TODO Error Handling */ return ows_srs_set_from_srid(o, bb->srs, srid); }
/* * Set projection value into srs structure */ bool ows_srs_set_from_srid(ows * o, ows_srs * s, int srid) { PGresult *res; buffer *sql; assert(o); assert(s); if (srid == -1 || srid == 0) { s->srid = -1; buffer_empty(s->auth_name); s->auth_srid = 0; s->is_degree = true; s->is_reverse_axis = false; s->is_eastern_axis = false; return true; } sql = buffer_init(); buffer_add_str(sql, "SELECT auth_name, auth_srid, "); buffer_add_str(sql, "position('+units=m ' in proj4text), "); buffer_add_str(sql, "(position('AXIS[\"X\",NORTH]]' in srtext) + position('AXIS[\"Northing\",NORTH]]' in srtext)) "); buffer_add_str(sql, "FROM spatial_ref_sys WHERE srid = '"); buffer_add_int(sql, srid); buffer_add_str(sql, "'"); res = ows_psql_exec(o, sql->buf); buffer_free(sql); /* If query dont return exactly 1 result, it mean projection not handled */ if (PQresultStatus(res) != PGRES_TUPLES_OK || PQntuples(res) != 1) { PQclear(res); return false; } buffer_add_str(s->auth_name, PQgetvalue(res, 0, 0)); s->auth_srid = atoi(PQgetvalue(res, 0, 1)); s->srid = srid; /* Such a way to know if units is meter or degree */ if (atoi(PQgetvalue(res, 0, 2)) == 0) s->is_degree = true; else s->is_degree = false; /* Is easting-northing SRID ? */ if (atoi(PQgetvalue(res, 0, 3)) != 0) s->is_eastern_axis = true; PQclear(res); return true; }
/* * Returns the list of possible values for a column according to the constraint value. * Used to return enumeration constraints in describe feature type request. */ list *ows_psql_column_check_constraint(ows * o, buffer * constraint_name) { buffer *sql; PGresult *res; list *intermediate_constraints; list *constraints; buffer *constraint_value; buffer *buf; size_t i; list_node *ln; constraints = list_init(); intermediate_constraints = list_init(); constraint_value = buffer_init(); assert(o); assert(constraint_name); sql = buffer_init(); buffer_add_str(sql, "SELECT check_clause FROM information_schema.check_constraints WHERE constraint_name = '"); buffer_add_str(sql, constraint_name->buf); buffer_add_str(sql, "'"); res = ows_psql_exec(o, sql->buf); if (PQresultStatus(res) != PGRES_TUPLES_OK || PQntuples(res) != 1) { PQclear(res); return constraints; } buffer_add_str(constraint_value, PQgetvalue(res, 0, 0)); PQclear(res); intermediate_constraints = list_explode(' ', constraint_value); for (ln = intermediate_constraints->first ; ln ; ln = ln->next) { if(ln->value->buf[0] == '\'') { buf = buffer_init(); for (i = 1; ln->value->buf[i] != '\0'; i++) { if(ln->value->buf[i] == '\'') break; else buffer_add(buf, ln->value->buf[i]); } list_add(constraints, buf); } } return constraints; }
/* * Set projection value into srs structure */ bool ows_srs_set(ows * o, ows_srs * c, const buffer * auth_name, int auth_srid) { PGresult *res; buffer *sql; assert(o); assert(c); assert(o->pg); assert(auth_name); sql = buffer_init(); buffer_add_str(sql, "SELECT srid, position('+units=m ' in proj4text)"); buffer_add_str(sql, ", (position('AXIS[\"X\",NORTH]]' in srtext) + position('AXIS[\"Northing\",NORTH]]' in srtext))"); buffer_add_str(sql, " FROM spatial_ref_sys WHERE auth_name='"); buffer_copy(sql, auth_name); buffer_add_str(sql, "' AND auth_srid="); buffer_add_int(sql, auth_srid); res = ows_psql_exec(o, sql->buf); buffer_free(sql); /* If query dont return exactly 1 result, it means projection is not handled */ if (PQresultStatus(res) != PGRES_TUPLES_OK || PQntuples(res) != 1) { PQclear(res); return false; } buffer_empty(c->auth_name); buffer_copy(c->auth_name, auth_name); c->auth_srid = auth_srid; c->srid = atoi(PQgetvalue(res, 0, 0)); /* Such a way to know if units is meter or degree */ if (atoi(PQgetvalue(res, 0, 1)) == 0) c->is_degree = true; else c->is_degree = false; /* Is easting-northing SRID ? */ if (atoi(PQgetvalue(res, 0, 2)) != 0) c->is_eastern_axis = true; PQclear(res); return true; }
/* * Return the number of rows returned by the specified requests */ int ows_psql_number_features(ows * o, list * from, list * where) { buffer *sql; PGresult *res; list_node *ln_from, *ln_where; int nb; assert(o); assert(from); assert(where); nb = 0; /* checks if from list and where list have the same size */ if (from->size != where->size) return nb; for (ln_from = from->first, ln_where = where->first; ln_from; ln_from = ln_from->next, ln_where = ln_where->next) { sql = buffer_init(); /* execute the request */ buffer_add_str(sql, "SELECT count(*) FROM \""); buffer_copy(sql, ln_from->value); buffer_add_str(sql, "\" "); buffer_copy(sql, ln_where->value); res = ows_psql_exec(o, sql->buf); buffer_free(sql); if (PQresultStatus(res) != PGRES_TUPLES_OK) { PQclear(res); return -1; } nb = nb + atoi(PQgetvalue(res, 0, 0)); PQclear(res); } return nb; }
/* * Retrieve not_null columns of a table related a given layer */ static void ows_storage_fill_not_null(ows * o, ows_layer * l) { int i, nb_result; buffer *sql, *b; PGresult *res; assert(o); assert(l); assert(l->storage); sql = buffer_init(); buffer_add_str(sql, "SELECT a.attname AS field FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n "); buffer_add_str(sql, "WHERE n.nspname = '"); buffer_copy(sql, l->storage->schema); buffer_add_str(sql, "' AND c.relname = '"); buffer_copy(sql, l->storage->table); buffer_add_str(sql, "' AND c.relnamespace = n.oid AND a.attnum > 0 AND a.attrelid = c.oid "); buffer_add_str(sql, "AND a.atttypid = t.oid AND a.attnotnull = 't'"); res = ows_psql_exec(o, sql->buf); buffer_free(sql); if (PQresultStatus(res) != PGRES_TUPLES_OK) { PQclear(res); ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to access pg_* tables.", "not_null columns"); return; } nb_result = PQntuples(res); if (nb_result) l->storage->not_null_columns = list_init(); for (i = 0 ; i < nb_result ; i++) { b = buffer_init(); buffer_add_str(b, PQgetvalue(res, i, 0)); list_add(l->storage->not_null_columns, b); } PQclear(res); }
/* * Check if a given WKT geometry is or not valid */ bool ows_psql_is_geometry_valid(ows * o, buffer * geom) { buffer *sql; PGresult *res; bool ret = false; assert(o); assert(geom); sql = buffer_init(); buffer_add_str(sql, "SELECT ST_isvalid(ST_geometryfromtext('"); buffer_copy(sql, geom); buffer_add_str(sql, "', -1));"); res = ows_psql_exec(o, sql->buf); buffer_free(sql); if (PQresultStatus(res) == PGRES_TUPLES_OK && PQntuples(res) == 1 && (char) PQgetvalue(res, 0, 0)[0] == 't') ret = true; PQclear(res); return ret; }
/* * Return SRID from a given geometry */ int ows_psql_geometry_srid(ows *o, const char *geom) { int srid; buffer *sql; PGresult *res; assert(o); assert(o->pg); assert(geom); sql = buffer_from_str("SELECT ST_SRID('"); buffer_add_str(sql, geom); buffer_add_str(sql, "'::geometry)"); res = ows_psql_exec(o, sql->buf); if (PQresultStatus(res) != PGRES_TUPLES_OK || PQntuples(res) != 1) srid = -1; else srid = atoi((char *) PQgetvalue(res, 0, 0)); buffer_free(sql); PQclear(res); return srid; }
/* * Transform a GML geometry to PostGIS EWKT * Return NULL on error */ buffer * ows_psql_gml_to_sql(ows * o, xmlNodePtr n, int srid) { PGresult *res; xmlNodePtr g; buffer *result, *sql, *gml; assert(o); assert(n); g = ows_psql_recursive_parse_gml(o, n, NULL); if (!g) return NULL; /* No Geometry founded in GML doc */ /* Retrieve the sub doc and launch GML parse via PostGIS */ gml = buffer_init(); cgi_add_xml_into_buffer(gml, g); sql = buffer_init(); buffer_add_str(sql, "SELECT ST_GeomFromGML('"); buffer_add_str(sql, gml->buf); if (ows_version_get(o->postgis_version) >= 200) { buffer_add_str(sql, "',"); buffer_add_int(sql, srid); buffer_add_str(sql, ")"); } else { /* Means PostGIS 1.5 */ buffer_add_str(sql, "')"); } res = ows_psql_exec(o, sql->buf); buffer_free(gml); /* GML Parse errors cases */ if (PQresultStatus(res) != PGRES_TUPLES_OK || PQntuples(res) != 1) { buffer_free(sql); PQclear(res); return NULL; } result = buffer_init(); buffer_add_str(result, PQgetvalue(res, 0, 0)); PQclear(res); /* Check if geometry is valid */ if (o->check_valid_geom) { buffer_empty(sql); buffer_add_str(sql, "SELECT ST_IsValid('"); buffer_add_str(sql, result->buf); buffer_add_str(sql, "')"); res = ows_psql_exec(o, sql->buf); if ( PQresultStatus(res) != PGRES_TUPLES_OK || PQntuples(res) != 1 || (char) PQgetvalue(res, 0, 0)[0] != 't') { buffer_free(sql); buffer_free(result); PQclear(res); return NULL; } PQclear(res); } buffer_free(sql); return result; }
/* * Generate a new buffer id supposed to be unique for a given layer name */ buffer *ows_psql_generate_id(ows * o, buffer * layer_name) { ows_layer_node *ln; buffer * id, *sql_id; FILE *fp; PGresult * res; int i, seed_len; char * seed = NULL; assert(o); assert(o->layers); assert(layer_name); /* Retrieve layer node pointer */ for (ln = o->layers->first ; ln ; ln = ln->next) { if (ln->layer->name && ln->layer->storage && !strcmp(ln->layer->name->buf, layer_name->buf)) break; } assert(ln); id = buffer_init(); /* If PK have a sequence in PostgreSQL database, * retrieve next available sequence value */ if (ln->layer->storage->pkey_sequence) { sql_id = buffer_init(); buffer_add_str(sql_id, "SELECT nextval('"); buffer_copy(sql_id, ln->layer->storage->pkey_sequence); buffer_add_str(sql_id, "');"); res = ows_psql_exec(o, sql_id->buf); buffer_free(sql_id); if (PQresultStatus(res) == PGRES_TUPLES_OK && PQntuples(res) == 1) { buffer_add_str(id, (char *) PQgetvalue(res, 0, 0)); PQclear(res); return id; } /* FIXME: Shouldn't we return an error there instead ? */ PQclear(res); } /* * If we don't have a PostgreSQL Sequence, we will try to * generate a pseudo random keystring using /dev/urandom * Will so work only on somes/commons Unix system */ seed_len = 6; seed = malloc(sizeof(char) * (seed_len * 3 + 1)); /* multiply by 3 to be able to deal with hex2dec conversion */ assert(seed); seed[0] = '\0'; fp = fopen("/dev/urandom","r"); if (fp) { for (i=0 ; i<seed_len ; i++) sprintf(seed,"%s%03d", seed, fgetc(fp)); fclose(fp); buffer_add_str(id, seed); free(seed); return id; } free(seed); /* Case where we not using PostgreSQL sequence, * and OS don't have a /dev/urandom support * This case don't prevent to produce ID collision * Don't use it unless really no others choices !!! */ srand((int) (time(NULL) ^ rand() % 1000) + 42); srand((rand() % 1000 ^ rand() % 1000) + 42); buffer_add_int(id, rand()); return id; }
/* * Retrieve pkey column of a table related a given layer * And if success try also to retrieve a related pkey sequence */ static void ows_storage_fill_pkey(ows * o, ows_layer * l) { buffer *sql; PGresult *res; assert(o); assert(l); assert(l->storage); sql = buffer_init(); buffer_add_str(sql, "SELECT c.column_name FROM information_schema.constraint_column_usage c, pg_namespace n "); buffer_add_str(sql, "WHERE n.nspname = '"); buffer_copy(sql, l->storage->schema); buffer_add_str(sql, "' AND c.table_name = '"); buffer_copy(sql, l->storage->table); buffer_add_str(sql, "' AND c.constraint_name = ("); buffer_add_str(sql, "SELECT c.conname FROM pg_class r, pg_constraint c, pg_namespace n "); buffer_add_str(sql, "WHERE r.oid = c.conrelid AND relname = '"); buffer_copy(sql, l->storage->table); buffer_add_str(sql, "' AND r.relnamespace = n.oid AND n.nspname = '"); buffer_copy(sql, l->storage->schema); buffer_add_str(sql, "' AND c.contype = 'p')"); res = ows_psql_exec(o, sql->buf); if (PQresultStatus(res) != PGRES_TUPLES_OK) { PQclear(res); buffer_free(sql); ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to access pg_* tables.", "pkey column"); return; } /* Layer could have no Pkey indeed... (An SQL view for example) */ if (l->pkey || PQntuples(res) == 1) { l->storage->pkey = buffer_init(); if (l->pkey) { /*TODO check the column (l->pkey) in the table */ buffer_copy(l->storage->pkey, l->pkey); } else { buffer_add_str(l->storage->pkey, PQgetvalue(res, 0, 0)); } buffer_empty(sql); PQclear(res); /* Retrieve the Pkey column number */ buffer_add_str(sql, "SELECT a.attnum FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n"); buffer_add_str(sql, " WHERE a.attrelid = c.oid AND a.atttypid = t.oid AND n.nspname='"); buffer_copy(sql, l->storage->schema); buffer_add_str(sql, "' AND c.relname='"); buffer_copy(sql, l->storage->table); buffer_add_str(sql, "' AND a.attname='"); buffer_copy(sql, l->storage->pkey); buffer_add_str(sql, "'"); res = ows_psql_exec(o, sql->buf); if (PQresultStatus(res) != PGRES_TUPLES_OK) { PQclear(res); buffer_free(sql); ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to find pkey column number.", "pkey_column number"); return; } /* -1 because column number start at 1 */ l->storage->pkey_column_number = atoi(PQgetvalue(res, 0, 0)) - 1; buffer_empty(sql); PQclear(res); /* Now try to find a sequence related to this Pkey */ buffer_add_str(sql, "SELECT pg_get_serial_sequence('"); buffer_copy(sql, l->storage->schema); buffer_add_str(sql, ".\""); buffer_copy(sql, l->storage->table); buffer_add_str(sql, "\"', '"); buffer_copy(sql, l->storage->pkey); buffer_add_str(sql, "');"); res = ows_psql_exec(o, sql->buf); if (PQresultStatus(res) != PGRES_TUPLES_OK) { PQclear(res); buffer_free(sql); ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to use pg_get_serial_sequence.", "pkey_sequence retrieve"); return; } /* Even if no sequence found, this function return an empty row * so we must check that result string returned > 0 char */ if (PQntuples(res) == 1 && strlen((char *) PQgetvalue(res, 0, 0)) > 0) { l->storage->pkey_sequence = buffer_init(); buffer_add_str(l->storage->pkey_sequence, PQgetvalue(res, 0, 0)); } buffer_empty(sql); PQclear(res); /* Now try to find a DEFAULT value related to this Pkey */ buffer_add_str(sql, "SELECT column_default FROM information_schema.columns WHERE table_schema = '"); buffer_copy(sql, l->storage->schema); buffer_add_str(sql, "' AND table_name = '"); buffer_copy(sql, l->storage->table); buffer_add_str(sql, "' AND column_name = '"); buffer_copy(sql, l->storage->pkey); buffer_add_str(sql, "' AND table_catalog = current_database();"); res = ows_psql_exec(o, sql->buf); if (PQresultStatus(res) != PGRES_TUPLES_OK) { PQclear(res); buffer_free(sql); ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to SELECT column_default FROM information_schema.columns.", "pkey_default retrieve"); return; } /* Even if no DEFAULT value found, this function return an empty row * so we must check that result string returned > 0 char */ if (PQntuples(res) == 1 && strlen((char *) PQgetvalue(res, 0, 0)) > 0) { l->storage->pkey_default = buffer_init(); buffer_add_str(l->storage->pkey_default, PQgetvalue(res, 0, 0)); } } PQclear(res); buffer_free(sql); }
/* * Retrieve columns name and type of a table related a given layer */ static void ows_storage_fill_attributes(ows * o, ows_layer * l) { buffer *sql; PGresult *res; buffer *b, *t; int i, end; list_node *ln; assert(o); assert(l); assert(l->storage); sql = buffer_init(); buffer_add_str(sql, "SELECT a.attname AS field, t.typname AS type "); buffer_add_str(sql, "FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n WHERE n.nspname = '"); buffer_copy(sql, l->storage->schema); buffer_add_str(sql, "' AND c.relname = '"); buffer_copy(sql, l->storage->table); buffer_add_str(sql, "' AND c.relnamespace = n.oid AND a.attrelid = c.oid AND a.atttypid = t.oid"); if (l->include_items) { buffer_add_str(sql, " AND a.attname IN ("); for (ln = l->include_items->first ; ln ; ln = ln->next) { buffer_add_str(sql, "'"); buffer_copy(sql, ln->value); buffer_add_str(sql, "', "); } if (l->include_items->first && l->storage->pkey) { buffer_add_str(sql, "'"); buffer_copy(sql, l->storage->pkey ); buffer_add_str(sql, "',"); } buffer_add_str(sql, " '');"); } else { buffer_add_str(sql, " AND a.attnum > 0;"); } res = ows_psql_exec(o, sql->buf); buffer_free(sql); if (PQresultStatus(res) != PGRES_TUPLES_OK) { PQclear(res); ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to access pg_* tables.", "fill_attributes"); return; } for (i = 0, end = PQntuples(res); i < end; i++) { b = buffer_init(); t = buffer_init(); buffer_add_str(b, PQgetvalue(res, i, 0)); buffer_add_str(t, PQgetvalue(res, i, 1)); /* If the column is a geometry, get its real geometry type */ if (buffer_cmp(t, "geometry")) { PGresult *geom_res; buffer *geom_sql = buffer_init(); buffer_add_str(geom_sql, "SELECT type from geometry_columns where f_table_schema='"); buffer_copy(geom_sql, l->storage->schema); buffer_add_str(geom_sql,"' and f_table_name='"); buffer_copy(geom_sql, l->storage->table); buffer_add_str(geom_sql,"' and f_geometry_column='"); buffer_copy(geom_sql, b); buffer_add_str(geom_sql,"';"); geom_res = ows_psql_exec(o, geom_sql->buf); buffer_free(geom_sql); if (PQresultStatus(geom_res) != PGRES_TUPLES_OK || PQntuples(geom_res) == 0) { PQclear(res); PQclear(geom_res); ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to access geometry_columns table, try Populate_Geometry_Columns()", "fill_attributes"); return; } buffer_empty(t); buffer_add_str(t, PQgetvalue(geom_res, 0, 0)); PQclear(geom_res); } array_add(l->storage->attributes, b, t); } PQclear(res); }
static void ows_layer_storage_fill(ows * o, ows_layer * l, bool is_geom) { buffer * sql; PGresult *res; int i, end; assert(o); assert(l); assert(l->storage); sql = buffer_init(); if (is_geom) buffer_add_str(sql, "SELECT srid, f_geometry_column FROM geometry_columns"); else buffer_add_str(sql, "SELECT srid, f_geography_column FROM geography_columns"); buffer_add_str(sql, " WHERE f_table_schema='"); buffer_copy(sql, l->storage->schema); buffer_add_str(sql, "' AND f_table_name='"); buffer_copy(sql, l->storage->table); buffer_add_str(sql, "'"); if (l->include_items) { buffer_add_str(sql, is_geom?" AND f_geometry_column IN ('":" AND f_geography_column IN ('"); list_implode(sql, "','", l->include_items); buffer_add_str(sql, "')"); } if (l->exclude_items) { buffer_add_str(sql, is_geom?" AND f_geometry_column NOT IN ('":" AND f_geography_column NOT IN ('"); list_implode(sql, "','", l->exclude_items); buffer_add_str(sql, "')"); } buffer_add_str(sql, ";"); res = ows_psql_exec(o, sql->buf); buffer_empty(sql); if (PQresultStatus(res) != PGRES_TUPLES_OK || PQntuples(res) == 0) { PQclear(res); ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "All config file layers are not availables in geometry_columns or geography_columns", "storage"); return; } l->storage->srid = atoi(PQgetvalue(res, 0, 0)); for (i = 0, end = PQntuples(res); i < end; i++) list_add_str(l->storage->geom_columns, PQgetvalue(res, i, 1)); buffer_add_str(sql, "SELECT * FROM spatial_ref_sys WHERE srid="); buffer_add_str(sql, PQgetvalue(res, 0, 0)); buffer_add_str(sql, " AND proj4text like '%%units=m%%'"); PQclear(res); res = ows_psql_exec(o, sql->buf); buffer_free(sql); if (PQntuples(res) != 1) l->storage->is_degree = true; else l->storage->is_degree = false; PQclear(res); ows_storage_fill_pkey(o, l); ows_storage_fill_attributes(o, l); ows_storage_fill_not_null(o, l); }
/* * Set a geobbox matching a layer's extent */ ows_geobbox *ows_geobbox_compute(ows * o, buffer * layer_name) { double xmin, ymin, xmax, ymax; buffer *sql; PGresult *res; ows_geobbox *g; ows_bbox *bb; list *geom; list_node *ln; bool first = true; assert(o); assert(layer_name); sql = buffer_init(); geom = ows_psql_geometry_column(o, layer_name); assert(geom); g = ows_geobbox_init(); xmin = ymin = xmax = ymax = 0.0; for (ln = geom->first; ln ; ln = ln->next) { buffer_add_str(sql, "SELECT ST_xmin(g), ST_ymin(g), ST_xmax(g), ST_ymax(g) FROM "); if (o->estimated_extent) { buffer_add_str(sql, "(SELECT ST_Transform(ST_SetSRID(ST_Estimated_Extent('"); buffer_copy(sql, ows_psql_schema_name(o, layer_name)); buffer_add_str(sql, "','"); buffer_copy(sql, ows_psql_table_name(o, layer_name)); buffer_add_str(sql, "','"); buffer_copy(sql, ln->value); buffer_add_str(sql, "'), (SELECT ST_SRID(\""); buffer_copy(sql, ln->value); buffer_add_str(sql, "\") FROM "); buffer_copy(sql, ows_psql_schema_name(o, layer_name)); buffer_add_str(sql, ".\""); buffer_copy(sql, ows_psql_table_name(o, layer_name)); buffer_add_str(sql, "\" LIMIT 1)) ,4326) AS g) AS foo"); } else { buffer_add_str(sql, "(SELECT ST_Transform(ST_SetSRID(ST_Extent(\""); buffer_copy(sql, ln->value); buffer_add_str(sql, "\"), (SELECT ST_SRID(\""); buffer_copy(sql, ln->value); buffer_add_str(sql, "\") FROM "); buffer_copy(sql, ows_psql_schema_name(o, layer_name)); buffer_add_str(sql, ".\""); buffer_copy(sql, ows_psql_table_name(o, layer_name)); buffer_add_str(sql, "\" LIMIT 1)), 4326) AS g "); buffer_add_str(sql, " FROM "); buffer_copy(sql, ows_psql_schema_name(o, layer_name)); buffer_add_str(sql, ".\""); buffer_copy(sql, ows_psql_table_name(o, layer_name)); buffer_add_str(sql, "\" ) AS foo"); } res = ows_psql_exec(o, sql->buf); buffer_empty(sql); if (PQresultStatus(res) != PGRES_TUPLES_OK) { PQclear(res); buffer_free(sql); return g; } if (first || atof(PQgetvalue(res, 0, 0)) < xmin) xmin = atof(PQgetvalue(res, 0, 0)); if (first || atof(PQgetvalue(res, 0, 1)) < ymin) ymin = atof(PQgetvalue(res, 0, 1)); if (first || atof(PQgetvalue(res, 0, 2)) > xmax) xmax = atof(PQgetvalue(res, 0, 2)); if (first || atof(PQgetvalue(res, 0, 3)) > ymax) ymax = atof(PQgetvalue(res, 0, 3)); first = false; PQclear(res); } buffer_free(sql); bb = ows_bbox_init(); ows_bbox_set(o, bb, xmin, ymin, xmax, ymax, 4326); ows_geobbox_set_from_bbox(o, g, bb); ows_bbox_free(bb); return g; }
/* * Set a given bbox matching a feature collection's outerboundaries * or a simple feature's outerboundaries * Bbox is set from a list containing one or several layer names * and optionnaly one or several WHERE SQL statement following each layer name */ ows_bbox *ows_bbox_boundaries(ows * o, list * from, list * where, ows_srs * srs) { ows_bbox *bb; buffer *sql; list *geom; list_node *ln_from, *ln_where, *ln_geom; PGresult *res; assert(o); assert(from); assert(where); assert(srs); bb = ows_bbox_init(); if (from->size != where->size) return bb; sql = buffer_init(); /* Put into a buffer the SQL request calculating an extent */ buffer_add_str(sql, "SELECT ST_xmin(g.extent), ST_ymin(g.extent), ST_xmax(g.extent), ST_ymax(g.extent) FROM "); buffer_add_str(sql, "(SELECT ST_Extent(foo.the_geom) as extent FROM ( "); /* For each layer name or each geometry column, make an union between retrieved features */ for (ln_from = from->first, ln_where = where->first; ln_from ; ln_from = ln_from->next, ln_where = ln_where->next) { geom = ows_psql_geometry_column(o, ln_from->value); for (ln_geom = geom->first ; ln_geom ; ln_geom = ln_geom->next) { buffer_add_str(sql, " (SELECT ST_Transform(\""); buffer_copy(sql, ln_geom->value); buffer_add_str(sql, "\"::geometry, "); buffer_add_int(sql, srs->srid); buffer_add_str(sql, ") AS \"the_geom\" FROM "); buffer_copy(sql, ows_psql_schema_name(o, ln_from->value)); buffer_add_str(sql, ".\""); buffer_copy(sql, ows_psql_table_name(o, ln_from->value)); buffer_add_str(sql, "\" "); buffer_copy(sql, ln_where->value); buffer_add_str(sql, ")"); if (ln_geom->next) buffer_add_str(sql, " UNION ALL "); } if (ln_from->next) buffer_add_str(sql, " UNION ALL "); } buffer_add_str(sql, " ) AS foo) AS g"); res = ows_psql_exec(o, sql->buf); buffer_free(sql); if (PQresultStatus(res) != PGRES_TUPLES_OK && PQntuples(res) != 4) { PQclear(res); return bb; } bb->xmin = strtod(PQgetvalue(res, 0, 0), NULL); bb->ymin = strtod(PQgetvalue(res, 0, 1), NULL); bb->xmax = strtod(PQgetvalue(res, 0, 2), NULL); bb->ymax = strtod(PQgetvalue(res, 0, 3), NULL); /* TODO Error handling */ ows_srs_copy(bb->srs, srs); PQclear(res); return bb; }