Hi all,
I’m facing several problems with the spacial extension of Monetdb.
Here are the basics about my environment: I am using MonetDB v11.17.21 (Jan2014-SP3) on a Suse Linux Enterprise Server 11 (x86_64, Patchlevel 2). I installed MonetDB by compiling from source as there is no Release for SLES available.
1) First of all, I had a glance at the documentation about the spatial functions [1]. I did need some time to realise that every function that is described with the prefix “ST_” in the documentation works without the prefix. Could somebody please change the documentation so that it is according to the sql functions?
2) I would like to use the “Intersects” function that says whether two geometries intersect or not intersect. Apparently my Monetdb-Server doesn’t know the function although he knows other ones like the Equals function. Let’s create two tables with Geometries, fill them with one Polygon each and then check whether the two Polygons intersect:
sql>create table one (idone INT, shape Polygon); sql>create table two (idtwo INT, figure Polygon); sql>insert into one values (0, GeomFromText('Polygon((0 0, 1 0, 1 1, 0 1, 0 0))', 4326)); sql>insert into two values (0, GeomFromText('Polygon((0 0, 2 0, 2 2, 0 2, 0 0))', 4326)); sql>select Intersects(shape, figure) from one join two ON idone = idtwo;
As result of the last query I receive the following: SELECT: no such binary operator 'intersects(polygon,polygon)’
I tried this on the sql console using mclient, I tried the same inside my python script using the mounted.sql API. Both times I received the same message.
3) I would like to measure the query execution time for all my queries. I found [2] where it is described how to measure the execution time. Following the steps and running the queries from the sql console, everything works fine. When I want to do the same inside my python script, I get an error on running the following statement:
select id, query, avg(run) from sys.querylog_history group by id,query;
This is the traceback, I skipped the parts of the trace that belong to my own code. The first line is the place where I try to access the sys.querylog_history table.
Traceback (most recent call last): ... File "/Users/leokotschenreuther/EPIC/GIS benchmark/db-benchmark/db/monetdb_db.py", line 109, in runQueries self.cursor.execute("select id, query, avg(run) from sys.querylog_history group by id,query") File "/usr/local/lib/python2.7/site-packages/monetdb/sql/cursors.py", line 170, in execute self.__store_result(block) File "/usr/local/lib/python2.7/site-packages/monetdb/sql/cursors.py", line 397, in __store_result values = self.__parse_tuple(line) File "/usr/local/lib/python2.7/site-packages/monetdb/sql/cursors.py", line 440, in __parse_tuple self.description)]) File "/usr/local/lib/python2.7/site-packages/monetdb/sql/pythonize.py", line 139, in convert raise ProgrammingError("type %s is not supported" % type_code) monetdb.exceptions.ProgrammingError: type oid is not supported
Does somebody know whether there is a chance to do the time measuring in python as well? I know that I could just use the time module in python but I want to measure the database server execution time.
Thanks for reading and helping!
Regards Leo
[1]: https://www.monetdb.org/Documentation/Extensions/GIS/SpatialFunctions https://www.monetdb.org/Documentation/Extensions/GIS/SpatialFunctions [2]: https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryHistoryNew https://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/QueryHistoryNew