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

[Pierre GIRAUD]

Did you test it ?
If so, I can apply the patch in the CVS version right now.

Thanks.

Bart van den Eijnden (OSGIS) wrote:

> 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
>>>>
>>>
>>>
>>
>>
>
>