babelfish_extensions/test/JDBC/expected/Test-spatial-functions-2-vu-prepare.out at 8a6544d0362231150cc0abcc52e20d1aca2cedb5 · babelfish-for-postgresql/babelfish_extensions · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
# This file tests support for these functions: STDimension, STDisjoint, STIntersects, STIsClosed, STIsEmpty, STIsValid

CREATE DATABASE TestGeospatialMethods_DB;

USE TestGeospatialMethods_DB;

CREATE TABLE TestGeospatialMethods_YourTable1Temp ( ID INT PRIMARY KEY, PointColumn geometry );
INSERT INTO TestGeospatialMethods_YourTable1Temp (ID, PointColumn) VALUES (1, geometry::Point(3.0, 4.0, 4326)), (2, geometry::Point(5.0, 6.0, 4326)), (3, geometry::Point(3.0, 4.0, 0)), (4, geometry::STPointFromText('POINT EMPTY', 4123));
~~ROW COUNT: 4~~


CREATE TABLE TestGeospatialMethods_YourTable3Temp ( ID INT PRIMARY KEY, PointColumn geography );
INSERT INTO TestGeospatialMethods_YourTable3Temp (ID, PointColumn) VALUES (1, geography::Point(3.0, 4.0, 4326)), (2, geography::Point(5.0, 6.0, 4121)), (3, geography::Point(3.0, 4.0, 4122)), (4, geography::STPointFromText('POINT EMPTY', 4123));
~~ROW COUNT: 4~~


USE MASTER

CREATE TABLE TestGeospatialMethods_YourTableTemp ( ID INT PRIMARY KEY, PointColumn geometry );
INSERT INTO TestGeospatialMethods_YourTableTemp (ID, PointColumn) VALUES (1, geometry::Point(3.0, 4.0, 4326)), (2, geometry::Point(5.0, 6.0, 4326)), (3, geometry::Point(3.0, 4.0, 0));
~~ROW COUNT: 3~~


CREATE TABLE TestGeospatialMethods_YourTable2Temp ( ID INT PRIMARY KEY, PointColumn1 geometry, PointColumn2 geometry );
INSERT INTO TestGeospatialMethods_YourTable2Temp (ID, PointColumn1, PointColumn2) VALUES (1, geometry::Point(3.0, 4.0, 4326), geometry::Point(3.0, 4.0, 4326));
~~ROW COUNT: 1~~


CREATE TABLE TestGeospatialMethods_TableATemp (ID INT PRIMARY KEY, PointA geometry);
INSERT INTO TestGeospatialMethods_TableATemp (ID, PointA) VALUES (1, geometry::Point(1.0, 2.0, 4326));
~~ROW COUNT: 1~~


CREATE TABLE TestGeospatialMethods_TableBTemp (ID INT PRIMARY KEY, PointB geometry);
INSERT INTO TestGeospatialMethods_TableBTemp (ID, PointB) VALUES (1, geometry::Point(3.0, 4.0, 4326));
~~ROW COUNT: 1~~


CREATE TABLE TestGeospatialMethods_SPATIALPOINTGEOG_dttemp (location geography);
INSERT INTO TestGeospatialMethods_SPATIALPOINTGEOG_dttemp (location) VALUES ( geography::STGeomFromText('Point(47.65100 -22.34900)', 4326) );
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_SPATIALPOINTGEOG_dttemp (location) VALUES ( geography::STGeomFromText('Point(1.0 2.0)', 4326) );
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_SPATIALPOINTGEOG_dttemp (location) VALUES ( geography::STGeomFromText('Point(1.0 2.0)', 4326) );
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_SPATIALPOINTGEOG_dttemp (location) VALUES ( geography::STPointFromText('Point(1.0 2.0)', 4326) );
~~ROW COUNT: 1~~


