[postgis-users] Intersection problems

[manuel gimond]

I want to intersect a shapefile (stored in PGSQL as a geom table called 
"layer")
and save the output to a separate table called "tmptable" in the same 
database.
The shapefile was loaded in the database using PGSQL with the shp2psql 
loader. I have successfully queried the table "layer" using Mapserver. The 
script I have so far is as follows:


(FYI The variable $Ipolygon points to a string of coordinate values... this
SQL statement is embedded in a PERL script)

======================== start of SQL statement  
============================

CREATE TABLE public.tmptable (gid serial not null,
                               code integer,
                               desc character varying,
                               the_geom public.geometry,
                               CONSTRAINT tmptable_key PRIMARY KEY (gid),
                               CONSTRAINT "\$1" CHECK (srid(the_geom) = -1),
                               CONSTRAINT "\$2" CHECK 
(geometrytype(the_geom) =
                                      'MULTIPOLYGON'::text OR the_geom IS 
NULL)
                               ) with OIDS;
INSERT INTO tmptable (code,desc,the_geom)
  SELECT lc.code,lc.desc,lc.the_geom from
    (SELECT layer.gid as gid,
            
GeometryFromText(intersection(layer.the_geom,'POLYGON(($Ipolygon))'),-1) as 
the_geom,
            layer.code as code,
            layer.desc as desc
            from layer ) AS lc where (npoints(lc.the_geom) >1) ;
CREATE INDEX tmptable_index ON tmptable USING GIST(the_geom);
CREATE INDEX tmptable_oid ON tmptable ( oid );

======================= END of SQL Statement ===============================

I have 2 problems:

1) The intersection routine tries to populate the new table "tmptable" with 
both
POLYGON and MULTIPOLYGON geometries even though the table "layer" has only
MUTLIPOLYGON geometires.

2) If I add the constraint " AND (geometrytype(the_geom) = 'MULTIPOLYGON')"
in the SELECT statement  to temporarely bypass the problem
previously described, I get the following error message in the log file when
attempting to query the table "tmptable" from a Mapserver mapfile:

ERROR:  find_srid() - couldnt find the corresponding SRID - is the geometry
registered in the GEOMETRY_COLUMNS table?  Is there an uppercase/lowercase
missmatch?

Additional info: I am able to perform certain geometric functions on the 
newly created  "tmptable" such as AREA(geometry), and when I do a SELECT * 
FROM tmptable  I see both consitent GID
and THE_GEOM data.

Manny

_________________________________________________________________
Find high-speed ‘net deals — comparison-shop your local providers here. 
https://broadband.msn.com