[Mapserver-users] geometryfromtext in orcale spatial?

Tue Jun 22 07:46:47 PDT 2004

This is a multi-part message in MIME format.
--------------020704010408010600090804
Content-Type: text/plain; charset=GB2312
Content-Transfer-Encoding: 8bit

Hi,
Sorry one more time for the delay to report you. Well, exist two ways to
do this. One, by Mapscript, and other using plsql. I don't know but I
believe that exist others ways to do this, but I don't know if can be
more easy.
The first, I worked using this, php-mapscript, is executing the query
that return the points for line directly in php and store this return in
a array, after implode this array to a string separated by comma and
unsing this string inside the query. And after set the /data/ for the
layer using mapscript.
The second is creating one plsql function that return the points to the
second query. I never worked using this way.
So, the way to return only the point x an y, by sql in php is "/select
t.geom.sdo_point.x, t.geom.sdo_point.y from table t/". This query return
only X and Y from the points stored in geometry column. Look that you
need to define one alias to the table.
Sorry my poor english.

====================================================
Fernando Simon - simon at cttmar.univali.br
====================================================
DataBase Manager, Mapserver, OracleSpatial Developer
G10 - Laboratorio de Computacao Aplicada
http://g10.cttmar.univali.br
====================================================

> Fernando Simon,
> Thank you!
> I think your suggestion work. But "*THE QUERY THAT RETURN THE POINTS
> FOR LINE"*seems doesn't accept statement like "select the_geom from
> gpsTable where route_id=xxx". How can translate the query result
> (points) into something SDO_ORDINATE_ARRAY will accept?
> Is there any function in oracle spatial to do this work?
>
>     -----Original Message-----
>     *From:* mapserver-users-admin at lists.gis.umn.edu
>     [mailto:mapserver-users-admin at lists.gis.umn.edu]*On Behalf Of
>     *Fernando S.
>     *Sent:* Monday, June 14, 2004 10:52 PM
>     *To:* Fu Chen; mapserver-users at lists.gis.umn.edu
>     *Subject:* Re: [Mapserver-users] geometryfromtext in orcale spatial?
>
>     Hi,
>     Well, sorry to report you only now. I believe that you need to
>     define the geometry column when you execute the query. I don't
>     test this query, but I believe that work's. I already work with
>     this way, and run correctly with mapserv.
>     So try to use:
>     data "the_geom from (select id, mdsys.SDO_GEOMETRY(2002, NULL,
>     NULL, mdsys.SDO_ELEM_INFO_ARRAY(1, 2, 1),
>     mdsys.SDO_ORDINATE_ARRAY( *THE QUERY THAT RETURN THE POINTS FOR
>     LINE *)) as the_geom FROM table where id = XXXXX)"
>     2002 - to define line
>     (1, 2, 1) - to define line
>
>     Here I used this and work correctly:
>     data "shape from (select mdsys.SDO_GEOMETRY(2003, 82295, NULL,
>     mdsys.SDO_ELEM_INFO_ARRAY(1, 1003, 4),
>     mdsys.SDO_ORDINATE_ARRAY(667052.077, 7092501.44, 657052.077,
>     7082501.44, 667052.077, 7072501.44)) as shape FROM fazendas where
>     codigo_int = 79284237)"
>
>     I believe that this example can help you to define your query.
>     If you need some help you can report me.
>
>
>     ==================================================================
>     Fernando Simon - simon at cttmar.univali.br
>     ==================================================================
>     DataBase Manager, Mapserver, OracleSpatial Developer
>     G10 - Laborat¨®rio de Computac,a~o Aplicada
>     http://g10.cttmar.univali.br
>     ==================================================================
>
>
>     Fu Chen wrote:
>
>>Hi!
>>I need to make a feature, that link gps points into line to render in mapserver.
>>Vladimir Ilich Guzm¨¢n R. suggest to use geometryfromtest feature in postgis to achieve it. 
>>It is something like
>>
>>$data="
>>the_geom from (select oid,rumbo, geometryfromtext('MULTILINESTRING((' ||
>>(select x(gps_geom) || ' ' || y(gps_geom) from gps_vehi ant where
>>ant.gid<act.gid and ant.id_vehi=$id_vehi order by gid desc limit 1) ||
>>',' || x(gps_geom) || ' ' || y(gps_geom) || '))',1) as the_geom from
>>gps_vehi act where act.tiempo>='$from_time' AND act.tiempo<'$to_time'
>>AND act.id_vehi=$id_vehi) as foo using unique oid using srid=1";
>>$layer=$map->getLayerByName("secuencia_gps");
>>$layer->set("data",$data);
>>
>>This method works in postgis. But this time I have to use oracle database.
>>How can I achieve this feature in oracle spatial? Is there any oracle function I can use to replace geometryfromtext?
>>
>>Thanks!
>>
>>-----Original Message-----
>>From: mapserver-users-admin at lists.gis.umn.edu
>>[mailto:mapserver-users-admin at lists.gis.umn.edu]On Behalf Of Vladimir
>>Guzm¨¢n
>>Sent: Thursday, June 03, 2004 8:01 PM
>>To: Fu Chen
>>Cc: mapserver-users at lists.gis.umn.edu
>>Subject: Re: [Mapserver-users] store and draw route
>>
>>
>>Hello.
>>We use postgis + phpmapscript, and is something like this:
>>
>>$data="
>>the_geom from (select oid,rumbo, geometryfromtext('MULTILINESTRING((' ||
>>(select x(gps_geom) || ' ' || y(gps_geom) from gps_vehi ant where
>>ant.gid<act.gid and ant.id_vehi=$id_vehi order by gid desc limit 1) ||
>>',' || x(gps_geom) || ' ' || y(gps_geom) || '))',1) as the_geom from
>>gps_vehi act where act.tiempo>='$from_time' AND act.tiempo<'$to_time'
>>AND act.id_vehi=$id_vehi) as foo using unique oid using srid=1";
>>$layer=$map->getLayerByName("secuencia_gps");
>>$layer->set("data",$data);
>>
>>Basically, what we do is a subquery to create a multilinestring from a
>>point geometry.
>>
>>Hpe that helps,
>>
>>Vladimir Ilich Guzm¨¢n R.
>>------------------------
>>http://maintask.com 
>>
>>
>>
>>Fu Chen wrote:
>>
>>
>>>Hi!
>>>I try to use mapserver and postgis to record and render the some mobile worker's route.
>>>Those routes are record by a data table like (workorID, time, position).
>>>In the webgis interface, we try to draw a route map by mapserver.
>>>Queried by the workor's ID, the position is sort by the time. then these points is linked with a line. We would like to draw a circle(or other symbol) on the key point if it is possible.
>>>I don't know how to write map file or mapscript to get the feature.
>>>Anybody can give me some suggestion?
>>>Thank you!
>>>
>>>_______________________________________________
>>>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
>>
>>_______________________________________________
>>Mapserver-users mailing list
>>Mapserver-users at lists.gis.umn.edu
>>http://lists.gis.umn.edu/mailman/listinfo/mapserver-users
>>
>

