Map Server Export - POSTGIS data

[smccabe33]

I am using the latest build from your site When I export to a Map Server file, I have to remove the "" form the table name and the geometry name e.g. the file has this in it DATA '"way" FROM "osm_polygon"' I need to change it to DATA 'way FROM osm_polygon'

I also have an issue where the FILTER if badly formatted e.g. it appears like this: FILTER ( "nature"='water' or "landuse"='basin' or "landuse"='reservoir' or "waterway"='riverbank' ) and I have to change it to FILTER ( "nature"='water' or "landuse"='basin' or "landuse"='reservoir' or "waterway"='riverbank')

[rduivenvoorde]

We escape all table and variable names to be safe for names using spaces etc ...

BUT there is an ceveat there, see: ​http://www.postgresql.org/docs/8.4/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Quote: Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower (!) case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other.

So I reckon you have tablenames which are NOT lowercase... ?

Should we consider this a bug?

We could NOT quote table and variable names? We cannot test for spaces etc in tablenames or so, because we do not parse the data/sql ourselves.

[jef]

Replying to smccabe33:

I am using the latest build from your site When I export to a Map Server file, I have to remove the "" form the table name and the geometry name e.g. the file has this in it DATA '"way" FROM "osm_polygon"' I need to change it to DATA 'way FROM osm_polygon'

hm, and why do you need to do that? Looks fine to me. What error message do you get?

[jef]

Replying to jef:

hm, and why do you need to do that? Looks fine to me. What error message do you get?

Probably something like ​http://lists.osgeo.org/pipermail/mapserver-users/2009-June/061786.html here. So we'd need to add a using unique theprimarykeyfield using SRID=thesrid.

[rduivenvoorde]

the plugin does already do this (adding "using unique ...") via some guessing based on column names... So my guess is that the column names or tablenames of smccabe33 were non-lowercase, so after adding the quotes those names did not match any more (while when removing the quotes: postgresql are always "folded to lowercase" it was ok).

By the way: the FILTER-examples look exactly the same for me in the example above, but I think it's the same problem.

Bringing it back to the question (I think): "should or should we not put quotes around column and table names in DATA and FILTER rulse" in the mapfile.

[schpidi]

In order to reproduce your error could you please post a complete working mapfile that had to be changed as described.

Thanks,
Stephan

[jef]

Replying to rduivenvoorde:

the plugin does already do this (adding "using unique ...") via some guessing based on column names...

Does it also add srid? mapserver might detect that it doesn't need to parse the SQL statement to get table and column name to query geometry_columns, if both are there. And that parsing might be the origin of the problem.

speaking of which: r12369 the key field is in all postgres layers - not only views like before.

So my guess is that the column names or tablenames of smccabe33 were non-lowercase, so after adding the quotes those names did not match any more (while when removing the quotes: postgresql are always "folded to lowercase" it was ok).

That shouldn't matter. If the column name was indeed uppercase, QGIS would have reported it uppercase - and I guess you would have quoted it just like that.

The postgres provider even works with column names like This is a ridiculously non-trivial
"column name", isn't it? (ie. mixed case identifier with blanks, quotes, a question mark and even a newline. Not sure about mapserver.

Bringing it back to the question (I think): "should or should we not put quotes around column and table names in DATA and FILTER rulse" in the mapfile.

I think we must quote identifiers - that also what the postgres provider does.

[rduivenvoorde]

we quote table-identifiers, but NOT column identifiers anymore(?):

Given a postgresql table named foo with a geometry column name geom

the following DATA strings are working:

'geom from "foo" USING UNIQUE gid USING srid=28992' (<- this one we build)

'geom from foo USING UNIQUE gid USING srid=28992'

"geom from foo USING UNIQUE gid USING srid=28992"

trying to quote the column name:

'"geom" from "foo" USING UNIQUE gid USING srid=28992'

raises an mapserver exception:

msDrawMap(): Image handling error. Failed to draw layer named 'foo'. msPostGISLayerWhichShapes(): Query error. Error (ERROR: zero-length delimited identifier at or near """" LINE 1: select encode(AsBinary(force_collection(force_2d(""geom"")),... ^{) executing query: select encode(AsBinary(force_collection(force_2d(""geom"")),'NDR'),'hex') as geom,"gid" from "foo" where "geom" && GeomFromText('POLYGON((63988.550219 408504.55046,63988.550219 464973.449801,120457.44956 464973.449801,120457.44956 408504.55046,63988.550219 408504.55046))',28992) and ( GeometryType("geom") IN ('POINT','MULTIPOINT') )}

There is some mixing up with quotes IN MAPSERVER I think?

I close this issue given smccabe33 did not provided further info, and this is more a 'quoting' problem for the mapserver/postgresql combination?