[Qgis-developer] Problem with Postgis 2.0 Views and picking (info-tool)

[Andreas Neumann]

Hi,

I turned on logging on the Postgres-Server and here is what I got:

--------------------------
LOG:  duration: 0.058 ms  statement: BEGIN READ ONLY
LOG:  duration: 1.164 ms  statement: DECLARE qgisf1 BINARY CURSOR FOR
SELECT
st_asbinary(st_force_2d("the_geom"),'NDR'),"gid","tid"::text,"plz"::text,"ort"::text
FROM "admin"."postkreise" WHERE "the_geom" &&
st_makeenvelope(695791,245010,696000,245220,0) AND
st_intersects("the_geom",st_makeenvelope(695791,245010,696000,245220,0))
AND st_srid("the_geom")=21781
ERROR:  Operation on mixed SRID geometries
STATEMENT:  FETCH FORWARD 2000 FROM qgisf1
ERROR:  current transaction is aborted, commands ignored until end of
transaction block
STATEMENT:  CLOSE qgisf1
LOG:  duration: 0.046 ms  statement: ROLLBACK
---------------------------------

so the error is easy to detect: st_makeenvelope() uses an SRID of 0 when
it should use 21781 which I specified when loading the view.

I hope this problem will be relatively easy to fix?

Thanks for having a look at it!

Andreas

On 01/18/2012 07:49 PM, Sandro Santilli wrote:
> On Wed, Jan 18, 2012 at 07:34:13PM +0100, Andreas Neumann wrote:
>> Meanwhile I tested with the same data on Postgis 1.5.3. Everything works
>> fine with Postgis 1.5.3
>>
>> So apparently there are issues with Postgis 2.0. I followed Denis'
>> advice and deliberately set the SRID of the_geom in my view to "-1" with
>> SetSRID(the_geom,-1), voila, it works!
>>
>> To me this sounds like a bug. QGIS somehow intersects (for picking and
>> selecting) an SRID of -1 with the layers SRID (in my case 21781) and
>> results in a mixed SRID error.
>>
>> Jürgen or Strk - can you help with this issue?
> 
> Unknown srid became 0 in PostGIS-2.0.
> The correct way to deal with unknown srids is to NOT specify them at all.
> 
> Check out the actual query being produced by qgis in the log window and
> try it manually. Let us know.
> 
> --strk; 
> 
>   ()   Free GIS & Flash consultant/developer
>   /\   http://strk.keybit.net/services.html