1 2select '113', ST_area2d('MULTIPOLYGON( ((0 0, 10 0, 10 10, 0 10, 0 0)),( (0 0, 10 0, 10 10, 0 10, 0 0),(5 5, 7 5, 7 7 , 5 7, 5 5) ) ,( (0 0, 10 0, 10 10, 0 10, 0 0),(5 5, 7 5, 7 7, 5 7, 5 5),(1 1,2 1, 2 2, 1 2, 1 1) ) )'::GEOMETRY) as value; 3 4select '114', ST_perimeter2d('MULTIPOLYGON( ((0 0, 10 0, 10 10, 0 10, 0 0)),( (0 0, 10 0, 10 10, 0 10, 0 0),(5 5, 7 5, 7 7 , 5 7, 5 5) ) ,( (0 0, 10 0, 10 10, 0 10, 0 0),(5 5, 7 5, 7 7, 5 7, 5 5),(1 1,2 1, 2 2, 1 2, 1 1) ) )'::GEOMETRY) as value; 5 6select '115', ST_3DPerimeter('MULTIPOLYGON( ((0 0 0, 10 0 0, 10 10 0, 0 10 0, 0 0 0)),( (0 0 0, 10 0 0, 10 10 0, 0 10 0, 0 0 0),(5 5 0, 7 5 0, 7 7 0, 5 7 0, 5 5 0) ) ,( (0 0 1, 10 0 1, 10 10 1, 0 10 1, 0 0 1),(5 5 1, 7 5 1, 7 7 1, 5 7 1, 5 5 1),(1 1 1,2 1 1, 2 2 1, 1 2 1, 1 1 1) ) )'::GEOMETRY) as value; 7 8select '116', ST_length2d('MULTILINESTRING((0 0, 1 1),(0 0, 1 1, 2 2) )'::GEOMETRY)::numeric(12,6) as value; 9select '117', ST_3dlength('MULTILINESTRING((0 0, 1 1),(0 0, 1 1, 2 2) )'::GEOMETRY)::numeric(12,6) as value; 10select '118', ST_3dlength('MULTILINESTRING((0 0 0, 1 1 1),(0 0 0, 1 1 1, 2 2 2) )'::GEOMETRY)::numeric(12,6) as value; 11 12select '134', ST_Distance('POINT(1 2)', 'POINT(1 2)'); 13select '135', ST_Distance('POINT(5 0)', 'POINT(10 12)'); 14 15select '136', ST_Distance('POINT(0 0)', ST_translate('POINT(0 0)', 5, 12, 0)); 16 17-- postgis-users/2006-May/012174.html 18select 'dist', ST_Distance(a,b), ST_Distance(b,a) from ( 19 select 'POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'::geometry as a, 20 'POLYGON((11 0, 11 10, 20 10, 20 0, 11 0), 21 (15 5, 15 8, 17 8, 17 5, 15 5))'::geometry as b 22 ) as foo; 23 24--#1502 25SELECT '#1502', ST_Dwithin(a,b,0.0) from 26(SELECT 'LINESTRING(-97364 -97364, 9736.4 9736.4)'::geometry a, 'POINT(0 0)'::geometry b ) foo; 27 28--st_shortestline 29 30select 'st_shortestline_134', st_astext(st_shortestline('POINT(1 2)', 'POINT(1 2)')); 31select 'st_shortestline_135', st_astext(st_shortestline('POINT(5 0)', 'POINT(10 12)')); 32 33select 'st_shortestline_136', st_astext(st_shortestline('POINT(0 0)', ST_translate('POINT(0 0)', 5, 12, 0))); 34 35-- postgis-users/2006-May/012174.html 36select 'st_shortestline_dist', st_astext(st_shortestline(a,b)), st_astext(st_shortestline(b,a)) from ( 37 select 'POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'::geometry as a, 38 'POLYGON((11 0, 12 10, 20 10, 20 0, 11 0), 39 (15 5, 15 8, 17 8, 17 5, 15 5))'::geometry as b 40 ) as foo; 41 42--st_maxdistance 43 44select 'st_maxdistance_134', st_maxdistance('POINT(1 2)', 'POINT(1 2)'); 45select 'st_maxdistance_135', st_maxdistance('POINT(5 0)', 'POINT(10 12)'); 46 47select 'st_maxdistance_136', st_maxdistance('POINT(0 0)', ST_translate('POINT(0 0)', 5, 12, 0)); 48 49-- postgis-users/2006-May/012174.html 50select 'st_maxdistance_dist', st_maxdistance(a,b)::numeric(12,6), st_maxdistance(b,a)::numeric(12,6) from ( 51 select 'POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'::geometry as a, 52 'POLYGON((11 0, 11 10, 20 10, 20 0, 11 0), 53 (15 5, 15 8, 17 8, 17 5, 15 5))'::geometry as b 54 ) as foo; 55 56--st_longestline 57 58select 'st_longestline_134', st_astext(st_longestline('POINT(1 2)', 'POINT(1 2)')); 59select 'st_longestline_135', st_astext(st_longestline('POINT(5 0)', 'POINT(10 12)')); 60 61select 'st_longestline_136', st_astext(st_longestline('POINT(0 0)', ST_translate('POINT(0 0)', 5, 12, 0))); 62 63-- postgis-users/2006-May/012174.html 64select 'st_longestline_dist', st_astext(st_longestline(a,b)), st_astext(st_longestline(b,a)) from ( 65 select 'POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'::geometry as a, 66 'POLYGON((11 0, 11 10, 20 10, 20 0, 11 0), 67 (15 5, 15 8, 17 8, 17 5, 15 5))'::geometry as b 68 ) as foo; 69 70select 'distancetest1', 71 ST_Distance(a, b), 72 st_maxdistance(a, b), 73 st_astext(st_shortestline(a,b)), 74 st_astext(st_shortestline(b,a)), 75 st_astext(st_longestline(a,b)), 76 st_astext(st_longestline(b,a)) from ( 77select 78 ST_GeomFromText('MULTILINESTRING((17 16, 16 17, 17 18, 17 17, 17 16), (28 35,29 39, 30 35))') as a, 79 ST_GeomFromText('MULTIPOLYGON(((-1 -1, -1 25, 25 25, 25 -1, -1 -1), (14 14,14 19,19 19,19 14,14 14)),((33 35,33 40, 35 40, 35 35, 33 35)))') as b 80) as foo; 81 82select 'distancetest2', 83 ST_Distance(a, b), 84 st_maxdistance(a, b), 85 round(st_x(st_startpoint(st_shortestline(a,b)))::numeric, 10), 86 round(st_y(st_startpoint(st_shortestline(a,b)))::numeric, 10), 87 round(st_x(st_endpoint(st_shortestline(a,b)))::numeric, 10), 88 round(st_y(st_endpoint(st_shortestline(a,b)))::numeric, 10), 89 st_astext(st_longestline(a,b)), 90 st_astext(st_longestline(b,a)) from ( 91select 92 ST_GeomFromText('LINESTRING(-40 -20 , 4 2)') as a, 93 ST_GeomFromText('LINESTRING(-10 20, 1 -2)') as b 94) as foo; 95 96select 'distancepoly1', 97 ST_Distance(a, b), 98 st_maxdistance(a, b), 99 st_astext(st_shortestline(a,b)), 100 st_astext(st_shortestline(b,a)), 101 st_astext(st_longestline(a,b)), 102 st_astext(st_longestline(b,a)) from ( 103select 104 ST_GeomFromText('MULTIPOLYGON(((17 16, 16 17, 17 18, 17 17, 17 16)), ((28 35,29 39, 30 35, 28 35)))') as a, 105 ST_GeomFromText('MULTIPOLYGON(((-1 -1, -1 25, 25 25, 25 -1, -1 -1), (14 14,14 19,19 19,19 14,14 14)),((33 35,33 40, 35 40, 35 35, 33 35)))') as b 106) as foo; 107 108select 'distancepoly2', 109 ST_Distance(a, b), 110 st_maxdistance(a, b)::numeric(12,6), 111 st_astext(st_shortestline(a,b)), 112 st_astext(st_shortestline(b,a)), 113 st_astext(st_longestline(a,b)), 114 st_astext(st_longestline(b,a)) from ( 115 select ST_GeomFromText('POLYGON((17 14, 16 17, 17 18, 17 17, 17 14))') as a, 116 ST_GeomFromText('POLYGON((-1 -1, -1 25, 25 25, 25 -1, -1 -1), (14 14,14 19,19 19,19 14,14 14))') as b 117 ) as foo; 118 119select 'distancepoly3', 120 ST_Distance(a, b), 121 st_maxdistance(a, b)::numeric(12,6), 122 st_astext(st_shortestline(a,b)), 123 st_astext(st_shortestline(b,a)), 124 st_astext(st_longestline(a,b)), 125 st_astext(st_longestline(b,a)) from ( 126 select ST_GeomFromText('POLYGON((17 16, 16 17, 17 19, 17 17, 17 16))') as a, 127 ST_GeomFromText('POLYGON((-1 -1, -1 25, 25 25, 25 -1, -1 -1), (14 14,14 19,19 19,19 14,14 14))') as b 128 ) as foo; 129 130select 'distancepoly4', 131 ST_Distance(a, b), 132 st_maxdistance(a, b)::numeric(12,6), 133 st_astext(st_shortestline(a,b)), 134 st_astext(st_shortestline(b,a)), 135 st_astext(st_longestline(a,b)), 136 st_astext(st_longestline(b,a)) from ( 137 select ST_GeomFromText('POLYGON((17 16, 16 17, 16 20, 18 20, 18 17, 17 16))') as a, 138 ST_GeomFromText('POLYGON((-1 -1, -1 25, 25 25, 25 -1, -1 -1), (14 14,14 19,19 19,19 14,14 14))') as b 139 ) as foo; 140 141select 'distancepoly5', 142 ST_Distance(a, b), 143 st_maxdistance(a, b)::numeric(12,6), 144 st_astext(st_shortestline(a,b)), 145 st_astext(st_shortestline(b,a)), 146 st_astext(st_longestline(a,b)), 147 st_astext(st_longestline(b,a)) from ( 148 select ST_GeomFromText('POLYGON((17 12, 16 17, 17 18, 17 17, 17 12))') as a, 149 ST_GeomFromText('POLYGON((-1 -1, -1 25, 25 25, 25 -1, -1 -1), (14 14,14 19,19 19,19 14,14 14))') as b 150 ) as foo; 151 152select 'distancepoly6', 153 ST_Distance(a, b), 154 st_maxdistance(a, b)::numeric(12,6), 155 st_astext(st_shortestline(a,b)), 156 st_astext(st_shortestline(b,a)), 157 st_astext(st_longestline(a,b)), 158 st_astext(st_longestline(b,a)) from ( 159 select ST_GeomFromText('POLYGON((2 2, 2 3, 3 3, 3 2, 2 2))') as a, 160 ST_GeomFromText('POLYGON((-1 -1, -1 25, 25 25, 25 -1, -1 -1), (14 14,14 19,19 19,19 14,14 14))') as b 161 ) as foo; 162 163--3D Distance functions 164 165SELECT '3dDistancetest1', 166 ST_3DDistance(a,b)::numeric(12,6), 167 ST_3DMaxDistance(a,b)::numeric(12,6), 168 ST_3DDWithin(a,b,5), 169 ST_3DDFullyWithin(a,b,5), 170 ST_ASEWKT(ST_3DShortestline(a,b)), 171 ST_ASEWKT(ST_3DClosestpoint(a,b)), 172 ST_ASEWKT(ST_3DLongestline(a,b)) FROM ( 173 SELECT 'POINT(1 1 1)'::geometry as a, 'POINT(3 2 7)'::geometry as b 174 ) as foo; 175 176SELECT '3dDistancetest2', 177 ST_3DDistance(a,b), 178 ST_3DMaxDistance(a,b)::numeric(12,6), 179 ST_3DDWithin(a,b,5), 180 ST_3DDFullyWithin(a,b,5), 181 ST_ASEWKT(ST_3DShortestline(a,b)), 182 ST_ASEWKT(ST_3DClosestpoint(a,b)), 183 ST_ASEWKT(ST_3DLongestline(a,b)) FROM ( 184 SELECT 'POINT(1 1 1)'::geometry as a, 'LINESTRING(0 0 0, 2 2 2)'::geometry as b 185 ) as foo; 186 187SELECT '3dDistancetest3', 188 ST_3DDistance(a,b)::numeric(12,6), 189 ST_3DMaxDistance(a,b)::numeric(12,6), 190 ST_3DDWithin(a,b,5), 191 ST_3DDFullyWithin(a,b,5), 192 ST_ASEWKT(ST_SnapToGrid(ST_3DShortestline(a,b), 1e-14)), 193 ST_ASEWKT(ST_3DClosestpoint(a,b)), 194 ST_ASEWKT(ST_3DLongestline(a,b)) FROM ( 195 SELECT 'POINT(1 1 1)'::geometry as a, 'LINESTRING(5 2 6, -3 -2 4)'::geometry as b 196 ) as foo; 197 198SELECT '3dDistancetest4', 199 ST_3DDistance(a,b), 200 ST_3DMaxDistance(a,b)::numeric(12,6), 201 ST_3DDWithin(a,b,5), 202 ST_3DDFullyWithin(a,b,5), 203 ST_ASEWKT(ST_3DShortestline(a,b)), 204 ST_ASEWKT(ST_3DClosestpoint(a,b)), 205 ST_ASEWKT(ST_3DLongestline(a,b)) FROM ( 206 SELECT 'LINESTRING(1 1 3, 5 7 8)'::geometry as a, 'POINT(1 1 1)'::geometry as b 207 ) as foo; 208 209 SELECT '3dDistancetest5', 210 ST_3DDistance(a,b), 211 ST_3DMaxDistance(a,b), 212 ST_3DDWithin(a,b,5), 213 ST_3DDFullyWithin(a,b,5), 214 ST_ASEWKT(ST_3DShortestline(a,b)), 215 ST_ASEWKT(ST_3DClosestpoint(a,b)), 216 ST_ASEWKT(ST_3DLongestline(a,b)) FROM ( 217 SELECT 'LINESTRING(1 0 5, 11 0 5)'::geometry as a, 'LINESTRING(5 2 0, 5 2 10, 5 0 13)'::geometry as b 218 ) as foo; 219 220SELECT '3dDistancetest6', 221 ST_3DDistance(a,b) FROM ( 222 SELECT 'LINESTRING(1 1 1 , 2 2 2)'::geometry as a, 'POLYGON((0 0 0, 2 2 2, 3 3 3, 0 0 0))'::geometry as b) as foo; 223 224SELECT '3dDistancetest7', 225 ST_3DDistance(a,b) FROM ( 226 SELECT 'LINESTRING(1 1 1 , 2 2 2)'::geometry as a, 'POLYGON((0 0 0, 2 2 2, 3 3 1, 0 0 0))'::geometry as b) as foo; 227 228-- 3D mixed dimmentionality #2034 229--closestpoint with 2d as first point and 3d as second 230select st_astext(st_3dclosestpoint('linestring(0 0,1 1,2 0)'::geometry, 'linestring(0 2 3, 3 2 3)'::geometry)); 231 232--closestpoint with 3d as first point and 2d as second 233select st_astext(st_3dclosestpoint('linestring(0 0 1,1 1 2,2 0 3)'::geometry, 'linestring(0 2, 3 2)'::geometry)); 234 235--shortestline with 2d as first point and 3d as second 236select st_astext(st_3dshortestline('linestring(0 0,1 1,2 0)'::geometry, 'linestring(0 2 3, 3 2 3)'::geometry)); 237 238--shortestline with 3d as first point and 2d as second 239select st_astext(st_3dshortestline('linestring(0 0 1,1 1 2,2 0 3)'::geometry, 'linestring(0 2, 3 2)'::geometry)); 240 241--distance with 2d as first point and 3d as second 242select st_3ddistance('linestring(0 0,1 1,2 0)'::geometry, 'linestring(0 2 3, 3 2 3)'::geometry); 243 244--distance with 3d as first point and 2d as second 245select st_3ddistance('linestring(0 0 1,1 1 2,2 0 3)'::geometry, 'linestring(0 2, 3 2)'::geometry); 246 247select ST_AsText(ST_3DClosestPoint('POINT(0 0 0)', 'POINT(0 0)')); 248select ST_AsText(ST_3DShortestLine('LINESTRING(2 1, 3 0)', 'LINESTRING(0 0 2, 3 3 -4)')); 249 250-- Area of an empty polygon 251select 'emptyPolyArea', st_area('POLYGON EMPTY'); 252 253-- Area of an empty linestring 254select 'emptyLineArea', st_area('LINESTRING EMPTY'); 255 256-- Area of an empty point 257select 'emptyPointArea', st_area('POINT EMPTY'); 258 259-- Area of an empty multipolygon 260select 'emptyMultiPolyArea', st_area('MULTIPOLYGON EMPTY'); 261 262-- Area of an empty multilinestring 263select 'emptyMultiLineArea', st_area('MULTILINESTRING EMPTY'); 264 265-- Area of an empty multilipoint 266select 'emptyMultiPointArea', st_area('MULTIPOINT EMPTY'); 267 268-- Area of an empty collection 269select 'emptyCollectionArea', st_area('GEOMETRYCOLLECTION EMPTY'); 270 271-- 272select 'spheroidLength1_deprecated', round(ST_LengthSpheroid('MULTILINESTRING((-118.584 38.374,-118.583 38.5),(-71.05957 42.3589 , -71.061 43))'::geometry,'SPHEROID["GRS_1980",6378137,298.257222101]'::spheroid)::numeric,5); 273select 'spheroidLength1', round(ST_LengthSpheroid('MULTILINESTRING((-118.584 38.374,-118.583 38.5),(-71.05957 42.3589 , -71.061 43))'::geometry,'SPHEROID["GRS_1980",6378137,298.257222101]'::spheroid)::numeric,5); 274 275select 'length2d_spheroid', ST_Length2DSpheroid('LINESTRING(0 0 0, 0 0 100)'::geometry, 'SPHEROID["GRS_1980",6378137,298.257222101]'); 276select 'length_spheroid', ST_LengthSpheroid('LINESTRING(0 0 0, 0 0 100)'::geometry, 'SPHEROID["GRS_1980",6378137,298.257222101]'); 277 278 279-- Solid intersects solid when contains it 280select '#4278.1', ST_3DIntersects('BOX3D(0 0 0, 1 1 1)'::box3d::geometry, 'BOX3D(-1 -1 -1, 2 2 2)'::box3d::geometry); 281select '#4278.2', ST_3DDistance('BOX3D(0 0 0, 1 1 1)'::box3d::geometry, 'BOX3D(-1 -1 -1, 2 2 2)'::box3d::geometry); 282-- cast to text and back as a way of getting rid of solid flag 283select '#4278.3', ST_3DIntersects('BOX3D(0 0 0, 1 1 1)'::box3d::geometry::text::geometry, 'BOX3D(-1 -1 -1, 2 2 2)'::box3d::geometry::text::geometry); 284select '#4278.4', ST_3DDistance('BOX3D(0 0 0, 1 1 1)'::box3d::geometry::text::geometry, 'BOX3D(-1 -1 -1, 2 2 2)'::box3d::geometry::text::geometry); 285 286 287SELECT '#4328.1', ST_Intersects('TIN(((0 0,1 0,0 1,0 0)))'::geometry, 'POINT(.1 .1)'::geometry), ST_3DIntersects('TIN(((0 0,1 0,0 1,0 0)))'::geometry, 'POINT(.1 .1)'::geometry); 288SELECT '#4328.2', ST_Intersects('TIN(((0 0,1 0,0 1,0 0)))'::geometry, 'LINESTRING(.1 .1, .2 .2)'::geometry), ST_3DIntersects('TIN(((0 0,1 0,0 1,0 0)))'::geometry, 'LINESTRING(.1 .1, .2 .2)'::geometry); 289SELECT '#4328.3', ST_Intersects('TIN(((0 0,1 0,0 1,0 0)))'::geometry, 'TRIANGLE((.1 .1, .2 .2, .2 .1, .1 .1))'::geometry), ST_3DIntersects('TIN(((0 0,1 0,0 1,0 0)))'::geometry, 'TRIANGLE((.1 .1, .2 .2, .2 .1, .1 .1))'::geometry); 290SELECT '#4328.4', ST_Intersects('TIN(((0 0,1 0,0 1,0 0)))'::geometry, 'POLYGON((.1 .1, .2 .2, .2 .1, .1 .1))'::geometry), ST_3DIntersects('TIN(((0 0,1 0,0 1,0 0)))'::geometry, 'POLYGON((.1 .1, .2 .2, .2 .1, .1 .1))'::geometry); 291SELECT '#4328.5', ST_Intersects('TIN(((0 0,3 0,0 3,0 0)))'::geometry, 'CIRCULARSTRING(1.1 1.1, 1.2 1.2, 1.2 1.1)'::geometry), ST_3DIntersects('TIN(((0 0,3 0,0 3,0 0)))'::geometry, 'CIRCULARSTRING(1.1 1.1, 1.2 1.2, 1.2 1.1)'::geometry); 292SELECT '#4328.6', ST_Intersects('TIN(((0 0,3 0,0 3,0 0)))'::geometry, 'CURVEPOLYGON(CIRCULARSTRING(1.1 1.1, 1.2 1.2, 1.2 1.1, 1.2 1.2, 1.1 1.1))'::geometry), ST_3DIntersects('TIN(((0 0,3 0,0 3,0 0)))'::geometry, 'CURVEPOLYGON(CIRCULARSTRING(1.1 1.1, 1.2 1.2, 1.2 1.1, 1.2 1.2, 1.1 1.1))'::geometry); 293