--------------020704010408010600090804
Content-Type: text/html; charset=GB2312
Content-Transfer-Encoding: 8bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=GB2312" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<font face="Helvetica, Arial, sans-serif">Hi,<br>
    Sorry one more time for the delay to report you. Well, exist two
ways to do this. One, by Mapscript, and other using plsql. I don't know
but I believe that exist others ways to do this, but I don't know if
can be more easy.<br>
    The first, I worked using this, php-mapscript, is executing the
query that return the points for line directly in php and store this
return in a array, after implode this array to a string separated by
comma and unsing this string inside the query. And after set the <i>data</i>
for the layer using mapscript.<br>
    The second is creating one plsql function that return the points to
the second query. I never worked using this way.<br>
    So, the way to return only the point x an y, by sql</font><font
 face="Helvetica, Arial, sans-serif"> in php is "<i>select
t.geom.sdo_point.x, t.geom.sdo_point.y from table t</i>". This query
return only X and Y from the points stored in geometry column. Look
that you need to define one alias to the table.<br>
    Sorry my poor english.<br>
</font><font face="Helvetica, Arial, sans-serif">       <br>
<br>
==================================================== <br>
Fernando Simon - <a class="moz-txt-link-abbreviated"
 href="mailto:simon at cttmar.univali.br">simon at cttmar.univali.br</a><br>
==================================================== <br>
DataBase Manager, Mapserver, OracleSpatial Developer <br>
G10 - Laboratorio de Computacao Aplicada <br>
<a class="moz-txt-link-freetext" href="http://g10.cttmar.univali.br">http://g10.cttmar.univali.br</a>
<br>
==================================================== </font><br>
<blockquote cite="midHLENLPCAGDEHMNMNLIDJCEDPCDAA.fchen at ne.rsgs.ac.cn"
 type="cite">
  <div><font face="Helvetica, Arial, sans-serif"><span
 class="609354811-15062004">Fernando Simon,</span></font></div>
  <div><font face="Helvetica, Arial, sans-serif"><span
 class="609354811-15062004">Thank you!</span></font></div>
  <div><font face="Helvetica, Arial, sans-serif"><span
 class="609354811-15062004">I think your suggestion work. But "<strong>THE
