Re: SRID conflict, PostgreSQL 8.3

This should be addressed to the Postgis list.

However, you are spatially joining two geometries, and they need be in the same coordinate system.

The column "the_geom" has a defined SRID (spatial reference id) when created in it's original table. Your hard coded POLYGON in the SQL below has a SRID of -1 (the last value in the argument).

You can fix this by either changing the -1 to be the same number as the SRID specified for the_geom, or by setting this arbitrarily to -1 for this operation (as below):

SELECT "whatever"
FROM "a view into 'catalog'"
WHERE ((TRUE AND TRUE) AND SetSrid("the_geom", -1) && GeometryFromText('POLYGON
((-83.28 26.07,
-83.28 28.26,
-81.59 28.26,
-81.59 26.07,
-83.28 26.07))', -1));

(This assumes that "the_geom" & the coordinates you specify in the query are in fact in the same coordinate system
Note that SRID of -1 means unknown coordinate system.)

I'm not sure of the relevance of the (TRUE AND TRUE) in the where clause, it seems redundant, as it will always return true.

Cheers,

Brent Wood

Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> csmith <csmith(at)insequenceinc(dot)com> 03/21/09 8:57 AM >>>
Hello,

I serve a Geospatial IS project that for some years has used PostgreSQL
and PostGIS. A strategy/scheme that has worked for all previous
versions has failed with a recent upgrade to 8.3 (e.g. 8.3.6-1).

Relation "catalog" is a large geospatially-enabled aggregation of data
with a variety of SRID's imbedded within "the_geom" attribute values.
Querying a view into the "catalog" which describes a subset of it's
tuples with identical SRID's (the uniqueness of the SRID associated with
this view's data is easily demonstrable with an ad-hoc query) has always
worked smoothly. With the 8.3 engine, an error is introduced:

SELECT "whatever"
FROM "a view into 'catalog'"
WHERE ((TRUE AND TRUE) AND "the_geom" && GeometryFromText('POLYGON
((-83.28 26.07,
-83.28 28.26,
-81.59 28.26,
-81.59 26.07,
-83.28 26.07))', -1))

results in this error:

"Operation on two geometries with different SRIDs"

The result of the GeometryFromText routine has, of course, but one SRID,
thus the SRID from "the_geom" must be the culprit. It's as if the
query is considering tuples in "catalog" outside of the view's domain.
(note: I can offer further evidence of this behavior- removing all tuples
from "catalog" other than those returned by a query against the view
eliminates the conflict/error).

Can someone comment on this mystery/phenomenon vis-a-vis PostgreSQL
version 8.3 (PostGIS 1.3.5)?

Many thanks,
Christopher Smith

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.