CREATE TABLE TestGeospatialMethods_SPATIALPOINTGEOM_dttemp (location geometry);
INSERT INTO TestGeospatialMethods_SPATIALPOINTGEOM_dttemp (location) VALUES ( geometry::STGeomFromText('Point(47.65100 -22.34900)', 4326) );
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_SPATIALPOINTGEOM_dttemp (location) VALUES ( geometry::STGeomFromText('Point(1.0 2.0)', 4326) );
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_SPATIALPOINTGEOM_dttemp (location) VALUES ( geometry::STGeomFromText('Point(47.65100 -22.34900)', 0) );
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_SPATIALPOINTGEOM_dttemp (location) VALUES ( geometry::STPointFromText('Point(1.0 2.0)', 4326) );
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_SPATIALPOINTGEOM_dttemp (location) VALUES ( geometry::Point(47.65100, -22.34900, 4326) );
~~ROW COUNT: 1~~


CREATE VIEW TestGeospatialMethods_point_intersect1Temp AS SELECT p1.location.STIntersects(p2.location) AS intersection FROM TestGeospatialMethods_SPATIALPOINTGEOM_dttemp p1 CROSS JOIN TestGeospatialMethods_SPATIALPOINTGEOM_dttemp p2;

CREATE VIEW TestGeospatialMethods_disjointTemp AS SELECT p1.location.STDisjoint(p2.location) AS isIN FROM TestGeospatialMethods_SPATIALPOINTGEOM_dttemp p1 CROSS JOIN TestGeospatialMethods_SPATIALPOINTGEOM_dttemp p2 ORDER BY p1.location.STX;

CREATE VIEW TestGeospatialMethods_ValFromGeomTemp AS SELECT location.STDimension() FROM TestGeospatialMethods_SPATIALPOINTGEOM_dttemp ORDER BY location.STX;

CREATE VIEW TestGeospatialMethods_TextFromGeogTemp AS SELECT  location.STDimension() AS Dimension FROM TestGeospatialMethods_SPATIALPOINTGEOG_dttemp ORDER BY location.Lat;

CREATE VIEW TestGeospatialMethods_point_intersectTemp AS SELECT p1.location.STIntersects(p2.location) AS Intersection FROM TestGeospatialMethods_SPATIALPOINTGEOG_dttemp p1 CROSS JOIN TestGeospatialMethods_SPATIALPOINTGEOG_dttemp p2 ORDER BY p1.location.Lat;

CREATE VIEW TestGeospatialMethods_point_disjointTemp AS SELECT p1.location.STDisjoint(p2.location) AS disjoint FROM TestGeospatialMethods_SPATIALPOINTGEOG_dttemp p1 CROSS JOIN TestGeospatialMethods_SPATIALPOINTGEOG_dttemp p2 ORDER BY  p1.location.Lat;

CREATE VIEW TestGeospatialMethods_valid_ValFromGeomTemp AS SELECT location.STIsValid() FROM TestGeospatialMethods_SPATIALPOINTGEOM_dttemp ORDER BY location.STX;

CREATE VIEW TestGeospatialMethods_valid_TextFromGeogTemp AS SELECT  location.STIsValid() AS Dimension FROM TestGeospatialMethods_SPATIALPOINTGEOG_dttemp ORDER BY location.Lat;

CREATE VIEW TestGeospatialMethods_closed_ValFromGeomTemp AS SELECT location.STIsClosed() FROM TestGeospatialMethods_SPATIALPOINTGEOM_dttemp ORDER BY location.STX;

CREATE VIEW TestGeospatialMethods_closed_TextFromGeogTemp AS SELECT  location.STIsClosed() AS Dimension FROM TestGeospatialMethods_SPATIALPOINTGEOG_dttemp ORDER BY location.Lat;

CREATE VIEW TestGeospatialMethods_empty_ValFromGeomTemp AS SELECT location.STIsEmpty() FROM TestGeospatialMethods_SPATIALPOINTGEOM_dttemp ORDER BY location.STX;

