[postgis-users] PostGIS/Mapserver Mapfile Question

[Mark Cave-Ayland]

Hi Steve,

Did you add the geometry column using SELECT AddGeometryColumn(..) or did
you simply add a column of type geometry to your table? Try cutting and
pasting the SELECT statement on its own into psql and seeing what the
resulting error is, e.g.

SELECT
water::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
from zcta WHERE the_geom && setSRID('BOX3D(-73.5081481933594
40.272331237793,-69.9282608032227 43.8522186279297)'::BOX3D,
find_srid('','zcta','the_geom') )


Kind regards,

Mark.

------------------------
WebBased Ltd
17 Research Way
Tamar Science Park
Plymouth
PL6 8BT 

T: +44 (0)1752 797131
F: +44 (0)1752 791023
W: http://www.webbased.co.uk
 

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Stephen Woodbridge
> Sent: 28 June 2005 16:33
> To: PostGIS Users Discussion
> Subject: [postgis-users] PostGIS/Mapserver Mapfile Question
> 
> 
> Hi all,
> 
> I'm having a problem with a mapfile connection having heart burn with 
> the generate SQL. This is on Windows XP using a copy of the software 
> from the PostGIS workshop at MUM3.
> 
> POSTGIS="1.0.1" GEOS="2.1.1" PROJ="Rel. 4.4.9, 29 Oct 2004" USE_STATS 
> DBPROC="0.3.0" RELPROC="0.3.0"
> 
> It looks like the problem is find_srid('','zcta','the_geom') where '' 
> the SRID is undefined! but this example follows the workshop 
> example as 
> far as I can tell. So I probably am missing some step along 
> the way. Any 
> ideas what?
> 
> Thanks,
>    -Steve
> 
> The layer definition:
> 
>    LAYER
>      NAME "zcta"
>      CONNECTIONTYPE postgis
>      CONNECTION "host=localhost port=5432 dbname=census password=xxxx 
> user=xxxx"
>      DATA "the_geom from zcta"
>      TYPE POLYGON
>      STATUS ON
>      PROJECTION
>        #"proj=latlong"
>        "init=epsg:4326"
>      END
>      CLASSITEM "water"
>      CLASS
>        EXPRESSION "t"
>        NAME "Water"
>        OUTLINECOLOR 102 102 204
>        COLOR 102 102 204
>      END
>      CLASS
>        NAME "ZCTA5"
>        OUTLINECOLOR 0 0 0
>        COLOR 255 255 200
>      END
>    END
> 
> msDrawMap(): Image handling error. Failed to draw layer named 'zcta'.
> prepare_database(): Query error. Error executing POSTGIS DECLARE (the 
> actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT 
> water::text,asbinary(force_collection(force_2d(the_geom)),'NDR
> '),gid::text 
> from zcta WHERE the_geom && setSRID('BOX3D(-73.5081481933594 
> 40.272331237793,-69.9282608032227 43.8522186279297)'::BOX3D, 
> find_srid('','zcta','the_geom') )'
> 
> Postgresql reports the error as 'ERROR: find_srid() - couldnt 
> find the 
> corresponding SRID - is the geometry registered in the 
> GEOMETRY_COLUMNS 
> table? Is there an uppercase/lowercase missmatch? '
> 
> and the table is defined:
> 
> CREATE TABLE zcta
> (
>    gid serial NOT NULL,
>    area numeric,
>    perimeter numeric,
>    name varchar,
>    the_geom geometry,
>    water bool DEFAULT false,
>    state char(2) DEFAULT ''::bpchar,
>    CONSTRAINT zcta_pkey PRIMARY KEY (gid),
>    CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2),
>    CONSTRAINT enforce_geotype_the_geom CHECK 
> (geometrytype(the_geom) = 
> 'MULTIPOLYGON'::text OR the_geom IS NULL),
>    CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 4326)
> )
> WITH OIDS;
> 
> _______________________________________________
> postgis-users mailing list postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>