<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=iso-8859-2"><meta name=Generator content="Microsoft Word 14 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.StylE-mailovZprvy17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.StylE-mailovZprvy18
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.StylE-mailovZprvy19
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=CS link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span lang=EN-US style='color:#1F497D'>Jukka,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'>Thank you for your tip, for now I solved it with the ‘USING NONE’ clause. As for the rest of you answer, the problem is that it needs to work with at least 5 different databases owned and maintained by 5 different companies with the data being provided by yet another companies (I’m not even sure which ones</span><span lang=EN-US style='font-family:Wingdings;color:#1F497D'>J</span><span lang=EN-US style='color:#1F497D'>) so you see that the control we have over the db is minimal. In the future we plan to do the adjustments you suggested but it’s gonna take a while and I needed to get this working asap. <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'>Thanks for your time,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'>Jakub<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma","sans-serif";mso-fareast-language:CS'>From:</span></b><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma","sans-serif";mso-fareast-language:CS'> mapserver-users-bounces@lists.osgeo.org [mailto:mapserver-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Rahkonen Jukka (Tike)<br><b>Sent:</b> Thursday, March 13, 2014 11:57 AM<br><b>To:</b> mapserver-users@lists.osgeo.org<br><b>Subject:</b> Re: [mapserver-users] Layer with point from X and Y columns in<o:p></o:p></span></p></div></div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'>Hi,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'>There is another way by using a GDAL virtual format <a href="http://www.gdal.org/ogr/drv_vrt.html">http://www.gdal.org/ogr/drv_vrt.html</a>.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'>But it does not really make much sense to have a spatial table in Oracle that is to be used for rendering maps without having a spatial index. Without spatial index the BBOX of the map cannot be converted into working spatial query from Oracle. The best you can obtain would lead to selecting the whole table for each pan or zoom. So I suggest you to put all your effort into contacting a manager in a high enough position and tell that the spatial index is absolutely needed. And when the manager puts the db admins into work, they can at the same time add triggers to populate the SDO_GEOMETRY fields automatically from X and Y columns after each insert and update. If you do it right, your manager believes that it was his/her idea and everything goes fluently and yields much better result than any workaround.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'>-Jukka Rahkonen-<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0cm 0cm 0cm 4.0pt'><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal style='margin-bottom:12.0pt'><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma","sans-serif";mso-fareast-language:FI'>Jakub Štambachr<o:p></o:p></span></p></div></div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>Hi,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>I have table in Oracle Database that contains points specified by columns X and Y (The table also contains SDO_Geometry column but it’s not always filled with data and I’m not at liberty to modify the table). <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>My question is: Is it possible to specify layers with point using the columns X and Y?<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>So far I have something like this and I need an alternative to replace the DATA <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:black;background:white;mso-highlight:white'>TYPE POINT<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:black;background:white;mso-highlight:white'>CONNECTIONTYPE oraclespatial<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:black;background:white;mso-highlight:white'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:black;background:white;mso-highlight:white'>CONNECTION "isypoo/password@orcl"<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:black;background:white;mso-highlight:white'>DATA "shape FROM (select table.shape as SHAPE from table)" <o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:black;background:white;mso-highlight:white'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>I tried the following:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:black;background:white;mso-highlight:white'>DATA "shape FROM (select MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(table.x, table.Y, NULL), NULL, NULL) AS shape from table)"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>But got the following error:<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:black;background:white;mso-highlight:white'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:black'>msDrawMap(): Image handling error. Failed to draw layer named 'mosty'.<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:black'>msOracleSpatialLayerWhichShapes(): OracleSpatial error. Error: ORA-13226: interface not supported without a spatial index<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:black'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:black'>Is there any other way of doing this without modifying the database?<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:black'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:black'>Thank you for your help,<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:black'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.5pt;font-family:Consolas;color:black'>Jakub<span style='background:white;mso-highlight:white'><o:p></o:p></span></span></p></div></div></body></html>