[Cartoweb-users] Re: problem with edit plugin and SRID

[Bart van den Eijnden (OSGIS)]

Hi Pierre,

thanks for the pointer to the bugzilla entry.

There is yet another of these errors (also in CVS) in the function that 
selects with a box:

                case 'Rectangle':
                    $sql = sprintf('SELECT *, astext(%s) as %s FROM 
%s.%s ' .
                                   'WHERE intersects (%s, ' .
                                   "'BOX3D(%s %s, %s %s)'::box3d)",
                                   $this->geomColumn,
                                   $this->geomColumn,
                                   $this->editSchema,
                                   $this->editTable,
                                   $this->geomColumn,
                                   $shape->minx,
                                   $shape->miny,
                                   $shape->maxx,
                                   $shape->maxy
                                   );

should be:
                case 'Rectangle':
                    $sql = sprintf('SELECT *, astext(%s) as %s FROM 
%s.%s ' .
                                   'WHERE intersects (%s, ' .
                                   "SetSRID('BOX3D(%s %s, %s 
%s)'::box3d, %s))",
                                   $this->geomColumn,
                                   $this->geomColumn,
                                   $this->editSchema,
                                   $this->editTable,
                                   $this->geomColumn,
                                   $shape->minx,
                                   $shape->miny,
                                   $shape->maxx,
                                   $shape->maxy,
                                   $this->getSrid()
                                   );

Best regards,
Bart

Pierre GIRAUD wrote:

> You're right.
> This is an already fixed bug (CVS version).
>
> http://bugzilla.maptools.org/show_bug.cgi?id=1482
>
> Regards
>
> Pierre
>
> Bart van den Eijnden (OSGIS) wrote:
>
>> I fixed it by changing the following in the function 
>> selectFeaturesByShape of ServerEdit.php
>>
>>                    $sql = sprintf('SELECT *, astext(%s) as %s FROM 
>> %s.%s ' .
>>                                   "WHERE %s && setSRID('%s'::box3d, 
>> %s) ".
>>                                   "AND distance (%s, 
>> GeometryFromText( '".
>>                                   "POINT(%s %s)', %s ) ) < %s",
>>                                   $this->geomColumn,
>>                                   $this->geomColumn,
>>                                   $this->editSchema,
>>                                   $this->editTable,
>>                                   $this->geomColumn,
>>                                   $bbox3D,
>>                                   $this->getSrid(),
>>                                   $this->geomColumn,
>>                                   $shape->x,
>>                                   $shape->y,
>>                                   $this->getSrid(),
>>                                   $toleranceGeo
>>                                   );
>>
>> Best regards,
>> Bart
>>
>> Bart van den Eijnden (OSGIS) wrote:
>>
>>> Hi list,
>>>
>>> when trying to select a polygon in the edit demo in one of my 
>>> PostGIS (version 1.1.2) layers, Cartoweb generates the following query:
>>>
>>> message:   Unable to select feature(s) in database Message: DB 
>>> Error: unknown error  Userinfo: SELECT *, astext(geometrie) as 
>>> geometrie FROM bestemmingsplannen.bouwvlakken WHERE geometrie && 
>>> 'BOX3D(51605.3112 365837.217533, 51606.1447556 
>>> 365838.051089)'::box3d AND distance (geometrie, GeometryFromText( 
>>> 'POINT(51605.727977809336 365837.6343111111)', -1 ) ) < 
>>> 0.833555555556 [nativecode=ERROR:  Operation on two geometries with 
>>> different SRIDs]
>>>
>>> My PostGIS table has SRID 90112 for the Dutch national system. 
>>> Adding features works fine by the way.
>>>
>>> There are 2 errors in this query:
>>>
>>> 1) the Box3D is missing an setSRID call, I believe it somehow also 
>>> relates to this:
>>>
>>> http://www.postgis.org/pipermail/postgis-users/2006-May/012049.html
>>>
>>> so it used to be allowed by PostGIS, but not anymore (at least 
>>> that's my interpretation).
>>>
>>> 2) the GeomFromText needs to get SRID 90112 instead of -1
>>>
>>> The following query does work fine:
>>>
>>> SELECT *, astext(geometrie) as geometrie FROM 
>>> bestemmingsplannen.bouwvlakken WHERE geometrie && 
>>> setSRID('BOX3D(51605.3112 365837.217533, 51606.1447556 
>>> 365838.051089)'::box3d, 28992) AND distance (geometrie, 
>>> GeometryFromText( 'POINT(51605.727977809336 365837.6343111111)', 
>>> 28992 ) ) < 0.833555555556
>>>
>>> Are these bugs, or am I missing something?
>>>
>>> For reference, this is my MAP file LAYER:
>>>
>>> LAYER
>>>   NAME EDITBOUWVLAK
>>>   STATUS ON
>>>   TYPE POLYGON
>>>   CONNECTIONTYPE POSTGIS
>>>   CONNECTION 'dbname=terneuzen user=terneuzen_www 
>>> password=trnzn_www_2006 host=localhost'
>>>   DATA 'geometrie from bestemmingsplannen.bouwvlakken USING UNIQUE 
>>> bouwvlak_id SRID 90112'
>>>   TEMPLATE 'ttt'
>>>   TRANSPARENCY 50
>>>   PROJECTION
>>>     "init=epsg:28992"
>>>   END
>>>   LABELITEM "bouwvlak_id"
>>>   CLASS
>>>     NAME "class"
>>>     STYLE
>>>       COLOR 50 50 255
>>>       OUTLINECOLOR 255 50 50
>>>     END
>>>     LABEL
>>>       TYPE TRUETYPE
>>>       FONT "Vera"
>>>       SIZE 7
>>>       COLOR 0 0 0
>>>       POSITION cc
>>>       BACKGROUNDCOLOR 245 235 235
>>>       BACKGROUNDSHADOWCOLOR 55 55 55
>>>       BACKGROUNDSHADOWSIZE 1 1
>>>     END
>>>   END
>>>   LABELMAXSCALE 50000
>>>   METADATA
>>>     'id_attribute_string' 'bouwvlak_id' # query
>>>     'query_returned_attributes' 'parc_id name culture surf parc_type'
>>>     'edit_table' 'bestemmingsplannen.bouwvlakken' # PostGIS table
>>>     'edit_geometry_column' 'geometrie' # PostGIS geometry column
>>>     'edit_geometry_type' 'polygon' # PostGIS geometry type
>>>     'edit_srid' '28992'
>>>     'edit_attributes' 'intekenaar|string' # list of the editable fields
>>>     'edit_filter' '' # mapserver filter
>>>   END
>>> END
>>>
>>> Thanks in advance.
>>>
>>> Best regards,
>>> Bart
>>>
>>
>>
>
>


-- 
Bart van den Eijnden
OSGIS, Open Source GIS
http://www.osgis.nl