Map Server Export - POSTGIS data

[qgib]

Author Name: smccabe33 - (smccabe33 -)
Original Redmine Issue: 2189

Redmine category:python_plugins
Assignee: Richard Duivenvoorde

---

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')

[qgib]

Author Name: Richard Duivenvoorde (@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.

---

• status_id was changed from Open to In Progress

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

Replying to "smccabe33":/issues/show/2189:

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?

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

Replying to [comment:4 jef]:

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

[qgib]

Author Name: Richard Duivenvoorde (@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.

[qgib]

Author Name: Stephan Meissl - (Stephan Meissl -)

---

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

Thanks,

Stephan

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

Replying to [comment:6 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: 1077ec7 (SVN r12370) 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.

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.

[qgib]

Author Name: Richard Duivenvoorde (@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] 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?

---

• resolution was configured as wontfix
• status_id was changed from In Progress to Closed