Hi, in short (sorry, no tiem for more right now ...), the MonetDB geom module is basically a sallow wrapper for the geos library. The alter takes care of constructing a Point(x,y) from longitude (x) and latitude (y). You could profile yoru queries by prefixig them with TRACE to check where time goes. Note also, that the Point representations of the geos library is quite large, i.e., in the order of 50 - 60 bytes per point; this is why converting a complete table takes that much space ... Stefan On Fri, Aug 06, 2010 at 03:19:11PM +0200, Mitja Kleider wrote:

Hi,

I am having trouble with the geom module (Jun2010-SP1). A query constructing a Point(x,y) from longitude (x) and latitude (y) takes much longer than expected, see below:

# MonetDB server v5.20.3, based on kernel v1.38.3 # Serving database 'germany3', using 2 threads # Compiled for x86_64-pc-linux-gnu/64bit with 64bit OIDs dynamically linked # MonetDB/SQL module v2.38.3 loaded # MonetDB/GIS module v0.18.3 loaded

sql>\d nodes CREATE TABLE "sys"."nodes" ( "id" int NOT NULL DEFAULT next value for "sys"."s_nodes", "long" decimal(10,7), "lat" decimal(10,7), CONSTRAINT "pk_nodes_id" PRIMARY KEY ("id") );

sql>select long,lat from nodes limit 1; +--------------+--------------+ | long | lat | +==============+==============+ | 8.9766191 | 47.7913748 | +--------------+--------------+ 1 tuple (1.0s)

sql>select point(8.9766191, 47.7913748); +------------------------------------------------+ | point_single_value | +================================================+ | POINT (8.9766191000000006 47.7913747999999998) | +------------------------------------------------+ 1 tuple (0.897ms)

sql>select point(long,lat) from nodes limit 1; +------------------------------------------------+ | point_long | +================================================+ | POINT (8.9766191000000006 47.7913747999999998) | +------------------------------------------------+ 1 tuple (1m 23s)

And executing something similar for the whole table caused a segfault after eating a lot of virtual memory:

sql>ALTER TABLE nodes ADD geom POINT DEFAULT NULL; sql>UPDATE nodes SET geom = Point(long,lat) WHERE geom IS NULL;

In case you would like to try yourself, here is the import script and the parser to get such a database:

http://gitorious.org/osm-poi-tools/monetdb/blobs/master/scripts/import.sh http://gitorious.org/osm-poi-tools/monetdb/blobs/master/osmparser/src/osmpar...

Thanks for your help, Mitja

------------------------------------------------------------------------------ This SF.net email is sponsored by

Make an app they can't live without Enter the BlackBerry Developer Challenge http://p.sf.net/sfu/RIM-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4199 |