[Mapserver-users] Unable to display data stored in Oracle Spatial

Mon Mar 22 06:53:35 EST 2004

This is a multi-part message in MIME format.

------=_NextPart_000_0070_01C4100C.B016C230
Content-Type: text/plain;
	charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable

Ferando,

thank you very much, your hints were extremaly helpful. I was using =
gtype 3007. It was pretty easy to switch from 3D to 2D, since I didn't =
use z-coord (in fact it was always 0). So, now I've got gtype 20xx.

Regarding the conversion to SDO. I do not use ArcGIS neither conversion =
utility shp2sdo. I read geometries from text file (it's a proprietary =
format called VTX used just in our country) and creating CSV file for =
Oracle SQLLDR utility.

Then I execute:
  SQLLDR  UMN/UMN at KATASTER CONTROL=3DPARCELS.CTL         =
LOG=3DLOG\PARCELS.LOG
The example of control file (PARCELS.CTL):
  LOAD DATA=20
  INFILE 'PARCELS.DAT'
  APPEND INTO TABLE PARCELS
  FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'=20
  TRAILING NULLCOLS=20
  (=20
    GR_ID,=20
    NUM,=20
    BOUNDARY COLUMN OBJECT=20
    ( sdo_gtype INTEGER EXTERNAL,=20
      sdo_srid INTEGER EXTERNAL,=20
       isnull FILLER CHAR,=20
      SDO_POINT COLUMN OBJECT  NULLIF BOUNDARY.isnull=3D"pt"=20
      ( X INTEGER EXTERNAL,=20
        Y INTEGER EXTERNAL,=20
        Z INTEGER EXTERNAL),=20
      SDO_ELEM_INFO VARRAY terminated by ';'=20
        (SDO_ORDINATES char(38)),=20
      SDO_ORDINATES VARRAY terminated by ':'=20
        (SDO_ORDINATES char(38))) ,=20
    STATUS
  )

The example of data file (PARCELS.DAT):
  1|"00001001_"|2007| =
|pt||||1|1003|1|;-550000|-1280000|-550010|-1280000|-550010|-1280010|-5500=
00|-1280010|-550000|-1280000|:1|
  2|"00001002_"|2007| =
|pt||||1|1003|1|;-550010|-1280000|-550020|-1280000|-550020|-1280010|-5500=
10|-1280010|-550010|-1280000|:1|

Thank you for helping me,
Jirka

  ----- Original MDessage -----=20
  From: Fernando S.=20
  To: Jiri Potocny ; mapserver-users at lists.gis.umn.edu=20
  Sent: Thursday, March 18, 2004 6:45 PM
  Subject: Re: [Mapserver-users] Unable to display data stored in Oracle =
Spatial

  Jiri,
      I believe that you need a quickly report. Well, if you execute a =
CGI call to mapserv, what's happen? Can you see any image with db data?
      Try to execute the same query in SQLPLUS, SELECT BOUNDARY FROM =
PARCELS. What the sqplus report? Can you report one complete line that =
sql report? Like this:=20

  SQL> select shape from cola_markets;

  SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, =
SDO_ORDINATES)
  =
-------------------------------------------------------------------------=
-------
  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), =
SDO_ORDINATE_ARR
  AY(1, 1, 5, 7))

      What kind of geometry report? 2003?, 3003? If it's report 300X, =
this is the problem, when you import the data using loader, it import =
with type 300X (if you have this type of shape), the mapserv can't show =
this type. Only 100x or 200x.
       When you convert your shape to sdo, what command you execute? I =
use this ( I didn't use SRID in this example):=20
          shp2sdo.exe -o <SHAPE> <TABLE> -g <GEOMETRY_COLUMNS> -d -x =
(-180,180) -y (-90,90) -v
      And after don't forget to execute this function (select =
sdo_tune.extent_of('table with geometry_column', 'geometry_columns') =
from dual;)=20
  =20
      Well, here we developer one project tha use all of informations, =
