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

[Bart van den Eijnden (OSGIS)]

Hi Pierre,

yes I tested it.

Best regards,
Bart

Pierre GIRAUD wrote:

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

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

_______________________________________________
Cartoweb-users mailing list
Cartoweb-users@lists.maptools.org
http://lists.maptools.org/mailman/listinfo/cartoweb-users