QUERY THAT RETURN THE POINTS FOR LINE" </strong>seems doesn't accept
statement like "select the_geom from gpsTable where route_id=xxx". How
can translate the query result (points) into something
SDO_ORDINATE_ARRAY will accept?</span></font></div>
  <div><font face="Helvetica, Arial, sans-serif"><span
 class="609354811-15062004">Is there any function in oracle spatial to
do this work?</span></font></div>
  <div><font face="Helvetica, Arial, sans-serif"><span
 class="609354811-15062004"></span> </font></div>
  <blockquote>
    <div class="OutlookMessageHeader" dir="ltr" align="left"><font
 face="Helvetica, Arial, sans-serif" size="2">-----Original Message-----<br>
    <b>From:</b> <a class="moz-txt-link-abbreviated" href="mailto:mapserver-users-admin at lists.gis.umn.edu">mapserver-users-admin at lists.gis.umn.edu</a>
[<a class="moz-txt-link-freetext" href="mailto:mapserver-users-admin at lists.gis.umn.edu">mailto:mapserver-users-admin at lists.gis.umn.edu</a>]<b>On Behalf Of </b>Fernando
S.<br>
    <b>Sent:</b> Monday, June 14, 2004 10:52 PM<br>
    <b>To:</b> Fu Chen; <a class="moz-txt-link-abbreviated" href="mailto:mapserver-users at lists.gis.umn.edu">mapserver-users at lists.gis.umn.edu</a><br>
    <b>Subject:</b> Re: [Mapserver-users] geometryfromtext in orcale
spatial?<br>
    <br>
    </font></div>
    <font face="Helvetica, Arial, sans-serif">Hi, <br>
    Well, sorry to report you only now. I believe that you need to
define the geometry column when you execute the query. I don't test
this query, but I believe that work's. I already work with this way,
and run correctly with mapserv.<br>
    So try to use:<br>
       data "the_geom from (select id, mdsys.SDO_GEOMETRY(2002, NULL,
NULL, mdsys.SDO_ELEM_INFO_ARRAY(1, 2, 1), mdsys.SDO_ORDINATE_ARRAY( <b>THE
QUERY THAT RETURN THE POINTS FOR LINE </b>)) as the_geom FROM table
where id = XXXXX)"<br>
    2002 - to define line<br>
    (1, 2, 1) - to define line<br>
    <br>
    Here I used this and work correctly:<br>
    data "shape from (select mdsys.SDO_GEOMETRY(2003, 82295, NULL,
mdsys.SDO_ELEM_INFO_ARRAY(1, 1003, 4),
mdsys.SDO_ORDINATE_ARRAY(667052.077, 7092501.44, 657052.077,
7082501.44, 667052.077, 7072501.44)) as shape FROM fazendas where
codigo_int = 79284237)"<br>
    <br>
    I believe that this example can help you to define your query. <br>
    If you need some help you can report me.<br>
    <br>
    <br>
================================================================== <br>
Fernando Simon - <a class="moz-txt-link-abbreviated"
 href="mailto:simon at cttmar.univali.br">simon at cttmar.univali.br</a><br>
================================================================== <br>
DataBase Manager, Mapserver, OracleSpatial Developer <br>
G10 - Laborat¨®rio de Computação Aplicada <br>
    <a class="moz-txt-link-freetext" href="http://g10.cttmar.univali.br">http://g10.cttmar.univali.br</a><br>
================================================================== <br>
    </font> <font face="Helvetica, Arial, sans-serif"><br>
    <br>
Fu Chen wrote:</font>
    <blockquote
 cite="midHLENLPCAGDEHMNMNLIDJCECGCDAA.fchen at ne.rsgs.ac.cn" type="cite">
      <pre wrap=""><font face="Helvetica, Arial, sans-serif">Hi!
I need to make a feature, that link gps points into line to render in mapserver.
Vladimir Ilich Guzm¨¢n R. suggest to use geometryfromtest feature in postgis to achieve it. 
It is something like

