Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

GML To KML

569268Feb 26 2009 — edited Mar 14 2009
Dear All,
I had created a Stored Procedure to Convert Oracle Spatial Polygon Data to KML .
the Procedure works very fine however the Generated Kml File have a line break every 1000 Char so these breaks are making problems on the KML Format
for examples
</cord
inates>
also in the Cooridinates 55.2222,25.2222
2222 55,1234
I tried to increase the Size of the Line to 32767 however i sometimes have records which length be more that 114000
when i manually tried to open the kml file and remove those breaks it worked very fine
Please help as this procedure is about to be finished except that problem
here is my store Procedure

CREATE OR REPLACE PROCEDURE "PARCEL"."GENERATEPARCELS" is
y number;
f utl_file.file_type;
s varchar2(200);
begin
y:=1;
owa_util.mime_header('application/vnd.google-earth.kml+xml', true, 'ISO-8859-1');
f := utl_file.fopen('SAMPLEDIR','communities.kml','w',32767);
s := ('<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.0">
<Document>
<name>Communities</name>
<description>Dubai Communities</description> ');
utl_file.put_line(f,s);
for x in (select u.CNAME_E name,
replace( replace(replace(replace(replace(replace(replace(
regexp_replace(sdo_util.to_gmlgeometry( SDO_CS.TRANSFORM(u.geom, m.diminfo, 8199)),
'([[:digit:]])[ ](?-[[:digit:]])','\1,0 \2\3'),'gml:',''),'</coordinates>',',0 </coordinates>'),
'MultiPolygon','GeometryCollection'),'<polygonMember>',''),'</polygonMember>','') ,'srsName="SDO:8199" xmlns:gml="http://www.opengis.net/gml"','') ,' decimal="." cs="," ts=" "','') gml1
from communities u, user_sdo_geom_metadata m WHERE m.table_name = 'COMMUNITIES' AND m.column_name = 'GEOM')
loop
utl_file.put_line(f,'<Placemark>');
utl_file.put_line(f,'<name>'||x.name||'</name>');
utl_file.put_line(f,'<Style>
<PolyStyle>
<color>7fcccccc</color>
<colorMode>random</colorMode>
<fill>1</fill>
<outline>1</outline>
</PolyStyle>
</Style>');
while y <=length(x.gml1)
loop
utl_file.put_line(f,substr(x.gml1,y,1000));
y:=y+1000;
end loop;
y:=1;
utl_file.put_line(f,'</Placemark>');
end loop;
utl_file.put_line(f,'</Document></kml>');
utl_file.fclose(f);

end;

it is obvious that i defined the 1000 Char ...however i dont know what is the right number to define
i tried large numbers , instr (x.gml,chr(13) but it also didnt work
please advise
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 11 2009
Added on Feb 26 2009
7 comments
5,711 views