Hi Mark,
I created the table from a version 0.9 backup that I restored to the
version 1.0 database. This might be the problem. So I guess the question
is can it be fixed, or do I need to drop the table and reload it from
the shapefile. Also, I think the table was orginially loaded on 0.9 with
SRID=-1, but all my tables on 1.0 have a valid SRID set, so maybe this
table is hopelessy mangled.
And I get the same error if I run the query in psql.
-Steve
Mark Cave-Ayland wrote:
> 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
>>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>