CREATE VIEW TestGeospatialMethods_empty_TextFromGeomTemp AS SELECT  location.STIsEmpty() AS Dimension FROM TestGeospatialMethods_SPATIALPOINTGEOG_dttemp ORDER BY location.Lat;

CREATE TABLE TestGeospatialMethods_GeomTableTemp ( ID INT PRIMARY KEY, GeomColumn geometry );
INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (1, geometry::STGeomFromText('LINESTRING(0 0, 3 0)', 0));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (2, geometry::STGeomFromText('LINESTRING(0 0, 3 4)', 0));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (3, geometry::STGeomFromText('LINESTRING(0 0, 5 0, 5 5)', 0));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (4, geometry::STGeomFromText('LINESTRING(0 0, 1 1, 2 0, 3 1, 4 0)', 0));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (5, geometry::STGeomFromText('LINESTRING(0 0, 10 0, 10 10, 0 10, 0 0)', 0));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (6, geometry::STGeomFromText('LINESTRING(0 0, 1 0, 2 0, 3 0, 4 0, 5 0)', 0));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (7, geometry::STGeomFromText('LINESTRING(0 0 0, 3 0 4)', 0));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (8, geometry::STGeomFromText('LINESTRING(0 0 0, 3 0 4)', 0));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (9, geometry::STGeomFromText('LINESTRING(0 0 0, 1 1 1, 2 2 2)', 0));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (10, geometry::STGeomFromText('LINESTRING(0 0 0 0, 3 0 4 1)', 0));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (11, geometry::STGeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 0));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (12, geometry::STGeomFromText('POLYGON((0 0, 5 0, 5 5, 0 5, 0 0))', 0));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (13, geometry::STGeomFromText('POLYGON((0 0, 20 0, 20 20, 0 20, 0 0), (5 5, 15 5, 15 15, 5 15, 5 5))', 0));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (14, geometry::STGeomFromText('POLYGON((0 0, 3 0, 0 4, 0 0))', 0));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (15, geometry::STGeomFromText('LINESTRING(0 0 0, 3 4 5)', 0));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (16, geometry::STGeomFromText('LINESTRING(0 0 0 0, 3 4 5 10)', 0));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (17, geometry::STGeomFromText('POLYGON((0 0 1, 4 0 2, 4 4 3, 0 4 4, 0 0 1))', 0));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeomTableTemp (ID, GeomColumn) VALUES (18, geometry::STGeomFromText('MULTILINESTRING((0 0 0, 3 0 5), (10 10 0, 10 13 5))', 0));
~~ERROR (Code: 33557097)~~

~~ERROR (Message: MULTILINESTRING is not supported)~~


CREATE TABLE TestGeospatialMethods_GeogTableTemp ( ID INT PRIMARY KEY, GeogColumn geography );
INSERT INTO TestGeospatialMethods_GeogTableTemp (ID, GeogColumn) VALUES (1, geography::STGeomFromText('LINESTRING(-122.34900 47.65100, -122.34950 47.65150)', 4326));
~~ROW COUNT: 1~~

INSERT INTO TestGeospatialMethods_GeogTableTemp (ID, GeogColumn) VALUES (2, geography::STGeomFromText('LINESTRING(-122.34900 47.65100, -122.34950 47.65150)', 0));
~~ERROR (Code: 33557097)~~

~~ERROR (Message: Invalid SRID)~~

INSERT INTO TestGeospatialMethods_GeogTableTemp (ID, GeogColumn) VALUES (3, geography::STGeomFromText('LINESTRING EMPTY', 4326));
~~ROW COUNT: 1~~


CREATE VIEW TestGeospatialMethods_lengthTemp AS SELECT CAST(GeomColumn.STLength() AS numeric(20, 6)) AS Length FROM TestGeospatialMethods_GeomTableTemp ORDER BY ID;

CREATE VIEW TestGeospatialMethods_lengthGeogTemp AS SELECT CAST(GeogColumn.STLength() AS numeric(20, 6)) AS Length FROM TestGeospatialMethods_GeogTableTemp ORDER BY ID;