[mapserver-users] Postgis data source: quoting table name problem

[DeDuikertjes]

Milo, List,

changing to

DATA 'tc_vlak_geometry from "nl_imro_0026_svbbu_0001" using unique geoidn using SRID = 28992'

and also 

DATA 'tc_vlak_geometry from "nl_imro_0026_svbbu_0001" using unique geoidn using srid = 28992'

fails, but:


DATA 'tc_vlak_geometry from "nl_imro_0026_svbbu_0001" using unique 
geoidn using SRID=28992'

and

DATA 'tc_vlak_geometry from "nl_imro_0026_svbbu_0001" using unique 
geoidn using srid=28992'

both work as it should.

I allready tried things like that before, but apparently got something 
wrong in the details like having spaces around the =

Great, thank you very much.

MArco



Milo van der Linden schreef:
> Hello Marco,
>
>
> If you do not specify ...using unique geoidnd using SRID=28992'
> mapserver will search the table for srid information, again sending the
> table name in a query to the database as seen in the error message.
>
> I agree that this might be a tiny bug, but it might be a workaround to
> define the srid in the DATA clause.
>
> Kind regards,
>
> Milo van der Linden
>
>
>
> On Mon, 2009-06-22 at 15:02 +0200, DeDuikertjes wrote:
>   
>> Dear List,
>>
>> I've extensively searched the archives, saw a couple of more or less
>> related posts, but couldn't find any solutions for my problem.
>>
>> In my mapserver application I use postgis as a data source.
>> In my application the tables containing geometry columns do need to have
>> odd characters like . and - in their names (for clarity: the table names
>> that is, not the column names).
>> Postgis allows this by double qouting the table name in functions,
>> queries and sql statements.
>>
>> So in mapserver I like to do that as well as in the following example:
>>
>> DATA 'tc_vlak_geometry from "nl_imro_0026_svbbu_0001" using unique geoidn'
>>
>> Unfortunately that fails with a message from the WMS:
>>
>> msDrawMap(): Image handling error. Failed to draw layer named
>> 'geoidNL.IMRO.SVBBU-102-_vlak'. prepare_database(): Query error. Error
>> executing POSTGIS DECLARE (the actual query) statement: 'DECLARE
>> mycursor BINARY CURSOR FOR SELECT
>> asbinary(force_collection(force_2d(tc_vlak_geometry)),'NDR'),geoidn::text
>> from "nl_imro_0026_svbbu_0001" WHERE tc_vlak_geometry &&
>> setSRID('BOX3D(155016 467631,156758 470080)'::BOX3D,
>> find_srid('','"nl_imro_0026_svbbu_0001"','tc_vlak_geometry') )'
>> Postgresql reports the error as 'ERROR: find_srid() - couldnt find the
>> corresponding SRID - is the geometry registered in the GEOMETRY_COLUMNS
>> table? Is there an uppercase/lowercase missmatch? ' More Help: Error
>> with POSTGIS data variable. You specified 'check your .map file'.
>> Standard ways of specifiying are : (1) 'geometry_column from
>> geometry_table' (2) 'geometry_column from (sub query) as foo using
>> unique column name using SRID=srid#' Make sure you put in the 'using
>> unique column name' and 'using SRID=#' clauses in. For more help, please
>> see http://postgis.refractions.net/documentation/ Mappostgis.c - version
>> of Jan 23/2004
>>
>> Removing the quotes works fine and gives a map from the WMS.
>> Of course removing the quotes in this example is no problem because
>> there are no odd characters like . and -.
>> When tablenames do have these characters, the query of course fails.
>>
>> Are there any workarounds for this problem other than renaming tables to
>> names without odd characters?
>> (and yes, I've tried to add using srid = 28992 to the DATA string, but
>> that doesn't help).
>> (and yes, I've tried escaping double quotes like DATA "'tc_vlak_geometry
>> from \"nl_imro_0026_svbbu_0001\" using unique geoidn"')
>>
>> Any help greatly appreciated.
>>
>> MArco
>>
>> _______________________________________________
>> mapserver-users mailing list
>> mapserver-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>>