Opened 5 years ago

Closed 5 years ago

#3149 closed defect (invalid)

3D-Distance/KNN distances don't agree sometimes

Reported by: robe Owned by: pramsey
Priority: blocker Milestone: PostGIS 2.2.0
Component: postgis Version: master
Keywords: Cc:

Description

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

Change History (2)

comment:1 Changed 5 years ago by robe

oops hold on just realized my install is old for some reason. Let me build with latest and then complain :)

comment:2 Changed 5 years ago by robe

Resolution: invalid
Status: newclosed

nevermind works with latest

Note: See TracTickets for help on using tickets.