Hello, I have a question for Geomodul. Is it possible to execute the following query with a LineString. This is my query with a point and the sample data set from the documentation. SELECT forests.name,buildings.name FROM forests,buildings WHERE "Intersect"(buildings.outline, Point(53.9,29.9)) = true; Now I want to replace the Point with a LineString like this in mysql. SELECT forests.name,buildings.name FROM forests,buildings WHERE "Intersect"(buildings.outline, LineString(Point(53.9,29.9), Point(53.0, 29.0))) = true; TestDatabase CREATE TABLE forests(id INT,name TEXT,shape MULTIPOLYGON); CREATE TABLE buildings(id INT,name TEXT,location POINT,outline POLYGON); INSERT INTO forests VALUES(109, 'Green Forest', 'MULTIPOLYGON( ((28 26,28 0,84 0,84 42,28 26), (52 18,66 23,73 9,48 6,52 18)), ((59 18,67 18,67 13,59 13,59 18)))'); INSERT INTO buildings VALUES(113, '123 Main Street', 'POINT( 52 30 )', 'POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) )'); INSERT INTO buildings VALUES(114, '215 Main Street', 'POINT( 64 33 )', 'POLYGON( ( 66 34, 62 34, 62 32, 66 32, 66 34) )'); SELECT forests.name,buildings.name FROM forests,buildings WHERE forests.name = 'Green Forest' and Overlaps(forests.shape, buildings.outline) = true; many thanks Dennis ________________________________ Avitech GmbH Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany Court Registration: Amtsgericht Ulm | HRB 728293 Gesch?ftsf?hrer/Managing Director: Antonio Maria Gonzalez Gorostiza VAT No.: DE223719716 http://avitech.aero
Hi Denis, You have a typo on your query. In order to create a LineString geometry from text you should the function linefromtext or geomfromtext. SELECT forests.name,buildings.name FROM forests,buildings WHERE "Intersect"(buildings.outline, LineFromText('LINESTRING(53.9 29.9, 53.0 29.0)', 0)) = true; or SELECT forests.name,buildings.name FROM forests,buildings WHERE "Intersect"(buildings.outline, GeomFromText('LINESTRING(53.9 29.9, 53.0 29.0)', 0)) = true; You can also check out which similar function are available by executing the following query: SELECT * FROM functions WHERE mod='geom' AND name LIKE '%fromtext%'; Cheers, Kostis On 15-06-15 10:26, Dennis Wiest wrote:
Hello,
I have a question for Geomodul. Is it possible to execute the following query with a LineString.
This is myquery with a point and the sample data set from the documentation.
SELECT forests.name,buildings.name FROM forests,buildings WHERE "Intersect"(buildings.outline, Point(53.9,29.9)) = true;
Now I want toreplace the Pointwith aLineString like this in mysql.
SELECT forests.name,buildings.name FROM forests,buildings WHERE "Intersect"(buildings.outline, LineString(Point(53.9,29.9), Point(53.0, 29.0))) = true;
TestDatabase
CREATE TABLE forests(id INT,name TEXT,shape MULTIPOLYGON);
CREATE TABLE buildings(id INT,name TEXT,location POINT,outline POLYGON);
INSERT INTO forests VALUES(109, 'Green Forest',
'MULTIPOLYGON( ((28 26,28 0,84 0,84 42,28 26), (52 18,66 23,73 9,48 6,52 18)), ((59 18,67 18,67 13,59 13,59 18)))');
INSERT INTO buildings VALUES(113, '123 Main Street',
'POINT( 52 30 )',
'POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) )');
INSERT INTO buildings VALUES(114, '215 Main Street',
'POINT( 64 33 )',
'POLYGON( ( 66 34, 62 34, 62 32, 66 32, 66 34) )');
SELECT forests.name,buildings.name
FROM forests,buildings
WHERE forests.name = 'Green Forest' and
Overlaps(forests.shape, buildings.outline) = true;
many thanks
Dennis
------------------------------------------------------------------------ Avitech GmbH Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany Court Registration: Amtsgericht Ulm | HRB 728293 Geschäftsführer/Managing Director: Antonio Maria Gonzalez Gorostiza VAT No.: DE223719716 http://avitech.aero
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- Kostis E. Kyzirakos, Ph.D. Centrum voor Wiskunde en Informatica DB Architectures (DA) Office L315 Science Park 123 1098 XG Amsterdam (NL) tel: +31 (20) 592-4034 mobile: +31 (0) 6422-95345 e-mail: Kostis.Kyzirakos@cwi.nl
Hello Kostis, perfect thanks :) Dennis -----Ursprüngliche Nachricht----- Von: users-list [mailto:users-list-bounces+dennis.wiest=avitech.aero@monetdb.org] Im Auftrag von Kostis Kyzirakos Gesendet: Montag, 15. Juni 2015 11:41 An: Communication channel for MonetDB users Betreff: Re: Geommodul and Linestring Query Hi Denis, You have a typo on your query. In order to create a LineString geometry from text you should the function linefromtext or geomfromtext. SELECT forests.name,buildings.name FROM forests,buildings WHERE "Intersect"(buildings.outline, LineFromText('LINESTRING(53.9 29.9, 53.0 29.0)', 0)) = true; or SELECT forests.name,buildings.name FROM forests,buildings WHERE "Intersect"(buildings.outline, GeomFromText('LINESTRING(53.9 29.9, 53.0 29.0)', 0)) = true; You can also check out which similar function are available by executing the following query: SELECT * FROM functions WHERE mod='geom' AND name LIKE '%fromtext%'; Cheers, Kostis On 15-06-15 10:26, Dennis Wiest wrote:
Hello,
I have a question for Geomodul. Is it possible to execute the following query with a LineString.
This is myquery with a point and the sample data set from the documentation.
SELECT forests.name,buildings.name FROM forests,buildings WHERE "Intersect"(buildings.outline, Point(53.9,29.9)) = true;
Now I want toreplace the Pointwith aLineString like this in mysql.
SELECT forests.name,buildings.name FROM forests,buildings WHERE "Intersect"(buildings.outline, LineString(Point(53.9,29.9), Point(53.0, 29.0))) = true;
TestDatabase
CREATE TABLE forests(id INT,name TEXT,shape MULTIPOLYGON);
CREATE TABLE buildings(id INT,name TEXT,location POINT,outline POLYGON);
INSERT INTO forests VALUES(109, 'Green Forest',
'MULTIPOLYGON( ((28 26,28 0,84 0,84 42,28 26), (52 18,66 23,73 9,48 6,52 18)), ((59 18,67 18,67 13,59 13,59 18)))');
INSERT INTO buildings VALUES(113, '123 Main Street',
'POINT( 52 30 )',
'POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) )');
INSERT INTO buildings VALUES(114, '215 Main Street',
'POINT( 64 33 )',
'POLYGON( ( 66 34, 62 34, 62 32, 66 32, 66 34) )');
SELECT forests.name,buildings.name
FROM forests,buildings
WHERE forests.name = 'Green Forest' and
Overlaps(forests.shape, buildings.outline) = true;
many thanks
Dennis
---------------------------------------------------------------------- -- Avitech GmbH Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany Court Registration: Amtsgericht Ulm | HRB 728293 Geschäftsführer/Managing Director: Antonio Maria Gonzalez Gorostiza VAT No.: DE223719716 http://avitech.aero
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- Kostis E. Kyzirakos, Ph.D. Centrum voor Wiskunde en Informatica DB Architectures (DA) Office L315 Science Park 123 1098 XG Amsterdam (NL) tel: +31 (20) 592-4034 mobile: +31 (0) 6422-95345 e-mail: Kostis.Kyzirakos@cwi.nl _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (2)
-
Dennis Wiest -
Kostis Kyzirakos