Changes between Initial Version and Version 1 of Ticket #3675, comment 1
- Timestamp:
- 12/03/16 12:33:39 (8 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Ticket #3675, comment 1
initial v1 1 okay on closer inspection, the ST_SetSRID seems to be at fault .1 okay 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. 2 2 3 because this has the index kick in. 3 4 5 I 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 7 Here is the full test to exercise issue: 8 4 9 5 10 {{{ 6 SELECT e.gid 7 FROM e1 , e2 8 WHERE ST_Intersects(e1.geom, ST_MakePoint(e2.longitude, e2.latitude) ) 11 CREATE TABLE e AS SELECT row_number() OVER() As gid, ST_SetSRID( ST_Point(x,y) ,4326) As geom, x as longitude, y as latitude 12 FROM generate_series(-90,90) As y, generate_series(-179,179) As x; 13 14 CREATE INDEX idx_e_geom_gist ON e USING gist(geom); 15 16 -- good 17 SELECT gid 18 FROM e 19 WHERE ST_DWithin(e.geom , 20 ST_SetSRID(ST_Point( 151.27544336, -33.87718472), 4326), 1); 21 22 -- bad 23 SELECT gid 24 FROM e, (SELECT longitude, latitude FROM e WHERE longitude = -1 and latitude = -1) As c 25 WHERE ST_DWithin(e.geom , 26 ST_SetSRID(ST_Point( c.longitude, c.latitude), 4326), 1); 27 28 29 -- good 30 SELECT gid 31 FROM e, (SELECT longitude, latitude, geom 32 FROM e WHERE longitude = -1 and latitude = -1) As c 33 WHERE ST_DWithin(e.geom ,c.geom, 1); 34 35 -- I'm really puzzled this one is good 36 37 38 WITH c AS (SELECT ST_SetSRID(ST_Point(-1,-1),4326) As geom) 39 SELECT gid 40 FROM e, c 41 WHERE ST_DWithin(e.geom ,c.geom, 1); 9 42 }}} 10 43 11 44 12 So this could be a simple fix. I suspect the issue exists in 9.5 as well but still need to check that. 45 Now if I do this: 46 47 {{{ 48 CREATE 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 59 The bad now uses an index.