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