[postgis-users] ERROR: Operation on two GEOMETRIESwithdifferentSRIDs

[Mark Cave-Ayland]

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of Johan V
> Sent: 14 July 2006 14:31
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] ERROR: Operation on two
> GEOMETRIESwithdifferentSRIDs
> 
> Hi Mark,
> 
> Here are the descriptions of the tables (in the same database running
> POSTGIS 0.8.2):
> 
> [database_x]=# \d reflijst
>           Table "public.reflijst"
>   Column   |       Type        | Modifiers
> -----------+-------------------+-----------
>  artid     | integer           |
>  titre     | text              |
>  annee     | integer           |
>  volume    | character varying |
>  firstpage | character varying |
>  auteurs   | text              |
>  publica   | text              |
>  strat     | character varying |
>  domg_ol   | character varying |
>  xlambert  | bigint            |
>  ylambert  | bigint            |
>  numcarte  | integer           |
>  the_geom  | geometry          |
> Check constraints: "$1" (srid(the_geom) = 103300)
>                    "$2" ((geometrytype(the_geom) =
> 'MULTIPOLYGON'::text) OR (the_geom IS NULL))
> 
> 
> [database_x]=# \d gnosbgm
>              Table "public.gnosbgm"
>    Column   |          Type          | Modifiers
> ------------+------------------------+-----------
>  id         | numeric(28,6)          |
>  gemeente   | character varying(254) |
>  x          | numeric(28,6)          |
>  y          | numeric(28,6)          |
>  z          | numeric(28,6)          |
>  kaart      | character varying(254) |
>  logdiepte  | numeric(28,6)          |
>  diepte     | numeric(28,6)          |
>  bgdnr      | character varying(254) |
>  bgdcode    | character varying(254) |
>  refer      | character varying(254) |
>  hotlink    | character varying(254) |
>  imagepath1 | character varying(254) |
>  logcomp    | character varying(254) |
>  date       | character varying(10)  |
>  tert_plio  | character varying(1)   |
>  tert_mio   | character varying(1)   |
>  tert_paleo | character varying(1)   |
>  tert_eoc   | character varying(1)   |
>  tert_oligo | character varying(1)   |
>  primair    | character varying(1)   |
>  secundair  | character varying(1)   |
>  tertiair   | character varying(1)   |
>  quartair   | character varying(1)   |
>  the_geom   | geometry               |
> Indexes: sidx_gnosbgm gist (the_geom)
> Check constraints: "$1" (srid(the_geom) = 103300)
>                    "$2" ((geometrytype(the_geom) = 'POINT'::text) OR
> (the_geom IS NULL))
> 
> And the two sql statements:
> 
> SELECT the_geom FROM gnosbgm WHERE ((the_geom && 'POLYGON
> ((33595.443392445566 15067.465440617714,281187.7093687274
> 15067.465440617714,281187.7093687274
> 254932.5345593823,33595.443392445566
> 254932.5345593823,33595.443392445566 15067.465440617714))'))
> 
> SELECT the_geom FROM reflijst WHERE ((the_geom && 'POLYGON
> ((33595.443392445566 15067.465440617714,281187.7093687274
> 15067.465440617714,281187.7093687274
> 254932.5345593823,33595.443392445566
> 254932.5345593823,33595.443392445566 15067.465440617714))'))
> 
> They are just the same, only the first give me the required answer!
> 
> Grz,
> Johan


Hi Johan,

I think that the problem is being caused by the fact that in older versions
of PostGIS, index scans ignored the SRID of the geometry during comparisons
(they don't have a RECHECK clause) where as sequential scans did take them
into account. My guess is that if you force an index scan on your reflijst
table, your query will start to work. Can you try the following in a psql
session:


CREATE INDEX sidx_reflijst ON reflijst USING gist (the_geom
gist_geometry_ops);

VACUUM ANALYZE reflijst;

SET enable_seqscan = 'f';

SELECT the_geom FROM reflijst WHERE ((the_geom &&
'POLYGON((33595.443392445566 15067.465440617714,281187.7093687274
15067.465440617714,281187.7093687274 254932.5345593823,33595.443392445566
254932.5345593823,33595.443392445566 15067.465440617714))'));


Kind regards,

Mark.

------------------------
WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk   
http://www.infomapper.com
http://www.swtc.co.uk  

This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.