-
15. Re: Custom connector/translator to access remote files.
dsusin Feb 22, 2013 4:58 PM (in response to rareddy)Sure, here you go:
-
linea_bus.kml.zip 72.1 KB
-
-
16. Re: Custom connector/translator to access remote files.
rareddy Feb 26, 2013 12:45 PM (in response to dsusin)Daniel,
I took your KML file, and simplicity sake I only used the Dynamic VDB feature. So, here are my results
1) Step one I copied your KML file to a folder "/home/rareddy/testing", and ran the following CLI script to create resource-adaptor connection to access the file. You can also edit the standalone-teiid.xml file to do this. See "docs/teiid/datasources/file" for more info. The below I executed in the "jboss-cli.sh" console.
/subsystem=resource-adapters/resource-adapter=fileDS:add(archive=teiid-connector-file.rar, transaction-support=NoTransaction) /subsystem=resource-adapters/resource-adapter=fileDS/connection-definitions=fileDS:add(jndi-name=java:/fileDS, class-name=org.teiid.resource.adapter.file.FileManagedConnectionFactory, enabled=true, use-java-context=true) /subsystem=resource-adapters/resource-adapter=fileDS/connection-definitions=fileDS/config-properties=ParentDirectory:add(value=/home/rareddy/testing/) /subsystem=resource-adapters/resource-adapter=fileDS/connection-definitions=fileDS/config-properties=AllowParentPaths:add(value=true) /subsystem=resource-adapters/resource-adapter=fileDS:activate
2) Next I defined the following Dynamic VDB. I called it as "kml-vdb.xml"
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <vdb name="kml" version="1"> <model name="geodata"> <source name="connector" translator-name="file" connection-jndi-name="java:/fileDS"/> </model> <model name="lines" visible = "true" type = "VIRTUAL"> <metadata type="DDL"><![CDATA[ CREATE VIEW coordinates (description varchar(50), styleUrl varchar(50), coordinates varchar(256)) AS SELECT description, styleUrl, coordinates FROM (EXEC geodata.getTextFiles('linea_bus.xml')) AS f, XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.opengis.net/kml/2.2', 'http://www.w3.org/2005/Atom' AS atom, 'http://www.google.com/kml/ext/2.2' AS gx, 'http://www.opengis.net/kml/2.2' AS kml), '/kml/Document/Folder/Placemark' PASSING XMLPARSE(DOCUMENT file) COLUMNS description string PATH 'description/text()', styleUrl string PATH 'styleUrl/text()', coordinates string PATH 'LineString/coordinates/text()') AS A; ]]> </metadata> </model> </vdb>
again using the above CLI console, I executed
deploy ~/testing/kml-vdb.xml
this does nothing but deploys the VDB. There are various ways to deploy a VDB, however this is very handy (to me atleast). Then I made sure the VDB is deployed in ACTIVE state.
3) Now using SquirreL, a JDBC client tool, I executed following statements and retrived results
SELECT description,styleUrl,coordinates FROM "lines"."coordinates";
Results:
description styleUrl coordinates L1<BR><BR><B>ELEVATION</B> = 0.0 #line1 /n0.6284924124,41.616147843,0 0.6277497789000001,41.6153274216,0 0.6273421575,41.6149463127,0 0.6271728671,41.6147880315,0 L2<BR><BR><B>ELEVATION</B> = 0.0 #line2 /n0.6318306513000001,41.6198268262,0 0.6301297715000001,41.6179343437,0 L3<BR><BR><B>ELEVATION</B> = 0.0 #line3 /n0.6285107655,41.6161360742,0 0.6277690892,41.6153167105,0 0.6271931447,41.614778225,0 L3<BR><BR><B>ELEVATION</B> = 0.0 #line3 /n0.6359559547,41.6224152488,0 0.6357793022,41.6222449221,0 0.6357114529,41.6221775905,0 0.6378917407,41.6209229742,0 L3<BR><BR><B>ELEVATION</B> = 0.0 #line3 /n0.6417442044,41.6277261661,0 0.6419231921,41.6277913063,0 0.6421492635,41.6277381733,0 0.6419100919,41.6269979737,0 0.6415759166,41.6264477442,0 0.6414579110000001,41.626265031,0 0.6412832156,41.6259275726,0 0.6410743894000001,41.6255493486,0 0.6410190958000001,41.6254368645,0 L3<BR><BR><B>ELEVATION</B> = 0.0 #line3 /n0.6376295144,41.6263087275,0 0.6391218344,41.6266750708,0 0.6399708311,41.6269739106,0 0.6401028561,41.6270548332,0 0.640272271,41.6271586729,0 0.6403449546,41.627190532,0 SELECT x.* FROM "lines"."coordinates" as t, TEXTTABLE(t.coordinates COLUMNS first string, "second" string, third string, forth string, fifth string) x
first second third forth fifth 0.6284924124 41.616147843 0 0.6277497789000001 41.6153274216 0 0.6273421575 0.6318306513000001 41.6198268262 0 0.6301297715000001 41.6179343437 0 0.6285107655 41.6161360742 0 0.6277690892 41.6153167105 0 0.6271931447 0.6359559547 41.6224152488 0 0.6357793022 41.6222449221 0 0.6357114529 0.6417442044 41.6277261661 0 0.6419231921 41.6277913063 0 0.6421492635 0.6376295144 41.6263087275 0 0.6391218344 41.6266750708 0 0.6399708311 0.6135180759 41.6121874219 0 0.6126851181 41.6122955532 0 0.6124476357000001 Let me know if this satisfies your question about support of the KML with Teiid? I did find a bug with Designer, when I was trying to use it, I logged it here https://issues.jboss.org/browse/TEIIDDES-1606
Thanks. Please do share your end application results in blog with us.
Ramesh..
-
17. Re: Custom connector/translator to access remote files.
dsusin Feb 27, 2013 3:47 AM (in response to rareddy)Hi Ramesh, definitely it was this Designer bug what was holding me back, I just did not know there was a bug in there... I think I'll keep using my translator, though, because I took the time to program it and it supports also KMZ files (which are a zipped file structure with a "doc.kml" file in it). Also this way I do not have to worry about deploying extra VDBs.
Thanks.
-
18. Re: Custom connector/translator to access remote files.
rareddy Feb 27, 2013 10:30 AM (in response to dsusin)Daniel,
Even with above you would need a translator for unzip process. However I did not understand your extra VDBs comment, even with your translator, you still would need to define a VDB, albeit lot more simple one than above.
One question I have is that, is KML has any schema?
Ramesh..
-
19. Re: Custom connector/translator to access remote files.
dsusin Feb 27, 2013 10:57 AM (in response to rareddy)You're right about the additional VDB, was not thinking clear here. Have you tried with the Designer, but using invokeHttp instead of gettextfiles? Also I see you renamed the extension from .kml to .xml, any reason for that? That reminds me that the Designer's xml wizard cannot be used in this case for the simple reason that extension must be literally '.xml'. I think that it should be more flexible and offer also the option to select any extension.
The schema for KML woud be this one I guess: https://developers.google.com/kml/schema/kml21.xsd
-
20. Re: Custom connector/translator to access remote files.
rareddy Feb 27, 2013 11:06 AM (in response to dsusin)Daniel,
Have you tried with the Designer, but using invokeHttp instead of gettextfiles?
In my case I had the file locally on the drive so I used file translator along with file-resource-adaptor, if your KML is served from a web-service, then you can use invokeHttp which would do same functionality of getting the contents from remote service. No, I have not tried with Designer after I found the bug. However, the transformation can be manually altered to make it work in Designer. It is the wizard issue.
You're right about the additional VDB, was not thinking clear here. Have you tried with the Designer, but using invokeHttp instead of gettextfiles? Also I see you renamed the extension from .kml to .xml, any reason for that? That reminds me that the Designer's xml wizard cannot be used in this case for the simple reason that extension must be literally '.xml'. I think that it should be more flexible and offer also the option to select any extension.
Yes, that is correct. Initially I was trying to use Designer, and it would not let me select a .KML extension file, so I had renamed it. However, in my example shown above there is no such restriction. You should log enhancement request in Designer JIRA for this, as this is a valid case.
Thanks for the KML schema link.
Ramesh..