tag:blogger.com,1999:blog-4451657389087868432.post7692874271373634846..comments2023-05-29T02:10:01.374-06:00Comments on sworldwatch: Using SQL from Magik against VMDS #swucAlfred Sawatzkyhttp://www.blogger.com/profile/15845723068853240174noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-4451657389087868432.post-58245360685057825562011-01-17T09:02:56.828-07:002011-01-17T09:02:56.828-07:00I've run a simple query using IN operator both...I've run a simple query using IN operator both in sql2_parser and sql() procedure. But it takes too long time. Anyone can help me how to tune up sql2_parser so that can run faster? I've already created the index for all fields to be called in the query.<br /><br />MagikSF> p << sql2_parser.new_with_properties(:soc, gis_program_manager.spatial_object_controller(:gis), :dataset, gis_program_manager.cached_dataset(:water))<br />$<br />p.parse_sql("select * from premise_table where premise_id in ('000049') ")<br />$<br />res << p.process_sql()<br />$Syafiudinnoreply@blogger.comtag:blogger.com,1999:blog-4451657389087868432.post-49770861552609335222010-10-01T06:35:21.623-06:002010-10-01T06:35:21.623-06:00here is a code snippet that you can use to do what...here is a code snippet that you can use to do what you want. change complex area name and name of circuit table and geometry accordingly - <br /><br />_block<br /> circs << rope.new()<br /><br /> _for sa _over po!complex_area.table.select(predicate.eq(:name,"COMPLEX AREA NAME")).an_element().simple_areas.elements() <br /> _loop<br /> circs.add_all_last(evc[:CIRCUIT_TABLE].select(predicate_spatial.new(:CIRCUIT_GEOMETRY, :inside, {sa.rwo.area})))<br /> _endloop<br /> <br />_endblockAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-4451657389087868432.post-57703591287161793852010-09-30T09:14:10.128-06:002010-09-30T09:14:10.128-06:00Thanks for the quick responses; it worked.
Now I ...Thanks for the quick responses; it worked.<br /><br />Now I am trying to use the sql2_parser to run an existing SQL Select stmt we have that gets Circuits for a specified Complex_Area. But some of the Oracle tables used are not found; I'm guessing they are not 'reverse engineered' into Smallworld VMDS?<br /><br />Does anyone know of a way to get Circuits for a given PO_Complex_Area in Smallworld PowerOn, using objects in the Electric datastore? (here's where a Smallworld object model diagram, showing properties, methods and relationships would come in handy)<br /><br />Thanks!Peter Realehttps://www.blogger.com/profile/15699550920665830024noreply@blogger.comtag:blogger.com,1999:blog-4451657389087868432.post-41924036247797952582010-09-30T07:20:15.956-06:002010-09-30T07:20:15.956-06:00Thanks for the quick responses; it worked.
I am n...Thanks for the quick responses; it worked.<br /><br />I am now trying to use the sql2_parser in Magik code, to get a list of Circuits by Complex_Area, but the SQL Select stmt we have already coded, uses Oracle tables that are not 'reverse engineered' into Smallworld. Does anyone know of a way to map Complex_Areas to Circuits within PowerOn VMDS?<br /><br />Thanks!Peter Realehttps://www.blogger.com/profile/15699550920665830024noreply@blogger.comtag:blogger.com,1999:blog-4451657389087868432.post-34524793725345769162010-09-29T07:13:00.070-06:002010-09-29T07:13:00.070-06:00Just by pressing the 'Enter' at the 'S...Just by pressing the 'Enter' at the 'SQL>' promptUnknownhttps://www.blogger.com/profile/10923858216815137986noreply@blogger.comtag:blogger.com,1999:blog-4451657389087868432.post-86956922682128866962010-09-29T07:05:04.776-06:002010-09-29T07:05:04.776-06:00Hi Peter,
Just click return/enter a couple of tim...Hi Peter,<br /><br />Just click return/enter a couple of times and you will exit the SQL prompt.<br /><br />AlfredAlfred Sawatzkyhttps://www.blogger.com/profile/15845723068853240174noreply@blogger.comtag:blogger.com,1999:blog-4451657389087868432.post-26522650889854550352010-09-29T06:50:11.127-06:002010-09-29T06:50:11.127-06:00I'm thinking about using SQL for an on-demand ...I'm thinking about using SQL for an on-demand report, but first wanted to try it out at the console. I can get the SQL> prompt by using the sql() procedure, but how do I 'exit' out of SQL> SQL> and get back to the MagikSF> prompt?<br /><br />Thanks,<br />Peter RealePeter Realehttps://www.blogger.com/profile/15699550920665830024noreply@blogger.comtag:blogger.com,1999:blog-4451657389087868432.post-34602331643712608442010-09-11T07:16:13.647-06:002010-09-11T07:16:13.647-06:00I've found SQL useful in cases when I need to ...I've found SQL useful in cases when I need to see UNIQUE values of a not enumerated field(s) :<br /><br />MagikSF> sql(gis_program_manager.cached_dataset(:gis))<br />$<br />SQL> select DISTINCT street from address<br />$<br />SQL> select DISTINCT zip_code, street from address<br />$<br /><br />This way I can see the result quickly without writing _loop/_if statements.Unknownhttps://www.blogger.com/profile/10923858216815137986noreply@blogger.com