Bug report #2189

Map Server Export - POSTGIS data

Added by smccabe33 - about 15 years ago. Updated over 14 years ago.

Status:Closed
Priority:Low
Assignee:Richard Duivenvoorde
Category:Python plugins
Affected QGIS version: Regression?:No
Operating System:Debian Easy fix?:No
Pull Request or Patch supplied: Resolution:wontfix
Crashes QGIS or corrupts data: Copied to github as #:12249

Description

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

History

#1 Updated by Richard Duivenvoorde about 15 years ago

  • Status changed from Open to In Progress

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.

#2 Updated by Jürgen Fischer about 15 years ago

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?

#3 Updated by Jürgen Fischer about 15 years ago

Replying to [comment:4 jef]:

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


    

  

  
  

    

    
#4
    
    Updated by Richard Duivenvoorde about 15 years ago


    
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.

    

  

  
  

    

    
#5
    
    Updated by Stephan Meissl - about 15 years ago


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



	
Thanks,



	
Stephan

    

  

  
  

    

    
#6
    
    Updated by Jürgen Fischer about 15 years ago


    
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: 1077ec7d (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.




<pre>

> 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.

    

  

  
  

    

    
#7
    
    Updated by Richard Duivenvoorde over 14 years ago


    
    
       
  • Resolution set to wontfix
    • 
       
  • Status changed from In Progress to Closed
    • 
    

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

    

  

  























Also available in:  Atom
  PDF