$data="
the_geom from (select oid,rumbo, geometryfromtext('MULTILINESTRING((' ||
(select x(gps_geom) || ' ' || y(gps_geom) from gps_vehi ant where
ant.gid<act.gid and ant.id_vehi=$id_vehi order by gid desc limit 1) ||
',' || x(gps_geom) || ' ' || y(gps_geom) || '))',1) as the_geom from
gps_vehi act where act.tiempo>='$from_time' AND act.tiempo<'$to_time'
AND act.id_vehi=$id_vehi) as foo using unique oid using srid=1";
$layer=$map->getLayerByName("secuencia_gps");
$layer->set("data",$data);

This method works in postgis. But this time I have to use oracle database.
How can I achieve this feature in oracle spatial? Is there any oracle function I can use to replace geometryfromtext?

Thanks!

-----Original Message-----
From: <a class="moz-txt-link-abbreviated"
 href="mailto:mapserver-users-admin at lists.gis.umn.edu">mapserver-users-admin at lists.gis.umn.edu</a>
[<a class="moz-txt-link-freetext"
 href="mailto:mapserver-users-admin at lists.gis.umn.edu">mailto:mapserver-users-admin at lists.gis.umn.edu</a>]On Behalf Of Vladimir
Guzm¨¢n
Sent: Thursday, June 03, 2004 8:01 PM
To: Fu Chen
Cc: <a class="moz-txt-link-abbreviated"
 href="mailto:mapserver-users at lists.gis.umn.edu">mapserver-users at lists.gis.umn.edu</a>
Subject: Re: [Mapserver-users] store and draw route

Hello.
We use postgis + phpmapscript, and is something like this:

$data="
the_geom from (select oid,rumbo, geometryfromtext('MULTILINESTRING((' ||
(select x(gps_geom) || ' ' || y(gps_geom) from gps_vehi ant where
ant.gid<act.gid and ant.id_vehi=$id_vehi order by gid desc limit 1) ||
',' || x(gps_geom) || ' ' || y(gps_geom) || '))',1) as the_geom from
gps_vehi act where act.tiempo>='$from_time' AND act.tiempo<'$to_time'
AND act.id_vehi=$id_vehi) as foo using unique oid using srid=1";
$layer=$map->getLayerByName("secuencia_gps");
$layer->set("data",$data);

Basically, what we do is a subquery to create a multilinestring from a
point geometry.

Hpe that helps,

Vladimir Ilich Guzm¨¢n R.
------------------------
<a class="moz-txt-link-freetext" href="http://maintask.com">http://maintask.com</a> 

Fu Chen wrote:

</font></pre>
      <blockquote type="cite">
        <pre wrap=""><font face="Helvetica, Arial, sans-serif">Hi!
I try to use mapserver and postgis to record and render the some mobile worker's route.
Those routes are record by a data table like (workorID, time, position).
In the webgis interface, we try to draw a route map by mapserver.
Queried by the workor's ID, the position is sort by the time. then these points is linked with a line. We would like to draw a circle(or other symbol) on the key point if it is possible.
I don't know how to write map file or mapscript to get the feature.
Anybody can give me some suggestion?
Thank you!

_______________________________________________
Mapserver-users mailing list
<a class="moz-txt-link-abbreviated"
 href="mailto:Mapserver-users at lists.gis.umn.edu">Mapserver-users at lists.gis.umn.edu</a>
<a class="moz-txt-link-freetext"
 href="http://lists.gis.umn.edu/mailman/listinfo/mapserver-users">http://lists.gis.umn.edu/mailman/listinfo/mapserver-users</a>

</font></pre>
      </blockquote>
      <pre wrap=""><!----><font face="Helvetica, Arial, sans-serif">
_______________________________________________
Mapserver-users mailing list
<a class="moz-txt-link-abbreviated"
 href="mailto:Mapserver-users at lists.gis.umn.edu">Mapserver-users at lists.gis.umn.edu</a>
<a class="moz-txt-link-freetext"
 href="http://lists.gis.umn.edu/mailman/listinfo/mapserver-users">http://lists.gis.umn.edu/mailman/listinfo/mapserver-users</a>

_______________________________________________
Mapserver-users mailing list
<a class="moz-txt-link-abbreviated"
 href="mailto:Mapserver-users at lists.gis.umn.edu">Mapserver-users at lists.gis.umn.edu</a>
<a class="moz-txt-link-freetext"
 href="http://lists.gis.umn.edu/mailman/listinfo/mapserver-users">http://lists.gis.umn.edu/mailman/listinfo/mapserver-users</a>

</font></pre>
    </blockquote>
    <font face="Helvetica, Arial, sans-serif"><br>
    </font></blockquote>
</blockquote>
<br>
</body>
</html>

--------------020704010408010600090804--