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;
}
