Explore our sites

Stack Exchange
tour help
Take the tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 2 down vote favorite

I've just installed MapServer on a linux server and I am trying to understand how to display a basic map using a PostGIS database. I have displayed a shapefile in the browser, so I know that the mapserver setup works. My mapfile is as follows:

MAP

        NAME "sample"
        EXTENT -180 -90 180 90 # Geographic
        SIZE 800 400

        LAYER
          CONNECTIONTYPE postgis
          NAME "roads"
          CONNECTION "user=username password=**** dbname=databasename host=hostdb.com port=5432"
          DATA "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 by using a url string:

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

However, I get the error:

loadLayer(): Unknown identifier. Parsing error near (MLSOA_London):(line 11)

I have also tried calling my database just MLSOA and 'MLSOA'. If I use the line DATA "geom from MLSOA" I get the following error:

msDrawMap(): Image handling error. Failed to draw layer named 'roads'. msPostGISLayerWhichShapes(): Query error. Error (ERROR: relation "mlsoa" does not exist ) executing query: select encode(ST_AsBinary(ST_Force_2D("geom"),'NDR'),'hex') as geom,"gid" from MLSOA where geom && GeomFromText('POLYGON((-180.225563909774 -90,-180.225563909774 90,180.225563909774 90,180.225563909774 -90,-180.225563909774 -90))',find_srid('','MLSOA','geom'))

I think that the problem is with the table name, but I'm not sure how it should be named. Using pgAdminIII, if I want to run a query on this table I use the following syntax:

select getsrid(the_geom) from public."MLSOA" limit 10

What is the correct syntax for my mapfile?

share|improve this question
 
HAve you tried using lower case? Maybe DATA behaves as EXPRESSION : postgis.refractions.net/docs/ch05.html#id2634416 –  simo Oct 19 '11 at 14:55
 
Thanks @unicoletti - that seemed to fix the problem. I did get another problem after that worked with the following error message. I assume that it is unrelated to the upper-case/lower case? –  djq Oct 19 '11 at 15:02
 
msDrawMap(): Image handling error. Failed to draw layer named 'roads'. msPostGISLayerWhichShapes(): 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')) –  djq Oct 19 '11 at 15:02
1  
@celenius well, the error says it all: to use a postgis table in mapserver it is required that is is registered in the GEOMETRY_COLUMNS table. This table works as a dictionary/metadata repository fro all postgis-enabled objects in postgres database. Usually the GEOMETRY_SOLUMNS table is automatically updated by the AddGeometryColumn function. Please see the postgis manual for the manual registration: postgis.refractions.net/docs/… –  unicoletti Oct 19 '11 at 15:23
 
Thanks @unicoletti - I'm slightly confused why I need to update the GEOMETRY_COLUMNS. I imported a projected shapefile using the GUI importer, and just ran SELECT Probe_Geometry_Columns(); but I still have the same problem. I'll try updating this table manually. –  djq Oct 19 '11 at 15:28
add comment

1 Answer

up vote 2 down vote accepted

Postgres naming is case sentitive (it's the only one db that I'm aware of that has this feature) that is why you have to use the double quotes in your query in pgAdmin.

In the .map file Unix-style escaping should work:

DATA "geom from public.\"MLSOA\""

Manual registration of the geometry column(s) can be performed as documented here:

http://postgis.refractions.net/docs/ch04.html#Manual_Register_Spatial_Column

share|improve this answer
4  
Por performance reason, it is advisable that after the line 'CONNECTION', adding the line PROCESSING "CLOSE_CONNECTION=DEFER". Thus, established database connections will be reused instead of closing them (postgis.refractions.net/docs/ch05.html, section 5.1.1). –  dariapra Oct 19 '11 at 14:54
 
@dariapra good point, you deserve bonus points ;-) mapserver.org/optimization/… –  unicoletti Oct 19 '11 at 15:27
 
You can't add "public." part, since it results in an error. See this for a better DATA configuration. –  Mike Toews Oct 19 '11 at 21:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.