(user datas, shapes, user points) from Oracle. Look in this link =
(http://g10novo.cttmar.univali.br/gtznovo/principal.php).

      If you need some help, you can report me.

  =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20
  Fernando Simon - simon at cttmar.univali.br=20
  =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20
  DataBase Manager, Mapserver, OracleSpatial and PostGis Developer=20
  G10 - Laborat=F3rio de Computa=E7ao Aplicada=20
  Fone: 047 - 341 7960=20
  http://g10.cttmar.univali.br=20
  =
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

  Jiri Potocny wrote:=20
Fernando,

thank you for your hints.

I used Oracle SQL loader utility to import the data:
SQLLDR  uid/pwd at srvcname control=3Dparcely log=3Dparcely.log
The data were stored in comma separated value text file in the format =
like
this:
4|777777||"1"|1|3007|
|pt||||1|1003|1|;-554984,87|-1285550,04|0|-554998,75|-1285529,3200000001|=
0|-
555001,98999999999|-1285530,8100000001|0|-554984,87|-1285550,04|0|:1|02/1=
3/2
004 00:00:00||

Anyway, I tried to execute SDO_MIGRATE.TO_CURRENT(' TABLE ','
GEOMETRY_COLUMN '), but the problem remains.

Regards,
Jirka

P.S. Your English is perfect enough for this purposes, no need to
appologize.

----- Original Message -----
From: Fernando S.
To: Jiri Potocny ; mapserver-users at lists.gis.umn.edu
Sent: Wednesday, March 17, 2004 6:12 PM
Subject: Re: [Mapserver-users] Unable to display data stored in Oracle
Spatial

Hi Jiri,
    I don't know if I can help you. But I will try.
    I need to do some questions to help you:
        How you imported the data to Oracle?
        Using shp2sdo? If yes, after you imported the data, did you =
execute
this function:
            SDO_MIGRATE.TO_CURRENT(' TABLE ',' GEOMETRY_COLUMN ');
         If you create a table with geometry columns, and populate it, =
and
try show it in the mapserv? It appeared in the image?

    I don't know if I help you, sorry my poor english.
    If you need some help, you can report me.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Fernando Simon - simon at cttmar.univali.br
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
DataBase Manager, Mapserver, OracleSpatial and PostGis Developer
G10 - Laborat=F3rio de Computa=E7ao Aplicada
Fone: 047 - 341 7960
http://g10.cttmar.univali.br
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Jiri Potocny wrote:
Dear MapServer experts,

could you please help me with following problem?

I'm trying to display data strored in Oracle Spatial, but I allways get =
a
blank map.
The map file is as follows:

MAP
  IMAGETYPE      PNG
  EXTENT -551051 -1288960 -548185 -1286773
  SIZE           800 600
  IMAGECOLOR     255 255 255

  LAYER
    NAME           parcels
    DUMP           TRUE
    STATUS         DEFAULT
    TYPE           POLYGON

    CONNECTIONTYPE oraclespatial
    CONNECTION     username/password at netservisname
    DATA           "BOUNDARY FROM PARCELS"

    CLASS
      COLOR        232 232 232
      OUTLINECOLOR 32 32 32
    END
  END

END

If I export the data from oracle into the shapefile and use following =
map
file, everything is O.K.

MAP
  IMAGETYPE      PNG
  EXTENT -551051 -1288960 -548185 -1286773
  SIZE           800 600
  SHAPEPATH      "data"
  IMAGECOLOR     255 255 255

  LAYER
    NAME           parcely
    DUMP           TRUE
    STATUS         DEFAULT
    TYPE           POLYGON
    DATA          PARCELS

    CLASS
      COLOR        232 232 232
      OUTLINECOLOR 32 32 32
    END
  END

END

The only difference is in the data source, the data (attributes as well =
as
geometries) are the same. I'm running MapServer 4.0.1 with Oracle =
Spatial
support (downloaded from
http://hypnos.cbs.umn.edu/projects/downloads/ms401_png_oracle.zip) and
Oracle Database Server version 9.2.0.1.0 on Windows 2000 Professional SP =
3.

Could anybody get throught this problem.

Much appreciated,
Jirka

_______________________________________________
Mapserver-users mailing list
Mapserver-users at lists.gis.umn.edu
http://lists.gis.umn.edu/mailman/listinfo/mapserver-users

_______________________________________________
Mapserver-users mailing list
Mapserver-users at lists.gis.umn.edu
http://lists.gis.umn.edu/mailman/listinfo/mapserver-users

 =20

------=_NextPart_000_0070_01C4100C.B016C230
Content-Type: text/html;
	charset="iso-8859-2"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE></TITLE>
<META http-equiv=3DContent-Type =
content=3Dtext/html;charset=3DISO-8859-2>
<META content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#d7d7d7>
<DIV><FONT face=3DArial size=3D2>Ferando,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>thank you very much, your hints =
were&nbsp;extremaly=20
helpful.&nbsp;I was using&nbsp;gtype 3007. It was pretty easy to switch =
from 3D=20
to 2D, since I didn't use z-coord (in fact it was always 0). So, now=20
I've&nbsp;got gtype 20xx.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Regarding the conversion to SDO. I do =
not=20
use&nbsp;ArcGIS neither conversion utility shp2sdo. I read geometries =
from text=20
file (it's a proprietary&nbsp;format called VTX used just in our =
country) and=20
creating CSV file for Oracle SQLLDR utility.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Then I execute:</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
  <DIV><FONT face=3D"Courier New" size=3D1>SQLLDR&nbsp; </FONT><A=20
  href=3D"mailto:UMN/UMN at KATASTER"><FONT face=3D"Courier New"=20
  size=3D1>UMN/UMN at KATASTER</FONT></A><FONT face=3D"Courier New" =
size=3D1>=20
  CONTROL=3DPARCELS.CTL&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
  LOG=3DLOG\PARCELS.LOG</FONT></DIV></BLOCKQUOTE>
<DIV><FONT face=3DArial size=3D2>The example of control file=20
(PARCELS.CTL):</FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
  <DIV><FONT face=3D"Courier New" size=3D1>LOAD DATA <BR>INFILE=20
  'PARCELS.DAT'<BR>APPEND INTO TABLE PARCELS<BR>FIELDS TERMINATED BY '|' =

  OPTIONALLY ENCLOSED BY '"' <BR>TRAILING NULLCOLS <BR>( <BR>&nbsp; =
GR_ID,=20
  <BR>&nbsp; NUM, <BR>&nbsp; BOUNDARY COLUMN OBJECT <BR>&nbsp; ( =
sdo_gtype=20
  INTEGER EXTERNAL, <BR>&nbsp;&nbsp;&nbsp; sdo_srid INTEGER EXTERNAL,=20
  <BR>&nbsp;&nbsp;&nbsp;&nbsp; isnull FILLER CHAR, =
<BR>&nbsp;&nbsp;&nbsp;=20
  SDO_POINT COLUMN OBJECT&nbsp; NULLIF BOUNDARY.isnull=3D"pt"=20
  <BR>&nbsp;&nbsp;&nbsp; ( X INTEGER EXTERNAL,=20
  <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Y INTEGER EXTERNAL,=20
  <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Z INTEGER EXTERNAL), =
<BR>&nbsp;&nbsp;&nbsp;=20
  SDO_ELEM_INFO VARRAY terminated by ';' =
<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
  (SDO_ORDINATES char(38)), <BR>&nbsp;&nbsp;&nbsp; SDO_ORDINATES VARRAY=20
  terminated by ':' <BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SDO_ORDINATES =
char(38)))=20
  , <BR>&nbsp; STATUS<BR>)<BR></FONT></DIV></BLOCKQUOTE>
<DIV><FONT face=3DArial size=3D2>
<DIV><FONT face=3DArial size=3D2>The example of&nbsp;data file=20
(PARCELS.DAT):</FONT></DIV></FONT></DIV>
<BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px">
  <DIV><FONT face=3D"Courier New" size=3D1>1|"00001001_"|2007|=20
  =
|pt||||1|1003|1|;-550000|-1280000|-550010|-1280000|-550010|-1280010|-5500=
00|-1280010|-550000|-1280000|:1|<BR>2|"00001002_"|2007|=20
  =
|pt||||1|1003|1|;-550010|-1280000|-550020|-1280000|-550020|-1280010|-5500=
10|-1280010|-550010|-1280000|:1|</FONT></DIV></BLOCKQUOTE>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thank you for helping me,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Jirka</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<BLOCKQUOTE=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV style=3D"FONT: 10pt arial">----- Original MDessage ----- </DIV>
  <DIV=20
  style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: =
black"><B>From:</B>=20
  <A title=3Dsimon at inf.univali.br =
href=3D"mailto:simon at inf.univali.br">Fernando=20
  S.</A> </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A =
title=3Dpotocny at espace.cz=20
  href=3D"mailto:potocny at espace.cz">Jiri Potocny</A> ; <A=20
  title=3Dmapserver-users at lists.gis.umn.edu=20
  =
href=3D"mailto:mapserver-users at lists.gis.umn.edu">mapserver-users at lists.g=
is.umn.edu</A>=20
  </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Thursday, March 18, 2004 =
6:45=20
  PM</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> Re: [Mapserver-users] =
Unable to=20
  display data stored in Oracle Spatial</DIV>
  <DIV><FONT face=3DArial size=3D2></FONT><FONT face=3DArial =
size=3D2></FONT><FONT=20
  face=3DArial size=3D2></FONT><FONT face=3DArial size=3D2></FONT><FONT =
face=3DArial=20
  size=3D2></FONT><FONT face=3DArial=20
  size=3D2></FONT><BR></DIV>Jiri,<BR>&nbsp;&nbsp;&nbsp; I believe that =
you need a=20
  quickly report. Well, if you execute a CGI call to mapserv, what's =
happen? Can=20
  you see any image with db data?<BR>&nbsp;&nbsp;&nbsp; Try to execute =
the same=20
  query in SQLPLUS, SELECT BOUNDARY FROM PARCELS. What the sqplus =
report? Can=20
  you report one complete line that sql report? Like this: =
<BR><BR>SQL&gt;=20
  select shape from cola_markets;<BR><BR>SHAPE(SDO_GTYPE, SDO_SRID, =
SDO_POINT(X,=20
  Y, Z), SDO_ELEM_INFO,=20
  =
SDO_ORDINATES)<BR>-------------------------------------------------------=
-------------------------<BR>SDO_GEOMETRY(2003,=20
  NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARR<BR>AY(1, =
1, 5,=20
  7))<BR><BR>&nbsp;&nbsp;&nbsp; What kind of geometry report? 2003?, =
3003? If=20
  it's report 300X, this is the problem, when you import the data using =
loader,=20
  it import with type 300X (if you have this type of shape), the mapserv =
can't=20
  show this type. Only 100x or 200x.<BR>&nbsp;&nbsp;&nbsp;&nbsp; When =
you=20
  convert your shape to sdo, what command you execute? I use this ( I =
didn't use=20
  SRID in this example): <BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; =
shp2sdo.exe=20
  -o &lt;SHAPE&gt; &lt;TABLE&gt; -g &lt;GEOMETRY_COLUMNS&gt; -d -x =
(-180,180) -y=20
  (-90,90) -v<BR>&nbsp;&nbsp;&nbsp; And after don't forget to execute =
this=20
  function (select sdo_tune.extent_of('table with geometry_column',=20
  'geometry_columns') from dual;) <BR>&nbsp;<BR>&nbsp;&nbsp;&nbsp; Well, =
here we=20
  developer one project tha use all of informations, (user datas, =
shapes, user=20
  points) from Oracle. Look in this link (<A =
class=3Dmoz-txt-link-freetext=20
  =
href=3D"http://g10novo.cttmar.univali.br/gtznovo/principal.php">http://g1=
0novo.cttmar.univali.br/gtznovo/principal.php</A>).<BR><BR><BR>&nbsp;&nbs=
p;&nbsp;=20
  If you need some help, you can report me.<BR><BR><FONT face=3DArial =
size=3D2><SPAN=20
  style=3D"FONT-SIZE: 10pt; FONT-FAMILY: =
Arial">=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20
  <BR>Fernando Simon - <A=20
  href=3D"mailto:simon at cttmar.univali.br">simon at cttmar.univali.br</A>=20
  =
<BR>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20
  <BR>DataBase Manager, Mapserver, OracleSpatial and PostGis Developer =
<BR>G10 -=20
  Laborat=F3rio de Computa=E7&atilde;o Aplicada <BR>Fone: 047 - 341 7960 =
<BR><A=20
  href=3D"http://g10.cttmar.univali.br">http://g10.cttmar.univali.br</A> =

  =
<BR>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</SPAN></FONT><BR><=
BR>Jiri=20
  Potocny wrote:=20
  <BLOCKQUOTE cite=3Dmid00d901c40cec$c7869e40$bc03a8c0 at twoflower =
type=3D"cite"><PRE wrap=3D"">Fernando,