[postgis-tickets] [PostGIS] #5009: Inconsistent results when using empty geometries with different SRIDS

[PostGIS]

#5009: Inconsistent results when using empty geometries with different SRIDS
----------------------------+---------------------------
 Reporter:  samuelspurling  |      Owner:  pramsey
     Type:  defect          |     Status:  new
 Priority:  low             |  Milestone:  PostGIS 3.1.5
Component:  postgis         |    Version:  3.1.x
 Keywords:                  |
----------------------------+---------------------------
 I first noticed this issue when upgrading from PostGIS 2 to 3.

 Running the following on PostGIS 2 results in false, whereas on PostGIS 3
 it returns an mixed SRID error (which I understand should probably be the
 correct response).

 {{{
 SELECT st_dwithin(st_setsrid(ST_GeomFromText('MULTILINESTRING EMPTY'),
 27700), st_setsrid(ST_GeomFromText('MULTILINESTRING EMPTY'), 0), 0.01);
 }}}

 This caused some errors in our codebase (our fault for mixing the SRIDs).
 However, I have now realised that I think it returns the incorrect
 response sometimes in PostGIS 3 also.

 In PostGIS 2 it used the wrapper around the st_dwithin function with:

 {{{
 SELECT $1 OPERATOR(public.&&) public.ST_Expand($2,$3) AND $2
 OPERATOR(public.&&) public.ST_Expand($1,$3) AND public._ST_DWithin($1, $2,
 $3)
 }}}

 which filtered out all the empty geometries as they would not get past the
 && operator with the bounding boxes. Basically this meant our dodgy code
 managed to fluke its way through before.

 However, because of the addition of using support functions in PostGIS 3
 this now means that the response for empty geometries with mixed SRIDs is
 now not consistent. When using an index, it is filtered out with no error
 but when called without using an index it returns an error.

 Proof of concept below:

 {{{
 -- Mixed SRID set of a LINESTRING of SRID 27700 and MULTILINESTRING EMPTY
 of SRID 0 result in error
 SELECT st_dwithin(st_setsrid(ST_GeomFromText('LINESTRING
 (798.7163221763004 206.6033707592214, 862.7163221763004
 208.6033707592214)'), 27700), st_setsrid(ST_GeomFromText('MULTILINESTRING
 EMPTY'), 0), 0.01);
 -- ERROR: LWGEOM_dwithin: Operation on mixed SRID geometries (LineString,
 27700) != (MultiLineString, 0)

 -- Create a dummy test data set of 100,000 LINESTRINGs with an index on
 the geom column
 CREATE TABLE test_table_lines (
         initial_geom geometry(point, 27700) NOT NULL,
         geom geometry(linestring, 27700)
 );

 CREATE INDEX test_table_lines_geom_idx ON public.test_table_lines USING
 gist (geom);

 INSERT INTO test_table_lines
 SELECT (st_dump(st_generatepoints(st_geomfromtext('POLYGON((0 0, 0 1000,
 1000 1000, 1000 0, 0 0))', 27700), 100000))).geom;

 UPDATE test_table_lines
 SET geom = st_makeline(
         initial_geom,
         st_setsrid(
                 st_makepoint(
                         st_x(initial_geom) + floor(random() * 100 +
 1)::int,
                         st_y(initial_geom) + floor(random() * 100 +
 1)::int
                 ),
         27700)
 );

 -- Now the mixed SRID set does not error, even though the data input
 matches the top query
 SELECT *
 FROM test_table_lines t
 WHERE st_dwithin(st_setsrid(ST_GeomFromText('MULTILINESTRING EMPTY'), 0),
 t.geom, 1);
 }}}

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5009>
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.