mapserver-postgis centroid function

[Obe, Regina DND\MIS]

The other option is use a different alias for the_geom
 
e.g.
 
    DATA "the_centroid FROM (select centroid(the_geom) AS the_centroid,*
from cal_parcel) AS foo USING UNIQUE gid USING SRID=26929"

 

-----Original Message-----
From: Kumar [mailto:kumar at NEWFIELDS.COM] 
Sent: Tuesday, September 20, 2005 5:20 PM
To: MAPSERVER-USERS at LISTS.UMN.EDU
Subject: [UMN_MAPSERVER-USERS] mapserver-postgis centroid function



Hi all,

I am trying to get the centroid of the parcels using postgis to get the
centroid of each parcel. I have pasted the layer definition below.

 

 

  LAYER

    NAME "Calhoun_Parcel_centroid"

    GROUP "Calhoun County Data"

    STATUS OFF

    DATA "the_geom FROM (select centroid(the_geom) AS the_geom,* from
cal_parcel) AS foo USING UNIQUE gid USING SRID=26929"

    TYPE POINT

    CONNECTIONTYPE POSTGIS

    CONNECTION "user=parcel password=dbpass dbname=AnnistonDB host=localhost
port=5432"

    UNITS METERS

    SIZEUNITS PIXELS

            LABELCACHE ON

    LABELITEM "PPIN"

    LABELMAXSCALE 6000

    TOLERANCE 0

    TOLERANCEUNITS PIXELS

    TEMPLATE "ttt_query"

    METADATA

      "layer_title" "Selected Parcel"

    END

    CLASS

      NAME "Selected Parcel"

      LABEL

        TYPE TRUETYPE

        FONT "fritqat"

        SIZE 8

        MINSIZE 4

        MAXSIZE 256

        POSITION CC

        OFFSET 0 0 

        ANGLE AUTO

        BUFFER 0

                        MINDISTANCE -1

        MINFEATURESIZE -1

        COLOR 0 0 0

        OUTLINECOLOR 200 200 200

        ANTIALIAS TRUE

        PARTIALS TRUE

        FORCE FALSE

      END

      STYLE

        SYMBOL 3

        COLOR 200 0 200

        SIZE 10

      END

    END

  END

 

I am getting this warning if I try to use this layer

 

Warning: [MapServer Error]: msDrawMap(): Failed to draw layer named
'Calhoun_Parcel_centroid'. in C:\ms4w\Apache\htdocs\Anniston11\Develop2.php
on line 82

Warning: [MapServer Error]: prepare_database(): Error executing POSTGIS
DECLARE (the actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR
SELECT asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text from
(select centroid(the_geom) AS the_geom,* from cal_parcel) AS foo WHERE
the_geom && setSRID('BOX3D(626801.874849 1124507.96601,683350.291609
1166919.27858)'::BOX3D, -1 )' 

Postgresql reports the error as 'ERROR: column reference "the_geom" is
ambiguous '

More Help:

Error with POSTGIS data variable. You specified '<check your .map file>'.
Standard ways of specifiying are : 
(1) 'geometry_column from geometry_table' 
(2) 'geometry_column from (<sub query>) as foo using unique <column name>
using SRID=<srid#>' 

Make sure you put in the 'using unique <column name>' and 'using SRID=#'
clauses in.

 

 

I found that I was losing the SRID information when I tried to use the
centroid function in postgis. I have pasted my output below. I was wondering
if anyone can help me fix this error. Any input would be appreciated.

 

 

AnnistonDB=# select the_geom from cal_parcel where ppin = '123';

 

 

 the_geom

----------------------------------------------------------------------------
----------------------------------------------SRID=26929;MULTIPOLYGON(((5921
29.329345788......)))

(1 row)

 

 

 

 

AnnistonDB=# select centroid(the_geom) from cal_parcel where ppin = '123';

                     centroid

--------------------------------------------------

 SRID=-1;POINT(592030.853820728 1186660.20937603)

(1 row)

 

 

AnnistonDB=# select postgis_full_version();

                                         postgis_full_version

----------------------------------------------------------------------------
--------------------------

 POSTGIS="0.9.1" GEOS="2.1.4" PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS
DBPROC="0.0.1" RELPROC="0.0.1"

 

 

Thanks

-           kumar

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20050920/0721f682/attachment.htm>