[postgis-tickets] [PostGIS] #3149: 3D-Distance/KNN distances don't agree sometimes

[PostGIS]

#3149: 3D-Distance/KNN distances don't agree sometimes
---------------------+---------------------------
 Reporter:  robe     |      Owner:  pramsey
     Type:  defect   |     Status:  new
 Priority:  blocker  |  Milestone:  PostGIS 2.2.0
Component:  postgis  |    Version:  trunk
 Keywords:           |
---------------------+---------------------------
 I started putting together the knn recheck tests for 3d, all was good
 until I got into more complex types:

 -- script to create the 3d geometry table is as follows:
 {{{
 CREATE TABLE knn_recheck_geom_nd(gid serial primary key, geom geometry);
 INSERT INTO knn_recheck_geom_nd(gid,geom)
 SELECT ROW_NUMBER() OVER(ORDER BY x,y) AS gid,
 ST_MakePoint(x*0.777,y*0.887,z*1.05) As geom
 FROM generate_series(-100,1000, 7) AS x ,
     generate_series(-300,1000,9) As y,
  generate_series(1005,10000,5555) As z ;

  -- 3d lines
 INSERT INTO knn_recheck_geom_nd(gid, geom)
 SELECT 500000 + i, ST_Translate('LINESTRING(-100 300 500, 500 700 600, 400
 123 0, 500 10000 -1234, 1 1 5000)'::geometry, i*2000,0)
 FROM generate_series(0,10) i;


 -- 3d polygons
 INSERT INTO knn_recheck_geom_nd(gid, geom)
 SELECT 500100 + i, ST_Translate('POLYGON((100 800 5678, 100 700 5678, 400
 123 5678, 405 124 5678, 100 800 5678))'::geometry,0,i*2000)
 FROM generate_series(0,3) i;

 -- polyhedral surface --
 INSERT INTO knn_recheck_geom_nd(gid,geom)
 SELECT 600000 + row_number() over(), ST_Translate(the_geom,100, 450,1000)
 As the_geom
                 FROM (VALUES ( ST_GeomFromText(
 'PolyhedralSurface(
 ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
 ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0
 0)),  ((1 1 0, 1 1 1, 1 0 1, 1 0 0, 1 1 0)),
 ((0 1 0, 0 1 1, 1 1 1, 1 1 0, 0 1 0)),  ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0
 1))
 )') ) ,
 ( ST_GeomFromText(
 'PolyhedralSurface(
 ((0 0 0, 0 0 1, 0 1 1, 0 1 0, 0 0 0)),
 ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)) )') ) )
 As foo(the_geom) ;
 }}}

 The results of this test don't agree with the ST_3DDistance answer:


 {{{
 SELECT '#2nd-3' As t, gid, ST_GeometryType(geom),
     ST_3dDistance( 'MULTILINESTRING((-95 -300 5000, 100 450 1000, 100 323
 200),(-50 2000 456, 30 6000 789))'::geometry, geom)::numeric(12,4),
                    'MULTILINESTRING((-95 -300 5000, 100 450 1000, 100 323
 200),(-50 2000 456, 30 6000 789))'::geometry <<->> geom As knn_dist
 FROM knn_recheck_geom_nd
 ORDER BY 'MULTILINESTRING((-95 -300 5000, 100 450 1000, 100 323 200),(-50
 2000 456, 30 6000 789))'::geometry <<->> geom LIMIT 5;

 }}}



 {{{
    t    |  gid   | st_geometrytype | st_3ddistance |     knn_dist
 --------+--------+-----------------+---------------+------------------
  #2nd-3 | 500000 | ST_LineString   |       47.9294 | 2275.46599623022
  #2nd-3 |   4640 | ST_Point        |      447.4051 | 2500.71300499541
  #2nd-3 |   4349 | ST_Point        |      448.6050 |   2500.715907504
  #2nd-3 |   4929 | ST_Point        |      446.2682 | 2500.72193218324
  #2nd-3 |   4060 | ST_Point        |      449.8674 | 2500.73063966772
 (5 rows)
 }}}

 I think the knn_dist answer is wrong because I purposely carefully
 constructed my multilinestring so it would cut into my polyhedral surface,
 and my polyhedral surface is no where in sight in this query:


 {{{
 SELECT
  ST_3dDistance( 'MULTILINESTRING((-95 -300 5000, 100 450 1000, 100 323
 200),(-50 2000 456, 30 6000 789))'::geometry, geom)::numeric(12,4),
                    'MULTILINESTRING((-95 -300 5000, 100 450 1000, 100 323
 200),(-50 2000 456, 30 6000 789))'::geometry <<->> geom As knn_dist
  from knn_recheck_geom_nd where gid = 600001;

  st_3ddistance |     knn_dist
 ---------------+------------------
         0.0000 | 2885.41236221099
 }}}

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3149>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.