MapServer PostGIS Query Error – How to Troubleshoot and Fix

mapserverpostgispostgresql

I'm having some difficulty understanding the error I am getting from my mapserver/postgis program. I have the following map file:

MAP     # start of map file

        NAME "sample"
        EXTENT  82299.5 5002.78 655979.76 657599.55
        SIZE 800 400

        PROJECTION
                "init=epsg:27700"
        END

        LAYER   # layer info
                CONNECTIONTYPE postgis
                NAME "roads
                CONNECTION "user=username 
                password=**** dbname=databasename host=hostdb.com port=5432" 
                DATA "the_geom from public.\"MLSOA\""
                STATUS ON
                TYPE LINE
                 CLASS
                        STYLE
                                COLOR 0 0 0
                        END
                 END
        END    
END     # end of map file

I then call this using the following command:

http://mysite.com/mapserv.cgi?map=/home/username/webapps/htdocs/mapfile_pg.map&layer=roads&mode=map

which gives me the following error: 

msDrawMap(): Image handling error. Failed to draw layer named 'roads'. 
sPostGISLayerWhichShapes(): Query error. Error (ERROR: find_srid() 
- couldnt find the corresponding SRID - is the geometry registered in the 
GEOMETRY_COLUMNS table? Is there an uppercase/lowercase missmatch? ) 
executing query: select encode(ST_AsBinary(ST_Force_2D("the_geom"),'NDR'),'hex') 
as geom,"oid" from public."MLSOA" where the_geom && GeomFromText
('POLYGON((-180.225563909774 -90,-180.225563909774 
90,180.225563909774 90,180.225563909774 -90,-180.225563909774 
-90))',find_srid('','public."MLSOA"','the_geom'))

I asked a question on GIS.SE earlier today about a problem I was having connecting to the DB, and @unicoletti helpfully solved my earlier problem about the database name. However I have now tried all the options that I understand could be causing this current error and have not figured out the problem.

Things that I have tried:

  1. Checking that the the table is registered in GEOMETRY_COLUMNS (it is, as I could not manually add it as it was there already).
  2. Adding in a projection to the Mapserver file
  3. Running SELECT Probe_Geometry_Columns() to update GEOMETRY_COLUMNS
  4. Creating an index using the following command: CREATE INDEX table_the_geom ON "MLSOA" USING GIST ( the_geom );
    CLUSTER table_the_geom ON public."MLSOA";

Are there any other things that could be causing this error? I'm using PostGreSQL 8.3, PostGIS 1.5.3 and Mapserver 6.0.1. and these programs are all running on a Linux server.

Best Answer

The sub-query that throws the error (Find_SRID) is incorrect:

select find_srid('','public."MLSOA"','the_geom')

Do you really need to specify the public schema in your query? Normally this is part of your search_path variable, so you normally don't need it. Try using this in your MAP file:

DATA "the_geom from \"MLSOA\" using unique gid using srid=27700"

(Note: update gid with your integer/serial primary key column name, if it is different)

Related Question