Changes between Initial Version and Version 1 of Ticket #3675, comment 1


Ignore:
Timestamp:
Dec 3, 2016, 12:33:39 PM (7 years ago)
Author:
robe

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #3675, comment 1

    initial v1  
    1 okay on closer inspection, the ST_SetSRID seems to be at fault.
     1okay on closer inspection, the ST_SetSRID seems to be at fault and I was close before with costing.  It's the cost we put on ST_SetSRID of 10 that is screwing us.  If I switch it back to 1 all is good.
    22
    3 because this has the index kick in.
     3 
     4
     5I tested on both my PostgreSQL 9.6.1 and PostgreSQL 9.5.2 64-bit windows builds with PostGIS 2.3.1 loaded and both have the same issue.
     6
     7Here is the full test to exercise issue:
     8
    49
    510{{{
    6 SELECT e.gid
    7 FROM e1 , e2
    8 WHERE ST_Intersects(e1.geom, ST_MakePoint(e2.longitude, e2.latitude) )
     11CREATE TABLE e AS SELECT row_number() OVER() As gid, ST_SetSRID( ST_Point(x,y) ,4326) As geom, x as longitude, y as latitude
     12FROM generate_series(-90,90) As y, generate_series(-179,179) As x;
     13
     14CREATE INDEX idx_e_geom_gist ON e USING gist(geom);
     15
     16-- good
     17SELECT gid
     18FROM  e
     19WHERE   ST_DWithin(e.geom ,
     20ST_SetSRID(ST_Point( 151.27544336, -33.87718472), 4326), 1);
     21
     22-- bad
     23SELECT gid
     24FROM  e, (SELECT longitude, latitude FROM e WHERE longitude = -1 and latitude = -1) As c
     25WHERE   ST_DWithin(e.geom ,
     26ST_SetSRID(ST_Point( c.longitude, c.latitude), 4326), 1);
     27
     28
     29-- good
     30SELECT gid
     31FROM  e, (SELECT longitude, latitude, geom
     32    FROM e WHERE longitude = -1 and latitude = -1) As c
     33WHERE   ST_DWithin(e.geom ,c.geom, 1);
     34
     35-- I'm really puzzled this one is good
     36
     37
     38WITH c AS (SELECT ST_SetSRID(ST_Point(-1,-1),4326) As geom)
     39SELECT gid
     40FROM  e, c
     41WHERE   ST_DWithin(e.geom ,c.geom, 1);
    942}}}
    1043
    1144
    12 So this could be a simple fix.  I suspect the issue exists in 9.5 as well but still need to check that. 
     45Now if I do this:
     46
     47{{{
     48CREATE OR REPLACE FUNCTION public.st_setsrid(
     49    geometry,
     50    integer)
     51  RETURNS geometry AS
     52'$libdir/postgis-2.3', 'LWGEOM_set_srid'
     53  LANGUAGE c IMMUTABLE STRICT
     54  COST 1;
     55
     56}}}
     57
     58
     59The bad now uses an index.