Configure Adhoc Query

From Omar Wiki

Jump to: navigation, search
[edit]

Create a spatial adhoc query

  • Back up misc/SubmitObjectsRequest_AdhocQuery.xml to SubmitObjectsRequest_AdhocQuery_bak.xml
  • Edit misc/SubmitObjectsRequest_AdhocQuery.xml with the following contents 

   <?xml version = "1.0" encoding = "UTF-8"?>
   <SubmitObjectsRequest xmlns="urn:oasis:names:tc:ebxml-regrep:xsd:lcm:3.0" 
       xmlns:lcm="urn:oasis:names:tc:ebxml-regrep:xsd:lcm:3.0" 
       xmlns:query="urn:oasis:names:tc:ebxml-regrep:xsd:query:3.0" 
       xmlns:rim="urn:oasis:names:tc:ebxml-regrep:xsd:rim:3.0" 
       xmlns:rs="urn:oasis:names:tc:ebxml-regrep:xsd:rs:3.0" 
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
       xsi:schemaLocation="urn:oasis:names:tc:ebxml-regrep:xsd:lcm:3.0  ../../../../ebxmlrr-spec/misc/3.0/schema/lcm.xsd">
       <rim:RegistryObjectList>   
           <rim:AdhocQuery lid="urn:x-ogc:specification:csw-ebrim:Query:findByWKT" id="urn:x-ogc:specification:csw-ebrim:Query:findByWKT">
               <rim:Name>
                   <rim:LocalizedString value="findByWKT"/>
               </rim:Name>
               <rim:Description>
                   <rim:LocalizedString value="Returns a list of registered datasets contained within the specified EWKT"/>
               </rim:Description>
               <rim:QueryExpression queryLanguage="urn:oasis:names:tc:ebxml-regrep:QueryLanguage:SQL-92">
                   SELECT eo.* FROM ExtrinsicObject eo, dataset_extents ds WHERE intersects(ds.extents, GEOMFROMEWKT($wkt)) AND ds.extrinsic_object_fk=eo.Id;
               </rim:QueryExpression>
           </rim:AdhocQuery>        
       </rim:RegistryObjectList>   
   </SubmitObjectsRequest>

this is where we have queried our dataset_extents table (with spatial index) using the intersects function

  • Run build loadAdhocQuery from omar.home
  • Edit /src/java/org/freebxml/omar/client/ui/common/conf/config.xml and add the query 

     <Query>
       <AdhocQueryRef id="urn:x-ogc:specification:csw-ebrim:Query:findByWKT"/>
      <Parameter parameterName="$wkt" datatype="string" defaultValue="SRID=4326;POLYGON(( -180 90, -180 -90, 180 -90, 180 90, -180 90))">
        <rim:Name>
          <rim:LocalizedString charset="UTF-8" value="label.wkt"/>
        </rim:Name>
        <rim:Description>
          <rim:LocalizedString charset="UTF-8" value="label.wkt.desc"/>
        </rim:Description>
      </Parameter>
   </Query>

  • Edit resource.bundle at omar.home\src\resources\org\freebxml\omar\client\ui\common and add 

   # spatial extensions
   label.wkt=OGC Well Known Text (extended by PostGIS EWKT)
   label.wkt.desc=Use EWKT e.g. SRID=4326;POLYGON(( -180 90, -180 -90, 180 -90, 180 90, -180 90))

  • we have to add a per query bypass method so that we can call spatial functions, so edit omar.home/conf/omar.properties and add

org.freebxml.omar.server.query.sql.SQLQueryProcessor.bypassSQLParser.ids=urn:x-ogc:specification:csw-ebrim:Query:findByWKT

Then we make a simple code changes to org.freebxml.omar.server.query.sql.SQLQueryProcessor, we add the property bypassSQLParserIds, and comment out bypassSQLParser so that we can use our default queries as well as the spatial queries. 

   //boolean bypassSQLParser = false;
   private ArrayList bypassSQLParserIds = new ArrayList();

change the constructor to look like 

   protected SQLQueryProcessor() {
      /* bypassSQLParser = Boolean.valueOf(RegistryProperties.getInstance()
           .getProperty("org.freebxml.omar.server.query.sql.SQLQueryProcessor.bypassSQLParser", "false")).booleanValue();*/
       String ids = RegistryProperties.getInstance().getProperty("org.freebxml.omar.server.query.sql.SQLQueryProcessor.bypassSQLParser.ids", "");
       bypassSQLParserIds.addAll(java.util.Arrays.asList(ids.split(",")));
   }

and finally in executeQuery 

           if (!bypassSQLParserIds.contains(context.getQueryId())) {
               //parse the queryString to sget at certain info like the select column and table name etc.
               InputStream stream = new ByteArrayInputStream(sqlQuery.getBytes("utf-8"));
               SQLParser parser = new SQLParser(new InputStreamReader(stream, "utf-8"));
               fixedQuery = parser.processQuery(user, responseOption);
               log.debug("Fixed query: " + fixedQuery + ";");
               tableName = parser.firstTableName;
           } else {
               String[] strs = sqlQuery.toUpperCase().split(" FROM ");
               if (strs.length > 1) {
                   tableName = (strs[1].split(" "))[0];
               }
               //tableName = sqlQuery.substring(sqlQuery.indexOf("FROM"));
           }

to enable spatial queries Note there is an RFE out to change this in CVS!!

  • Then rebuild, and deploy as before
Retrieved from "http://ebxmlrr.sourceforge.net/wiki/index.php/Configure_Adhoc_Query"