X

Oracle Spatial and Graph – technical tips, best practices, and news from the product team

Recent Posts

Spatial Studio Best Practice - Spatial Filters

We will be providing blog posts on best practices with Spatial Studio, and we begin here with spatial filters. But before we begin, a quick comment;  understanding best practices such as the info below will become less and less important with future releases. This is because we plan to enhance Spatial Studio with an increasingly guided UX to avoid pitfalls. Now on with the info... Spatial Studio supports multi-step analyses, where the result of an analysis is input to another analysis. In these cases the steps can be accomplished in more than 1 possible order of operations. However the order of operations can make a huge difference in performance, particularly with spatial filters. The rules to follow for spatial filters are: Apply spatial filters to datasets that are based on tables. Do not apply a spatial filter to an analysis result. The layer to use as the spatial filter (i.e. search area) can be a based on a table or an analysis. Use analyses to create a final search area, and use it to filter a dataset based on a table. Here's an example to illustrate this: In a previous article we demonstrated a spatial filter to identify Urban Areas located in the British Isles (UK and Ireland). The spatial filter operation was a 1-step process involving datasets based on tables (uploaded from Shapefiles) which worked just fine and there was no ambiguity in any order of operations. As a multi-step example, we'll identify Airports located in an Urban Area and in the British Isles. We begin by adding AIRPORTS from Natural Earth using the same steps as shown in previous article linked above: We have a few options to accomplish our scenario and need to understand which is the right way to go. Option 1:  Filter for Airports in British Isles, and then filter that result for items in Urban Areas. We reject this option since it applies a spatial filter to an analysis result Option 2:  Filter for Airports in Urban Areas, and then filter that result for items in British Isles. Again, we reject this option since it applies a spatial filter to an analysis result Option 3:  Filter for Urban Areas in British Isles, and then filter Airports using the result of the 1st analysis as the search area. This follows the rule of creating the overall spatial filter using analyses, and applying the spatial filter to a layer based on a table We now proceed with the scenario using the preferred Option 3 above: We encourage you to follow this approach to ensure performant spatial filter analyses. If you run into performance issues with a spatial filter analysis, the first thing to check will be whether this best practice has been followed.        

We will be providing blog posts on best practices with Spatial Studio, and we begin here with spatial filters. But before we begin, a quick comment;  understanding best practices such as the info...

Spatial and Graph

Spatial Studio - Hello World!

The newly released Oracle Spatial Studio ("Spatial Studio") simplifies spatial analysis and visualization capabilities in Oracle Database. This is the first installment of blog posts providing how-to, tips, best practices, and overall guidance in the use of Spatial Studio. We look forward to your feedback and creative uses of Spatial Studio! For an overview of Oracle Spatial Studio, please visit the product page at https://www.oracle.com/database/technologies/spatial-studio.html.  Spatial Studio is provided as both a deployable Java EE application and a pre-deployed, self-contained Quick Start. The Quick Start is an easy way to get up and running in just a few minutes. You just need an Oracle Database with Spatial and Graph, version 12.2 or newer. For instructions on setting up Spatial Studio, please refer to https://www.oracle.com/database/technologies/spatial-studio/get-started.html. Once you have Spatial Studio running, follow along with the basic workflow steps below: In this workflow we will load country and urban area polygons and then determine which urban areas are in a country of interest. Download Shapefiles: Go to https://www.naturalearthdata.com/downloads/10m-cultural-vectors/ Download "Admin 0 - Countries" and 'Urban Areas" and unzip the files You should now have the following 2 folders containing the Shapefiles: Create datasets: Log into Spatial Studio If using the Quick Start, log in as Admin user If using a WebLogic deployment, log in as the WebLogic admin user Click Create > Dataset Create Datasets from Shapefiles: Create Project: Click Create > Project Add datasets Create map and style layers: Perform Spatial Analysis: We will apply a spatial filter to identify Urban Areas in the British Isles Select the United Kingdom and Ireland. This will be our spatial filter Open Spatial Analysis for Urban Areas Configure and run spatial filter Drag analysis result into map and set style: There's lots more to cover, such as Spatial Analysis best practices Feeding the results of a spatial analysis into another analysis Saving our project so that we can revisit our work later Publishing our project for others to consume Exporting our results for upload to others applications such as Oracle Data Visualization Exposing our results as a GeoJSON REST endpoint for application integration These an other topics will be covered in subsequent posts so please stay tuned.      

The newly released Oracle Spatial Studio ("Spatial Studio") simplifies spatial analysis and visualization capabilities in Oracle Database. This is the first installment of blog posts providing how-to,...

Spatial and Graph

Announcing Oracle Spatial Studio

Oracle Spatial Studio, a self-service application for creating interactive maps and performing spatial analysis on your data, is now available for download on Oracle Technical Resources (formerly Oracle Technology Network) and Oracle Service Delivery Cloud. It has been designed for business users who do not have any GIS or Spatial knowledge. Now business users and non-GIS developers have a simple user interface to access the spatial features in Oracle Database and incorporate spatial analysis in applications and workflows. Users can easily get their data in Spatial Studio, prepare their data, visualize and perform spatial analysis on their data, and publish the results. Spatial Studio is included as an application with all Autonomous Database subscriptions and offerings that include Oracle Spatial and Graph option. These include Autonomous Database, Autonomous Data Warehouse, Autonomous Transaction Processing, Oracle Database Cloud Service High Performance Edition, Oracle Database Cloud Service Extreme Performance Edition, Oracle Database Exadata Cloud, Oracle Database Exadata Cloud at Customer, and Oracle Database Enterprise Edition with Spatial and Graph option. For more information on how to get started and to view an Oracle Spatial Studio demo, go to Oracle Spatial Studio.  

Oracle Spatial Studio, a self-service application for creating interactive maps and performing spatial analysis on your data, is now available for download on Oracle Technical Resources (formerly...

Graph Features

A simple Customer 360 analytics example with Oracle Property Graph

Customer 360 example Note: This is based on Ryota Yamanaka's (Senior Solutions Consultant, Big Data & Analytics, APAC) demos. The original content is here in his Github repository.    Introduction   This example shows how integrating multiple datasets, using a graph, facilitates additional analytics can lead to new insights. We will use three small datasets for illustrative purposes. The first contains accounts and account owners. The second is purchases by the people who own those accounts. The third is transactions between these accounts.    The combined dataset is then used to perform the following common graph query and analyses: Pattern Matching (e.g. a path meeting specified criteria), Detection of cycles (e.g. circular payments), Finding weakest or strongest links (e.g. influencer identification), Community Detection (i.e. connected components), and Recommendation (aka link prediction).   The datasets The data can come from multiple sources including database tables, web services, or files. We use a simple property graph descriptive format to list each one. The file lists the nodes, edges, and their properties. Each line consists of IDs (one for Nodes and Source and Destination for Edges), the Type (or Label), and key:value pairs of properties.   Accounts and account owners:    # Nodes 101 type:customer name:"John" age:15 location:"Boston" 102 type:customer name:"Mary" gender:"F" 103 type:customer name:"Jill" location:"Boston" 104 type:customer name:"Todd" student:"true" 201 type:account account_no:"xxx-yyy-201" open:"2015-10-04" 202 type:account account_no:"xxx-yyy-202" open:"2012-09-13" 203 type:account account_no:"xxx-yyy-203" open:"2016-02-04" 204 type:account account_no:"xxx-yyy-204" open:”2018-01-05"   # Edges 201 -> 101 :owned_by 202 -> 102 :owned_by 203 -> 103 :owned_by 204 -> 104 :owned_by 103 -> 104 :parent_of   and the resulting subgraph (for these nodes and edges) is shown in the figure below. The PGQL query for it is  SELECT * MATCH ()-[e:parent_of|owned_by]->()  which selects all edges (and their nodes) that have a label of owned_by or parent_of.  Purchases:   # Nodes 301 type:merchant name:"Apple Store" 302 type:merchant name:"PC Paradise" 303 type:merchant name:"Kindle Store" 304 type:merchant name:"Asia Books" 305 type:merchant name:"ABC Travel"   # Edges 201 -> 301 :purchased amount:800 201 -> 302 :purchased amount:15 202 -> 301 :purchased amount:150 202 -> 302 :purchased amount:20 202 -> 304 :purchased amount:10 203 -> 301 :purchased amount:350 203 -> 302 :purchased amount:20 203 -> 303 :purchased amount:15 204 -> 303 :purchased amount:10 204 -> 304 :purchased amount:15 204 -> 305 :purchased amount:45   and the resulting subgraph (for these nodes and edges) is shown in the figure below. The PGQL query for it is  SELECT * MATCH ()-[e:purchased]->()  which selects all edges (and their nodes) that have a label of purchased. Transactions:   # Nodes 211 type:account account_no:xxx-zzz-001 212 type:account account_no:xxx-zzz-002   # Edges 201 -> 202 :transfer amount:200 date:"2018-10-05" 211 -> 202 :transfer amount:900 date:"2018-10-06" 202 -> 212 :transfer amount:850 date:"2018-10-06" 201 -> 203 :transfer amount:500 date:"2018-10-07" 203 -> 204 :transfer amount:450 date:"2018-10-08" 204 -> 201 :transfer amount:400 date:"2018-10-09" 202 -> 203 :transfer amount:100 date:"2018-10-10" 202 -> 201 :transfer amount:300 date:”2018-10-10"   and the resulting subgraph (for these nodes and edges) is shown in the figure below. The PGQL query for it is  SELECT * MATCH ()-[e:transfer]->()  which selects all edges (and their nodes) that have a label of transfer.   And the full graph, once created, is as shown below.  Getting started   The sample data, and some config files, are available from Ryota's GitHub pgx-training repository under the c360/2019-05-06 directory. PGX is part of the supported Big Data Spatial and Graph or Database Spatial and Graph releases and included in those downloads. A standalone, unsupported, version of the PGX server package is also available from the Oracle Labs PGX site on the Oracle Technology Network. Download and install PGX server and the required Groovy and set the GROOVY_HOME environment variable to point to its installation.  Verify that the pgx shell works. e.g.    echo $GROOVY_HOME /opt/groovy-2.5.6  cd ~/Examples/pgx-19.1.0 ./bin/pgx [WARNING] PGX shell is using Groovy installed in /opt/groovy-2.5.6. It is the responsibility of the user to use the latest version of Groovy and to protect against known Groovy vulnerabilities. PGX Shell 19.1.0 PGX server version: 19.1.0 type: SM PGX server API version: 3.3.0 PGQL version: 1.2 type :help for available commands variables instance, session and analyst ready to use   Load the C360 sample data. Let's assume the pgx-training repository was downloaded to ~/Examples/pgx-training-master.    pgx> c360graph = session.readGraphWithProperties("~/Examples/pgx-training-master/c360/all.pgx.json") ==> PgxGraph[name=all.pgx,N=15,E=19,created=1556742411188]    Performing Graph Analyses    Now let's use PGQL to perform some pattern matching and graph analyses like detecting cycles, or finding communities and influencers.   Pattern Matching   Let’s say we’re looking for accounts that are mostly, or only, used as a temporary holding area and thus have inbound and outbound transfers within a short time period. For example, we might look for accounts that had an inbound and an outbound transfer, of over 500, on the same day. The PGQL query for this is: SELECT a.account_no, t1.amount, t2.amount, t1.date MATCH (a) <-[t1:transfer]-(a1),       (a)- [t2:transfer]->(a2)  WHERE t1.date = t2.date AND t1.amount > 500 AND t2.amount > 500     We can execute this in the PGX shell using (note: the triple quotes """ mark the start and end of a multiline statement)   c360graph.queryPgql(""" SELECT a.account_no, t1.amount, t2.amount, t1.date MATCH (a) <-[t1:transfer]-(a1),       (a)- [t2:transfer]->(a2)  WHERE t1.date = t2.date AND t1.amount > 500 AND t2.amount > 500  “"").print()   and the result is   +---------------------------------------------------+ | a.account_no | t1.amount | t2.amount | t1.date    | +---------------------------------------------------+ | xxx-yyy-202  | 900       | 850       | 2018-10-06 | +---------------------------------------------------+ In this particular instance the transfers are from and to an external entity and hence this account may be unprofitable depending on its fee structure. Detect Cycles   Next we use PGQL to find a series of transfers that start and end at the same account such as A to B to A, or A to B to C to A. The first query could be expressed as:   SELECT a1.account_no, t1.date, t1.amount, a2.account_no, t2.date, t2.amount MATCH (a1)-[t1:transfer]->(a2)-[t2:transfer]->(a1) WHERE t1.date < t2.date   Which when executed in the PGX shell using   c360graph.queryPgql(""" SELECT a1.account_no, t1.date, t1.amount, a2.account_no, t2.date, t2.amount MATCH (a1)-[t1:transfer]->(a2)-[t2:transfer]->(a1) WHERE t1.date < t2.date “"").print();   gives the following result.   +---------------------------------------------------------------------------------+ | a1.account_no | t1.date    | t1.amount | a2.account_no | t2.date    | t2.amount | +---------------------------------------------------------------------------------+ | xxx-yyy-201   | 2018-10-05 | 200       | xxx-yyy-202   | 2018-10-10 | 300       | +---------------------------------------------------------------------------------+ The second query just adds one more transfer to the pattern (list) and could be expressed as:   SELECT a1.account_no, t1.date, t1.amount, a2.account_no, t2.date, t2.amount, a3.account_no, t3.amount  MATCH (a1)-[t1:transfer]->(a2)-[t2:transfer]->(a3)-[t3:transfer]->(a1) WHERE t1.date < t2.date AND t2.date < t3.date   Which when executed in the PGX shell using   c360graph.queryPgql(""" SELECT a1.account_no, t1.date, t1.amount, a2.account_no, t2.date, t2.amount, a3.account_no, t3.amount MATCH (a1)-[t1:transfer]->(a2)-[t2:transfer]->(a3)-[t3:transfer]->(a1) WHERE t1.date < t2.date AND t2.date < t3.date “"").print();   gives the following result.   +-------------------------------------------------------------------------------------------------------------+ | a1.account_no | t1.date    | t1.amount | a2.account_no | t2.date    | t2.amount | a3.account_no | t3.amount | +-------------------------------------------------------------------------------------------------------------+ | xxx-yyy-201   | 2018-10-07 | 500       | xxx-yyy-203   | 2018-10-08 | 450       | xxx-yyy-204   | 400       | +-------------------------------------------------------------------------------------------------------------+ Finding Communities (or connected components)   Let's find which subsets of accounts form communities. That is, there are more transfers among accounts in the same subset than there are between those and accounts in another subset. We'll use the built-in Kosaraju strongly connected components algorithm. The first step is to create a subgraph that only has the accounts and the transfers among them. This is done by creating and applying an edge filter (for edges with the table "transfer') to the c360graph.   acctsSubgraph = c360graph.filter(new EdgeFilter("edge.label()='transfer' ")) ==> PgxGraph[name=sub-graph_13,N=6,E=8,created=1556744669838]   Then run sccKosaraju on that subgraph.   result = analyst.sccKosaraju(acctsSubgraph) ==> ComponentCollection[name=compproxy_14,graph=sub-graph_13] ==> ComponentCollection[name=compproxy_14,graph=sub-graph_13] ==> ComponentCollection[name=compproxy_14,graph=sub-graph_13]   Check the results; i.e. iterate through the result and print the number of vertices in each component (or community).   result.eachWithIndex {   it, index -> println "Partition ${index} has ${it.size()} vertices" } Partition 0 has 1 vertices Partition 1 has 4 vertices Partition 2 has 1 vertices   Setting this result to node properties, it can be retrieved by PGQL queries.        cs = sg.createChangeSet()     rs = sg.queryPgql("SELECT DISTINCT a WHERE (a)-[:transfer]-()")     for (r in rs) {       v = r.getVertex(1)       i = result.getPartitionIndexOfVertex(v)       cs.updateVertex(v.getId()).setProperty("component", i)     }     sg = cs.build()     sg.queryPgql("""       SELECT a.component, COUNT(a.account_no), MAX(a.account_no)       MATCH (a)       GROUP BY a.component       ORDER BY a.component     """).print() +-------------------------------------------------------+ | a.component | COUNT(a.account_no) | MAX(a.account_no) | +-------------------------------------------------------+ | 0           | 1                   | xxx-zzz-001       | | 1           | 4                   | xxx-yyy-204       | | 2           | 1                   | xxx-zzz-002       | +-------------------------------------------------------+   If we look at the initial subgraph of accounts we see that these correspond to the red (4 vertices), pink (1) and orange (1) vertices. Lastly let's use Personalized PageRank to find stores that John may purchase from given that people he is connected to have made purchases from those stores.   Recommendation   Create a subgraph with customers and merchants, i.e. filter the original graph based on the edge label “purchased".   customersAndMerchants = c360graph.filter(new EdgeFilter("edge.label()='purchased' "))   The algorithms require bi-directional edges so we add them programmatically. The first step is to create a change set with the required updates, i.e. added reverse edges for the purchases. Then web build a new graph using that change set.   purchases_changeSet = customersAndMerchants.createChangeSet() // get the vertices and add a reverse edge purchases_resultSet  = customersAndMerchants.queryPgql("SELECT a, x MATCH (a)-[:purchased]->(x)") for (r in purchases_resultSet) {       a = r.getVertex(1).getId()       x = r.getVertex(2).getId()       purchases_changeSet.addEdge(x, a).setLabel("purchased_by") } // build the graph purchases_Graph = purchases_changeSet.build() // query it to test that the build worked  purchases_Graph.queryPgql("SELECT x.name, label(r), a.account_no MATCH (x)-[r:purchased_by]->(a)") Now let's compute the personalized page rank (PPR) for John's account (account "a01"). A PPR is biased towards a specified set of vertices. So first we create and populate that vertex set. Then we compute the PPR.   ppr_vertexSet = purchases_Graph.createVertexSet(); ppr_vertexSet.addAll("101"); ppr = analyst.personalizedPagerank(purchases_Graph, ppr_vertexSet);   Now query the graph to get the results (i.e. computed ppr values which are stored in a new property named "pagerank"). The query "find merchants that John has not purchased from and list them ordered by their ppr" is expressed as follows.   purchases_Graph.queryPgql(""" SELECT ID(x), x.name, x.pagerank MATCH (x) WHERE x.type = 'merchant' AND NOT EXISTS ( SELECT * MATCH (x)-[:purchased_by]->(a) WHERE ID(a)='101') ORDER BY x.pagerank DESC """).print()   which gives the results shown below.   +--------------------------------------------+ | ID(x) | x.name       | x.pagerank          | +--------------------------------------------+ | 303   | Kindle Store | 0.04932640133302745 | | 304   | Asia Books   | 0.04932640133302745 | | 305   | ABC Travel   | 0.01565535511504672 | +--------------------------------------------+

Customer 360 example Note: This is based on Ryota Yamanaka's (Senior Solutions Consultant, Big Data & Analytics, APAC) demos. The original content is here in his Github repository.    Introduction   This...

Graph Features

RDF Graph – Standards and Open Source Support

In the RDF graph area, Oracle supports leading open source technologies and actively participates in the W3C standards body to meet our customers’ business-critical application needs.  Here is a list of some of the technologies and standards organizations we support. W3C – Semantic Web and Semantic Web Activity, RDF, RDF Schema, RIF OWL, SPARQL, SKOS, URI, RDF/XML Jena—Java framework for building Semantic Web applications. It provides a programmatic environment for RDF, RDFS and OWL, SPARQL and includes a rule-based inference engine. Joseki—an HTTP engine that supports the SPARQL Protocol and the SPARQL RDF Query language. ARQ—a query engine for Jena that supports the SPARQL RDF Query language. TDB—Jena component that provides large scale storage and query of RDF datasets using a pure Java engine and SPARQL. SDB—Jena component that provides for scalable storage and query w/ SPARQL of RDF datasets using conventional SQL databases for use in standalone applications, J2EE and other application frameworks Sesame—an open source framework for storage, inferencing and querying of RDF data. Pellet—OWL 2 reasoner for Oracle Database 11g R2, providing scalable and correct OWL reasoning. D2RQ—declarative language to describe mappings between relational database schemata and OWL/RDFS ontologies, enables applications to access a RDF-view on a non-RDF database through the Jena and Sesame APIs, as well as over the Web via the SPARQL Protocol and as Linked Data. Jetty—provides an HTTP server, HTTP client, and javax.servlet container. Cytoscape—Open source visualization tool for graph data GATE—Entity extraction & text analysis engine Protégé—Open source ontology editor  

In the RDF graph area, Oracle supports leading open source technologies and actively participates in the W3C standards body to meet our customers’ business-critical application needs.  Here is a list...

Spatial and Graph

Oracle’s Spatial Map Visualization Component: Getting Started and Resources

  Here are resources to help you get started using the map visualization component of Oracle Spatial and Graph. Overview The Oracle Spatial and Graph map visualization component is a programmable tool for rendering maps using spatial data managed by Oracle Spatial and Graph. The map visualization component provides tools that hide the complexity of spatial data queries and cartographic rendering, while providing customizable options for more advanced users. These tools can be deployed in a platform-independent manner and are designed to integrate with map-rendering applications. Location of kits, documentation, and other collateral The spatial visualization EAR file ships with the database software media. It can be found in the ORACLE_HOME/rdbms/md/jlib directory. The EAR file can be deployed to a supported JEE container The following blog post explains how to deploy, and use, it in the Oracle Public Cloud https://blogs.oracle.com/oraclespatial/deploy-spatial-and-graph-map-visualization-component-to-oracle-cloud Documentation is part of the database documentation library.  18c version of the Spatial and Graph Map Visualization Developer's Guide The component for Oracle Database 12.2 and 18c is compatible with the Fusion Middleware MapViewer release 12.2.1.3. So the quickstart kit, map authoring tool (aka MapBuilder) and demos/tutorial (i.e. mvdemo war file and dataset), which are available at its download page, can be used as an alternate way to get started with this component.    

  Here are resources to help you get started using the map visualization component of Oracle Spatial and Graph. Overview The Oracle Spatial and Graph map visualization component is a programmable tool...

Graph Features

Executing Federated SPARQL Queries with Oracle Spatial and Graph – RDF Knowledge Graph

Linked (Open) Data is all about publishing data in a standard way so that it can be integrated and linked with other data, which leads to much more useful data. The W3C has defined several standards as part of its Data Activity to enable linked data, and thanks to adoption of W3C standards, there are currently over 1,200 datasets listed in the Linked Open Data Cloud. Much of this data can be accessed by standard SPARQL endpoints. The folks at https://lod-cloud.net/ have created an image to nicely illustrate the LOD Cloud. The SPARQL 1.1 standard defines a SERVICE keyword for executing federated SPARQL queries that combine data from a local database and one or more remote SPARQL endpoints. Oracle Spatial and Graph has supported federated SPARQL queries since version 12.1.0.2 (see the user guide for more information). In this blog post, we show how to configure and execute federated queries against public SPARQL endpoints provided by the Italian National Institute of Statistics and the Japanese Statistics Bureau. We will be using a 12c Release 2 database, and our examples assume that a semantic network and a user named RDFUSER have been created. We built this environment using the Oracle Database Developer Day VM available here. See a previous post for instructions on how to create a user and semantic network using SQL Developer. First, create an RDF model owned by RDFUSER with the following SQL commands: create table atab(tri sdo_rdf_triple_s) compress; grant insert on atab to mdsys; exec sem_apis.create_sem_model('M1','ATAB','TRI'); RDFUSER needs some additional privileges to run federated queries in the database. We need to grant execute privileges on MDSYS.SPARQL_SERVICE and use DBMS_NETWORK_ACL_ADMIN to give RDFUSER privileges to connect to external websites. Run the following SQL commands as a privileged user. grant execute on mdsys.sparql_service to rdfuser; begin   dbms_network_acl_admin.create_acl (     acl       => 'rdfuser.xml',     description => 'Allow rdfuser to query SPARQL endpoints',     principal => 'RDFUSER',     is_grant  => true,     privilege => 'connect'   );   dbms_network_acl_admin.assign_acl (     acl  => 'rdfuser.xml',     host => '*'   ); end; / You can use UTL_HTTP.SET_PROXY for any proxy settings. Now that RDFUSER has appropriate privileges, we can try a simple SEM_MATCH query that uses the SERVICE keyword to execute a portion of the query remotely on the Italian National Institute of Statistics’ SPARQL endpoint located at http://datiopen.istat.it/sparql/oracle. More specifically, the portion of the query inside SERVICE <http://datiopen.istat.it/sparql/oracle> { } is sent to the remote endpoint. select s$rdfterm, p$rdfterm, o$rdfterm from table(sem_match( 'SELECT *  WHERE {    SERVICE <http://datiopen.istat.it/sparql/oracle> {      SELECT * WHERE { ?s ?p ?o } LIMIT 3    }  }' ,sem_models('M1') ,null,null,null,null ,' PLUS_RDFT=VC ')); That’s it! Thanks to adoption of W3C SPARQL protocol standards, we just sent a SPARQL query pattern to a remote endpoint in Italy where the remote endpoint evaluated the query and sent the result back to our local query processor. We will use the Japanese Statistics Bureau’s SPARQL endpoint located at https://data.e-stat.go.jp/lod/sparql/alldata/query for our next example. select s$rdfterm, p$rdfterm, o$rdfterm from table(sem_match( 'SELECT *  WHERE {    SERVICE <https://data.e-stat.go.jp/lod/sparql/alldata/query> {      SELECT * WHERE { ?s ?p ?o } LIMIT 3    }  }' ,sem_models('M1') ,null,null,null,null ,' PLUS_RDFT=VC ')); Note that this endpoint uses HTTPS instead of HTTP, so we will need to use some certificates to properly connect to the endpoint. Without certificates, we get an HTTP request failed error. To properly connect to this HTTPS endpoint, we need to use the orapki utility to create a wallet with the certificates for this site. The first step is to download certificates from the site. Open a web browser to https://data.e-stat.go.jp/lod/sparql/alldata/query (we are using Firefox) and click on the green lock icon to the left of the address bar. Then click the right arrow next to data.e-stat.go.jp in the popup. Click More Information at the bottom of the next screen. Then click the View Certificate button on the right. Next, click the Details tab. Now we need to export the two non-leaf certificates (DigiCert Global Root CA and GeoTrust RSA CA 2018). Highlight the certificate name and click Export. Save the certificate to the filesystem as a .crt file. Repeat this process to save the other non-leaf certificate. Next, we will use the orapki utility to create a wallet with these certificates. Open up a UNIX command prompt to the directory where you saved the certificates and execute the following commands. Note that you will need to enter a password for your wallet during the wallet create command. mkdir wallet $ORACLE_HOME/bin/orapki wallet create -wallet <path_to_your_wallet_directory> $ORACLE_HOME/bin/orapki wallet add -wallet <path_to_your_wallet_directory> -trusted_cert -cert "<path_to_your_certificate>/DigiCertGlobalRootCA.crt" -pwd <passwd> $ORACLE_HOME/bin/orapki wallet add -wallet <path_to_your_wallet_directory> -trusted_cert -cert "<path_to_your_certificate>/GeoTrustRSACA2018.crt" -pwd <passwd> $ORACLE_HOME/bin/orapki wallet display -wallet <path_to_your_wallet_directory> -pwd <passwd> Now that the wallet is created, we need to load it into our database session with UTL_HTTP.SET_WALLET. Execute the following command in your SQL*Plus session. EXEC UTL_HTTP.set_wallet('file:<path_to_your_wallet_directory>', '<passwd_for_wallet>'); Now we can execute a federated query against the Japanese Statistics Bureau’s HTTPS SPARQL endpoint from this session. So far, we have only done simple queries against these remote endpoints where our whole SPARQL query is just a SERVICE clause. Let’s now do query were we join some local data with a remote endpoint. To illustrate this, we’ll insert a triple locally using one of the subject URIs from the data.e-stat.go.jp endpoint. begin   sem_apis.update_model('M1', 'INSERT DATA {    <urn:a> <urn:p> <http://data.e-stat.go.jp/lod/dataset/gridCode/dm012015405/obsTD7WVMQBYGBXALAVG4CWDNNO675M64S4> .  }'); end; / Now, we’ll execute the query below that does a simple object-subject join with the remote endpoint. select s$rdfterm, p$rdfterm, o$rdfterm from table(sem_match( 'SELECT *  WHERE {    <urn:a> <urn:p> ?s    SERVICE <https://data.e-stat.go.jp/lod/sparql/alldata/query> {      SELECT * WHERE { ?s ?p ?o }    }  } LIMIT 3' ,sem_models('M1') ,null,null,null,null ,' PLUS_RDFT=VC SERVICE_JPDWN=T ')); Note that we are using a very important option in our SEM_MATCH  query: SERVICE_JPDWN=T. This option tells the query engine to push the join down into the SERVICE clause. The SPARQL standard specifies a bottom-up query execution, which means that the fully unbound { ?s ?p ?o } pattern should execute on the remote endpoint first and return all these intermediate results back to our query processor to then join with the <urn:a> <urn:p> ?s triple pattern in the top level of the query. The data.e-stat.go.jp endpoint contains over 1 billion triples, so this bottom-up evaluation will obviously be problematic. We don’t want to send 1 billion triples back to our local database over HTTP. With join push down, we follow a top-down evaluation and evaluate the <urn:a> <urn:p> ?s triple pattern first. Then we will execute one or more remote queries in a loop, but we will add constraints on ?s to the remote query ( { ?s ?p ?o FILTER (?s = <http://data.e-stat.go.jp/lod/dataset/gridCode/dm012015405/obsTD7WVMQBYGBXALAVG4CWDNNO675M64S4>) } in this case). In general, the SERVICE_JPDWN=T option should be used any time you have a selective local query pattern and an unselective remote query pattern. Alright, this is great if we are running SQL SEM_MATCH queries, but what if we want to run these queries as pure SPARQL queries from a Fuseki endpoint connected to our database? One simple way to do this is to create a database logon trigger for the database user in our Fuseki connection. In this case, we are using RDFUSER for the Fuseki connection, so a privileged database user can create the following trigger. create or replace trigger rdfuser_fedq after logon on database declare begin   if (user = 'RDFUSER') then     -- set proxy     UTL_HTTP.SET_PROXY('www-proxy.com:80');        -- set wallet     UTL_HTTP.set_wallet('file:<path_to_wallet_directory>', '<wallet_passwd>');   end if;   exception when others then null; end; / Now, our database session for Fuseki will be properly configured to connect to the data.e-stat.go.jp endpoint, and we can run the same query through Fuseki. Note how we used the special ORACLE_SEM_HT_NS prefix to pass the SERVICE_JPDWN=T hint along to the database server. See a previous blog post for more information about how to setup Fuskei. That’s it. Now have fun running queries against all the SPARQL endpoints out there in the Linked Open Data Cloud!

Linked (Open) Data is all about publishing data in a standard way so that it can be integrated and linked with other data, which leads to much more useful data. The W3C has defined several standards...

Graph Features

Accessing PGX REST Endpoint using C++

This post is based a recent collaboration with Yogesh Purohit (primary contributor)  who is a passionate expert from one of the largest industrial manufacturing companies. In this collaboration, we used C++ to access a PGX REST endpoint and also compared the performance of C++/REST and Oracle's built-in Java/REST calls. Below we focus on illustrating how one can access a PGX REST endpoint using C++. Requirements first, we need this latest Oracle Spatial and Graph Property Graph patch and you may also want to check this blog to see some details of the underlying REST calls. The following code snippet uses Poco for HTTP calls. It starts with getting a CSRF token and then uses it to establish a new PGX session.     unsigned short oraclePort = 7007;     std::string pgxHostName = "localhost";         session = new Poco::Net::HTTPClientSession(pgxHostName, oraclePort);     session->setKeepAlive(true);     std::string oraclePath = "/token";     std::string finalBody =     "";     // send request     req = new Poco::Net::HTTPRequest(Poco::Net::HTTPRequest::HTTP_GET, oraclePath, Poco::Net::HTTPMessage::HTTP_1_1);     req->setContentType("application/json; charset=UTF-8; stream=true");     req->setContentLength(finalBody.length());     std::ostream &os = session->sendRequest(*req);     os << finalBody.c_str();     os.flush();     std::istream &is1 = session->receiveResponse(res);     std::vector<HTTPCookie> cookies;     res.getCookies(cookies);     std::string csrfToken = cookies[0].getValue();     //2///////////////////////////     oraclePath = "/core/v1/sessions";     req->setMethod(Poco::Net::HTTPRequest::HTTP_POST);     req->setURI(oraclePath);     finalBody = "{                    \         \"source\" : \"graph-session\",    \         \"idleTimeout\": null,    \         \"taskTimeout\":null,    \         \"timeUnitName\":null,    \         \"_csrf_token\" :'" +  csrfToken + "'}";     req->setContentLength(finalBody.length());     NameValueCollection reqCookies;     reqCookies.add("_csrf_token", csrfToken );     req->setCookies(reqCookies);     std::ostream &os2 = session->sendRequest(*req);     os2 << finalBody.c_str();     os2.flush();     std::istream &is2 = session->receiveResponse(res);     std::vector<HTTPCookie> sid;     res.getCookies(sid);     std::string sessionId = sid[0].getValue(); Thanks, Zhe

This post is based a recent collaboration with Yogesh Purohit (primary contributor)  who is a passionate expert from one of the largest industrial manufacturing companies. In this collaboration, we...

Graph Features

Finding Relevant REST APIs for Your Graph Operations

From time to time, I got a request asking for details on the REST APIs for the PGX component of Oracle Spatial and Graph (OSG) and Oracle Big Data Spatial and Graph (BDSG). In this blog post, I am going to show a simple way, demonstrated to me by my colleague Korbi Schmid,  to find details on REST API calls using the most recent property graph patch. It is actually very simple. Just enable the trace level log4j setting for Groovy (or your own Java program), connect to a PGX instance, run some Java APIs, and you will find the relevant REST APIs from the trace. The following trace was from a flow of establishing a PGX session, loading a graph from Oracle Database, and running a PGQL. It's annotated for better readability. As a convention, "<<" denotes an incoming response from an REST call, while ">>" denotes an outgoing REST request. -- Assume we have a graph config as follows and the PGX endpoint used is  http://127.0.0.1:7007/ {"attributes":{},"username":"pg","error_handling":{},"db_engine":"RDBMS","vertex_id_type":"long","format":"pg","jdbc_url":"jdbc:oracle:thin:@127.0.0.1:1521:orcl122","max_num_connections":2,"loading":{"load_edge_label":false},"vertex_props":[{"type":"timestamp_with_timezone","name":"ts"},{"type":"string","default":"default_name","name":"name"}],"name":"g1","password":"*******","edge_props":[{"type":"double","default":"1000000","name":"cost"}]} ** Get a CSRF token and PGX server version info 15:35:48.650 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-0 >> "GET /token HTTP/1.1[\r][\n]" 15:35:48.656 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-0 << "HTTP/1.1 201 [\r][\n]" 15:35:48.656 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-0 << "SET-COOKIE: _csrf_token=a838d0ad-e165-4e79-928f-139628db11fc;Version=1; HttpOnly[\r][\n]" 15:35:48.656 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-0 << "Content-Length: 0[\r][\n]" 15:35:48.656 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-0 << "Date: Thu, 16 Aug 2018 22:35:48 GMT[\r][\n]" 15:35:48.656 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-0 << "[\r][\n]" 15:35:48.684 [pgx-client-thread-1] DEBUG org.apache.http.wire - http-outgoing-1 >> "GET /version?extendedInfo=true HTTP/1.1[\r][\n]" 15:35:48.684 [pgx-client-thread-1] DEBUG org.apache.http.wire - http-outgoing-1 >> "Host: 127.0.0.1:7007[\r][\n]" 15:35:48.684 [pgx-client-thread-1] DEBUG org.apache.http.wire - http-outgoing-1 >> "Connection: Keep-Alive[\r][\n]" 15:35:48.684 [pgx-client-thread-1] DEBUG org.apache.http.wire - http-outgoing-1 >> "User-Agent: Apache-HttpClient/4.5.4 (Java/1.8.0_144)[\r][\n]" 15:35:48.684 [pgx-client-thread-1] DEBUG org.apache.http.wire - http-outgoing-1 >> "Cookie: _csrf_token=18bf8ab2-c1af-49ce-9a33-190ab1208647[\r][\n]" 15:35:48.684 [pgx-client-thread-1] DEBUG org.apache.http.wire - http-outgoing-1 >> "Accept-Encoding: gzip,deflate[\r][\n]" 15:35:48.684 [pgx-client-thread-1] DEBUG org.apache.http.wire - http-outgoing-1 >> "[\r][\n]" 15:35:48.691 [pgx-client-thread-1] DEBUG org.apache.http.wire - http-outgoing-1 << "HTTP/1.1 200 [\r][\n]" 15:35:48.691 [pgx-client-thread-1] DEBUG org.apache.http.wire - http-outgoing-1 << "Content-Type: application/json;charset=utf-8[\r][\n]" 15:35:48.691 [pgx-client-thread-1] DEBUG org.apache.http.wire - http-outgoing-1 << "Content-Length: 130[\r][\n]" 15:35:48.691 [pgx-client-thread-1] DEBUG org.apache.http.wire - http-outgoing-1 << "Date: Thu, 16 Aug 2018 22:35:48 GMT[\r][\n]" 15:35:48.691 [pgx-client-thread-1] DEBUG org.apache.http.wire - http-outgoing-1 << "[\r][\n]" 15:35:48.691 [pgx-client-thread-1] DEBUG org.apache.http.wire - http-outgoing-1 << "{"version":"3.1.0","commit":"cb648f2da0cdf006f10588a69bb1beca81b65190","server_type":"sm","built":"2018-08-06T22:29:43.011-07:00"}" * * Create a new PGX session. Need to pass in the CSRF token * 15:35:48.704 [pgx-client-thread-2] DEBUG org.apache.http.wire - http-outgoing-0 >> "POST /core/v1/sessions HTTP/1.1[\r][\n]" 15:35:48.704 [pgx-client-thread-2] DEBUG org.apache.http.wire - http-outgoing-0 >> "Content-Length: 132[\r][\n]" 15:35:48.704 [pgx-client-thread-2] DEBUG org.apache.http.wire - http-outgoing-0 >> "Content-Type: application/json; charset=UTF-8[\r][\n]" 15:35:48.704 [pgx-client-thread-2] DEBUG org.apache.http.wire - http-outgoing-0 >> "Host: 127.0.0.1:7007[\r][\n]" 15:35:48.704 [pgx-client-thread-2] DEBUG org.apache.http.wire - http-outgoing-0 >> "Connection: Keep-Alive[\r][\n]" 15:35:48.704 [pgx-client-thread-2] DEBUG org.apache.http.wire - http-outgoing-0 >> "User-Agent: Apache-HttpClient/4.5.4 (Java/1.8.0_144)[\r][\n]" 15:35:48.704 [pgx-client-thread-2] DEBUG org.apache.http.wire - http-outgoing-0 >> "Cookie: _csrf_token=a838d0ad-e165-4e79-928f-139628db11fc[\r][\n]" 15:35:48.704 [pgx-client-thread-2] DEBUG org.apache.http.wire - http-outgoing-0 >> "Accept-Encoding: gzip,deflate[\r][\n]" 15:35:48.704 [pgx-client-thread-2] DEBUG org.apache.http.wire - http-outgoing-0 >> "[\r][\n]" 15:35:48.704 [pgx-client-thread-2] DEBUG org.apache.http.wire - http-outgoing-0 >> "{"source":"session1","idleTimeout":null,"taskTimeout":null,"timeUnitName":null,"_csrf_token":"a838d0ad-e165-4e79-928f-139628db11fc"}" 15:35:48.748 [pgx-client-thread-2] DEBUG org.apache.http.wire - http-outgoing-0 << "HTTP/1.1 201 [\r][\n]" 15:35:48.748 [pgx-client-thread-2] DEBUG org.apache.http.wire - http-outgoing-0 << "SET-COOKIE: SID=e2b2ead4-10b6-41e0-a7f4-4e960859da12;Version=1; HttpOnly[\r][\n]" 15:35:48.748 [pgx-client-thread-2] DEBUG org.apache.http.wire - http-outgoing-0 << "Content-Length: 0[\r][\n]" 15:35:48.749 [pgx-client-thread-2] DEBUG org.apache.http.wire - http-outgoing-0 << "Date: Thu, 16 Aug 2018 22:35:48 GMT[\r][\n]" 15:35:48.749 [pgx-client-thread-2] DEBUG org.apache.http.wire - http-outgoing-0 << "[\r][\n]" * * Issue a load graph command. Need to pass in session ID, CSRF token, and the graph config itself. * 15:50:12.799 [main] DEBUG org.apache.http.wire - http-outgoing-2 >> "POST /core/v1/loadGraph HTTP/1.1[\r][\n]" 15:50:12.799 [main] DEBUG org.apache.http.wire - http-outgoing-2 >> "Content-Length: 503[\r][\n]" 15:50:12.799 [main] DEBUG org.apache.http.wire - http-outgoing-2 >> "Content-Type: application/json; charset=UTF-8[\r][\n]" 15:50:12.799 [main] DEBUG org.apache.http.wire - http-outgoing-2 >> "Host: 127.0.0.1:7007[\r][\n]" 15:50:12.799 [main] DEBUG org.apache.http.wire - http-outgoing-2 >> "Connection: Keep-Alive[\r][\n]" 15:50:12.799 [main] DEBUG org.apache.http.wire - http-outgoing-2 >> "User-Agent: Apache-HttpClient/4.5.4 (Java/1.8.0_144)[\r][\n]" 15:50:12.799 [main] DEBUG org.apache.http.wire - http-outgoing-2 >> "Cookie: SID=e2b2ead4-10b6-41e0-a7f4-4e960859da12; _csrf_token=a838d0ad-e165-4e79-928f-139628db11fc[\r][\n]" 15:50:12.799 [main] DEBUG org.apache.http.wire - http-outgoing-2 >> "Accept-Encoding: gzip,deflate[\r][\n]" 15:50:12.799 [main] DEBUG org.apache.http.wire - http-outgoing-2 >> "[\r][\n]" 15:50:12.799 [main] DEBUG org.apache.http.wire - http-outgoing-2 >> "{"graphConfig":{"username":"pg","db_engine":"RDBMS","format":"pg","jdbc_url":"jdbc:oracle:thin:@127.0.0.1:1521:orcl122","attributes":{},"error_handling":{},"max_num_connections":2,"loading":{"load_edge_label":false},"vertex_props":[{"type":"timestamp_with_timezone","name":"ts"},{"type":"string","default":"default_name","name":"name"}],"name":"g1","password":"pg","edge_props":[{"type":"double","default":"1000000","name":"cost"}]},"graphName":null,"_csrf_token":"a838d0ad-e165-4e79-928f-139628db11fc"}" 15:50:12.901 [main] DEBUG org.apache.http.wire - http-outgoing-2 << "HTTP/1.1 202 [\r][\n]" 15:50:12.901 [main] DEBUG org.apache.http.wire - http-outgoing-2 << "Location: http://127.0.0.1:7007/core/v1/futures/4cb75a56-8acc-4958-bc67-eab12c67e513/status[\r][\n]" 15:50:12.901 [main] DEBUG org.apache.http.wire - http-outgoing-2 << "Content-Type: application/json;charset=utf-8[\r][\n]" 15:50:12.901 [main] DEBUG org.apache.http.wire - http-outgoing-2 << "Content-Length: 51[\r][\n]" 15:50:12.901 [main] DEBUG org.apache.http.wire - http-outgoing-2 << "Date: Thu, 16 Aug 2018 22:50:12 GMT[\r][\n]" 15:50:12.901 [main] DEBUG org.apache.http.wire - http-outgoing-2 << "[\r][\n]" 15:50:12.902 [main] DEBUG org.apache.http.wire - http-outgoing-2 << "{"futureId":"4cb75a56-8acc-4958-bc67-eab12c67e513"}" * * Wait for the async task (future) to complete * 15:50:12.908 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "GET /core/v1/futures/4cb75a56-8acc-4958-bc67-eab12c67e513/status HTTP/1.1[\r][\n]" 15:50:12.908 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "Host: 127.0.0.1:7007[\r][\n]" 15:50:12.908 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "Connection: Keep-Alive[\r][\n]" 15:50:12.908 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "User-Agent: Apache-HttpClient/4.5.4 (Java/1.8.0_144)[\r][\n]" 15:50:12.908 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "Cookie: SID=e2b2ead4-10b6-41e0-a7f4-4e960859da12; _csrf_token=a838d0ad-e165-4e79-928f-139628db11fc[\r][\n]" 15:50:12.908 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "Accept-Encoding: gzip,deflate[\r][\n]" 15:50:12.908 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "[\r][\n]" 15:50:12.930 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "HTTP/1.1 200 [\r][\n]" 15:50:12.931 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "Content-Type: application/json;charset=utf-8[\r][\n]" 15:50:12.931 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "Content-Length: 577[\r][\n]" 15:50:12.931 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "Date: Thu, 16 Aug 2018 22:50:12 GMT[\r][\n]" 15:50:12.931 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "[\r][\n]" 15:50:12.931 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "{"id":"4cb75a56-8acc-4958-bc67-eab12c67e513","links":[{"href":"http://127.0.0.1:7007/core/v1/futures/4cb75a56-8acc-4958-bc67-eab12c67e513/status","rel":"self","method":"GET","interaction":["async-polling"]},{"href":"http://127.0.0.1:7007/core/v1/futures/4cb75a56-8acc-4958-bc67-eab12c67e513","rel":"abort","method":"DELETE","interaction":["async-polling"]},{"href":"http://127.0.0.1:7007/core/v1/futures/4cb75a56-8acc-4958-bc67-eab12c67e513/status","rel":"canonical","method":"GET","interaction":["async-polling"]}],"progress":"processing","completed":false,"intervalToPoll":1}" * * The above has not completed, yet. Try again. * 15:50:18.066 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "GET /core/v1/futures/4cb75a56-8acc-4958-bc67-eab12c67e513/status HTTP/1.1[\r][\n]" 15:50:18.066 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "Host: 127.0.0.1:7007[\r][\n]" 15:50:18.066 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "Connection: Keep-Alive[\r][\n]" 15:50:18.066 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "User-Agent: Apache-HttpClient/4.5.4 (Java/1.8.0_144)[\r][\n]" 15:50:18.066 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "Cookie: SID=e2b2ead4-10b6-41e0-a7f4-4e960859da12; _csrf_token=a838d0ad-e165-4e79-928f-139628db11fc[\r][\n]" 15:50:18.066 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "Accept-Encoding: gzip,deflate[\r][\n]" 15:50:18.066 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "[\r][\n]" 15:50:18.070 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "HTTP/1.1 200 [\r][\n]" 15:50:18.070 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "Content-Type: application/json;charset=utf-8[\r][\n]" 15:50:18.070 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "Content-Length: 733[\r][\n]" 15:50:18.070 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "Date: Thu, 16 Aug 2018 22:50:18 GMT[\r][\n]" 15:50:18.070 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "[\r][\n]" 15:50:18.070 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "{"id":"4cb75a56-8acc-4958-bc67-eab12c67e513","links":[{"href":"http://127.0.0.1:7007/core/v1/futures/4cb75a56-8acc-4958-bc67-eab12c67e513/status","rel":"self","method":"GET","interaction":["async-polling"]},{"href":"http://127.0.0.1:7007/core/v1/futures/4cb75a56-8acc-4958-bc67-eab12c67e513","rel":"abort","method":"DELETE","interaction":["async-polling"]},{"href":"http://127.0.0.1:7007/core/v1/futures/4cb75a56-8acc-4958-bc67-eab12c67e513/status","rel":"canonical","method":"GET","interaction":["async-polling"]},{"href":"http://127.0.0.1:7007/core/v1/futures/4cb75a56-8acc-4958-bc67-eab12c67e513/value","rel":"related","method":"GET","interaction":["async-polling"]}],"progress":"succeeded","completed":true,"intervalToPoll":1000}" * * Graph is now read into memory. Get some meta data of the graph * 15:50:19.074 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "GET /core/v1/futures/4cb75a56-8acc-4958-bc67-eab12c67e513/value HTTP/1.1[\r][\n]" 15:50:19.074 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "Host: 127.0.0.1:7007[\r][\n]" 15:50:19.074 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "Connection: Keep-Alive[\r][\n]" 15:50:19.074 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "User-Agent: Apache-HttpClient/4.5.4 (Java/1.8.0_144)[\r][\n]" 15:50:19.074 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "Cookie: SID=e2b2ead4-10b6-41e0-a7f4-4e960859da12; _csrf_token=a838d0ad-e165-4e79-928f-139628db11fc[\r][\n]" 15:50:19.074 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "Accept-Encoding: gzip,deflate[\r][\n]" 15:50:19.074 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 >> "[\r][\n]" 15:50:19.108 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "HTTP/1.1 201 [\r][\n]" 15:50:19.108 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "Location: http://127.0.0.1:7007/core/v1/graphs/g1[\r][\n]" 15:50:19.108 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "Content-Type: application/json;charset=utf-8[\r][\n]" 15:50:19.108 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "Content-Length: 2175[\r][\n]" 15:50:19.108 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "Date: Thu, 16 Aug 2018 22:50:19 GMT[\r][\n]" 15:50:19.108 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "[\r][\n]" 15:50:19.108 [pgx-client-thread-4] DEBUG org.apache.http.wire - http-outgoing-2 << "{"id":"g1","links":[{"href":"http://127.0.0.1:7007/core/v1/graphs/g1","rel":"self","method":"GET","interaction":["async-polling"]},{"href":"http://127.0.0.1:7007/core/v1/graphs/g1","rel":"canonical","method":"GET","interaction":["async-polling"]}],"nodeProperties":{"name":{"id":"name","links":[{"href":"http://127.0.0.1:7007/core/v1/graphs/g1/properties/name","rel":"self","method":"GET","interaction":["async-polling"]},{"href":"http://127.0.0.1:7007/core/v1/graphs/g1/properties/name","rel":"canonical","method":"GET","interaction":["async-polling"]}],"dimension":0,"name":"name","entityType":"vertex","type":"string","transi ent":false},"ts":{"id":"ts","links":[{"href":"http://127.0.0.1:7007/core/v1/graphs/g1/properties/ts","rel":"self","method":"GET","interaction":["async-polling"]},{"href":"http://127.0.0.1:7007/core/v1/graphs/g1/properties/ts","rel":"canonical","method":"GET","interaction":["async-polling"]}],"dimension":0,"name":"ts","entityType":"vertex","type":"timestamp_with_timezone","transient":false}},"metaData":{"id":null,"links":null,"numVertices":482372,"numEdges":241186,"memoryMb":34,"dataSourceVersion":"21321357","config":{"vertex_props":[{"name":"ts","type":"timestamp_with_timezone"},{"name":"name","default":"default_name","type":"string"}],"error_handling":{},"edge_props":[{"name":"cost","default":"1000000","type":"double"}],"password":"pg","format":"pg","attributes":{}, "name":"g1","max_num_connections":2,"jdbc_url":"jdbc:oracle:thin:@127.0.0.1:1521:orcl122","db_engine":"RDBMS","loading":{"load_edge_label":false},"username":"pg"},"creationRequestTimestamp":1534459812913,"creationTimestamp":1534459816597,"vertexIdType":"long","edgeIdType":"long","directed":true},"vertexLabels":null,"edgeLabel":null,"graphName":"g1","edgeProperties":{"cost":{"id":"cost","links":[{"href":"http://127.0.0.1:7007/core/v1/graphs/g1/properties/cost","rel":"self","method":"GET","interaction":["async-polling"]},{"href":"http://127.0.0.1:7007/core/v1/graphs/g1/properties/cost","rel":"canonical","method":"GET","interaction":["async-polling"]}],"dimension":0,"name":"cost","entityType":"edge", "type":"double","transient":false}},"ageMs":0,"transient":false}" * * Get ready to run PGQL. Need to pass in SID, CSRF token, and PGQL body + graph name etc. * Here I am running a very simple PGQL: SELECT n.ts WHERE (n), n.name = 'unique' 15:57:54.766 [main] DEBUG org.apache.http.wire - http-outgoing-3 >> "POST /core/v1/pgql/run HTTP/1.1[\r][\n]" 15:57:54.766 [main] DEBUG org.apache.http.wire - http-outgoing-3 >> "Content-Length: 180[\r][\n]" 15:57:54.766 [main] DEBUG org.apache.http.wire - http-outgoing-3 >> "Content-Type: application/json; charset=UTF-8[\r][\n]" 15:57:54.766 [main] DEBUG org.apache.http.wire - http-outgoing-3 >> "Host: 127.0.0.1:7007[\r][\n]" 15:57:54.766 [main] DEBUG org.apache.http.wire - http-outgoing-3 >> "Connection: Keep-Alive[\r][\n]" 15:57:54.766 [main] DEBUG org.apache.http.wire - http-outgoing-3 >> "User-Agent: Apache-HttpClient/4.5.4 (Java/1.8.0_144)[\r][\n]" 15:57:54.766 [main] DEBUG org.apache.http.wire - http-outgoing-3 >> "Cookie: SID=e2b2ead4-10b6-41e0-a7f4-4e960859da12; _csrf_token=a838d0ad-e165-4e79-928f-139628db11fc[\r][\n]" 15:57:54.766 [main] DEBUG org.apache.http.wire - http-outgoing-3 >> "Accept-Encoding: gzip,deflate[\r][\n]" 15:57:54.766 [main] DEBUG org.apache.http.wire - http-outgoing-3 >> "[\r][\n]" 15:57:54.766 [main] DEBUG org.apache.http.wire - http-outgoing-3 >> "{"pgqlQuery":"SELECT n.ts WHERE (n), n.name = 'unique'","semantic":"HOMOMORPHISM","graphName":"g1","schemaStrictnessMode":true,"_csrf_token":"a838d0ad-e165-4e79-928f-139628db11fc"}" 15:57:54.784 [main] DEBUG org.apache.http.wire - http-outgoing-3 << "HTTP/1.1 202 [\r][\n]" 15:57:54.784 [main] DEBUG org.apache.http.wire - http-outgoing-3 << "Location: http://127.0.0.1:7007/core/v1/futures/1e6c3163-d549-4357-8924-f539001a1640/status[\r][\n]" 15:57:54.784 [main] DEBUG org.apache.http.wire - http-outgoing-3 << "Content-Type: application/json;charset=utf-8[\r][\n]" 15:57:54.784 [main] DEBUG org.apache.http.wire - http-outgoing-3 << "Content-Length: 51[\r][\n]" 15:57:54.784 [main] DEBUG org.apache.http.wire - http-outgoing-3 << "Date: Thu, 16 Aug 2018 22:57:54 GMT[\r][\n]" 15:57:54.784 [main] DEBUG org.apache.http.wire - http-outgoing-3 << "[\r][\n]" 15:57:54.784 [main] DEBUG org.apache.http.wire - http-outgoing-3 << "{"futureId":"1e6c3163-d549-4357-8924-f539001a1640"}" * * Wait for the above async PGQL call to complete. Not yet. * 15:57:54.789 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "GET /core/v1/futures/1e6c3163-d549-4357-8924-f539001a1640/status HTTP/1.1[\r][\n]" 15:57:54.789 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "Host: 127.0.0.1:7007[\r][\n]" 15:57:54.789 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "Connection: Keep-Alive[\r][\n]" 15:57:54.789 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "User-Agent: Apache-HttpClient/4.5.4 (Java/1.8.0_144)[\r][\n]" 15:57:54.789 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "Cookie: SID=e2b2ead4-10b6-41e0-a7f4-4e960859da12; _csrf_token=a838d0ad-e165-4e79-928f-139628db11fc[\r][\n]" 15:57:54.789 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "Accept-Encoding: gzip,deflate[\r][\n]" 15:57:54.789 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "[\r][\n]" 15:57:54.793 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "HTTP/1.1 200 [\r][\n]" 15:57:54.793 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "Content-Type: application/json;charset=utf-8[\r][\n]" 15:57:54.793 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "Content-Length: 577[\r][\n]" 15:57:54.793 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "Date: Thu, 16 Aug 2018 22:57:54 GMT[\r][\n]" 15:57:54.793 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "[\r][\n]" 15:57:54.793 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "{"id":"1e6c3163-d549-4357-8924-f539001a1640","links":[{"href":"http://127.0.0.1:7007/core/v1/futures/1e6c3163-d549-4357-8924-f539001a1640/status","rel":"self","method":"GET","interaction":["async-polling"]},{"href":"http://127.0.0.1:7007/core/v1/futures/1e6c3163-d549-4357-8924-f539001a1640","rel":"abort","method":"DELETE","interaction":["async-polling"]},{"href":"http://127.0.0.1:7007/core/v1/futures/1e6c3163-d549-4357-8924-f539001a1640/status","rel":"canonical","method":"GET","interaction":["async-polling"]}],"progress":"processing","completed":false,"intervalToPoll":1}" * * Ping again to see if PGQL completes * 15:57:55.355 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "GET /core/v1/futures/1e6c3163-d549-4357-8924-f539001a1640/status HTTP/1.1[\r][\n]" 15:57:55.355 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "Host: 127.0.0.1:7007[\r][\n]" 15:57:55.355 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "Connection: Keep-Alive[\r][\n]" 15:57:55.355 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "User-Agent: Apache-HttpClient/4.5.4 (Java/1.8.0_144)[\r][\n]" 15:57:55.355 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "Cookie: SID=e2b2ead4-10b6-41e0-a7f4-4e960859da12; _csrf_token=a838d0ad-e165-4e79-928f-139628db11fc[\r][\n]" 15:57:55.355 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "Accept-Encoding: gzip,deflate[\r][\n]" 15:57:55.355 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "[\r][\n]" 15:57:55.359 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "HTTP/1.1 200 [\r][\n]" 15:57:55.359 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "Content-Type: application/json;charset=utf-8[\r][\n]" 15:57:55.359 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "Content-Length: 732[\r][\n]" 15:57:55.359 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "Date: Thu, 16 Aug 2018 22:57:54 GMT[\r][\n]" 15:57:55.359 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "[\r][\n]" 15:57:55.359 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "{"id":"1e6c3163-d549-4357-8924-f539001a1640","links":[{"href":"http://127.0.0.1:7007/core/v1/futures/1e6c3163-d549-4357-8924-f539001a1640/status","rel":"self","method":"GET","interaction":["async-polling"]},{"href":"http://127.0.0.1:7007/core/v1/futures/1e6c3163-d549-4357-8924-f539001a1640","rel":"abort","method":"DELETE","interaction":["async-polling"]},{"href":"http://127.0.0.1:7007/core/v1/futures/1e6c3163-d549-4357-8924-f539001a1640/status","rel":"canonical","method":"GET","interaction":["async-polling"]},{"href":"http://127.0.0.1:7007/core/v1/futures/1e6c3163-d549-4357-8924-f539001a1640/value","rel":"related","method":"GET","interaction":["async-polling"]}],"progress":"succeeded","completed":true,"intervalToPoll":512}" * * Fetch PGQL results. * 15:57:55.873 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "GET /core/v1/futures/1e6c3163-d549-4357-8924-f539001a1640/value HTTP/1.1[\r][\n]" 15:57:55.873 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "Host: 127.0.0.1:7007[\r][\n]" 15:57:55.873 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "Connection: Keep-Alive[\r][\n]" 15:57:55.873 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "User-Agent: Apache-HttpClient/4.5.4 (Java/1.8.0_144)[\r][\n]" 15:57:55.873 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "Cookie: SID=e2b2ead4-10b6-41e0-a7f4-4e960859da12; _csrf_token=a838d0ad-e165-4e79-928f-139628db11fc[\r][\n]" 15:57:55.873 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "Accept-Encoding: gzip,deflate[\r][\n]" 15:57:55.873 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "[\r][\n]" 15:57:55.878 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "HTTP/1.1 201 [\r][\n]" 15:57:55.878 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "Location: http://127.0.0.1:7007/core/v1/pgql/run[\r][\n]" 15:57:55.878 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "Content-Type: application/json;charset=utf-8[\r][\n]" 15:57:55.878 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "Content-Length: 587[\r][\n]" 15:57:55.878 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "Date: Thu, 16 Aug 2018 22:57:55 GMT[\r][\n]" 15:57:55.878 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "[\r][\n]" 15:57:55.878 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "{"id":"pgql_1","links":[{"href":"http://127.0.0.1:7007/core/v1/pgqlProxies/pgql_1","rel":"self","method":"GET","interaction":["sync"]},{"href":"http://127.0.0.1:7007/core/v1/pgqlResultProxies/pgql_1/elements","rel":"related","method":"GET","interaction":["sync"]},{"href":"http://127.0.0.1:7007/core/v1/pgqlResultProxies/pgql_1/results","rel":"related","method":"GET","interaction":["sync"]},{"href":"http://127.0.0.1:7007/core/v1/pgqlProxies/pgql_1","rel":"canonical","method":"GET","interaction":["async-polling"]}],"exists":true,"graphName":"g1","resultSetId":"pgql_1","numResults":1}" 15:57:55.890 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "GET /core/v1/pgqlProxies/pgql_1/elements HTTP/1.1[\r][\n]" 15:57:55.890 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "Host: 127.0.0.1:7007[\r][\n]" 15:57:55.890 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "Connection: Keep-Alive[\r][\n]" 15:57:55.890 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "User-Agent: Apache-HttpClient/4.5.4 (Java/1.8.0_144)[\r][\n]" 15:57:55.890 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "Cookie: SID=e2b2ead4-10b6-41e0-a7f4-4e960859da12; _csrf_token=a838d0ad-e165-4e79-928f-139628db11fc[\r][\n]" 15:57:55.890 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "Accept-Encoding: gzip,deflate[\r][\n]" 15:57:55.890 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 >> "[\r][\n]" 15:57:55.900 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "HTTP/1.1 200 [\r][\n]" 15:57:55.900 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "Content-Type: application/json;charset=utf-8[\r][\n]" 15:57:55.900 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "Content-Length: 488[\r][\n]" 15:57:55.900 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "Date: Thu, 16 Aug 2018 22:57:55 GMT[\r][\n]" 15:57:55.900 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "[\r][\n]" 15:57:55.900 [pgx-client-thread-5] DEBUG org.apache.http.wire - http-outgoing-3 << "{"id":"/core/v1/pgqlProxies/pgql_1/elements","links":[{"href":"http://127.0.0.1:7007/core/v1/pgqlProxies/pgql_1/elements","rel":"self","method":"GET","interaction":["sync"]},{"href":"http://127.0.0.1:7007/core/v1/pgqlProxies/pgql_1/elements","rel":"canonical","method":"GET","interaction":["async-polling"]}],"count":1,"totalItems":1,"items":[{"elementType":"TIMESTAMP_WITH_TIMEZONE","varName":"n.ts","vertexEdgeIdType":null}],"hasMore":false,"offset":0,"limit":1,"showTotalResults":true}" 15:57:55.924 [main] DEBUG org.apache.http.wire - http-outgoing-3 >> "GET /core/v1/pgqlProxies/pgql_1/results?start=0&size=2048 HTTP/1.1[\r][\n]" 15:57:55.924 [main] DEBUG org.apache.http.wire - http-outgoing-3 >> "Host: 127.0.0.1:7007[\r][\n]" 15:57:55.924 [main] DEBUG org.apache.http.wire - http-outgoing-3 >> "Connection: Keep-Alive[\r][\n]" 15:57:55.924 [main] DEBUG org.apache.http.wire - http-outgoing-3 >> "User-Agent: Apache-HttpClient/4.5.4 (Java/1.8.0_144)[\r][\n]" 15:57:55.924 [main] DEBUG org.apache.http.wire - http-outgoing-3 >> "Cookie: SID=e2b2ead4-10b6-41e0-a7f4-4e960859da12; _csrf_token=a838d0ad-e165-4e79-928f-139628db11fc[\r][\n]" 15:57:55.924 [main] DEBUG org.apache.http.wire - http-outgoing-3 >> "Accept-Encoding: gzip,deflate[\r][\n]" 15:57:55.924 [main] DEBUG org.apache.http.wire - http-outgoing-3 >> "[\r][\n]" 15:57:55.931 [main] DEBUG org.apache.http.wire - http-outgoing-3 << "HTTP/1.1 200 [\r][\n]" 15:57:55.931 [main] DEBUG org.apache.http.wire - http-outgoing-3 << "Content-Type: application/json;charset=utf-8[\r][\n]" 15:57:55.931 [main] DEBUG org.apache.http.wire - http-outgoing-3 << "Content-Length: 482[\r][\n]" 15:57:55.931 [main] DEBUG org.apache.http.wire - http-outgoing-3 << "Date: Thu, 16 Aug 2018 22:57:55 GMT[\r][\n]" 15:57:55.931 [main] DEBUG org.apache.http.wire - http-outgoing-3 << "[\r][\n]" 15:57:55.931 [main] DEBUG org.apache.http.wire - http-outgoing-3 << "{"id":"/core/v1/pgqlProxies/pgql_1/results","links":[{"href":"http://127.0.0.1:7007/core/v1/pgqlProxies/pgql_1/results","rel":"self","method":"GET","interaction":["sync"]},{"href":"http://127.0.0.1:7007/core/v1/pgqlProxies/pgql_1/results","rel":"canonical","method":"GET","interaction":["async-polling"]}],"count":1,"totalItems":1,"items":[[{"TIMESTAMP_PART_OF_TS_WITH_TZ":1534430004000,"TZ_PART_OF_TS_WITH_TZ":-25200}]],"hasMore":false,"offset":0,"limit":1,"showTotalResults":true}" opg-oracledb> pgxGraph.close()   // JAVA Call // Corresponding REST APIs to close the in-memory graph 17:20:07.314 [main] DEBUG org.apache.http.wire - http-outgoing-2 >> "DELETE /core/v1/graphs/g2?_csrf_token=86f245d0-c4bf-45ca-aa7c-3b3b8b3ec181&ignoreNotFound=false&retention=DESTROY_IF_NOT_USED HTTP/1.1[\r][\n]" 17:20:07.314 [main] DEBUG org.apache.http.wire - http-outgoing-2 >> "Host: 127.0.0.1:7007[\r][\n]" 17:20:07.314 [main] DEBUG org.apache.http.wire - http-outgoing-2 >> "Connection: Keep-Alive[\r][\n]" 17:20:07.314 [main] DEBUG org.apache.http.wire - http-outgoing-2 >> "User-Agent: Apache-HttpClient/4.5.4 (Java/1.8.0_144)[\r][\n]" 17:20:07.314 [main] DEBUG org.apache.http.wire - http-outgoing-2 >> "Cookie: SID=f83eb140-fef0-4a46-bef0-031a09f77f9e; _csrf_token=86f245d0-c4bf-45ca-aa7c-3b3b8b3ec181[\r][\n]" 17:20:07.314 [main] DEBUG org.apache.http.wire - http-outgoing-2 >> "Accept-Encoding: gzip,deflate[\r][\n]" 17:20:07.314 [main] DEBUG org.apache.http.wire - http-outgoing-2 >> "[\r][\n]" 17:20:07.320 [main] DEBUG org.apache.http.wire - http-outgoing-2 << "HTTP/1.1 202 [\r][\n]" 17:20:07.320 [main] DEBUG org.apache.http.wire - http-outgoing-2 << "Location: http://127.0.0.1:7007/core/v1/futures/f93147d3-9556-4245-b60a-a51b0fea2488/status[\r][\n]" 17:20:07.320 [main] DEBUG org.apache.http.wire - http-outgoing-2 << "Content-Type: application/json;charset=utf-8[\r][\n]" 17:20:07.320 [main] DEBUG org.apache.http.wire - http-outgoing-2 << "Content-Length: 51[\r][\n]" 17:20:07.320 [main] DEBUG org.apache.http.wire - http-outgoing-2 << "Date: Wed, 22 Aug 2018 00:20:07 GMT[\r][\n]" 17:20:07.320 [main] DEBUG org.apache.http.wire - http-outgoing-2 << "[\r][\n]" 17:20:07.320 [main] DEBUG org.apache.http.wire - http-outgoing-2 << "{"futureId":"f93147d3-9556-4245-b60a-a51b0fea2488"}" 17:20:07.321 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "GET /core/v1/futures/f93147d3-9556-4245-b60a-a51b0fea2488/status HTTP/1.1[\r][\n]" 17:20:07.321 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "Host: 127.0.0.1:7007[\r][\n]" 17:20:07.321 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "Connection: Keep-Alive[\r][\n]" 17:20:07.321 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "User-Agent: Apache-HttpClient/4.5.4 (Java/1.8.0_144)[\r][\n]" 17:20:07.321 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "Cookie: SID=f83eb140-fef0-4a46-bef0-031a09f77f9e; _csrf_token=86f245d0-c4bf-45ca-aa7c-3b3b8b3ec181[\r][\n]" 17:20:07.322 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "Accept-Encoding: gzip,deflate[\r][\n]" 17:20:07.322 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "[\r][\n]" 17:20:07.324 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "HTTP/1.1 200 [\r][\n]" 17:20:07.324 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "Content-Type: application/json;charset=utf-8[\r][\n]" 17:20:07.324 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "Content-Length: 730[\r][\n]" 17:20:07.324 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "Date: Wed, 22 Aug 2018 00:20:07 GMT[\r][\n]" 17:20:07.324 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "[\r][\n]" 17:20:07.324 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "{"id":"f93147d3-9556-4245-b60a-a51b0fea2488","links":[{"href":"http://127.0.0.1:7007/core/v1/futures/f93147d3-9556-4245-b60a-a51b0fea2488/status","rel":"self","method":"GET","interaction":["async-polling"]},{"href":"http://127.0.0.1:7007/core/v1/futures/f93147d3-9556-4245-b60a-a51b0fea2488","rel":"abort","method":"DELETE","interaction":["async-polling"]},{"href":"http://127.0.0.1:7007/core/v1/futures/f93147d3-9556-4245-b60a-a51b0fea2488/status","rel":"canonical","method":"GET","interaction":["async-polling"]},{"href":"http://127.0.0.1:7007/core/v1/futures/f93147d3-9556-4245-b60a-a51b0fea2488/value","rel":"related","method":"GET","interaction":["async-polling"]}],"progress":"succeeded","completed":true,"intervalToPoll":1}" 17:20:07.327 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "GET /core/v1/futures/f93147d3-9556-4245-b60a-a51b0fea2488/value HTTP/1.1[\r][\n]" 17:20:07.327 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "Host: 127.0.0.1:7007[\r][\n]" 17:20:07.327 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "Connection: Keep-Alive[\r][\n]" 17:20:07.327 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "User-Agent: Apache-HttpClient/4.5.4 (Java/1.8.0_144)[\r][\n]" 17:20:07.327 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "Cookie: SID=f83eb140-fef0-4a46-bef0-031a09f77f9e; _csrf_token=86f245d0-c4bf-45ca-aa7c-3b3b8b3ec181[\r][\n]" 17:20:07.327 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "Accept-Encoding: gzip,deflate[\r][\n]" 17:20:07.327 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "[\r][\n]" 17:20:07.328 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "HTTP/1.1 200 [\r][\n]" 17:20:07.328 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "Content-Type: application/json;charset=utf-8[\r][\n]" 17:20:07.328 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "Content-Length: 2[\r][\n]" 17:20:07.328 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "Date: Wed, 22 Aug 2018 00:20:07 GMT[\r][\n]" 17:20:07.328 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "[\r][\n]" 17:20:07.328 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "{}" opg-oracledb> pgxSession.destroy()   // Java CALL  // Corresponding REST calls to destroy the current PGX session 17:20:38.587 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "DELETE /core/v1/session?_csrf_token=86f245d0-c4bf-45ca-aa7c-3b3b8b3ec181 HTTP/1.1[\r][\n]" 17:20:38.587 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "Host: 127.0.0.1:7007[\r][\n]" 17:20:38.587 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "Connection: Keep-Alive[\r][\n]" 17:20:38.587 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "User-Agent: Apache-HttpClient/4.5.4 (Java/1.8.0_144)[\r][\n]" 17:20:38.587 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "Cookie: SID=f83eb140-fef0-4a46-bef0-031a09f77f9e; _csrf_token=86f245d0-c4bf-45ca-aa7c-3b3b8b3ec181[\r][\n]" 17:20:38.587 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "Accept-Encoding: gzip,deflate[\r][\n]" 17:20:38.587 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 >> "[\r][\n]" 17:20:38.592 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "HTTP/1.1 200 [\r][\n]" 17:20:38.592 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "Content-Type: application/json;charset=utf-8[\r][\n]" 17:20:38.592 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "Content-Length: 2[\r][\n]" 17:20:38.592 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "Date: Wed, 22 Aug 2018 00:20:38 GMT[\r][\n]" 17:20:38.592 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "[\r][\n]" 17:20:38.592 [pgx-client-thread-3] DEBUG org.apache.http.wire - http-outgoing-2 << "{}"   Cheers, Zhe

From time to time, I got a request asking for details on the REST APIs for the PGX component of Oracle Spatial and Graph (OSG) and Oracle Big Data Spatial and Graph (BDSG). In this blog post, I am...

Graph Features

Better support for Timestamp with Zone in the latest Oracle Spatial and Graph Property Graph Patch

Good news, we just released a patch for the Oracle Spatial and Graph Property Graph feature. It contains, among many things, PGX 3.1 and latest Data Access Layer. One important fix is a much better support of timestamp with time zone.  Here is a quick example that illustrates the improvement. cfg = GraphConfigBuilder.forPropertyGraphRdbms().setJdbcUrl("jdbc:oracle:thin:@127.0.0.1:1521:orcl122") .setUsername("pg").setPassword("<YOUR_PASSWORD>")  .setName("test_graph") .setMaxNumConnections(2) .setLoadEdgeLabel(false).addVertexProperty("ts", PropertyType.TIMESTAMP_WITH_TIMEZONE, null)  .addVertexProperty("name", PropertyType.STRING, "default_name")  .addEdgeProperty("cost", PropertyType.DOUBLE, "1000000")  .build(); opg = OraclePropertyGraph.getInstance(cfg); v=opg.addVertex(10000l); v.setProperty("ts", new Date(1000l)); opg.commit() v=opg.getVertex(10000l); ==>Vertex ID 10000 [vertex] {ts:dat:1969-12-31 16:00:01.0}   Note that the above getVertex returns a property of type java.util.Date of which java.sql.Timestamp is a subclass. Now, let's read this graph into a remote PGX endpoint. pgxSession=Pgx.getInstance("http://127.0.0.1:7007").createSession("session1"); analyst=pgxSession.createAnalyst(); pgxGraph = pgxSession.readGraphWithProperties(opg.getConfig(), true); For some older versions of OSG PG, you are likely going to hit the following exception:   "java.lang.UnsupportedOperationException: loading type time_with_timezone through DAL not yet supported" With this new patch, the above will go through without a problem. To sanity check, run a simple PGQL query and print the type of the timestamp property ts. pgxResultSet = pgxGraph.queryPgql("SELECT n.ts MATCH (n)") elem=pgxResultSet.getResults() e1=elem.iterator().next() twz=e1.getTimestampWithTimezone(0) ==>1969-12-31T16:00:01Z twz.getClass().getName() ==>java.time.OffsetDateTime   Cheers, Zhe References: Oracle Spatial and Graph Property Graph Patch (28577866): https://support.oracle.com/epmos/faces/PatchDetail?patchId=28577866 

Good news, we just released a patch for the Oracle Spatial and Graph Property Graph feature. It contains, among many things, PGX 3.1 and latest Data Access Layer. One important fix is a much better...

Graph Features

Using RDF Knowledge Graphs in the Oracle Public Cloud (Part 3)

This is the third and final installment of the series "Using RDF Knowledge Graphs in the Oracle Public Cloud." In this blog post, we will complete the setup of our RDF triplestore in the Oracle Public Cloud by configuring a W3C-standard SPARQL endpoint. Click the links for previous posts in this series: part 1, part 2. The W3C defines several standard REST APIs for querying and updating RDF data. You can read more about the standards here. Oracle RDF Semantic graph leverages Apache Jena Fuseki to provide an implementation of those interfaces. Oracle Support for Apache Jena provides a tight integration between Apache Jena and Oracle RDF Semantic Graph through Oracle-specific implementations of Apache Jena interfaces. This blog post will show how to setup and run Apache Jena Fuseki on our DBCS instance. Fuseki can run as a standalone server or a Java web application. In this case, we will run Fuseki as a standalone server on our DBCS instance. You could also setup an Oracle Java Cloud Service instance and deploy the Fuseki Java web application into the included Web Logic Server instance. The first step is to download the latest Oracle Support for Apache Jena from OTN. Open a web browser to our OTN downloads page. Choose Download Oracle Database 12c Release 12.1.0.2 Support for Apache Jena 3.1, Apache Jena Fuseki 2.4, and Protégé Desktop 5.0. Note that this download works with Oracle Database versions 12.1.0.2 and later, so it is compatible with our 18.1 DBCS instance. After the download completes, transfer the downloaded Oracle Support for Apache Jena file to your DBCS instance. In this example, we copied the file to /home/oracle. Further instructions on how to copy files to/from a DBCS instance can be found in the DBCS user guide or in the detailed HOW-TO available at the end of this post. Open an SSH connection to your DBCS instance as the oracle user. See the DBCS user guide for more information on how to make an SSH connection to a DBCS instance. Create a directory named Jena in /home/oracle. Then move rdf_semantic_graph_support_for_12c_and_jena310_protege_5.0_2017_01_19.zip to the newly created Jena directory and unzip the file. After the unzip command has finished, you will see several directories and a README file. We will now configure Fuseki to access the LGD_SPORT semantic model that we created earlier. Change directory to /fuseki and edit the config-oracle.ttl file. Change the following default <#oracle> dataset specification from <#oracle> rdf:type oracle:Dataset;     oracle:connection     [ a oracle:OracleConnection ;       oracle:jdbcURL "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))";       oracle:User "rdfuser" ;       oracle:Password "rdfuser"     ];     oracle:allGraphs [ oracle:firstModel "TEST_MODEL" ] . to <#oracle> rdf:type oracle:Dataset;     oracle:connection     [ a oracle:OracleConnection ;       oracle:jdbcURL "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PDB1.uspm020.oraclecloud.internal)))";       oracle:User "rdfuser" ;       oracle:Password "rdfuser"     ];     oracle:allGraphs [ oracle:firstModel "LGD_SPORT" ] . Note that SERVICE_NAME will be different depending on the settings for your particular DBCS instance. Next, we will change the default shiro.ini configuration to allow non-localhost connections. First, we need to startup Fuseki to create a /run directory. Simply execute the following command in the current /fuseki directory. ./fuseki-server Once you see the message that Fuseki has started on port 3030, kill the process with Ctrl-C. Now the /run directory should be created. Change directory to /run and edit shiro.ini. Replace /$/** = localhostFilter with /$/server = anon $/** = localhostFilter Change directory back to /fuseki and start the Fuseki service by running the following command: nohup ./fuseki-server --config config-oracle.ttl > fuseki_out.log & Note that we are using nohup to prevent the Fuseki process from terminating if our connection is closed. That’s it. A Fuseki SPARQL endpoint is now up and running on our DBCS instance. Now that the Fuseki server is up and running on port 3030 of our DBCS instance, there are two options for connecting: Create an SSH tunnel to our DBCS instance for port 3030. Create an access rule to open up port 3030 of our DBCS instance to the public internet. In this blog post, we will illustrate the first method using an SSH tunnel. See the detailed HOW-TO at the end of the post for instructions on the second option. Using an SSH tunnel allows us to securely access port 3030 on our DBCS instance without opening port 3030 to the public internet. First, use PuTTY or a similar tool to create an SSH tunnel to forward port 3030 on your client computer to port 3030 on your DBCS instance. We are using PuTTY in this example, as shown below. Refer to the DBCS user guide for detailed instructions. Click Open to open the SSH tunnel and then open a web browser to http://localhost:3030. Click query to open the SPARQL query interface. Click info to see all the available REST endpoints. Now we have used an SSH tunnel to connect to the SPARQL endpoint running on our DBCS instance. We can also use curl to directly test the SPARQL REST interface over the SSH tunnel. In this example, we are using a Cygwin terminal on a Windows client computer. The following curl command will send the SPARQL query in the file test_query.rq to the Fuseki endpoint running on our DBCS instance and print the result to stdout. curl –X POST –data-binary "@test_quey.rq" –H "Content-Type: application/sparql-query" –H "Accept: application/sparql-results+json" "http://localhost:3030/sparql" And we’re done! We have successfully accessed a W3C-standard SPARQL REST endpoint running on our DBCS instance. This concludes the final post in our "Using RDF Knowledge Graphs in the Oracle Public Cloud" series. In the first post, we setup and configured Oracle Spatial and Graph – RDF Semantic Graph on an 18.1 DBCS instance. In the second post, we loaded some RDF data and used SQL Developer’s SPARQL query editor to run some example queries, and, in this final post, we setup a W3C-standard SPARQL endpoint on our DBCS instance to provide a REST interface for our triplestore. Detailed steps for everything covered in this blog series are available in this HOW-TO.

This is the third and final installment of the series "Using RDF Knowledge Graphs in the Oracle Public Cloud." In this blog post, we will complete the setup of our RDF triplestore in the Oracle...

Graph Features

Using RDF Knowledge Graphs in the Oracle Public Cloud (Part 2)

This is the second installment of the series "Using RDF Knowledge Graphs in the Oracle Public Cloud." In this blog post, we will use SQL Developer to load some publicly available RDF data into our DBCS instance and execute a few SPARQL queries with SQL Developer's SPARQL query editor. Click here for part 1 of this series. The data we will use comes from the Linked Geo Data project: http://linkedgeodata.org/About The project provides several downloadable datasets, which are indexed here: https://hobbitdata.informatik.uni-leipzig.de/LinkedGeoData/downloads.linkedgeodata.org/releases/ We will be using a 1.2 million triple dataset of sports facilities: https://hobbitdata.informatik.uni-leipzig.de/LinkedGeoData/downloads.linkedgeodata.org/releases/2015-11-02/2015-11-02-SportThing.node.sorted.nt.bz2 Download SportThing.node.sorted.nt.bz2 to your client computer and use a program such as WinSCP to copy the file to your DBCS instance. Refer to the DBCS user guide for more information on how to copy files to/from your DBCS instance. Detailed instructions for WinSCP can be found in the HOW-TO document available at the end of this blog post. In this example, we have copied the file to /home/oracle on the DBCS instance. We will use a normal database user with connect, resource, and appropriate tablespace privileges to perform the load operation. Recall that we used the SYSTEM user to create and configure our semantic network in the previous installment. Open a connection for your desired database user in SQL Developer. We are using RDFUSER in this example. Expand the RDFUSER connection and expand the RDF Semantic Graph component. The first step is to create a semantic model to hold our RDF dataset. Right click on Models and choose New Model. Enter a Model Name and choose to create a new Application table with TRIPLE column. Choose the tablespace you used when creating the semantic network for Model tablespace. Click Apply. We have now created a model to hold our RDF data. If you expand Models and Regular Models under RDF Semantic Graph, you should see the LGD_SPORT model we created. Now, we will bulk load the downloaded RDF file. The bulk load process consists of two major steps: Loading the file from the file system into a simple staging table in the database. Loading data from the staging table into our semantic model. The first step involves loading from an external table, so we need to use the SYSTEM connection to create a DIRECTORY in the database and grant privileges on it to RDFUSER. Expand the SYSTEM connection and right-click on Directories. Then choose Create Directory. Enter a Directory Name and the full path for the directory on the database server. Click Apply. Expand Directories and click the directory name to see details. Now we need to grant privileges on this directory to RDFUSER. Click actions and select Grant. Grant READ and WRITE privileges to RDFUSER. Click Apply. RDFUSER now has access to the /home/oracle directory on our DBCS instance. Before loading into the staging table, we need to do a few operations from the Unix command line. The downloaded RDF file is compressed, so we will use a Unix named pipe to stream the uncompressed data rather than storing an uncompressed version of the file. Use PuTTY to make an SSH connection to the remote DBCS instance as the oracle user (see the DBCS user guide for more information on how to make an SSH connection). Then execute the following commands to create a named pipe to stream the uncompressed data. mkfifo named_pipe.nt bzcat 2015-11-02-SportThing.node.sorted.nt.bz2 > named_pipe.nt & Now, expand the RDFUSER connection in SQL Developer and expand the RDF Semantic Graph component. Then right click on Models and select “Load RDF data into staging table (External Table).” Choose a name for the external table to create (we are using LGD_EXT_TAB) and fill in the other fields on the Source External Table tab. Enter the names of the files to load (named_pipe.nt in this example) on the Input Files tab. Finally, use the Staging Table tab to enter a name for the staging table that will be created (we are using LGD_STG_TAB) and choose the appropriate format. Now click Apply to load the data into LGD_STG_TAB. Check the contents of LGD_STG_TAB. Next, we will load the data from LGD_STG_TAB into the LGD_SPORT semantic model. To bring up the bulk load interface, expand RDF Semantic Graph under the RDFUSER connection. Then, right-click on Models and select “Bulk Load into model from staging table”. Enter LGD_SPORT for Model and unselect the Create model option since we have already created this semantic model. Also, choose the LGD_STG_TAB for Staging table name. Be careful not to select the external table (LGD_EXT_TAB), as it will also be listed. Consult the user guide for more information on the other options for bulk load. Click Apply, and the load will finish in a minute or so. Now that we have completed the bulk load, it is a good idea to gather statistics on the whole RDF network. Only a privileged user can gather statistics on the whole RDF network, so we need to use the SYSTEM user or some other DBA user. Expand the RDF Semantic Graph component under the SYSTEM connection, right click on RDF Semantic Graph and select Gather Statistics. Enter the desired Degree of parallelism and click Apply. After gathering statistics, the data is ready to query. Now, we will use SQL Developer’s SPARQL query editor to query our dataset. Go back to the RDFUSER connection and expand Models and Regular Models under RDF Semantic Graph. Clicking LGD_SPORT will open the SPARQL query editor for this semantic model. You can edit and execute SPARQL queries here. In addition, several pre-created templates are available. Click Templates -> Demographics -> COUNT ALL to count all triples in the LGD_SPORT model. Click Yes when the warning comes up. Click the green triangle to run the query. You can also directly edit SPARQL queries. The following example shows a SPARQL query to get the top 10 properties and their triple counts.   In addition to SPARQL SELECT queries, CONSTRUCT and DESCRIBE queries are also supported. The query below describes a particular resource in the LGD_SPORT model. Note that any namespace PREFIXes used in the query will also be used to simplify values in the query result. Here we have added several more PREFIXes. That’s it. We have successfully bulk loaded a publicly available RDF dataset into our DBCS instance and executed some queries with SQL Developer’s SPARQL query editor. In the next installment, we will setup a W3C-standard SPARQL endpoint to provide a REST interface. A detailed HOW-TO is available HERE.

This is the second installment of the series "Using RDF Knowledge Graphs in the Oracle Public Cloud." In this blog post, we will use SQL Developer to load some publicly available RDF data into our...

Graph Features

Using RDF Knowledge Graphs in the Oracle Public Cloud (Part 1)

Oracle Database includes an enterprise-grade Resource Description Framework (RDF) triplestore as part of the Spatial and Graph option. RDF is a W3C-standard graph data model for representing knowledge graphs, which are increasingly used to underpin intelligent applications. This is part 1 of a blog series that shows how to setup a fully-functioning RDF triplestore on the Oracle Public Cloud. In part 1, we will show how configure Oracle Spatial and Graph – RDF Semantic Graph on an existing Oracle Database Cloud Service (DBCS) instance. In this example, we will use Oracle Database version 18.1. For more information on how to create a DBCS instance, refer to the tutorial here or download the detailed HOW-TO document at the end of this blog. Subsequent posts in this blog series will show how to load a publicly-available RDF dataset and configure a W3C-standard SPARQL endpoint. We will use Oracle SQL Developer for most of our interaction with the DBCS instance. Starting with version 18.1, SQL Developer includes a nice RDF Semantic Graph plugin that we will use in this blog post. First, use SQL Developer to open a connection to your DBCS instance for the SYSTEM user. There are several ways to connect to a DBCS instance with SQL Developer. Refer to the Database Cloud Service user guide for more information on connecting with SQL Developer. Use a SQL Worksheet for your SYSTEM connection to execute the following query to check the RDF Semantic Graph installation on your DBCS instance. SELECT * FROM MDSYS.RDF_PARAMETER; The query result should show a valid 18.1.0.0.0 installation of RDF Semantic Graph. Next, we will create a Semantic Network to prepare the database for storing RDF data. As a prerequisite, we need to create a tablespace for the Semantic Network. Run the following SQL statement as SYSTEM to create a tablespace for the Semantic Network. create bigfile tablespace rdftbs datafile '?/dbs/rdftbs.dat'  size 512M reuse autoextend on next 512M maxsize 10G  extent management local  segment space management auto; Now we can use the RDF Semantic Graph component of SQL Developer to create the Semantic Network. Expand the system connection by clicking the plus sign next to the connection name, and then scroll down to the RDF Semantic Graph component. Right-click on RDF Semantic Graph and select Create Semantic Network. Use the drop down menu to select the tablespace that we created earlier and click Apply. That’s it. We have verified the RDF Semantic Graph installation and created all the necessary database objects needed to store RDF data. Next, we will make some changes to the default index configuration for better general-purpose query performance. Expand Network Indexes under RDF Semantic Graph to see index codes for the current network indexes. Each letter in an index code corresponds to a component of an RDF quad: S – subject, P – predicate, C – canonical object, G – graph, M – model. By default, two indexes are created: a mandatory PCSGM unique index and a PSCGM index. This indexing scheme works very well when SPARQL triple patterns have constants in the predicate position, but this scheme may encounter performance problems if variables appear in the predicate position. For a more general scheme, a three-index combination of PCSGM, SPCGM and CPSGM works well, so we will drop the PSCGM index and add SPCGM and CPSGM indexes. Right click on RDF_LNK_PSCGM_IDX and select Drop Semantic Index.   Click Apply. Only the PCSGM index should appear under Network Indexes now. Right click on Network Indexes and select Create Semantic Index. Enter SPCGM as the Index code and click Apply. Next, repeat this process using CSPGM as the index code. You should now see CSPGM, PCSGM and SPCGM indexes under Network Indexes. At this point, we have created a semantic network on our DBCS instance and setup an indexing scheme for general-purpose SPARQL queries. Our DBCS instance is now ready to load some RDF data. The next blog post in this series will show how to load and query a publicly-available RDF dataset. A detailed HOW TO document for this blog series is available HERE.

Oracle Database includes an enterprise-grade Resource Description Framework (RDF) triplestore as part of the Spatial and Graph option. RDF is a W3C-standard graph data model for representing knowledge...

Graph Features

Effective Graph Visualization with Cytoscape and Oracle's Property Graph Database (3)

In this installment of the Cytoscape visualization for Oracle's Property Graph Database series, I am going to talk about key steps to make your first connection to a backend property graph (PG) database. Remember Oracle offers a PG DB on Big Data as well as a PG DB on big data platform. First of all, I'd like to make a quick announcement. Very recently, we released a Cytoscape Plugin for Oracle Database 18c. It is designed and tested for Oracle Database 18c. And we have fine tuned and improved performance when running Cytoscape client against a remote Oracle 18c database. To get this plugin, please click here and choose the one dated May 2018. Now, let me switch back to making connections to a backend PG database. There are two cases: 1) the backend PG database is an Oracle Database. Start Cytoscape, click File, Load, Property Graph, and Connect to Oracle Database. A screenshot is shown below. After that, type in JDBC connection url, username, password, and click the magnifier icon which is highlighted below. This is going to start a graph DB connection. Once a connection is established, you will see a drop down list under the "Graph name" and it is then up to you to pick a graph to start your visualization. 2) the backend PG database is either an Oracle NoSQL Database or an Apache HBase. The steps are almost identical other than the database connection settings are different among Oracle NoSQL Database, Apache HBase, and Oracle Database. Hope this helps you get started. Zhe  

In this installment of the Cytoscape visualization for Oracle's Property Graph Database series, I am going to talk about key steps to make your first connection to a backend property graph (PG)...

Graph Features

Oracle's Property Graph Database in Shining Armor (5)

This is the fifth installment of the series "Oracle's Property Graph Database in Shining Armor." In this blog, I am going to talk about fast property graph data loading using the built-in Java APIs on Oracle Database Cloud Service Bare Metal. Click 1, 2, 3, 4 for previous installments. The following is a code snippet running in Groovy. You can take it out and embed it in your Java application (with very minor changes including type declaration) if needed. -- Step 1: create a partitioned graph. 16 partitions are used. oracle=new Oracle("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=g.subnet201703141927320.graphdbnet1.oraclevcn.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdb1.subnet201703141927320.graphdbnet1.oraclevcn.com)))","pg", "<PASSWORD_HERE>"); opg=OraclePropertyGraph.getInstance(oracle, "lj", 16 /*iHashPartitionsNum*/); -- Step 2: create an instance of OraclePropertyGraph cfg = GraphConfigBuilder.forPropertyGraphRdbms().setJdbcUrl("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=g.subnet201703141927320.graphdbnet1.oraclevcn.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdb1.subnet201703141927320.graphdbnet1.oraclevcn.com)))") .setUsername("pg").setPassword("<PASSWORD_HERE>") .setName("lj") .setMaxNumConnections(16) .setLoadEdgeLabel(false).addEdgeProperty("weight", PropertyType.DOUBLE, "1000000")  .build(); opg=OraclePropertyGraph.getInstance(cfg); -- Step 3: load in parallel the LiveJ in .opv/.ope format (compressed) opg-oracledb> opgdl=OraclePropertyGraphDataLoader.getInstance(); opg-oracledb> vfile="/home/oracle/liveJ.opv.gz"  // vertex flat file opg-oracledb> efile="/home/oracle/liveJ.ope.gz"  // edge flat file opg-oracledb> lStarttime=System.currentTimeMillis();   opg-oracledb> opgdl.loadDataWithSqlLdr(opg, "pg", "<PASSWORD_HERE>", "orcl122", vfile, efile, 32/*dop*/, true/*bNamedPipe*/, "/u01/app/oracle/product/12.2.0.1/dbhome_1/bin/sqlldr", true, "PDML=T,PDDL=T,NO_DUP=T,"); opg-oracledb> System.out.println("total ms " + (System.currentTimeMillis() - lStarttime)); total ms 308560   opg-oracledb> opg-oracledb> opg.countEdges() ==>68993773 All done. The above API completed loading LiveJ graph (with 68.99M edges) in just 308 seconds. And this time included building a few unique key indexes and B*Tree indexes. Cheers, Zhe  

This is the fifth installment of the series "Oracle's Property Graph Database in Shining Armor." In this blog, I am going to talk about fast property graph data loading using the built-in Java APIs on...

Graph Features

Powerful and Effective Graph Visualization with Cytoscape and Oracle's Property Graph Database (2)

In this installment of the Cytoscape visualization for Oracle's Property Graph Database series, I am going to talk about key steps required to set up Cytoscape visualization for Oracle's Property Graph Database. These steps are the same for Oracle Spatial and Graph (OSG), and Oracle Big Data Spatial and Graph (BDSG). Assume you are using Linux or Mac OS. The major steps are as follows. 0) Make sure you have Oracle JDK 8. 1) Download & install Cytoscape (3.2.1 or above). Assume you install Cytoscape under   /Applications/Cytoscape_v3.6.1 2) Start Cytoscape to initialize. Make sure the following directory is created ~/CytoscapeConfiguration   Once the above directory is created, quit Cytoscape. 3) cd /Applications/Cytoscape_v3.6.1 4) Unzip the Cytoscape plugin for OSG (or BDSG) in the above directory. A new sub directory will be created. The directory name is oracle_property_graph_cytoscape/ if you are using Cytoscape plugin for Oracle Database. 5) Copy propertyGraphSupport*.jar from the jar/ in the above sub directory into ~/CytoscapeConfiguration/3/apps/installed/ 6) Copy propertyGraph.properties from the jar/ in the above sub directory into ~/CytoscapeConfiguration To customize this configuration, follow the usage guide (a PDF file you can find in the Cytoscape plugin zip file). 7) kick off Cytoscape by running the following under /Applications/Cytoscape_v3.6.1 sh ./startCytoscape.sh NOTE: it is important to use startCytoscape.sh to start the visualization. Do not use the original cytoscape.sh because you will not see any property graph related functions (highlighted below).   Cheers, Zhe References [1] http://www.oracle.com/technetwork/database/options/spatialandgraph/downloads/index-156999.html [2] Oracle Big Data Spatial and Graph Downloads    

In this installment of the Cytoscape visualization for Oracle's Property Graph Database series, I am going to talk about key steps required to set up Cytoscape visualization for Oracle's Property...

Graph Features

Oracle's Property Graph Database in Shining Armor (4)

This is the fourth installment of the series "Oracle's Property Graph Database in Shining Armor." In this blog, I am going to talk about starting PGX server in a few quick steps. Click 1, 2, 3 for previous installments. Note that if you chose Oracle Database 12.2.0.1 during service creation, then you may want to install this patch in order to run PGQL, a powerful graph pattern matching language. If you chose Oracle Database 18.1, then install patch 27639357 instead. To start PGX server, we first login to the machine running the Oracle Database instance. Here I am assuming you want to run PGX on the same hardware running Oracle Database. - Login  [rdf@hqgraph1 ~]$  ssh -i  <key_filename>   opc@<IP_HERE>   - Become oracle [opc@g dbhome_1]$ sudo su - oracle [oracle@g ~]$ cd /u01/app/oracle/product/12.2.0.1/dbhome_1   - Change enable_tls from true to false in the following configuration file [oracle@g dbhome_1]$ cd md/ [oracle@g md]$ cd property_graph [oracle@g property_graph]$ cat pgx/conf/server.conf {   "port": 7007,   "enable_tls": false,   "enable_client_authentication": false }   - Set a few options about number of workers, on-heap, off-heap memory size (explained in this blog).  [oracle@g property_graph]$ export _JAVA_OPTIONS="-Dpgx.num_workers_io=4 -Dpgx.max_off_heap_size=12000 -Dpgx.num_workers_analysis=4 -Xmx12000m "   - Kick off the server.  [oracle@g property_graph]$ cd pgx/bin/ [oracle@g bin]$ ./start-server Picked up _JAVA_OPTIONS: -Dpgx.num_workers_io=4 ... ... SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory] 00:05:09,092 INFO Ctrl$2 - >>> PGX engine running. May 05, 2018 12:05:11 AM org.apache.coyote.AbstractProtocol start INFO: Starting ProtocolHandler ["http-nio-7007"] ...   - Verify that the server is listening.  [oracle@g ~]$ netstat -an |grep 7007 tcp        0      0 0.0.0.0:7007                0.0.0.0:*                   LISTEN      [oracle@g ~]$   - Ping the endpoint.  [oracle@g ~]$ curl http://127.0.0.1:7007/version "2.X.0"   OK. All done. Cheers, Zhe  

This is the fourth installment of the series "Oracle's Property Graph Database in Shining Armor." In this blog, I am going to talk about starting PGX server in a few quick steps. Click 1, 2, 3 for pr...

Graph Features

Join AskTOM Office Hours on May 31 - Gain Insights with Graph Analytics

Want to learn how to use powerful Oracle Database graph analysis in your business applications?  Get a jumpstart in our free, monthly 1 hour sessions for developers in the AskTOM series.  This month’s topic: May 31, 2018    8:00 US PDT  |  17:00 CEST Exploring Gain Insights with Graph Analytics See the magic of graphs in this session. Graph analysis can answer questions like detecting patterns of fraud or identifying influential customers - and do it quickly and efficiently. Our experts Albert Godfrind and Zhe Wu will show you the APIs for accessing graphs and running analytics such as finding influencers, communities, anomalies, and how to use them from various languages including Groovy, Python, and Javascript, with Jupiter and Zeppelin notebooks. As always, feel free to bring any graph-related questions you have for us also. Learn more and register here:  https://devgym.oracle.com/pls/apex/dg/office_hours/3084 If you missed them, view replays from prior AskTOM property graph sessions here: Introduction to Property Graphs https://youtu.be/e_lBqPh2k6Y How To Model and Construct Graphs with Oracle Database  https://youtu.be/evFTmXWU7Zw    

Want to learn how to use powerful Oracle Database graph analysis in your business applications?  Get a jumpstart in our free, monthly 1 hour sessions for developers in the AskTOM series.  This month’s...

Graph Features

A Trick to Build Efficiently a Config for a Property Graph with Many Properties

If you have used Oracle's property graph database, then you are very likely familiar with "graph config" concept. Basically, it contains all the information required to read a graph from a backend database and construct an in-memory snapshot of the graph. Specifically, the config includes the graph name, the underlying graph database information, and the list of vertex/edge properties that need to be included in the in-memory snapshot of the graph. An example is as follows. cfg = GraphConfigBuilder.forPropertyGraphRdbms().setJdbcUrl("jdbc:oracle:thin:@127.0.0.1:1521:orcl122")        .setUsername("pg").setPassword("pg") .setName("lj1") .setMaxNumConnections(16)       .setLoadEdgeLabel(false).addEdgeProperty("weight", PropertyType.DOUBLE, "1000000")  .build(); Now, it is important that one lists out every single property to be included. If a property P is not declared in the config, then you cannot use P in a follow-up PGQL query. Declaring all properties required can be a tedious task when a graph happens to have many properties.  Fortunately, with a bit of SQL trick, this can be simplified. Say, we have a graph named myGraph stored in an Oracle Database (either on-prem or in Oracle Database Cloud Service). The following SQL lists out all the property keys and the data type of the property values. -- "1" denotes an integer property data type.  SQL>  select distinct k, t from myGraphVT$; K --------------------------------------------------------------------------------      T ---------- LOB      1 Label      1 Type      1 ... To generate the necessary config setting, I use a SQL statement as follows and it would produce a list of addVertexProperty commands in one shot. SQL> set heading off; SQL> set pagesize 9999 SQL> select distinct '.addVertexProperty("' || k || '", PropertyType.STRING, "null")' from myGraphVT$; .addVertexProperty("LOB", PropertyType.STRING, "null") .addVertexProperty("Label", PropertyType.STRING, "null") .addVertexProperty("Type", PropertyType.STRING, "null") .addVertexProperty("Address", PropertyType.STRING, "null") .addVertexProperty("Name", PropertyType.STRING, "null") .addVertexProperty("Web", PropertyType.STRING, "null")  ... The rest of the work is a simple copy & paste.  Cheers, Zhe

If you have used Oracle's property graph database, then you are very likely familiar with "graph config" concept. Basically, it contains all the information required to read a graph from a backend...

Graph Features

Graph resources - videos and blog posts

Recently, several helpful learning resources on using graph technologies have become available.  Check out these videos and blog posts! Videos and replays AskTOM Office Hours Introduction to Property Graphs – February session replay:  https://youtu.be/e_lBqPh2k6Y  An intro to property graph use cases and features with a demo in this developer-oriented series. Oracle Code Los Angeles session videos: Property Graph 101 – Dan Vlamis, Vlamis Software – https://youtu.be/qaQO-mW6lFs  A great introduction to property graphs from Vlamis. Analyzing Blockchain and Bitcoin Transaction Data – Zhe Wu, Oracle – https://youtu.be/w8OEVobyhFE   Zhe explains how graph databases can be used to gain insight into blockchain/bitcoin transaction data. ODTUG Webinar recording:  Property Graphs 101: How to Get Started with Property Graphs on the Oracle Database –  Arthur Dayton, Vlamis Software https://youtu.be/QSj0zOjOAWI  Arthur shows step-by-step how to use Oracle Database to take HR data in standard relational tables, convert them to Oracle’s property graph format, and perform graph visualizations and analysis to view relationships in the data. More Spatial and Graph videos on our YouTube channel:  https://www.youtube.com/channel/UCZqBavfLlCuS0il6zNY696w Blog posts & presentation slides Intro to Graphs at Oracle by Michael Sullivan, Oracle A-Team  http://www.ateam-oracle.com/intro-to-graphs-at-oracle/ A great intro to Oracle’s graph technologies, covering both RDF semantic graphs and property graphs on Oracle Database and Oracle Big Data platforms. GDPR and Analytics – Data Lineage on Steroids ITOUG presentation by Gianni Ceresa - https://speakerdeck.com/gianniceresa/gdpr-and-you-the-nightmare-of-ba Blog post by Gianni Ceresa on https://gianniceresa.com/ An end-to-end example of graphs in Oracle Database using OE Sample Schema Spatial and Graph Summit at Analytics and Data Summit, March 19-22, 2018 Slides are available at https://analyticsanddatasummit.org/schedule/ (currently for registered attendees only)    

Recently, several helpful learning resources on using graph technologies have become available.  Check out these videos and blog posts! Videos and replays AskTOM Office Hours Introduction to Property...

Graph Features

Oracle's Property Graph Database in Shining Armor (3)

This is the third installment of the series "Oracle's Property Graph Database in Shining Armor." In this blog, I am going to talk about configuring the database so that it is ready to run property graph functions. Click here for creating the database on OCI Bare Metal.  Step 1: Login, sudo to become "oracle", enable 32K (extended max string) support. Note that I am assuming the PDB is "PDB1" in the following script. $ ssh -i <your_key_file_here> opc@<YOUR_IP_HERE> Last login: Mon Mar 19 16:49:28 2018 from <YOUR_IP_HERE> [opc@g ~]$ [opc@g ~]$ sudo su - oracle [oracle@g ~]$ [oracle@g ~]$ cat /etc/oratab # db1:/u01/app/oracle/product/12.2.0.1/dbhome_2:N [oracle@g dbhome_2]$ tcsh [oracle@g dbhome_2]$ source bin/coraenv ORACLE_SID = [oracle] ? db1 The Oracle base has been set to /u01/app/oracle sqlplus / as sysdba alter system set max_string_size=extended scope=spfile; alter session set container=PDB1; alter system set max_string_size=extended scope=spfile; shutdown immediate; conn / as sysdba shutdown immediate; startup upgrade purge recyclebin; @?/rdbms/admin/utl32k.sql alter session set container=PDB1; startup upgrade purge recyclebin; @?/rdbms/admin/utl32k.sql shutdown immediate; conn / as sysdba shutdown immediate; startup    Step 2: Create a user 'PG' alter session set container=PDB1; CREATE bigfile TABLESPACE pgts DATAFILE '+DATA/pgts.data' SIZE 2G REUSE AUTOEXTEND ON next 128M maxsize unlimited EXTENT MANAGEMENT LOCAL ; create user pg identified by <YOUR_PASSWORD_HERE>; alter user pg  default tablespace pgts; grant connect, resource, alter session to pg; grant unlimited tablespace to pg;   Step 3: Create a test PG graph using PL/SQL API [oracle@g dbhome_2]$ sqlplus pg/<YOUR_PASSWORD_HERE>@db122 SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 6 23:50:54 2018 Copyright (c) 1982, 2016, Oracle.  All rights reserved. Connected to: Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bit Production SQL> exec opg_apis.create_pg('my_first_pg',4,4,'PGTS', null); PL/SQL procedure successfully completed. SQL> desc my_first_pgVT$;  Name                       Null?    Type  ----------------------------------------- -------- ----------------------------  VID                       NOT NULL NUMBER  K                            NVARCHAR2(3100)  T                            NUMBER(38)  V                            NVARCHAR2(15000)  VN                            NUMBER  VT                            TIMESTAMP(6) WITH TIME ZONE  SL                            NUMBER  VTS                            DATE  VTE                            DATE  FE                            NVARCHAR2(4000)   Note that if you encounter an "ORA-28353: failed to open wallet" exception, you can do the following.  tcsh  setenv ORACLE_UNQNAME <YOUR_DB_UNIQUE_NAME>  sqlplus / as sysdba alter system set encryption wallet open identified by <YOUR_PASSWORD>; SQL> conn / as sysdba alter session set container=PDB1; administer key management set keystore open identified by <YOUR_PASSWORD> ;   Cheers, Zhe

This is the third installment of the series "Oracle's Property Graph Database in Shining Armor." In this blog, I am going to talk about configuring the database so that it is ready to run property...

Graph Features

Oracle's Property Graph Database in Shining Armor (2)

This is the second installment of the series "Oracle's Property Graph Database in Shining Armor." In this blog, I am going to talk about setting up the property graph database on Oracle Cloud, and specifically, Oracle Database Cloud Service - Bare Metal. The database creation is straightforward. Just login to OCI with your credential (a screenshot as follows). Click on Database tab, and follow the UI. I must say the UI is really intuitive and easy to use. I did not read a single page of documentation and I managed to get a 12.2.0.1 database up and running in a few minutes.  Note that in order to use the property graph feature in Oracle Spatial and Graph, you can choose either Oracle Database 12.2 or 18.1. Once your database is installed, write down the IP address and of course the database name. The following screenshot shows the DB info, shape, and the public IP (masked out). For performance, I selected Bare Metal (BM DenseIO). And you can find Database Unique Name in the Databases section. In my setup, the unique name is graph_phx1hc, highlighted below. In a follow up blog, I am going to talk about a few simple configurations required to get the property graph functions ready for business.  Cheers, Zhe

This is the second installment of the series "Oracle's Property Graph Database in Shining Armor." In this blog, I am going to talk about setting up the property graph database on Oracle Cloud, and...

Using SSD to Speed up Property Graph Operations in Oracle Database (III)

This is the third installment of the series "Using SSD to Speed up Property Graph Operations in Oracle Database." For the first two, click 1st & 2nd. In this blog, I am going to show you how to serialize out an already-loaded in-memory graph into a .PGB format. A PGB format can be viewed as a memory dump of a graph snapshot. Hence it is very efficient to read back into memory. However, there is no easy way to make an update to an existing PGB. ... opg-oracledb> pgxServer="http://127.0.0.1:7007/" opg-oracledb> pgxSession=Pgx.getInstance(pgxServer).createSession("session1"); opg-oracledb> pgxGraph = pgxSession.readGraphWithProperties(opg.getConfig()); ... opg-oracledb> lStarttime=System.currentTimeMillis();  opg-oracledb> pgxGraph.store(Format.PGB, "/tmp/livej.pgb", true); opg-oracledb> System.out.println("total ms " + (System.currentTimeMillis() - lStarttime)); total ms 13306 opg-oracledb> pgxGraph.memoryMb ==>1745 $ ls -l /tmp/livej.pgb -rw-rw-r--. 1 user group 886096199 Mar  2 13:51 /tmp/livej.pgb   From the above results, we can see that it took about 13.3 seconds to write out a PGB of size 886 MB. In the next installment, I am going to talk about how to store this PGB persistently into Oracle Database. Cheers, Zhe Wu  

This is the third installment of the series "Using SSD to Speed up Property Graph Operations in Oracle Database." For the first two, click 1st & 2nd. In this blog, I am going to show you how to...

Graph Features

Announcing Graph Developer Training Day – March 19, 2018

The Oracle Spatial and Graph product team announces Graph Developer Training Day 2018, a free full-day workshop to help you understand and develop property graph applications using Oracle Database Spatial and Graph and Big Data Spatial and Graph.  Oracle partners, customers, attendees of Analytics and Data Summit 2018, and Oracle staff are invited.  Targeted toward developers, architects and data scientists.  Sessions will be delivered by Oracle developers and product managers. The event will take place at Oracle HQ on Monday, March 19, before Analytics and Data Summit.  Please RSVP to marion.smith@oracle.com by March 6 if you’re planning to attend!  Seating is limited.   Details: March 19, 2018 8:15am – 5:00pm Oracle Conference Center 350 Oracle Pkwy, Redwood City, CA 94065   Agenda (subject to change): 8:15 – 9:00am       Breakfast/Registration   9:00 - 10:30am      Getting Started with Graph Databases - Welcome and overview of graph technologies - Provisioning an Oracle Database Cloud Service (DBCS) - Understanding graph formats and efficient data loading   10:30 - 11:00am    Break   11:00 -12:30            Generating and Analyzing Graph Data - Graph generation - how to construct a graph from source data - Graph analytics using PGX and RDBMS   12:30 – 1:30pm     Networking Lunch   1:30 – 3:00pm                 Graph Query and Visualization - Property Graph Query Language (PGQL) - on PGX and RDBMS - Graph visualization (Cytoscape)   3:00 – 3:30pm                 Break   3:30 – 5:00pm                 New Tooling and Functionality + Lightning Round - Notebook UI - Graph Studio - Lightning Round   5:00                       Wrap-up and Close    

The Oracle Spatial and Graph product team announces Graph Developer Training Day 2018, a free full-day workshop to help you understand and develop property graph applications using Oracle Database...

Spatial and Graph

Spatial and Graph Sessions at Analytics and Data Summit 2018

All Analytics. All Data. No Nonsense. Featuring Spatial and Graph Summit March 20–22, 2018 Oracle HQ Conference Center 350 Oracle Pkwy, Redwood City, CA 94065 We’ve changed our name! Formerly called the BIWA Summit with the Spatial and Graph Summit.  Same great technical content – great new name! Announcing 24+ technical sessions, case studies, and hands on labs around spatial and graph technologies View the agenda here Register today for best rates. The agenda for Spatial and Graph Summit at Analytics and Data Summit 2018 is now available.  Join us for this premier event for spatial, graph, and analytics and attend: Technology sessions from Oracle experts on the latest developments, useful features and best practices Case studies from top customers and partners Hands-on Labs - ramp up fast on new technologies Keynotes by industry experts Sessions on machine learning, big data, cloud and database technologies from Analytics and Data Summit View the agenda at  http://www.biwasummit.org/schedule/ Selected Spatial + Graph sessions include the following.  Check out the agenda for more sessions and details. Spatial technologies for business applications and GIS Enriching Business Data with Location – Albert Godfrind, Oracle Using GeoJSON in the Oracle Database – Albert Godfrind, Oracle Powerful Spatial Features You Never Knew Existed in Oracle Spatial and Graph – Daniel Geringer, Oracle 18c Spatial New Features Update – Siva Ravada, Oracle Using Spatial in Oracle Cloud with Developer Tools and Frameworks – David Lapp & Siva Ravada, Oracle Spatial analytics with Oracle DV, Analytics Cloud, and Database Cloud Service – David Lapp & Jayant Sharma, Oracle Spatial Analytics with Spark & Big Data – Siva Ravada, Oracle Geospatial Industry Use Cases Country Scale digital maps data with Oracle Spatial & Graph – Ankeet Bhat, MapmyIndia Ordnance Survey Ireland: National Mapping as a Service – Éamonn Clinton, Ordnance Survey Ireland Feeding a Hungry World: Using Oracle Products to Ensure Global Food Security – Mark Pelletier, USDA/InuTeq 3D Spatial Utility Database at CALTRANS – Donna Rodrick, California Dept of Transportation Hands On Labs – Get started with property graphs Property Graph 101 on Oracle Database 12.2 for the completely clueless –  Arthur Dayton and Cathye Pendley, Vlamis Software Solutions Using Property Graph and Graph Analytics on NoSQL to Analyze Data on Meetup.com – Karin Patenge, Oracle Germany Using R for Big Data Advanced Analytics, Machine Learning, and Graph – Mark Hornick, Oracle Graph Technical Sessions An Introduction to Graph: Database, Analytics, and Cloud Services – Hans Viehmann, Zhe Wu, Jean Ihm, Oracle Sneak Preview:  Graph Cloud Services and Spatial Studio for Database Cloud – Jim Steiner & Jayant Sharma, Oracle Analyzing Blockchain and Bitcoin Transaction Data as Graph –Zhe Wu & Xavier Lopez, Oracle Ingesting streaming data into Graph database – Guido Schmutz, Trivadis   Applications of Graph Technologies Analyze the Global Knowledge Graph with Visualization, Cloud, & Spatial Tech – Kevin Madden, Tom Sawyer Software Graph Modeling and Analysis for Complex Automotive Data Management – Masahiro Yoshioka, Mazda Follow the Money: A Graph Model for Monitoring Bank Transactions – Federico Garcia Calabria, Oracle Anomaly Detection in Medicare Provider Data using OAAgraph – Sungpack Hong, Mark Hornick, Francisco Morales, Oracle Fake News, Trolls, Bots and Money Laundering – Find the truth with Graphs – Jim Steiner and Sungpack Hong, Oracle

All Analytics. All Data. No Nonsense. Featuring Spatial and Graph Summit March 20–22, 2018 Oracle HQ Conference Center 350 Oracle Pkwy, Redwood City, CA 94065 We’ve changed our name! Formerly called the...

Graph Features

Using SSD to Speed up Property Graph Operations in Oracle Database (II)

This is the second installment of the series "Using SSD to Speed up Property Graph Operations in Oracle Database." Click here for the first one. In this blog, I am going to share with you a follow up test after the graph data was loaded into the database. The same hardware and the same Oracle Database version are used as those in the first installment. However, I upgraded the OS version to the latest EL7. The upgrade was not related to this graph database performance series but rather due to a need to run Docker engine on the same hardware system. OK, enough talking, let's see some code snippets. On the PGX side, before I started the PGX server, I set the following options. export _JAVA_OPTIONS="-Dpgx.num_workers_io=8 -Dpgx.max_off_heap_size=12000 -Dpgx.num_workers_analysis=8 -Xmx12000m " Then, I kicked off the following Groovy script (Java code snippets). opg-oracledb> cfg = GraphConfigBuilder.forPropertyGraphRdbms().setJdbcUrl("jdbc:oracle:thin:@127.0.0.1:1521:orcl122") .setUsername("pg").setPassword("<YOUR_PASSWORD_HERE>") .setName("lj1") .setMaxNumConnections(16) .setLoadEdgeLabel(false).addEdgeProperty("weight", PropertyType.DOUBLE, "1000000")  .build(); pgxServer="http://127.0.0.1:7007/" lStarttime=System.currentTimeMillis(); pgxSession=Pgx.getInstance(pgxServer).createSession("session1"); pgxGraph = pgxSession.readGraphWithProperties(opg.getConfig()); System.out.println("total ms " + (System.currentTimeMillis() - lStarttime)); I did three consecutive runs (cold, warm, hot) and the time took to read this 70M-edges graph were: total ms 117335 total ms 87873 total ms 86983 As you can see, a warm run took a bit less than 90 seconds to read the whole graph into PGX's in-memory data structures. During the graph reading, I did a top to see the system workload and observed the following. It is obvious that both the Java process (PGX) and the database were working hard in parallel.  Tasks: 597 total,   4 running, 593 sleeping,   0 stopped,   0 zombie %Cpu(s): 30.0 us,  2.7 sy,  0.0 ni, 53.2 id, 13.1 wa,  0.0 hi,  1.0 si,  0.0 st KiB Mem : 65698368 total,  5023204 free, 15870304 used, 44804860 buff/cache KiB Swap: 28344760 total, 28342916 free,     1844 used. 37149828 avail Mem   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                        16600 user       20   0 20.575g 3.079g  25348 S 214.2  4.9   1:13.57 java                                           17282 user       20   0 19.236g 275956 269628 D  22.8  0.4   0:02.95 oracle_17282_or                                17256 user       20   0 19.236g 302200 295284 D  21.5  0.5   0:02.93 oracle_17256_or                                17258 user       20   0 19.236g 274808 268480 S  21.2  0.4   0:02.98 oracle_17258_or                                17262 user       20   0 19.236g 276572 270252 S  21.2  0.4   0:02.90 oracle_17262_or                                17264 user       20   0 19.236g 275912 269580 D  21.2  0.4   0:03.00 oracle_17264_or                                17268 user       20   0 19.236g 274812 268460 D  21.2  0.4   0:02.97 oracle_17268_or                                17270 user       20   0 19.236g 269464 263136 D  21.2  0.4   0:02.79 oracle_17270_or                                17278 user       20   0 19.236g 273056 266724 R  21.2  0.4   0:02.92 oracle_17278_or  Cheers,

This is the second installment of the series "Using SSD to Speed up Property Graph Operations in Oracle Database." Click here for the first one. In this blog, I am going to share with you a follow up...

Graph Features

Using Advanced Options When Creating a New Property Graph in Oracle Database

For property graph users that know Oracle Database well, you likely know it already that the PL/SQL API, opg_apis.create_pg, offers an options parameter. One of the supported option is "INMEMORY=T" which leverages the Database In-Memory feature.  A question arises then, can we use this option from Java or via the graph config? The answer is yes to both. To create a new property graph with an advanced option, the following static Java method (part of oracle.pg.rdbms.OraclePropertyGraph class) can be used. One can easily set the option(s) in the last parameter.   public static OraclePropertyGraph getInstance(Oracle oracle, String szGraphName,       int iHashPartitionsNum, int iDOP, String szTBS, String szOptions)     throws SQLException;   The following shows, on the other hand, a way to set the advanced option via graph config. Here we assume the graph name "tg1" is new. builder = GraphConfigBuilder.forPropertyGraphRdbms() builder.setJdbcUrl("jdbc:oracle:thin:@<HOST>:1521:<SID>") .setUsername("pg").setPassword("pg")  .setName("tg1") .setMaxNumConnections(2) .setLoadEdgeLabel(false) .addVertexProperty("name", PropertyType.STRING, "default_name")  .addEdgeProperty("cost", PropertyType.DOUBLE, "1000000") ; builder.setOptions("INMEMORY=T"); cfg = builder.build(); opg = OraclePropertyGraph.getInstance(cfg); Once the above Groovy script (running Java code snippets) completes, you can run a simple SQL to verify that the inmemory option is enabled. Note that there are 8 rows here because there are 8 partitions in this vertex table TG1VT$. SQL> select inmemory from user_tab_partitions where table_name='TG1VT$'; INMEMORY -------- ENABLED ENABLED ENABLED ENABLED ENABLED ENABLED ENABLED ENABLED 8 rows selected.   Cheers,    

For property graph users that know Oracle Database well, you likely know it already that the PL/SQL API, opg_apis.create_pg, offers an optionsparameter. One of the supported option is "INMEMORY=T"...

Graph Features

Using SSD to Speed up Property Graph Operations in Oracle Database (I)

First of all, Happy New Year!  At the end of last year, I got a question on property graph data loading performance in Oracle Database. After a while, I realized the performance issue was most likely caused by the database setup: a single SATA disk used in a VM. The IO performance was obviously poor in the setup. To illustrate the importance of a sufficiently fast storage to an Oracle Database, I did an experiment using an old commodity server setup which has 64 GB RAM and 2 quad-core CPUs. The server was assembled from parts 6 years ago and the total cost was < 1000 USD. There were a few SATA disks installed. To speed up storage, I added two  Samsung SSDs (850 Pro, 1TB each). After that, I installed Oracle Database release 12.2.0.1 and the two SSDs are under the management of ASM. external redundancy was chosen. The graph used is the LiveJournal which has around 70M edges. And the following code snippet was executed in Groovy. A property graph named lj1 was first created with 16 partitions. I then used the built-in loadDataWithSqlLdr API (part of Data Access Layer) to load the data into Oracle Database in parallel (DOP=8).  oracle=new Oracle("jdbc:oracle:thin:@127.0.0.1:1521:orcl122", "pg", "<password>"); opg=OraclePropertyGraph.getInstance(oracle, "lj1", 16 /*iHashPartitionsNum*/); opg-oracledb> opgdl=OraclePropertyGraphDataLoader.getInstance(); opg-oracledb> vfile="/home/rdf/liveJ.opv.gz"  // vertex flat file opg-oracledb> efile="/home/rdf/liveJ.ope.gz"  // edge flat file opg-oracledb> lStarttime=System.currentTimeMillis(); opg-oracledb> opgdl.loadDataWithSqlLdr(opg, "pg", "pg", "orcl122", vfile, efile, 8/*dop*/, false/*bNamedPipe*/, "/u01/app/rdf/product/12.2.0/dbhome_1/bin/sqlldr", true, "PDML=T,PDDL=T,NO_DUP=T,"); opg-oracledb> System.out.println("total time in ms " + (System.currentTimeMillis() - lStarttime)); total time in ms 710577   In this test, I bulk loaded ~70M edges in 710 seconds. This is at least 15x faster than the performance reported on that system with slow IO. Note that within 710 seconds, we not only loaded the data into Oracle Database, but also built a few key B*Tree indices on the underlying tables holding vertices and edges. Cheers,  

First of all, Happy New Year!  At the end of last year, I got a question on property graph data loading performance in Oracle Database. After a while, I realized the performance issue was most likely...

Graph Features

Deploy In-Memory Parallel Graph Analytics (PGX) to Oracle Java Cloud Service (JCS)

This is the follow up for one previous blog "How to enable Oracle Database Cloud Service with Property Graph Capabilities". A PGX server can run in two modes: embedded mode and remote mode. The embedded mode means using the PGX's embedded Java container; To run PGX in a remote mode, user needs to deploy the PGX web application to a remote Java container, for example, Tomcat, or WebLogic Server. Oracle Java Cloud Service offers a very simple way to host the PGX web application in a WebLogic Server. This blog tells you how to deploy the PGX web application to an existing JCS WebLogic server and connect to it through SSL. For tutorial on how to create a Oracle JCS WebLogic service, please visit the link, or you can download the HOW-TO document at the end of this blog. Download and Deploy PGX to JCS instance Download the Oracle Big Data Spatial and Graph with this link: https://support.oracle.com/epmos/faces/PatchDetail?patchId=27118297&requestId=21722011   Unzip the file and locate the pgx war file, pgx-webapp-wls.war, under folder: md/property_graph/pgx/server/  In your browser Open WebLogic Server Console log in page Log in with the user weblogic and the password you defined when creating the instance.  At the top left, click the button “Lock & Edit”  On the left under Domain Structure, click Deployments  In the middle under Deployments, click Install  Above the Path textbox, click the link to Upload your file(s)  ​ Next to Deployment Archive, click “Browse…” and open “pgx-webapp-<version>-wls.war” which you downloaded earlier Click Next and wait for the upload to complete.  Upload % complete is displayed on the lower left corner of your browser  When complete you will see a message indicating upload successful.  Click Next  Under “Choose installation type and scope”, leave the default selection (“Install this deployment as an application”)  Click Next  Under Servers check the JCS1_dom_adminserver, you can also use the clusters and check all servers in the cluster Click Next  Under General, leave the name as it is  Click Next  Under Additional Configuration leave default (“Yes, take me to the deployment's configuration screen.”)  Click Finish  At top left, click button “Activate Changes”  In the middle under “Settings for pgx-webapp-name”, click the tab “Control”  Check the box next to “pgx-webapp-name”  Click on the menu “Start” and select “Servicing all requests”  Click “Yes” when prompted to confirm  You should now see pgx state is “Active”  Congratulation! The PGX server is now deployed to Java Cloud Service and ready to be used with your PGX client instance for development. Connect to the PGX server using HTTPS/SSL Download the server side certificate Simply used Firefox, connected to the SSL-based endpoint, and exported the certificate. You may refer to the following screenshot to download the pgx server side self-signed certification, note the host name, you will need to add this to your client hosts file. Add the server side certificate to your client* key store Create an empty keystore The following example command creates a keystore.jks. It will ask a few questions along the way but those are very straightforward. You definitely want to use a much stronger password than "changeit".  keytool -genkey -keyalg RSA -alias selfsigned -keystore keystore.jks -storepass changeit -validity 360 -keysize 2048 Import the server side certificate to the above keystore keytool -import -trustcacerts -alias DemoCertFor_JCS1_domain -file <YOUR_CERTIFICATE_FILE_HERE>  -keystore keystore.jks Specify the keystore for your client For example, if you are using the built-in Groovy, you can add the following to the JAVA_OPTIONS setting in gremlin-opg-rdbms.sh. -Djavax.net.ssl.trustStore=/home/oracle/keystore.jks -Djavax.net.ssl.trustStorePassword=changei Or run below scripts with your console export JAVA_OPTIONS="$JAVA_OPTIONS -Djavax.net.ssl.trustStore=/home/oracle/jcs_final/keystore.jks -Djavax.net.ssl.trustStorePassword=changeit" Configure the client hosts file for the connection Because the self signed certificate uses host name instead of IP address, user needs to modify the client side hosts file to translate the specified host name to an IP address, you can follow the below steps to complete this operation 1, Login to the client machine 2, Open the /etc/hosts file (assume client system is Linux based) 3, Add one line like below to the end of the file and save the change <YOUR_IP_ADDRESS>   <YOUT_HOST_NAME> You may refer to another post for this topic: https://blogs.oracle.com/oraclespatial/four-quick-steps-to-use-a-https-ssl-based-pgx-service-endpoint Now it is time to test the secure connection to the PGX server from your client. cd $ORACLE_HOME/md/property_graph/dal/groovy sh ./gremlin-opg-rdbms.sh cfg =GraphConfigBuilder.forPropertyGraphRdbms().setJdbcUrl("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=YOUR_CLIENT_IP)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=PDB1.uspm020.oraclecloud.internal)))").setUsername("scott").setPassword("<YOUR_PASSWORD_HERE>").setName("connections").setMaxNumConnections(8).setLoadEdgeLabel(false).addVertexProperty("name",PropertyType.STRING, "default_name").addEdgeProperty("cost",PropertyType.DOUBLE, "1000000").build(); opg = OraclePropertyGraph.getInstance(cfg); opg.clearRepository(); // start from scratch opgdl=OraclePropertyGraphDataLoader.getInstance(); vfile="../../data/connections.opv" //vertex flat file efile="../../data/connections.ope" //edge flat file opgdl.loadData(opg, vfile, efile, 2,10000, true, null); session=Pgx.createSession("https://YOUR_HOST_NAME:PORT/pgx", "session-id-1231"); opg-nosql> analyst=session.createAnalyst(); opg-nosql> pgxGraph = session.readGraphWithProperties(opg.getConfig(),true); //count triangles analyst.countTriangles(pgxGraph, true); //pgql opg-oracledb> pgxResultSet = pgxGraph.queryPgql("SELECT n,m WHERE (n) -> (m)") pgxResultSet.print(10); +------------------------------------+ | n | m | +------------------------------------+ | PgxVertex[ID=2] | PgxVertex[ID=1] | | PgxVertex[ID=3] | PgxVertex[ID=1] | | PgxVertex[ID=6] | PgxVertex[ID=1] | | PgxVertex[ID=7] | PgxVertex[ID=1] | | PgxVertex[ID=8] | PgxVertex[ID=1] | | PgxVertex[ID=9] | PgxVertex[ID=1] | | PgxVertex[ID=10] | PgxVertex[ID=1] | | PgxVertex[ID=11] | PgxVertex[ID=1] | | PgxVertex[ID=12] | PgxVertex[ID=1] | | PgxVertex[ID=19] | PgxVertex[ID=1] |   *A PGX client could be any machine with the database (12.2 or above) installed HOW-TO document is available HERE.

This is the follow up for one previous blog "How to enable Oracle Database Cloud Service with Property Graph Capabilities". A PGX server can run in two modes: embedded mode and remote mode. The...

Use Python with Oracle Spatial and Graph Property Graph (III)

The previous two installments (I, II) talked about some basics on using Python with Oracle Spatial and Graph (OSG) Property Graph. In this 3rd installment, I will show how one uses BokehJS [1] together with property graph for interactive data visualization.  The graph data I am going to use is a movie graph which was created based on the movie demo that's bundled with Oracle Big Data Lite VM. There are two types of vertices in this graph: users and movies. And users and movies are linked with edges denoting click relationships between a user and a movie.  The following code snippet assumes we have opg, an instance of OraclePropertyGraph. It starts with reading two properties "budget" and "gross" from a set of movie vertices, plots a budget vs gross chart with matplotlib, and finally uses bokeh to plot the same data in an interactive manner. opg.getVertices() is an API to fetch detailed information on vertices. I am reading both movie and user vertices. It is smart to add a search criteria "type"="movie" to focus on just movies, but you get the idea. Plot out budget and gross on a static image with matplotlib.  Now, switch to BokehJS.   One can easily interact (pan and zoom) with this chart. The following shows a zoomed-in portion (bottom left corner) of the previous chart. Acknowledgement: many thanks to my teammates, Hugo, Gaby, and Ana for their contributions to this blog! Happy Thanksgiving!   References [1] https://bokeh.pydata.org/en/latest/docs/dev_guide/bokehjs.html

The previous two installments (I, II) talked about some basics on using Python with Oracle Spatial and Graph (OSG) Property Graph. In this 3rd installment, I will show how one uses BokehJS [1]...

Four Quick Steps to Use a HTTPS (SSL) based PGX Service Endpoint

In the past, I have shown many examples on connecting a client to a remote PGX service endpoint. For simplicity, the endpoint used was HTTP based. In practice, one may want to use HTTPS (SSL) based endpoint for better security. Given the most recent Equifax security breach, who wouldn't use better security?! If the SSL-enabled endpoint is using a self-signed certificate, then you will most likely see this error "unable to find valid certification path to requested target". In this blog, I am going to show you four easy steps to address that problem. Step 1. Create an empty keystore The following example command creates a keystore.jks. It will ask a few questions along the way but those are very straightforward. You definitely want to use a much stronger password than "changeit".    keytool -genkey -keyalg RSA -alias selfsigned -keystore keystore.jks -storepass changeit -validity 360 -keysize 2048 Step 2: Download the server side certificate I simply used Firefox, connected to the SSL-based endpoint, and exported the certificate. If you have never done this before, the following two screenshots can help you get there quickly. Step 3: Add the server side certificate to the above keystore keytool -import -trustcacerts -alias DemoCertFor_JCS2_domain -file <YOUR_CERTIFICATE_FILE_HERE>  -keystore keystore.jks   Step 4: Specify the keystore for your client For example, if you are using the built-in Groovy, you can add the following to the JAVA_OPTIONS setting in gremlin-opg-rdbms.sh. -Djavax.net.ssl.trustStore=/home/oracle/keystore.jks -Djavax.net.ssl.trustStorePassword=changeit    Time to test, the following should go through without any errors. Note that to avoid seeing "javax.net.ssl.SSLException: hostname in certificate didn't match", a hostname instead of an IP address has to be used when specifying <YOUR_PGX_SERVER>. opg-oracledb> s=Pgx.createSession("https://<YOUR_PGX_SERVER>:<PORT>/pgx", "s1") Cheers,  

In the past, I have shown many examples on connecting a client to a remote PGX service endpoint. For simplicity, the endpoint used was HTTP based. In practice, one may want to use HTTPS (SSL)...

Use Python with Oracle Spatial and Graph Property Graph (II)

This is the second installment of the "Use Python with Oracle Spatial and Graph Property Graph" series. Click here for the first one.  Once in a while, I got a question "Do you like Java or Python better?" I have used Java for 17+ years so I do like it a lot. In the past few years however I got to use Python more and more, partly because many sales/technical consultants use Python with graph technologies and I need to answer their Python questions, partly because of Python's use in Tensorflow, and partly because I am teaching my kids to write code in Python. There are certainly pros and cons of these two languages. To me, they are, however, just tools along the same line as SQL/NoSQL/MapReduce/Spark, etc. I use whatever tools that suit my purpose, put them aside once I am done. No strings attached. Seriously :) Now, back to business. The property graph feature (in OSG or BDSG) has many Java APIs. The existing Python wrappers do not cover all of them. So what do I do when I need to use a function in one of the Java APIs but not yet exposed by the Python wrapper? This is where JPype come into play. The following Jupyter Notebook snippets reflect a most recent Q/A regarding how to generate a graph out of a relational data source using Python. First, connect to an Oracle Database (12.2.0.1 or higher). Then, I invoke the convertRDBMSTable2OPV Java API from Python using JPype. In this example, a set of vertices is created out of the famous employee "EMP" table. As you can see, I am using JClass to pull in the functions I need. That's it. You should see a .opv file generated under /tmp. cat /tmp/testg.opv 7369,name,1,SMITH,, 7499,name,1,ALLEN,, 7521,name,1,WARD,, 7566,name,1,JONES,, 7654,name,1,MARTIN,, 7698,name,1,BLAKE,, 7782,name,1,CLARK,, 7788,name,1,SCOTT,, 7839,name,1,KING,, 7844,name,1,TURNER,, 7876,name,1,ADAMS,, 7900,name,1,JAMES,, 7902,name,1,FORD,, 7934,name,1,MILLER,, Cheers,

This is the second installment of the "Use Python with Oracle Spatial and Graph Property Graph" series. Click here for the first one.  Once in a while, I got a question "Do you like Java or...

How to enable Oracle Database Cloud Service with Property Graph Capabilities

With Oracle Database release 12.2, Oracle property graph capability is available from Oracle Database side as well. Oracle property graph feature is available with the Oracle Spatial and Graph product.  This feature provides graph data management and analysis. The property graph feature is also a feature of Oracle Big Data Spatial and Graph.  You can refer to Oracle Big Data Spatial and Graph for more details on this product. This blog tells you how to enable property graph capabilities with an existing Oracle Database Cloud service. For tutorial on how to create an Oracle Database Cloud service, please visit the link, or you can download the HOW-TO document at the end of this blog. Enable 32K Varchar2, which is required by property graph A detailed description of the following steps can be found in: https://docs.oracle.com/database/121/REFRN/GUID-D424D23B-0933-425F-BC69-9C0E6724693C.htm#REFRN10321 Before we do that, we may check the default MAX_STRING value SQL> show parameters max_string; SQL> ALTER SESSION SET CONTAINER=CDB$ROOT; Session altered. SQL> ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup upgrade; ORACLE instance started. … Database opened. SQL> ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE; Pluggable database altered. SQL> quit Disconnected from Oracle Database 12c EE High Perf Release 12.2.0.1… [oracle@GDBCS1 dbhome_1]$ cd $ORACLE_HOME/rdbms/admin [oracle@GDBCS1 admin]$ mkdir /u01/utl32k_cdb_pdbs_output [oracle@GDBCS1 admin]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -l '/u01/utl32k_cdb_pdbs_output' -b utl32k_cdb_pdbs_output utl32k.sql … Enter Password: catcon.pl: completed successfully [oracle@GDBCS1 admin]$ sqlplus / as sysdba SQL> shutdown immediate; SQL> startup SQL> ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE; Pluggable database altered. SQL> quit Disconnected from Oracle Database 12c EE High Perf Release 12.2.0.1… [oracle@GDBCS1 admin]$ mkdir /u01/utlrp_cdb_pdbs_output [oracle@GDBCS1admin]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -l '/u01/utlrp_cdb_pdbs_output' -b utlrp_cdb_pdbs_output utlrp.sql … Enter Password: … catcon.pl: completed successfully [oracle@GDBCS1 admin]$ Validate the change made to MAX_STRING_SIZE. To verify, run the following commands and you should see the value of max_string_size changed to "EXTENDED" [oracle@GDBCS1 admin]$ sqlplus / as sysdba SQL> alter session set container=PDB1; Session altered. SQL> show parameters max_string; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ max_string_size string EXTENDED Create a schema for later use by enabling user ‘scott’ SQL> conn / as sysdba Connected. SQL> alter session set container=PDB1; Session altered. SQL> alter user scott identified by tiger789#_O0 account unlock; User altered. Apply a patch to enable database support for PGQL Download this patch: https://support.oracle.com/epmos/faces/PatchDetail?patchId=25640325 Notes on transferring files to/from DBCS are here https://docs.oracle.com/en/cloud/paas/database-dbaas-cloud/csdbi/copy-files-node.html In this exercise, we will use WinSCP to upload the patch file into the DBCS with the private key. Follow the readme and apply the patch as described below. [oracle@GDBCS1 gpatch]$ cd 25640325/ [oracle@GDBCS1 25640325]$ ls p25640325_osg_pg_update.zip  README.txt [oracle@GDBCS1 25640325]$ rm -rf $ORACLE_HOME/md/property_graph/* [oracle@GDBCS1 25640325]$ rm -f $ORACLE_HOME/md/admin/*opg*  [oracle@GDBCS1 25640325]$ cd $ORACLE_HOME [oracle@GDBCS1 dbhome_1]$ unzip /home/oracle/gpatch/25640325/p25640325_osg_pg_update.zip Archive:  /home/oracle/gpatch/25640325/p25640325_osg_pg_update.zip    creating: md/property_graph/examples/    creating: md/property_graph/pyopg/ … … Re-install Oracle Spatial and Graph Property Graph schema PL/SQL packages cd $ORACLE_HOME/md/admin/ sqlplus /  as sysdba        alter session set container=pdb1; @catopg.sql   Loading a Property Graph in Oracle Database using Groovy Before you can start building a text index or running graph analytics, you will first need to get the graph data loaded into the database. The following shows an example flow of using the parallel property graph data loader to ingest a property graph that represents a small social network into an Oracle Database (12c Release 2). This property graph data is encoded in flat file format (.opv/.ope). DBCS includes a built-in Groovy shell (based on the Gremlin Groovy shell script). With this command line shell interface, you can perform graph operations using Java APIs. cd $ORACLE_HOME/md/property_graph/dal/groovy $ sh ./gremlin-opg-rdbms.sh  -------------------------------- opg-oracledb> // you need to customize JdbcURL, Username, and Password in the following graph config setting. opg-oracledb> cfg =GraphConfigBuilder.forPropertyGraphRdbms().setJdbcUrl("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST= 129.150.84.48)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=PDB1.uspm020.oraclecloud.internal)))").setUsername("scott").setPassword("tiger789#_O0").setName("connections") .setMaxNumConnections(8).setLoadEdgeLabel(false).addVertexProperty("name",PropertyType.STRING, "default_name").addEdgeProperty("weight",PropertyType.DOUBLE, "1000000").build();  opg-oracledb> opg = OraclePropertyGraph.getInstance(cfg); opg-oracledb> opg.clearRepository();   // start from scratch opg-oracledb> opgdl=OraclePropertyGraphDataLoader.getInstance(); opg-oracledb> vfile="../../data/connections.opv"  //vertex flat file opg-oracledb> efile="../../data/connections.ope"  //edge flat file opg-oracledb> opgdl.loadData(opg, vfile, efile, 2,10000, true, null); opg-oracledb> opg.countVertices() ==>78 opg-oracledb> opg.countEdges() ==>164 Run Graph Analysis and PGQL with an embedded PGX mode Find these people who has direct link between. // Create an in memory analytics session and analyst opg-oracledb> session=Pgx.createSession("session_ID_1"); opg-oracledb> analyst=session.createAnalyst(); // Read graph data from database into memory opg-oracledb> pgxGraph =session.readGraphWithProperties(opg.getConfig(),true); //count triangles analyst.countTriangles(pgxGraph, true); ==>22 opg-oracledb> pgxResultSet = pgxGraph.queryPgql("SELECT n,m WHERE (n) -> (m)") ==>PgqlResultSetImpl[graph=connections,numResults=164] opg-oracledb> pgxResultSet.print(10); +------------------------------------+ | n                | m               | +------------------------------------+ | PgxVertex[ID=2]  | PgxVertex[ID=1] | | PgxVertex[ID=3]  | PgxVertex[ID=1] | | PgxVertex[ID=6]  | PgxVertex[ID=1] | | PgxVertex[ID=7]  | PgxVertex[ID=1] | | PgxVertex[ID=8]  | PgxVertex[ID=1] | | PgxVertex[ID=9]  | PgxVertex[ID=1] | | PgxVertex[ID=10] | PgxVertex[ID=1] | | PgxVertex[ID=11] | PgxVertex[ID=1] | | PgxVertex[ID=12] | PgxVertex[ID=1] | | PgxVertex[ID=19] | PgxVertex[ID=1] | +------------------------------------+ Congratulations! you have now set up your Oracle Database Cloud Service instance with property graph capabilities, loaded a sample property graph data, and started performing property graph analysis with an embedded pgx, next you may follow another HOW-TO tutorial to deploy your own pgx service to a WebLogic server and access it remotely. HOW-TO document is available HERE    

With Oracle Database release 12.2, Oracle property graph capability is available from Oracle Database side as well. Oracle property graph feature is available with the Oracle Spatial and Graph...

Graph Features

Property Graph (v2.2) in a Box

To start, download Oracle Big Data Lite Virtual Machine v4.9 from the following page. http://www.oracle.com/technetwork/database/bigdata-appliance/oracle-bigdatalite-2104726.html It is recommended to use Oracle VM Virtual Box 5.0.16 or up to import this virtual machine. Once import is done successfully, login as oracle (default password is welcome1). On the desktop, there is a "Start/Stop Services" icon - double clicking that will lead to a console popup with a list of services. Check Oracle NoSQL Database, hit enter, and the built-in Oracle NoSQL Database will start automatically. If you need to shutdown the Oracle NoSQL Database, just repeat this process.  Next, I am going to show you a simple Groovy based script that loads a sample property graph representing a small social network, reads out vertices and edges, writes it out, and finally counts the number of triangles and run a simple pgql in this network. Open a Linux terminal in this virtual machine and type in the following: $ cd /opt/oracle/oracle-spatial-graph/property_graph/dal/groovy $ ./gremlin-opg-nosql.sh -------------------------------- opg-nosql> // // Connect to the Oracle NoSQL Database in this virtual box //     server = new ArrayList<String>();     server.add("bigdatalite:5000");     cfg = GraphConfigBuilder.forPropertyGraphNosql()             \       .setName("connections").setStoreName("kvstore")                \       .setHosts(server)                                          \       .addEdgeProperty("weight", PropertyType.DOUBLE, "1000000") \       .addVertexProperty("company", PropertyType.STRING, "NULL") \       .setMaxNumConnections(2).build(); // Get an instance of OraclePropertyGraph // opg = OraclePropertyGraph.getInstance(cfg); opg.clearRepository(); // // Use the parallel data loader API to load a // sample property graph in flat file formatwith a // degree of parallelism (DOP) 2 // vfile="../../data/connections.opv" efile="../../data/connections.ope" opgdl=OraclePropertyGraphDataLoader.getInstance(); opgdl.loadData(opg, vfile, efile, 2); // read through the vertices opg.getVertices(); // read through the edges opg.getEdges(); // // You can add vertices/edges, change properties etc. here. // ... // // // Serialize the graph out into a pair of flat files with DOP=2 // vOutput="/tmp/mygraph.opv" eOutput="/tmp/mygraph.ope" OraclePropertyGraphUtils.exportFlatFiles(opg, vOutput, eOutput, 2, false); // // Create an analyst instance // session=Pgx.createSession("session_ID_1"); analyst=session.createAnalyst(); // Read graph data from database into memory pgxGraph = session.readGraphWithProperties(opg.getConfig(), true); //call the count triangle api analyst.countTriangles(pgxGraph, true); ==>22 // run a pgql pgxResultSet = pgxGraph.queryPgql("SELECT n,m WHERE (n)->(m)->(n), n!=m") //print out the first 10 records from the resultset pgxResultSet.print(10); +-------------------------------------+ | n                | m                | +-------------------------------------+ | PgxVertex[ID=1]  | PgxVertex[ID=2]  | | PgxVertex[ID=36] | PgxVertex[ID=2]  | | PgxVertex[ID=37] | PgxVertex[ID=2]  | | PgxVertex[ID=1]  | PgxVertex[ID=11] | | PgxVertex[ID=10] | PgxVertex[ID=11] | | PgxVertex[ID=18] | PgxVertex[ID=16] | | PgxVertex[ID=15] | PgxVertex[ID=16] | | PgxVertex[ID=16] | PgxVertex[ID=18] | | PgxVertex[ID=21] | PgxVertex[ID=23] | | PgxVertex[ID=25] | PgxVertex[ID=23] | +-------------------------------------+ ==>null // get the total number of the records from the result set pgxResultSet.getNumResults()     ==>132 Note that the same virtual box has Apache HBase installed as well as Oracle NoSQL Database.Once Apache HBase is configured and started, the same script (except the DB connection initialization part) can be used without a change.

To start, download Oracle Big Data Lite Virtual Machine v4.9 from the following page. http://www.oracle.com/technetwork/database/bigdata-appliance/oracle-bigdatalite-2104726.htmlIt is recommended to...

Graph Features

A Few Easy Steps to Start a REST Endpoint for Oracle Spatial and Graph Property Graph

The property graph feature has been part of Oracle Spatial and Graph option starting from Oracle Database 12c Release 2 (12.2.0.1). With this feature, one can access and manipulate graph data stored in Oracle Database using Java APIs and Python wrappers. In this blog, I am going to show you a few easy steps to start an example REST endpoint for the property graph feature. cd ${ORACLE_HOME}/md/property_graph/dal/webapp # Make sure we have the following jar files under webapp/ directory # You can get the missing jar files from # (http://tinkerpop.com/downloads/rexster/rexster-server-2.3.0.zip) [webapp]$ ls -1 *.jar blueprints-rexster-graph-2.3.0.jar commons-cli-1.2.jar commons-collections-3.2.1.jar commons-configuration-1.6.jar commons-lang-2.4.jar commons-logging-1.0.4.jar grizzly-core-2.2.16.jar grizzly-http-2.2.16.jar grizzly-http-server-2.2.16.jar grizzly-http-servlet-2.2.16.jar javassist-3.15.0-GA.jar javax.servlet-api-3.0.1.jar jersey-core-1.17.jar jersey-json-1.17.jar jersey-server-1.17.jar jersey-servlet-1.17.jar jung-algorithms-2.0.1.jar jung-api-2.0.1.jar jung-visualization-2.0.1.jar msgpack-0.6.5.jar rexster-core-2.3.0.jar rexster-protocol-2.3.0.jar rexster-server-2.3.0.jar # Create rexster.sh under webapp/ $ cat rexster.sh #!/bin/bash CP=$( echo `dirname $0`/*.jar . | sed 's/ /:/g') CP=$CP:$(find -L `dirname $0`/../../lib/ -name "*.jar" | tr '\n' ':') CP=$CP:$(find -L `dirname $0`/../groovy/ -name "*.jar" | tr '\n' ':') PUBLIC=`dirname $0`/../public/ # Find Java if [ "$JAVA_HOME" = "" ] ; then     JAVA="java -server" else     JAVA="$JAVA_HOME/bin/java -server" fi # Set Java options if [ "$JAVA_OPTIONS" = "" ] ; then     JAVA_OPTIONS="-Xms2G -Xmx4G " fi # Launch the application $JAVA $JAVA_OPTIONS -cp $CP com.tinkerpop.rexster.Application $@ -wr $PUBLIC # Return the program's exit code exit $?   # # Create and customize rexster.xml. # $ cat rexster.xml <?xml version="1.0" encoding="UTF-8"?> <rexster>     <script-engine-reset-threshold>-1</script-engine-reset-threshold>     <script-engine-init>data/init.groovy</script-engine-init>     <script-engines>gremlin-groovy</script-engines>     <oracle-pool-size>3</oracle-pool-size>     <oracle-property-graph-backends>         <backend>             <backend-name>rdbms_connection</backend-name>             <backend-type>oracle_rdbms</backend-type>             <properties>                 <jdbcUrl>jdbc:oracle:thin:@127.0.0.1:1521:orcl</jdbcUrl>                 <user>YOUR_SCHEMA_HERE</user>                 <password>YOUR_PASSWORD_HERE</password>             </properties>         </backend>     </oracle-property-graph-backends>     <graphs>         <graph>             <graph-name>YOUR_GRAPH_NAME_HERE</graph-name>             <graph-type>oracle.pg.rdbms.OraclePropertyGraphConfiguration</graph-type>             <properties> <jdbcUrl>jdbc:oracle:thin:@127.0.0.1:1521:orcl</jdbcUrl> <user>YOUR_SCHEMA_HERE</user> <password>YOUR_PASSWORD_HERE</password> </properties>             <extensions> <allows> <allow>tp:gremlin</allow> </allows> </extensions>         </graph>     </graphs> </rexster> # Start the REST endpoint sh rexster.sh --start -c ./rexster.xml    # Give it a test, open the following URL in your web browser http://<YOUR_HOSTNAME>:8182/graphs/   Note: the following set of wget commands can fetch the libraries (jar files) mentioned in this blog post. One can run them from a Linux terminal. Be sure to set proxy if your machine is behind a firewall. echo "Downloading dependency blueprints-rexster-graph-2.3.0.jar" wget http://central.maven.org/maven2/com/tinkerpop/blueprints/blueprints-rexster-graph/2.3.0/blueprints-rexster-graph-2.3.0.jar echo "Downloading dependency commons-cli-1.2.jar" wget http://central.maven.org/maven2/commons-cli/commons-cli/1.2/commons-cli-1.2.jar echo "Downloading dependency commons-collections-3.2.1.jar" wget http://central.maven.org/maven2/commons-collections/commons-collections/3.2.1/commons-collections-3.2.1.jar echo "Downloading dependency commons-configuration-1.6.jar" wget http://central.maven.org/maven2/commons-configuration/commons-configuration/1.6/commons-configuration-1.6.jar echo "Downloading dependency commons-lang-2.4.jar" wget http://central.maven.org/maven2/commons-lang/commons-lang/2.4/commons-lang-2.4.jar echo "Downloading dependency commons-logging-1.0.4.jar" wget http://central.maven.org/maven2/commons-logging/commons-logging/1.0.4/commons-logging-1.0.4.jar echo "Downloading dependency grizzly-core-2.2.16.jar" wget http://central.maven.org/maven2/org/glassfish/grizzly/grizzly-core/2.2.16/grizzly-core-2.2.16.jar echo "Downloading dependency grizzly-http-2.2.16.jar" wget http://central.maven.org/maven2/org/glassfish/grizzly/grizzly-http/2.2.16/grizzly-http-2.2.16.jar echo "Downloading dependency grizzly-http-server-2.2.16.jar" wget http://central.maven.org/maven2/org/glassfish/grizzly/grizzly-http-server/2.2.16/grizzly-http-server-2.2.16.jar echo "Downloading dependency grizzly-http-servlet-2.2.16.jar" wget http://central.maven.org/maven2/org/glassfish/grizzly/grizzly-http-servlet/2.2.16/grizzly-http-servlet-2.2.16.jar echo "Downloading dependency javassist-3.15.0-GA.jar" wget http://central.maven.org/maven2/org/javassist/javassist/3.15.0-GA/javassist-3.15.0-GA.jar echo "Downloading dependency javax.servlet-api-3.0.1.jar" wget http://central.maven.org/maven2/javax/servlet/javax.servlet-api/3.0.1/javax.servlet-api-3.0.1.jar echo "Downloading dependency jersey-core-1.17.jar" wget http://central.maven.org/maven2/com/sun/jersey/jersey-core/1.17/jersey-core-1.17.jar echo "Downloading dependency jersey-json-1.17.jar" wget http://central.maven.org/maven2/com/sun/jersey/jersey-json/1.17.1/jersey-json-1.17.1.jar echo "Downloading dependency jersey-server-1.17.jar" wget http://central.maven.org/maven2/com/sun/jersey/jersey-server/1.17/jersey-server-1.17.jar echo "Downloading dependency jersey-servlet-1.17.jar" wget http://central.maven.org/maven2/com/sun/jersey/jersey-servlet/1.17/jersey-servlet-1.17.jar echo "Downloading dependency jung-algorithms-2.0.1.jar" wget http://central.maven.org/maven2/net/sf/jung/jung-algorithms/2.0.1/jung-algorithms-2.0.1.jar echo "Downloading dependency jung-api-2.0.1.jar" wget http://central.maven.org/maven2/net/sf/jung/jung-api/2.0.1/jung-api-2.0.1.jar echo "Downloading dependency jung-visualization-2.0.1.jar" wget http://central.maven.org/maven2/net/sf/jung/jung-visualization/2.0.1/jung-visualization-2.0.1.jar echo "Downloading dependency msgpack-0.6.5.jar" wget http://central.maven.org/maven2/org/msgpack/msgpack/0.6.5/msgpack-0.6.5.jar echo "Downloading dependency rexster-core-2.3.0.jar" wget http://central.maven.org/maven2/com/tinkerpop/rexster/rexster-core/2.3.0/rexster-core-2.3.0.jar echo "Downloading dependency rexster-protocol-2.3.0.jar" wget http://central.maven.org/maven2/com/tinkerpop/rexster/rexster-protocol/2.3.0/rexster-protocol-2.3.0.jar echo "Downloading dependency rexster-server-2.3.0.jar" wget http://central.maven.org/maven2/com/tinkerpop/rexster/rexster-server/2.3.0/rexster-server-2.3.0.jar  

The property graph feature has been part of Oracle Spatial and Graph option starting from Oracle Database 12c Release 2 (12.2.0.1). With this feature, one can access and manipulate graph data stored...

Graph Features

An Easy Way to Get Graph Config in JSON for Oracle Database

To use the property graph functions provided by Oracle Database 12.2.0.1 (+), it is important to get the graph configuration right. A typical graph configuration contains critical information like DB connection info, graph name, properties to be read/used, and more.  It is straightforward to construct a graph config Java object using the provided Java APIs. In some cases, however, a user may want to generate a graph config in JSON and use it in a web application. Below, I am showing an easy way to get a graph config in JSON for Oracle Database. First start the built-in groovy, [oracle@groovy/]$ sh ./gremlin-opg-rdbms.sh  Next, construct a graph config using the Java APIs. opg-oracledb> cfg = GraphConfigBuilder.forPropertyGraphRdbms().setJdbcUrl("jdbc:oracle:thin:@127.0.0.1:1521:orcl") .setUsername("scott").setPassword("<your_password>")  .setName("my_graph") .setMaxNumConnections(2) .setLoadEdgeLabel(false) .addVertexProperty("name", PropertyType.STRING, "default_name")  .addEdgeProperty("cost", PropertyType.DOUBLE, "1000000")  .build(); ==>{"max_num_connections":2,"username":"scott","db_engine":"RDBMS","loading":{"load_edge_label":false},"error_handling":{},"edge_props":[{"name":"cost","default":"1000000","type":"double"}],"vertex_props":[{"name":"name","default":"default_name","type":"string"}],"name":"my_graph","jdbc_url":"jdbc:oracle:thin:@127.0.0.1:1521:orcl","attributes":{},"format":"pg","password":"<your_password>"}   Hey, the JSON based graph config is already there!  Cheers, Zhe

To use the property graph functions provided by Oracle Database 12.2.0.1 (+), it is important to get the graph configuration right. A typical graph configuration contains critical information like...

Graph Features

Property Graph Query Language (PGQL) support has been added to Oracle Database 12.2.0.1!

Great news! Oracle just released a patch that adds Property Graph Query Language (PGQL) support to the existing property graph functions in Oracle Spatial and Graph in Oracle Database 12c Release 2 (12.2.0.1.). At a high level, PGQL is a SQL-like declarative language that allows a user to express a graph query pattern that consists of vertices and edges, and constraints on the properties of the vertices and edges. Once this patch is applied to your database instance, you can run the following quick test, Groovy/Java based, to see how it works. cd $ORACLE_HOME/md/property_graph/dal/groovy $ sh ./gremlin-opg-rdbms.sh -------------------------------- opg-oracledb> // It is very likely that you need to customize JdbcURL, Username, and Password in the following graph config setting. opg-oracledb> // opg-oracledb> cfg = GraphConfigBuilder.forPropertyGraphRdbms().setJdbcUrl("jdbc:oracle:thin:@127.0.0.1:1521:orcl") .setUsername("scott").setPassword("tiger")  .setName("connections") .setMaxNumConnections(8) .setLoadEdgeLabel(false) .addVertexProperty("name", PropertyType.STRING, "default_name")  .addEdgeProperty("cost", PropertyType.DOUBLE, "1000000")  .build(); opg-oracledb> opg = OraclePropertyGraph.getInstance(cfg); opg-oracledb> opg.clearRepository();     // start from scratch opg-oracledb> opgdl=OraclePropertyGraphDataLoader.getInstance(); opg-oracledb> vfile="../../data/connections.opv"  // vertex flat file ==>../../data/connections.opv opg-oracledb> efile="../../data/connections.ope"  // edge flat file ==>../../data/connections.ope opg-oracledb> opgdl.loadData(opg, vfile, efile, 2, 10000, true, null); ==>null opg-oracledb> opg.countVertices() ==>78 opg-oracledb> opg.countEdges() ==>164 opg-oracledb> // Create an in memory analytics session and analyst opg-oracledb> session=Pgx.createSession("session_ID_1"); opg-oracledb> analyst=session.createAnalyst(); opg-oracledb> opg-oracledb> // Read graph data from database into memory opg-oracledb> pgxGraph = session.readGraphWithProperties(opg.getConfig()); ==>PgxGraph[name=connections,N=78,E=164,created=1488415009543] opg-oracledb> pgxResultSet = pgxGraph.queryPgql("SELECT n,m WHERE (n) -> (m)") ==>PgqlResultSetImpl[graph=connections,numResults=164] opg-oracledb> pgxResultSet.print(10); +------------------------------------+ | n                | m               | +------------------------------------+ | PgxVertex[ID=2]  | PgxVertex[ID=1] | | PgxVertex[ID=3]  | PgxVertex[ID=1] | | PgxVertex[ID=6]  | PgxVertex[ID=1] | | PgxVertex[ID=7]  | PgxVertex[ID=1] | | PgxVertex[ID=8]  | PgxVertex[ID=1] | | PgxVertex[ID=9]  | PgxVertex[ID=1] | | PgxVertex[ID=10] | PgxVertex[ID=1] | | PgxVertex[ID=11] | PgxVertex[ID=1] | | PgxVertex[ID=12] | PgxVertex[ID=1] | | PgxVertex[ID=19] | PgxVertex[ID=1] | +------------------------------------+ Cheers, [1] https://support.oracle.com/epmos/faces/PatchDetail?patchId=25640325  

Great news! Oracle just released a patch that adds Property Graph Query Language (PGQL) support to the existing property graph functions in Oracle Spatial and Graph in Oracle Database 12c Release 2...

Graph Features

Loading a Property Graph in Oracle Database using Groovy

Before you can start building text index or running graph analytics, one obvious thing you need to do is to get the graph data loaded into the database. The following shows an example flow of using the parallel property graph data loader to ingest a property graph, representing a small social network and encoded in flat file format (.opv/.ope), into an Oracle Database (12c Release 2).cd $ORACLE_HOME/md/property_graph/dal/groovy $ sh ./gremlin-opg-rdbms.sh -------------------------------- opg-oracledb> // It is very likely that you need to customize JdbcURL, Username, and Password in the following graph config setting. opg-oracledb> // opg-oracledb> cfg = GraphConfigBuilder.forPropertyGraphRdbms().setJdbcUrl("jdbc:oracle:thin:@127.0.0.1:1521:orcl") .setUsername("scott").setPassword("tiger")  .setName("connections") .setMaxNumConnections(8) .setLoadEdgeLabel(false) .addVertexProperty("name", PropertyType.STRING, "default_name")  .addEdgeProperty("cost", PropertyType.DOUBLE, "1000000")  .build(); opg-oracledb> opg = OraclePropertyGraph.getInstance(cfg); opg-oracledb> opg.clearRepository();     // start from scratch opg-oracledb> opgdl=OraclePropertyGraphDataLoader.getInstance(); opg-oracledb> vfile="../../data/connections.opv"  // vertex flat file opg-oracledb> efile="../../data/connections.ope"  // edge flat file opg-oracledb> opgdl.loadData(opg, vfile, efile, 2, 10000, true, null); opg-oracledb> opg.countVertices() ==>78 opg-oracledb> opg.countEdges() ==>164Quite simple, isn't it?  Note that to use a higher Degree of Parallelism (DOP), simply customize the 4th argument to the above loadData API call.Cheers,Zhe

Before you can start building text index or running graph analytics, one obvious thing you need to do is to get the graph data loaded into the database. The following shows an example flow of using...

Graph Features

Graph Database Says Hello from the Cloud (Part III)

In this installment, I am going to show you the steps required to create your first property graph on the Cloud. - Login to the newly created database service. You can use Putty or SSH or  a tool of your choice. - Create a tablespace as follows: [oracle@graph122 dbhome_1]$ sqlplus / as sysdba Oracle Database 12c EE Extreme Perf Release 12.2.0.1.0 - 64bitProduction SQL> alter session set container=PDB1; create bigfile tablespace pgts datafile '?/dbs/pgts.dat' size 512M reuse autoextend on next512M maxsize 10G EXTENT MANAGEMENT LOCAL segment space managementauto; - Enable 32K Varchar2 which isrequired by the Property Graph feature in Oracle Spatial and Graph option.Detailed description of the following steps can be found in: https://docs.oracle.com/database/121/REFRN/GUID-D424D23B-0933-425F-BC69-9C0E6724693C.htm#REFRN10321 SQL> conn/ as sysdba SQL>ALTER SESSION SET CONTAINER=CDB$ROOT;SQL>ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE; SQL>shutdown immediate; ORACLE instanceshut down. SQL>startup upgrade; ORACLEinstance started. … Databasemounted. SQL>ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE; Pluggabledatabase altered. EXIT; SQL>Disconnected from Oracle Database 12c EE Extreme Perf Release 12.2.0.1… [oracle@graph122dbhome_1]$ cd $ORACLE_HOME/rdbms/admin [oracle@graph122admin]$ mkdir /u01/utl32k_cdb_pdbs_output [oracle@graph122admin]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d$ORACLE_HOME/rdbms/admin -l '/u01/utl32k_cdb_pdbs_output' -butl32k_cdb_pdbs_output utl32k.sql … EnterPassword: catcon.pl:completed successfully [oracle@graph122admin]$ sqlplus / as sysdba SQL>shutdown immediate; SQL>startup SQL>ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE; Pluggabledatabase altered. SQL> quit Disconnectedfrom Oracle Database 12c EE Extreme Perf Release 12.2.0.1… [oracle@graph122admin]$ mkdir /u01/utlrp_cdb_pdbs_output [oracle@graph122admin]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS -d$ORACLE_HOME/rdbms/admin -l '/u01/utlrp_cdb_pdbs_output' -butlrp_cdb_pdbs_output utlrp.sql … EnterPassword: … catcon.pl:completed successfully [oracle@graph122admin]$ - Validate the change made toMAX_STRING_SIZE. To verify, run the following commands and you should see thevalue of max_string_size changed to "EXTENDED" [oracle@graph122 admin]$ sqlplus / as sysdba SQL> alter session set container=PDB1; Session altered. SQL> show parameters max_string; NAME TYPE VALUE ------------------------------------ ----------------------------------------- max_string_size string EXTENDED - Create a simple Property Graph andadd one vertex with a name "Property Graph", a second vertex with aname "Oracle Database Cloud Service", and an edge with label"livesIn" linking these two vertices. In addition, this edge has aweight=1.0. SQL> conn scott/<password> SQL> exec opg_apis.create_pg('mypg', 4, 8, 'PGTS'); PL/SQL procedure successfully completed. SQL> insert into mypgVT$(vid,k,t,v)values(1,'name',1,'Property Graph'); SQL> insert into mypgVT$(vid,k,t,v) values(2,'name',1,'OracleDatabase Cloud Service'); SQL> insert intomypgGE$(EID,SVID,DVID,EL,K,T,VN) values(100,1,2,'livesIn','weight',3,1.0); SQL> commit; Commit complete. Cheers, Zhe

In this installment, I am going to show you the steps required to create your first property graph on the Cloud. - Login to the newly created database service. You can use Putty or SSH or  a tool of...

Graph Features

Graph Database Says Hello from the Cloud (Part II)

In this installment, I am going to show the steps needed to create a brand new Oracle Database 12.2 on the cloud. - Sign in to Oracle Cloud with your credential - Click Create Service button (assume you don't alreadyhave an Oracle Database 12c Release 2 service up and running) -  Choose Oracle Database 12c Release 2. You will need toprovide a SSH Public Key which can be generated with this user interface (clickEdit button will reveal a few choices). In addition, type in the Service Nameand choose appropriate Subscription Type and Billing Frequency. - Select the compute shapes, backup/recoveryconfiguration and more in the following page. Note that it is highlyrecommended to choose AL32UTF8 character set. In general, more CPUs and alarger RAM setting lead to better database performance. If one need to dealwith a very large graph, then a high storage setting is necessary. - Confirm your selections before clicking the Createbutton. - Wait for the database service creation to finish. Notethat you should see an "In Progress" status. - After the service is created, you will see a"Created On" message on the screen. Click on the newly created database service. You will notice a public IP assigned for this newlycreated database service. That is it, enjoy your new database on the cloud! For screenshots, please refer to this pdf.

In this installment, I am going to show the steps needed to create a brand new Oracle Database 12.2 on the cloud. - Sign in to Oracle Cloud with your credential - Click Create Service button (assume...

Join Oracle Spatial + BIWA Summit 2017 for Innovations in Geospatial Databases, Big Data, Cloud and Analytics

JoinOracle Spatial + BIWA Summit 2017 for Innovations in Geospatial Databases, BigData, Cloud and AnalyticsJanuary31 – February 2, 2017OracleConference Center, Redwood Shores, CA TheOracle Spatial and Graph SIGuser group andOracle’s Business Intelligence, Warehousing, and Analytics (BIWA) SIG present theOracle Spatial + BIWA Summit: THEBig Data + Analytics + Spatial + Cloud + IoT + Everything Cool User Conference (http://www.biwasummit.org). We hope you can join us for the majorworldwide Spatial event of the year – combined with the leadingbusiness intelligence and analytics conference for Oracle users. Register here. Whetheryou’re just getting started with incorporating maps into business apps, or aGIS expert, Spatial + BIWA Summit has something for you. Sessions will explore advances for geospatialapplications, and the latest location and map visualization features in BI, bigdata, and cloud tools and platforms. You’ll get to join · Technical sessions straight from the Oracle developers · Keynotes by industry leaders and hands on labs · Real-world use cases from customers and partners –from city modeling and airport GIS to insurance risk and fraud detection · Networking opportunities with the SIG user group · BIWA Summit – full access to 3 days/4 tracks onbusiness intelligence, data warehousing, analytics, machine learning, IOT Theagenda includes: SpatialTechnologies for Database, Big Data, and Cloud · Maps, 3-D, Tracking, JSON,and Location Analysis: What’s New with Oracle’s Spatial Technologies – SivaRavada, Oracle · Bringing LocationIntelligence To Big Data Applications on Spark, Hadoop, and NoSQL Hands On Lab –Siva Ravada/Eve Kleiman, Oracle · Deploying SpatialApplications in Oracle Public Cloud – David Lapp, Oracle · RESTful Spatial serviceswith Oracle Database as a Service and ORDS – Jayant Sharma, Oracle Fordevelopers of BI and business applications · Getting Started with Mapsin OBIEE, BI Cloud Service and Data Visualization Desktop – Wayne Van Sluys,Interrel · Deploy Custom Maps in OBIEEfor Free Hands On Lab – Arthur Dayton, Vlamis Software · See what's new in OracleBI: A Dive into DV, Mobile, BICS products – Philippe Lions, Oracle GeospatialApplications – Industry Use Cases and Best Practices · Smart 3D Cities andSituational Awareness Applications with Oracle Exadata and JET - Frank Suykens,Luciad · Spatial Best Practices,Tips, and Tricks with Oracle Spatial and Graph – Daniel Geringer, Oracle · Capturing Reality withPoint Clouds: Applications, Challenges and Solutions – Rico Richter, HassoPlattner Institute · Cadastral Management atItaly’s National Land Agency – Riccardo Del Frate, SOGEI · Location-based RiskManagement with Oracle Spatial Technologies – Ali Ufuk Peker, Infotech · Airport GIS at Los Angelesand Munich Airports · A Routing Solution for ImprovingEmergency Services Planning - Marc Lazarovici, Insitute for Emergency Medicine,LMU Munich Graph,Big Data, and Advanced Analytics · Dynamic Traffic Predictionin Road Networks - Ugur Demiryurek, USC · Analysing the Panama Paperswith Oracle Big Data Spatial and Graph - Robin Moffatt, Rittman Mead · Visualizing Graph Data withGeographical Information – Kevin Madden, Tom Sawyer Learnmore about the Summit and register at http://www.biwasummit.org. Wehope to see you there! Joinour social networks LinkedinOracle Spatial and Graph Group Google+ Spatial and GraphGroup Oracle Spatial and Graph SIG(join IOUG for free) Questions? Contact the SIG at oraclespatialsig@gmail.com

Join Oracle Spatial + BIWA Summit 2017 for Innovations in Geospatial Databases, Big Data, Cloud and AnalyticsJanuary 31 – February 2, 2017 Oracle Conference Center, Redwood Shores, CA TheOracle Spatial...

Oracle Spatial and Graph for Oracle Database 12c Release 2 (12.2) on Oracle Cloud is available!

Oracle Spatial and Graph for OracleDatabase 12c Release 2 (12.2), the latest generation of the world’smost popular database, is now available in the Oracle Cloud! With the 12.2 release, OracleSpatial and Graph introduces powerful new analytics, and features to makespatial and graph operations faster, cloud-ready and more developer friendly. New capabilities include Apowerful new property graph analysisfeature to address Big Data use cases such as making recommendations, findingcommunities and influencers, pattern matching, and identifying fraud and otheranomalies Nativesupport for GeoJSON AnHTML5 map visualization component Spatial index and partitioningimprovements toboost performance Location data enrichment services for unstructured textdocuments Location tracking service Enhancements for GeoRaster, NetworkData Model, and Spatial Web Services RDF Semantic Graph enhancements - SPARQL 1.1 update operations forpattern-matching queries, and integration with property graphs With 12.2, we continue to deliverthe most advanced spatial and graph database platform for applications from geospatialand location services to Internet of Things, social media, and big data. And it runs on Oracle DatabaseCloud – a great option to quickly get your environment up and running, whetheryou have a sandbox prototype, small departmental application, or large, missioncritical applications. Learn More Oracle Spatial and Graph /12.2 features on Oracle Cloud Oracle Spatial and Graph Data Sheet Spatialand Graph Analytics with Oracle Database 12c Release 2 Technical White Paper OracleDatabase 12c Release 2 Documentation OTNSpatial and Graph product website Oracle Database 12c Release 2 Oracle Openworld 2016 Keynote 'Transforming Data Management with Oracle Database 12c Release 2' White Paper: Transforming Data Management 12.2 on Oracle Cloud New Features Guide Oracle Database 12c Release 2 on OTN Oracle Cloud Database Services [#blog]Oracle Database 12c Release 2 on OracleCloud Now Available Join the conversation:#DB12c #FutureofDB #OracleSpatial

Oracle Spatial and Graph for Oracle Database 12c Release 2 (12.2), the latest generation of the world’s most popular database, is now available in the Oracle Cloud! With the 12.2 release, OracleSpatia...

Benefits of the 12c SDO_POINTINPOLYGON Function

Guest Post By: Nick Salem,Distinguished Engineer, Neustar and Technical Chair, OracleSpatial SIG The mdsys.SDO_POINTINPOLYGONfunction API is a new feature that was released in Oracle Database 12c. There is a nice blog post that explains how this feature can beused to address the challenges of ingesting large amounts of spatial data whereyou can handle the loading and querying of large spatial data sets without theoverhead associated with creating and maintaining a spatial index. The example shows how SDO_POINTINTPOLYGONcan really benefit massive scale operations, such as those using Exadataenvironments. In this post, I would like tocover some other benefits that the SDO_POINTINPOLYGON feature provides that canbe very helpful – especially for applications servicing a large number ofconcurrent spatial operations. This cangreatly improve performance for such applications that run on either Exadata ornon-Exadata environments. The fact that the SDO_POINTINPOLYGON does notuse a spatial index means that you can leverage data stored in an externaltable or a global temporary table to perform spatial point-in-polygonqueries. Global temporary tables aregreat for multi-session environments because every user session has their ownversion of the data for the same global temporary table, without any contentionor row locking conflicts between sessions. Furthermore in 12c, Oracle introduced some major performanceoptimizations to global temporary tables that result in substantially lowerredo and undo generation. You will needto make sure system parameter temp_undo_enabled is set to TRUE to ensure thatthe 12c global temporary tables optimization is fully in effect. Below is a screenshot fromNeustar’s ElementOne platform with a map showing a trade area and a set ofuploaded customer points. At Neustar, our clients workwith a lot of transient work data as part of a multi-step process for variousspatial and analytical use cases. Let’sput together a quick PL/SQL script that you can use to test drive the power ofthe SDO_POINTINPOLYGON function. Here, Iuse a simple polygon in the San Diego area and generate a set of randomcustomer points in and around the polygon. Then, I populate the global temporary table. The script is configurable: you can increaseor decrease the number of randomly generated customer points, and how far fromthe polygon centroid you may want to allow points to extend to. Once you have the data populated, you can runthe SDO_POINTINPOLYGON queries in serial or in parallel, or change some of theoptional MASK parameters. Here’s a screenshot of thetest polygon and a sample of randomly generated 1,000 customer points. 1. Create a globaltemporary table Ok, so let’s first create aglobal temporary table => create global temporary tableTMP_SPATIAL_POINT (x number,y number,id varchar2(512) )on commit preserve rows; 2. Generate a setof random points and populate the global temporary table Next, let’s run the followingscript to populate table TMP_SPATIAL_POINT. The script has two variables: maxDistanceInMeters and numberOfPoints inthe PL/SQL declaration section that you can adjust as needed. If you want to generate more points, then youcan change the value of numberOfPoints from 1000 to a greater number. In this example, I also have maxDistanceInMetersset to 4000. This will ensure that nocustomer points get generated further than 4000 meters away from the polygoncentroid; this can be increased or decreased as needed. The script goes through a loop up to thenumberOfPoints variable and uses the SDO_UTIL.POINT_AT_BEARING function to plotpoints around the centroid of the polygon using randomly generated values. The goal of the script is to quickly createsome test data you can play with. Ofcourse, you can also change the test polygon as well. declare polygon sdo_geometry; centroid sdo_geometry; newPoint sdo_geometry; maxDistanceInMeters number := 4000; numberOfPoints number := 10000; type tRecs is table oftmp_spatial_point%rowtype; recs tRecs := tRecs(); begin polygon := SDO_GEOMETRY(2003, 8307, NULL,SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(-117.1044,32.680882,-117.08895,32.661808,-117.06148,32.675102, -117.06045,32.697641, -117.09753,32.696774,-117.1044,32.680882)); centroid := SDO_GEOM.SDO_CENTROID( polygon, 0.05 ); recs.extend(numberOfPoints); for i in 1 .. numberOfPoints loop newPoint :=SDO_UTIL.POINT_AT_BEARING( start_point => centroid, bearing => dbms_random.value(0,6.283), distance => dbms_random.value(1,maxDistanceInMeters)); recs(i).id:= i; recs(i).x:= newPoint.sdo_point.x; recs(i).y:= newPoint.sdo_point.y; end loop; execute immediate ‘truncate tabletmp_spatial_point’; forall i in recs.first .. recs.last insert intotmp_spatial_point values ( recs(i).x, recs(i).y, recs(i).id ) ; commit; end; 3. RunSDO_POINTINPOLYGON queries (in serial or parallel) Ok, now we can startperforming queries using the SDO_POINTINPOLYGON function. Here’s a sample query that returns the countsof points that fall inside the polygon. The params parameter is optional; if omitted, a MASK=ANYINTERACT querywill be performed. set timing on select count(*) from table( SDO_PointInPolygon( cur => cursor(select * from tmp_spatial_point), geom_obj =>SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(-117.1044,32.680882, -117.08895,32.661808,-117.06148,32.675102, -117.06045,32.697641,-117.09753,32.696774, -117.1044,32.680882)), tol => 0.05, Params =>'MASK=INSIDE' ) ) t ; Here’s a another example of the query using parallelism andwith the params parameter omitted.  select /*+ parallel(8) */ count(*) from table( SDO_PointInPolygon( cur => cursor(select * from tmp_spatial_point), geom_obj =>SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(-117.1044,32.680882, -117.08895,32.661808,-117.06148,32.675102, -117.06045,32.697641,-117.09753,32.696774, -117.1044,32.680882)), tol => 0.05 ) ) t ; The SDO_POINTINPOLYGON function has been built to leverageOracle’s parallel processing capability.  To demonstrate the magnitude ofperformance gain when utilizing parallelism, I modified the point generationscript in part 2 to populate a million points with a max distance of 5,000meters from the center point.  I then tested the SDO_POINTINPOLYGON querywith no parallelism, and then with parallelism of 2, 4 and 8.  Here are theelapsed response times: Level of parallelism Elapsed time None 13.28 secs 2 9.62 secs 4 6.03 secs 8 3.43 secs  Utilizing parallelism can greatly shorten query processingtimes.  You can use these scripts in your environment to generatedifferent numbers of points, test various levels of parallelism, and comparethe response times. 

Guest Post By: Nick Salem, Distinguished Engineer, Neustar and Technical Chair, Oracle Spatial SIG The mdsys.SDO_POINTINPOLYGONfunction API is a new feature that was released in Oracle Database 12c....

Spatial & Graph Summit Presentations From BIWA Summit ’16 Now Available

Over 24presentations on spatial, map visualization, and graph technologies fordatabase, big data, and cloud platforms were delivered at BIWA Summit '16 – theOracle Big Data + Analytics + Spatial + YesSQL Community 3-Day User Conference,Jan. 26-28 at Oracle HQ. Slides andmaterials from technical sessions, handson labs, and customer and partneruse cases are now available. OTNSpatial and Graph Summit at BIWA ’16 Page:http://www.oracle.com/technetwork/database/options/spatialandgraph/learnmore/oracle-spatial-summit-at-biwa-2016-2881713.html Topics included bestpractices, point clouds/city models, rasters, mapping, big data technologies. New customer use cases came from government, telco, transportation, and energy,featuring Oracle Spatial and Graph/DB 12c deployments.  See below for the session list. For some usertips to get started on 2 new hands onlabs for Big Data Spatial and Graph, view TracyMcLane’s blog post here. And for more greatpresentations from BIWA Summit 16 – from tracks such as Advanced Analytics, BI,Data Warehousing, Big Data, and YesSQL, visit the BIWA Summit page. Many thanks to the BIWA Committee for makingthe event a success! Featured BIWA Session FS | What’s New with Spatial and Graph?Technologies to Better Understand Complex Relationships JamesSteiner, Oracle With theemergence of IoT, Cloud services, mobile tracking, social media, and real timesystems, we're finding new ways to incorporate social and location informationinto business workflows.  Learn how Oracle's spatial and graphtechnologies address these developments.  New offerings for the Cloud, andNoSQL and Hadoop platform are discussed.  A BIWA '16 featured talk. Oracle Technical Sessions TS | High Performance Raster Database Manipulation and Data Processing withOracle Spatial and Graph Qingyun(Jeffrey) Xie, Oracle Learn aboutthe core features of GeoRaster, the typical workflow for building large scaleraster databases, capabilities for in-database raster processing and analysis,and the architecture for building powerful raster data applications. TS | 3D Data Management - From Point Cloud to City Model HansViehmann, Oracle 3Dcity models generated from laser scanning or image matching data are findingmany applications today. Learn about thelatest 3D data support in Oracle Spatial and Graph and how it is applied tocity modeling – including how it handles large volumes of data sets such aspoint clouds, TINs, and 3D solids. TS | Best Practices, Tips and Tricks with Oracle Spatial and Graph DanielGeringer, Oracle Learntechniques to optimize performance with Oracle Spatial and Graph, such as parallelquery best practices, vector performance acceleration in 12c, and otheroptimizations deployed by real world customers. Techniques covered can apply to deployments on commodity hardware,Oracle engineered systems, or Oracle Database Cloud Service. TS | Big Data Spatial: Location Intelligence, Geo-enrichment and SpatialAnalytics Siva Ravada,Oracle Locationanalysis and map visualization are powerful tools to apply to data sources likesocial media feeds and sensor data, to uncover relationships and valuableinsights from big data. Learn more aboutOracle Big Data Spatial and Graph, which offers a set of analytic services anddata models that support Big Data workloads on Apache Hadoop and NoSQL databasetechnologies. TS | Map Visualization in Analytic Apps in the Cloud, On-Premise, andMobile LJ Qian,Oracle Learn about theinteractive map visualization capabilities of Oracle analytic and informationdiscovery applications. TS | The Power of Geospatial Visualization for Linear Assets Using OracleEnterprise Asset Management SudharsanKrishnamurthy, Oracle OracleEnterprise Asset Management provides features to manage pipelines, utilitytransmission and distribution lines, roads, and facilities. Learn how to leverage its geospatialcapabilities from Oracle Spatial and Graph, together with tools like Esri andGoogle Maps. TS | Massively Parallel Calculation of Catchment Areas in Retail Albert Godfrind, Oracle For site planning, targeted marketing, and other analyticalprocesses, analyzing catchment areas for retail points-of-sale is very useful. Learn about a highly scalable approach toassociate 1 million customers with their closest retail outlets by drivetime inthe US, using Oracle Spatial and Graph Network Data Model and Geocoder features. TS | Build Your Own Maps with the Big Data Discovery Custom VisualizationComponent Chris Hughes,Oracle Thispresentation provides a working example of a custom map visualization inside ofOracle Big Data Discovery – for deeper spatial context and analysis for usersto find, explore, transform, and analyze data in Hadoop. A cookbook and code are provided. TS | High Speed Video Processing for BigData Applications MelliyalAnnamalai, Oracle Applicationscentered on video such as surveillance, security, drone video capture, and trafficmanagement require fast, automated video processing and analysis.  Hadoop’s support for massive parallelism is uniquely suited to perform highspeed video processing.  We present an extensible framework in Hadoop thatcan be customized for many video analysis applications, for easy inclusion inanalytics tools and BI dashboards.  TS | Build Your Own Maps with the Big Data Discovery Custom Visualization Component Chris Hughes,Oracle Thispresentation provides a working example of a custom map visualization inside ofOracle Big Data Discovery – for deeper spatial context and analysis for usersto find, explore, transform, and analyze data in Hadoop. A cookbook and code are provided. Customer and Partner UseCase Sessions Large-ScaleSpatial Analytics and Cloud Deployments UC | Best Practices for Developing Geospatial Apps for the Cloud Nick Salem,Neustar Learn bestpractices and techniques for building robust, scalable, cloud-basedapplications using Oracle Database 12c Spatial and Graph, Partitioning, RAC,Advanced Security, and Weblogic 12c. Neustar organized a 2.5TB spatial databasefor maximum performance, deploying Oracle security, high availability, and richgeospatial analytics. Hear tips fromtheir experiences. UC | Implementation of LBS Services with Oracle Spatial and Graph andMapViewer in Zain Jordan Ali UfukPeker and Kerem Par, Infotech Zain is oneof the largest operators in Middle East, active in 7 countries with more than44 million subscribers. Learn how Infotech’splatform provides Zain with services such as location based advertisement,demographics, asset tracking and  fleet management, fully implemented withOracle Spatial and Graph and MapViewer. UC | Fast, High Volume, Dynamic Vehicle Routing Framework for E-Commerceand Fleet Management Ugur Demiryurek, University of Southern California Fleet management and scheduling are critical to e-commerce,utilities/telco, and military operations. Learn about an efficient, accurate, time-dependent solution for highvolume vehicle routing on real world road networks, using Oracle Spatial andGraph Network Data Model. LocationIntelligence UC | Electoral Fraud Location in Brazilian General Elections 2014 Alex Cordonand Henrique Da Silva, CDS A solution usingOracle MapViewer 12c and Oracle Business Intelligence Enterprise Editionallowed the Brazilian Electoral Justice to identify the location andgeographical distribution of potential fraud and failures in the 2014 Brazilianelections. Data came from 24 millionvoters using Brazil’s biometric voting system, with polling stations andgeographic locations associated with each voter. GIS forEngineering, Telco, and Energy UC | Oracle Spatial 12c as an Applied Science for Solving Today'sReal-World Engineering Problems Tracy McLane,Bechtel Corporation Bechtel'slarge global engineering and construction projects require a vast amount ofvaried spatial data for real-world problem solving – including earthquakecatalogs for seismic analysis, hydraulic data, infrastructure survey data, andindoor tracking locations. This presentation takes an in-depth look at OracleSpatial technologies used in the world of engineering. UC | Managing National Broadband Infrastructure at Turk Telekom with OracleSpatial and Graph MuratAltiparmak and Murat Hancerogtu, Turk Telekom Turk Telekomis Turkey's major fixed line and broadband operator, with 18 million+customers. Learn how they identify investment locations, infrastructureimprovement locations and available services for specific locations by thepower of its Oracle Spatial and Graph based GIS system. UC | ATLAS - Utilizing Oracle Spatial and Graph with Esri for Pipeline GISand Linear Asset Management DavidEllerbeck, Global Information Systems Existingtransmission pipeline models and data management systems pose challenges interms of maintenance, data quality, and redundancy. An approach thatcentralizes spatial data management in the database with Oracle Spatial andGraph addresses those issues. Real-world customer examples from the oil and gasindustry are shared. NationalGovernment and Land Management UC | Using Open Data Models to Rapidly Develop and Prototype a 3D NationalSDI in Bahrain DebbieWilson, Ordnance Survey Learn about aninnovative, large-scale land registration and spatial data management system prototypefor Smart Cities. It supports a national3D data model, and manages diverse data sets including topography, cadastre,urban planning, transport, utilities and industrial facilities in a singlesystem. UC | Delivering Smarter Spatial Data Management within Ordnance Survey, UK DebbieWilson, Ordnance Survey Learn how aleading national mapping agency has deployed an integrated geospatial datamanagement system based on Oracle technologies, to increase automation, reducecosts, and deliver higher value content and services. UC | Assembling a Large Scale Map for the Netherlands Using Oracle 12cSpatial and Graph RichardHuesken, Transfer Solutions TheNetherlands is combining diverse large scale map data from numerous governmentagencies into a single, topologically correct dataset for sharing andreuse. The BRAVOsystem uses Oracle Spatial and Graph 12c features for spatial data operationsfor validation, cleansing, and high performance. SpatialData ETL and the Cloud UC | Centralizing Spatial Data Management with Oracle Cloud Databases SteveMacCabe, Safe Software Clouddatabases provide a flexible and cost-effective way for organizations to managelarge volumes of data. Learn how totransform data in the cloud to take advantage of Oracle cloud data architectureusing Safe Software’s FME, the leading platform for spatial extract, transform,and load operations. GraphTechnologies UC | Graph Databases: A Social Network Analysis Use Case Xavier Lopez,Oracle, and Mark Rittman, Rittman Mead In this newpresentation, Mark Rittman demonstrates the use of Oracle Big Data Spatial andGraph to perform “sentiment analysis” and “influencer detection” across a realworld social network – Twitter feeds from the Rittman-Mead blog readercommunity.  Xavier Lopez also introduces graph databases and how theydrive social network analysis, IoT, and linked data applications.  UC | Dismantling Criminal Networks with Graph and Spatial Visualization andAnalysis Kevin Madden,Tom Sawyer Learn about ahighly visual situational awareness solution that combines linked data,temporal, and spatial analysis. Usingcrime incident data for a major city over a two year period, we use these techniquesto identify top criminal offenders and predict thelocation and time of future criminal activity. The application is created with Tom Sawyer Perspectives, and uses OracleSpatial and Graph for data storage and management. UC | Deploying a Linked Data Service at the Italian National Institute ofStatistics Monica Scannapieco and IstatGiovanni Corcione, Oracle Italy’s National Institute ofStatistics has developed a web portal to publish official census data foradministrative areas from the municipal to the national level. Learn how they have adopted innovative LinkedData techniques to harmonize data and provide advanced query and informationdiscovery services for policy makers and the public. UC | Hybrid Cloud Using Oracle DBaaS: How the Italian Workers CompensationAuthority Uses Graph Technology PatrizioGalasso, INAIL Italy, and Giovanni Corcionne, Oracle Organizationsare considering more nimble and lower cost cloud services to enhance workers’collaboration, productivity, and business insight. The Italian Government Workers CompensationAuthority chose the Oracle Cloud Platform, a public platform as a service(PaaS), to manage insurance coverage for workplace accidents and incidents inten Italian regions.  The platform incorporates W3C RDF linked open datastandards, using Oracle Database Cloud with Oracle Spatial and Graph option. Hands On Labs and DemoSessions HandsOn Lab materials on OTN HOL | Interactive Map Visualization ofLarge Datasets in Analytic Applications LJ Qian andDavid Lapp, Oracle This sessionpresents an approach to interactive map visualization using an HTML5 basedJavascript API, and includes a demonstration of powerful new features in themap visualization components of the Oracle Spatial and Graph product stack. HOL | Applying Spatial Analysis To BigData (Hands On Lab) Siva Ravadaand Eve Kleiman, Oracle In this HandsOn Lab, learn how to perform spatial categorization and filtering, and createmap visualizations on a Twitter dataset, and to load, prepare, and processlarge raster imagery datasets.  HOL | Gain Insight into Your Graph Data –A Hands-On Lab for Oracle Big Data Spatial and Graph (Hands On Lab) Zhe Wu,Oracle A set of property graph hands on lab exercisesand demos are available as part of the Big Data Lite VM.  Using areal-world social graph, these show how to manipulate graph data in NoSQL DB orHBase, and perform analytics like community detection and connectivity. 

Over 24 presentations on spatial, map visualization, and graph technologies for database, big data, and cloud platforms were delivered at BIWA Summit '16 – theOracle Big Data + Analytics + Spatial +...

Tips for Switching Between Geodetic and Mercator Projections

Guest Post By: Nick Salem, Distinguished Engineer, Neustar and Technical Chair of the Oracle Spatial SIG Note:  Thanks to Nick Salem for this valuable tip on handling multiple coordinate systems to optimize performance and storage! Oracle Spatial and Graph provides a feature rich coordinate system transformation and management capability for working with different map projections.  This includes utilities that convert spatial data from one coordinate system to another, from 2D to 3D projections, create EPSG rules, deal with various input and output formats and more.If you deal with geodetic data, you may have run into the need to display your data points and areas onto aerial or terrain maps.  For this, you could utilize the SDO_CS.TRANSFORM function to dynamically convert your geometries to the destination projection system.  The challenge we had at Neustar was that our customers wanted the option to switch frequently back and forth between our MapViewer geodetic base maps and aerial and terrain base maps with the Mercator projection.  They wanted to do this in a seamless and responsive manner.  And some of our customer datasets are fairly large.  The Neustar ElementOne system holds billions of rows of geospatial data.  We wanted to provide our customers with the capability to switch projections for any of their geometries, but we also wanted our system to scale and maintain quick responsiveness.  Coordinate transformation operations can be expensive, especially if they are performed on large volumes of geometries.  Initially, we tried to dynamically perform coordinate transformations on the fly for customer requests, but this did not give us the best performance, and resulted in some of the same geometries going through the same repetitive transformation over again and again.The solution for us was to maintain and manage two coordinate systems for all of our customer geometries.  For every spatial data record, we have two SDO_GEOMETRY columns, one to store the latitude/longitude geodetic data and other to store the Mercator projection data.  We use the geodetic geometries for queries and all spatial operations, and we use the Mercator projection solely for map visualizations.  The advantage of this approach is that every geometry goes through only one coordinate transformation during the data loading or updating process.  And for query visualizations, performance is optimal, since the data is already available for display.  This results in the best customer experience and snappy response times.  Another advantage of visualizing geodetic data using the Mercator projection is that radii appear circular instead of oval looking.Here’s a picture from Neustar’s ElementOne platform showing a 3 mile radius trade area. One obvious disadvantage of this approach is that it requires more storage as you store and manage two sets of geometry columns.  If you take a closer look at the geometries created by the coordinate transformations, the resulting geometry may include a greater amount of precision than your application actually needs.  A good rule of thumb is to only include the least amount of precision required to support your needs.  Let’s take a quick look at an example of converting a geodetic (8307) latitude/longitude point geometry to the Mercator (3785) projection.SQL> select  to_char(value(t).x) x,  to_char(value(t).y) yfrom   table(sdo_util.GetVertices(sdo_cs.transform(         sdo_geometry(2001,8307,sdo_point_type(-117.019493,32.765053,null),null,null)         ,0.5,3785))) t;X                                                        Y----------------------------------------         -----------------------------------------13026550.373647                             3864160.0406267The 8307 geodetic projection utilizes the unit of degrees for the latitude/longitude coordinates, while the 3785 Mercator projection uses meters as the measure.  From the example above, you can see up to 7 decimal places for the coordinates – which was far greater than what we need for our mapping analysis and visualization needs.  You may wonder why we should bother about the numeric precision of spatial geometries.  The answer is performance and storage savings.  The larger the precision, the more storage it will take.  The more storage for your geometries, the more Oracle blocks needed to store your data.  The more data blocks that the database has to fetch to satisfy a query, the longer the query will take.  To illustrate the amount of additional space that transformed geometries can take compared to the original geometries, I created 4 tables each consisting of 30,532 ZIP Code geometries. Next I ran a query joining USER_SEGMENTS and USER_LOBS to get the total space consumption of the SDO_ORDINATES for each of the 4 tables.   For polygon geometries, Oracle will likely store the geometry outside the table in LOB segments.SELECT   l.table_name, l.COLUMN_NAME, t.BYTES/(1024*1024) m_bytesFROM    user_segments t,     user_lobs lWHERE      t.segment_name = l.segment_name and      l.column_name like '%GEOM%SDO_ORDINATES%'; TABLE_NAME                                   COLUMN_NAME                            M_BYTES------------------------------                     ------------------------------                     ----------ZIP_CODE_SRID8307                       "GEOM"."SDO_ORDINATES"        120.1875ZIP_CODE_SRID3785                       "GEOM"."SDO_ORDINATES"        216.1875ZIP_CODE_SRID3785_ROUND0     "GEOM"."SDO_ORDINATES"        120.1875ZIP_CODE_SRID3785_ROUND1     "GEOM"."SDO_ORDINATES"        136.1875The original ZIP Code SDO_ORDINATES consumed 120M.  But when we converted the same ZIP geometries to the Mercator projection, we ended up with 216M - that is an 80% increase in size.  Then, when we truncated the decimals for the Mercator projected coordinates in table ZIP_CODE_SRID3785_ROUND0 –  this brought the size back to 120M, but we ended with 41 invalid ZIP boundaries.  Rounding to 1 decimal place resulted in 136M of size and all valid geometries.  The goal is to round the coordinates to the least decimal places needed for your application.  In our case, we used the Mercator projection geometries only for visualization – so we were not very concerned about how valid the geometries were, and opted for truncating the decimal places, which worked out great for us.  In your case, you can play around with what precision works out best for you.Here’s nice helper function that can be used to perform the coordinate transformation and then apply the required rounding all in one step.create or replace function transformToSRID (                           pGeometry    in sdo_geometry,                                                      pTolerance   in number,                           pToSRID      IN number,                           pRoundPos    in integer )return sdo_geometryisoutGeometry  sdo_geometry;beginoutGeometry := sdo_cs.transform( geom => pGeometry,                                 tolerance => pTolerance,                                 to_srid => pToSrid ) ;if outGeometry.sdo_point is not null then  outGeometry.sdo_point.x := round( outGeometry.sdo_point.x, pRoundPos );  outGeometry.sdo_point.y := round( outGeometry.sdo_point.y, pRoundPos );end if;if outGeometry.sdo_ordinates is not null then  for i in outGeometry.sdo_ordinates.first .. outGeometry.sdo_ordinates.last loop    outGeometry.sdo_ordinates(i) := round(outGeometry.sdo_ordinates(i),pRoundPos);  end loop;end if;return outGeometry;end;Quick usage example => SQL> select  to_char(value(t).x) x,  to_char(value(t).y) yfrom  table(sdo_util.GetVertices(transformToSRID( sdo_geometry(2001,8307,sdo_point_type(-117.019493,32.765053,null),null,null),0.5,3785,1)))X                                                        Y----------------------------------------         -----------------------------------------13026550.4                                       3864160Here are a picture from Neustar’s ElementOne platform overlaying a site trade area over a terrain map. Here’s another picture from Neustar’s ElementOne showing 10 and 15 minute drive time trade areas over an aerial map. In conclusion, the amount of precision for geometry coordinates matters for performance and storage.  If you perform a lot of repetitive coordinate transformation to support your application needs, you may want to consider storing the projected geometries.  By default, the SDO_CS.TRANSFORM function may create geometries with coordinates containing more precision than required for your needs.  You should always check the amount of precision of your geometries and round to the minimum number of decimal places needed to support your application requirements.

Guest Post By: Nick Salem, Distinguished Engineer, Neustar and Technical Chair of the Oracle Spatial SIG Note:  Thanks to Nick Salem for this valuable tip on handling multiple coordinate systems to...

Using SDO_UTIL Functions to Construct Geodetic Shapes

Guest Post By: Nick Salem, Distinguished Engineer, NeustarNote: Thanks to Nick Salem, Technical Chair of the Oracle Spatial SIG , for contributing another valuable tip and script example!  This is very useful for those who want to learn how to create grids for trade area/demographic analysis. Oracle Spatial & Graph provides a feature rich toolkit forbuilding and manipulating various types of geometries. There are a lot of great helper functionsthat you can utilize to construct a specific shape type. At Neustar, we leverage these functions tocreate square and hexagonal grids that we can overlay over a site’s trade areaor some market. These grids can be thenscored to show the variation of data across an area. Below is a picture from Neustar’s ElementOne platformdisplaying a 0.5 mile grid layer over a 3 mile radius of a prospect banksite. The grids are scored by the numberof households that have a bank savings account from high to low. Square grids are simple polygons that areeasy to create in a planar surface since they have edges of equal length. However in a geodetic projection, you cannotsimple use simple geometric calculations to construct these shapes or at leastnot as accurately as you may want especially if you are covering a larger area.Oracle provides the SDO_UTIL.CONVERT_UNIT and the SDO_UTIL.POINT_AT_BEARINGfunctions that can be helpful in creating shapes on geodetic surfaces. You can use the CONVERT_UNIT function toconvert units from degrees to radians and the POINT_AT_BEARING to place individualcoordinates of a geometry. Here’s a picture of a 1 mile hexagonal grid tessellation forthe number of households with a home equity loan for the county of Anne Arundelin Maryland. Hexagonal grids have oneadvantage over square grids in that they are more circular in shape and so the distancefrom the center to the sides and edges is even. Next I am going to show a simple PL/SQL script to create asimple trapezoid shape using the SDO_UTIL.CONVERT_UNIT andSDO_UNIT.POINT_AT_BEARING functions. Inthis example, I begin with a starting point located in San Diego county andthen go in a counter clockwise order to plot the subsequent coordinates thatwill make up the trapezoid polygon. Theangle passed into the SDO_UTIL.POINT_AT_BEARING function is measured clockwisefrom due north (i.e. bearing 0 is north,bearing 90 is east, bearing 180 is south, bearing 270 is west). I am creating a trapezoid polygon here as anexample since a trapezoid can represent the top or bottom part of a hexagon. Simple PL/SQL scriptexample: declarestartPoint sdo_geometry;nextPoint sdo_geometry;coords sdo_ordinate_array:=sdo_ordinate_array();shape sdo_geometry; begin -- first starting point startPoint :=sdo_geometry(2001,4326,                                 sdo_point_type(-117.0655,32.7475,null),null,null);coords.extend(2);coords(coords.count-1) :=startPoint.sdo_point.x;coords(coords.count) :=startPoint.sdo_point.y; -- place second coordinate 5,000meters away using 330 (northwest) degree angle nextPoint :=sdo_util.point_at_bearing( start_point => startPoint,                                         bearing=> sdo_util.convert_unit(330,'degree','radian'),                                         distance => 5000 );coords.extend(2);coords(coords.count-1) := nextPoint.sdo_point.x;coords(coords.count) :=nextPoint.sdo_point.y; -- place third coordinate 3,000meters away using 270 (west) degrees angle startPoint := nextPoint;nextPoint :=sdo_util.point_at_bearing( start_point => startPoint,                                         bearing=> sdo_util.convert_unit(270,'degree','radian'),                                         distance => 3000 );coords.extend(2);coords(coords.count-1) := nextPoint.sdo_point.x;coords(coords.count) :=nextPoint.sdo_point.y; -- place fourth coordinate 5,000meters away using 210 (southwest) degrees angle startPoint := nextPoint;nextPoint :=sdo_util.point_at_bearing( start_point => startPoint,                                         bearing =>sdo_util.convert_unit(210,'degree','radian'),                                         distance => 5000 );coords.extend(2);coords(coords.count-1) := nextPoint.sdo_point.x;coords(coords.count) :=nextPoint.sdo_point.y; -- add starting coordinate as 5thcoordinate to close the polygon coords.extend(2);coords(coords.count-1) := coords(1);coords(coords.count) := coords(2); -- construct shape geometry shape :=sdo_geometry( 2003,4326,null,                           sdo_elem_info_array(1,1003,1), coords ) ;end; Here’s the result of the shape geometry from script abovedisplayed on a map. In conclusion, Oracle Spatial & Graph provides us with somenice helper functions that we can use to construct shapes on a geodeticsurface. In this post, we demonstrated how one can use theSDO_UTIL.POINT_AT_BEARING and SDO_UTIL.CONVERT_UNIT to plot coordinate pointsfor a geometric polygon.

Guest Post By: Nick Salem, Distinguished Engineer, Neustar Note: Thanks to Nick Salem, Technical Chair of the Oracle Spatial SIG , for contributing another valuable tip and script example!  This is...

The Importance of Organizing Spatial Data By Proximity

Guest Post By: Nick Salem, Distinguished Engineer, Neustar Note: Thanks to NickSalem, Technical Chair of the Oracle Spatial SIG , for contributing this valuable tuning and performance tip that will beuseful to most Oracle Spatial users! The goal of this post is to shed some light on a technique thatI feel is many times overlooked by users working to tune large spatial datasets:to organize the rows in a table by their spatial proximity. Dan Geringer alluded to this in the post “Tipson tuning SDO_NN (nearest neighbor) queries” . What I want to highlight is thatthis technique is not only beneficial for SDO_NN queries, but also for basicallyall queries that use any of the spatial query operators like SDO_FILTER,SDO_RELATE, SDO_WITHIN_DISTANCE, as well as the SDO_RELATE helper operatorssuch as SDO_ANYINTERACT, SDO_INSIDE and so on. The SDO_JOIN operator by itself may not benefit from this approachbecause it relies solely on the spatial index, but if you decide to join theresultset of the SDO_JOIN operation back to the input tables of the joinoperation, then you will most likely also benefit from having the data storedby proximity. To understand the dynamics of this, one has to understand ingeneral how Oracle queries work. When auser issues a spatial query, a spatial index is used to find the rowids for therows that need to be returned. Oracleuses these rowids to retrieve the database blocks from disk into the buffercache where it can process the data. Each database block can contain one or more rows of data. In order for Oracle to retrieve data for aquery, it needs to retrieve all the blocks needed to satisfy a query. The more blocks that need to be scanned, themore I/O operations are performed and the longer the query will take. For example, a spatial query resulting in thescan of one or two adjacent blocks will return a lot faster than the samespatial query needing to scan a large number of disparate blocks to process thesame results. Of course, this issue isnot confined to just spatial queries. Infact, most Oracle DBAs are aware of this with the concept of the indexclustering factor which describes the relationship between the order of anindex and its corresponding table. Fornon-spatial data, achieving an optimal index clustering factor can be as easyas ordering the data in a table by a scalar data type column that you plan toindex and then creating that index. Forspatial data, this can be a little trickier because one cannot just simplyorder by the SDO_GEOMETRY column. InDan’s post “Tips on tuning SDO_NN (nearest neighbor) queries”, he shows an example using the MD.HHENCODE_BYLEVEL function to return avalue that you can use to sort your spatial data by. In this post, I will show an example of a use case I testedthat highlights the impact on performance when ordering spatial data byproximity. The example will includetaking the US Business listing table and creating two copies: one that is not ordered by proximity, and theother ordered by proximity using the MD.HHENCODE_BYLEVEL function. Then, I will test running a simpleSDO_WITHIN_DISTANCE query on both tables to retrieve the sum of employees foran arbitrary 5 mile radius and compare the results. Example Pre-requisites In this example, I am starting with a 16 million row tablecontaining all the US business locations with approx 30 columns calledBUSINESS1. This table is not ordered byspatial proximity. BUSINESS1 also hasa spatial index created on column GEOM. Step 1) Next I willcreate table BUSINESS2, which will be an exact copy of BUSINESS1, but will beordered by proximity using the MD.HHENCODE_BY_LEVEL function as described inDan’s post. CREATE TABLE business2 PCTFREE 0 NOLOGGING PARALLELasSELECT /*+ parallel(8) */  b.*FROM  business1 bORDER BY   row_number() over (order by md.hhencode_bylevel(      b.geom.sdo_point.x,-180,180,27,     b.geom.sdo_point.y,-90,90,26)) ; * Note: theMD.HHENCODE_BYLEVEL function takes an x and y coordinate and so the exampleworks well with the US Business points. If you are working with polygons instead of points, you will need toretrieve the centroid of the shape and then pass the X/Y point coordinates tofunction. For more info, please refer toDan Geringer’s original post “Tips on tuning SDO_NN (nearest neighbor) queries” to see an example of how this is done. * Note: only use PCTFREE 0 for read only tables. In this example, the BUSINESS2 is read onlyand using PCTFREE 0 allows more rows to be packed into a single database block. Step 2) add diminfo andcreate spatial index begin mdsys.sdo_meta.change_all_sdo_geom_metadata( USER, ‘BUSINESS2’, ‘GEOM’, mdsys.sdo_dim_array (     mdsys.sdo_dim_element('X', -180, 180, .5),     mdsys.sdo_dim_element('Y', -90, 90, .5) ),8307);end;/CREATE INDEX xsp_business2 ON business2(geom) INDEXTYPE is mdsys.spatial_index PARAMETERS(' SDO_RTR_PCTFREE=0 WORK_TABLESPACE=WORK'); * Note: it isrecommended to use a WORK_TABLESPACE that is a different tablespace than theone where the index will be created. Although optional, the use of a WORK_TABLESPACE can reduce thefragmentation of your spatial index which is important for performance. * Note: only useSDO_RTR_PCTFREE=0 for read only data. Inthis example, the US BUSINESS listing table is pretty much a read only datasetand gets completely replaced every month. Step 3) Perform querycomparison test In SQL*PLUS, set timing and flush the buffer cache SQL> SET TIMING ON;SQL>SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;System altered. * Note: you should notflush the buffer cache on a production system. But in a test environment, flushing the buffer cache can help withtesting comparative performance by ensuring there are no blocks in the SGA fromprior queries that could skew performance results. Run the first query on the non-spatially ordered table select  sum(b.num_of_employees)from business1 bwhere  sdo_within_distance( b.geom,                       mdsys.sdo_geometry(2001,8307,                       mdsys.sdo_point_type( -117.047071, 32.75521, null),null,null),                       'distance=5 unit=mile' ) = 'TRUE';SUM(B.NUM_OF_EMPLOYEES)-----------------------                 137964 Elapsed: 00:00:35.14 Run the query on the spatially ordered business table andcompare results. select  sum(b.num_of_employees)from business2 bwhere  sdo_within_distance( b.geom,                       mdsys.sdo_geometry(2001,8307,                       mdsys.sdo_point_type( -117.047071, 32.75521, null),null,null),                       'distance=5 unit=mile' ) = 'TRUE';SUM(B.NUM_OF_EMPLOYEES)-----------------------                 137964 Elapsed: 00:00:01.49 Conclusion In this example, I was able to achieve more than 20X betterperformance just by taking the same table and ordering by a geographicextent. The queries in the exampleutilized the spatial operator SDO_WITHIN_DISTANCE to return the sum ofemployees with a 5 mile radius around a location. Since both tables (BUSINESS1 and BUSINESS2)are identical except in storage of the order of rows of data, the spatial indexperformance should be pretty much the same. The difference in performance that we are seeing is due to the amount ofdisk and memory I/O processing caused by the different number of blocks thatneeded to be accessed for each of the queries. This is a substantial improvement in performance and highlights theimportance of the order of spatial data by geographic proximity. And as mentioned in the beginning of thepost, ordering spatial data may boost any spatial query operations, whether youare performing within distance queries, sdo relate queries, nearest neighbor orperforming map visualizations. Resultscan vary based on size of table, the speed of disk I/O and also the order ofthe original dataset. It is possiblethat the original dataset you are working with is already organized by somegeographic extent such as county or ZIP Code so additional ordering using themethod described in this post could result in some performance gains butnothing as significant as the 20X I have experienced. But if the order of thetable is completely random and not tied to any geographic extent, then you can expectto see greater performance gains. Allin all, I definitely recommend looking into organizing larger and morefrequently queried spatial data by geographic proximity as a best practicetechnique for optimizing your spatial data.

Guest Post By: Nick Salem, Distinguished Engineer, Neustar Note: Thanks to Nick Salem, Technical Chair of the Oracle Spatial SIG , for contributing this valuable tuning and performance tip that will...

How much space do your SDO_GEOMETRY tables consume?

 By Karin Patenge, Principal Sales Consultant, Oracle Germany I would like to briefly offer a simple function to calculate the memory allocated for SDO_GEOMETRY tables. This takes into account only tables, LOB and index segments (this is not applicable for partitioned tables). create or replace function alloc_space_in_mbytes (tablename in varchar2) return number  is table size number; begin   select      sum (bytes) into table size   from (     select        segment_name,        bytes       from        USER_SEGMENTS - table segments                  where         segment_name = tablename     union all     select        s.segment_name segment_name,        s.bytes bytes     from        user_lobs l, - Lobsegmente       USER_SEGMENTS s     where        l.table_name = tablename and       s.segment_name = l.segment_name     union all     select        s.segment_name segment_name,        s.bytes bytes     from        USER_INDEXES i, - index segments       USER_SEGMENTS s     where        i.table_name = tablename and       s.segment_name = i.index_name);   table size: = tablesize/1024/1024; - Conversion in MB   return table size; end; / The call to the function can then look like this: select   alloc_space_in_mbytes ('GEOM_TABLE_UNTRIMMED') untrimmed,    alloc_space_in_mbytes ('GEOM_TABLE_TRIMMED') trimmed from dual / A test table with 218 237 polygons, a total of 60,754,462 bases and existing spatial index result in the following values: • SDO_ORDINATE_ARRAY values with predominantly 12 to 13 decimal places: 1643.5 MB • SDO_ORDINATE_ARRAY values truncated to 5 decimal places: 1033.5 MB

 By Karin Patenge, Principal Sales Consultant, Oracle Germany I would like to briefly offer a simple function to calculate the memory allocated for SDO_GEOMETRY tables. This takes into account only...

Tips on tuning SDO_NN (nearest neighbor) queries

By:  Daniel Geringer, Senior Development Manager, Oracle Spatial and Graph  Many times I come across customers who ask for help tuning their SDO_NN queries. I generally group SDO_NN queries into two categories. The category defines which SDO_NN parameter to use, SDO_NUM_RES or SDO_BATCH_SIZE. Choose either SDO_NUM_RES or SDO_BATCH_SIZE, never both. SDO_NUM_RES -    Find the nearest candidates only with respect to proximity to the window geometry.                                     There are no additional conditions in the WHERE clause.  SDO_BATCH_SIZE - Find the nearest candidates, and match additional conditions in the WHERE clause.                                       For example, find the 5 nearest banks with name = 'CITIBANK' For "SDO_NN with SDO_BATCH_SIZE", a template is recommended. Optimizations have been developed when the template is used for "SDO_NN with SDO_BATCH_SIZE" queries. The template must include the FIRST_ROWS hint and ORDER BY distance for the optimization to take effect. The following is an example of the template. -- --  Find 30 closest banks named CITIBANK to the window geometry no further than 8046.72 meters (or 5 miles) away. --   This SDO_BATCH_SIZE query includes the first_rows hint, and also ORDER BY distance. -- --   Specifying the cutoff distance is not required, but it can also really help performance. --   For example, distance=8046.72 means stop searching once you reach 5 miles away from the window geometry. -- SELECT bank_id, distance FROM (SELECT /*+ first_rows */ a.bank_id, sdo_nn_distance(1) distance              FROM banks a              WHERE sdo_nn (geometry, window_geometry, 'SDO_BATCH_SIZE=10 distance=8046.72', 1)='TRUE'                  AND a.bank_name = 'CITIBANK'              ORDER BY distance) WHERE rownum <=  30; Not always necessary, but for SDO_NN queries against very dense data (for example, when searching customers in a city with a large population), major performance gains can be achieved by ordering the data in your table spatially by a linear key. The idea is to order the table during load, where rows containing geometries close to each other get placed in the same database block. That way, when nearest neighbor gets the next sdo_batch_size set of rows, and checks additional conditions for a match, the data for the additional condition checks are likely to be in a database block that is already cached in the SGA.  If the table is not ordered spatially, potentially, every nearest neighbor candidate (obtained by the spatial index) might have its additional attributes in database blocks spread all over the place.  This results in much more disk reads vs in-memory hits to compare the additional conditions in the query. SDO_NN with SDO_BATCH_SIZE can perform much faster when this is done. Below are a polygon and point example of how to order a table spatially by a linear key: -------------------------------------------------------------------------------- -- Make a copy of the POLYGON table.  Call it POLYGON_ORDERED.-------------------------------------------------------------------------------- connect / as sysdba grant execute on mdsys.md to some_user; exit; connect some_user/some_user SET TIMING ON ALTER SESSION ENABLE PARALLEL DDL; ALTER SESSION ENABLE PARALLEL DML; ALTER SESSION ENABLE PARALLEL QUERY; DROP TABLE polygon_ordered; CREATE TABLE polygon_ordered NOLOGGING TABLESPACE users PARALLEL AS WITH   part1 AS (SELECT /*+ parallel (8) */                    column1,                    column2,                    column3,                    geom,                    sdo_geom.sdo_centroid(geom,.05) centroid               FROM polygon_table) SELECT /*+ parallel (8) */        p1.column1,        p1.column2,        p1.column3,        p1.geom,        row_number() OVER (ORDER BY md.hhencode_bylevel (p1.centroid.sdo_point.x, -180, 180, 27,                                                                                                      p1.centroid.sdo_point.y,  -90,  90, 26)) rn FROM part1 p1; -------------------------------------------------------------------------------- -- Make a copy of the POINT table.  Call it POINT_ORDERED.-------------------------------------------------------------------------------- connect / as sysdba grant execute on mdsys.md to some_user; exit; connect some_user/some_user SET TIMING ON ALTER SESSION ENABLE PARALLEL DDL; ALTER SESSION ENABLE PARALLEL DML; ALTER SESSION ENABLE PARALLEL QUERY; DROP TABLE point_ordered; CREATE TABLE point_ordered NOLOGGING TABLESPACE users PARALLEL AS WITH   part1 AS (SELECT /*+ parallel (8) */                    column1,                    column2,                    column3,                    geom               FROM point_table) SELECT /*+ parallel (8) */        p1.column1,        p1.column2,        p1.column3,        row_number() OVER (ORDER BY md.hhencode_bylevel (p1.geom.sdo_point.x, -180, 180, 27,                                                                                                      p1.geom.sdo_point.y,  -90,  90, 26)) rn FROM part1 p1;

By:  Daniel Geringer, Senior Development Manager, Oracle Spatial and Graph  Many times I come across customers who ask for help tuning their SDO_NN queries.I generally group SDO_NN queries into two...

Tips for using the Oracle Spatial and Graph geocoding engine to convert addresses directly in the Oracle database

By Karin Patenge, Principal SalesConsultant, Oracle Germany Many OLTP systems and data warehouse implementations would benefit fromhaving an address cleansing process included which at the same time associatesgeographic coordinates with each address for spatial analytics. Together withreference datasets from HERE/Nokia or TomTom the Oracle Geocoder can providethis functionality out-of-the-box. The Geocoding engine in Oracle Spatial and Graph can be accessed usingPL/SQL, via the SDO_GCDR package, which belongs to the user MDSYS. In addition to the the actual conversion ofan address line into a point geometry (SDO_GEOMETRY with the geometry type2001) using GEOCODE_AS_GEOMETRY, this package includes the GEOCODE function: selectSDO_GCDR.GEOCODE(user, SDO_KEYWORDARRAY('Schiffbauergasse 14', '14467 Potsdam'),'DE','DEFAULT') from dual; This function provides the second important capability of the geocoder(in addition to the calculation of the geo-coordinates), namely to performaddress verification. The returnvalue is an object of type SDO_GEO_ADDR.Based on the address match against the reference dataset, this functionreturns information such as the quality of the match as well as the completedor corrected address from the reference dataset. The command describe SDO_GEO_ADDR displays the structureof the object. The result for the query above can be found below, but withoutlooking in the Oracle Spatial Developer's Guide only some parts are meaningful. MDSYS.SDO_GEO_ADDR(MDSYS.SDO_KEYWORDARRAY(),  NULL,NULL,NULL,NULL,NULL,  'Potsdam','BRANDENBURG','DE','14467',  NULL,NULL,NULL,NULL,NULL,NULL,'F','F',  NULL,NULL,'L',0,64959047,'??????????B281CP?',4,'DEFAULT',  13.04793,52.39935,'???11111110??400?') To understand the output more easily, here is a PL/SQL script thatextracts the array values and interprets the ERRORMESSAGE, MATCHVECTOR andMATCHMODE strings. createor replace procedure format_geo_addr(  address SDO_GEO_ADDR)AS  type strings is table of varchar2(30);  match_names strings := strings (   '?            ',    '0 (MATCHED)  ',    '1 (ABSENT)   ',    '2 (CORRECTED)',    '3 (IGNORED)  ',    '4 (SUPPLIED) '  );  address_elements strings := strings (    null,    null,    'X Address Point',    'O POI Name',    '# House or building number',    'E Street prefix',    'N Street base name',    'U Street suffix',    'T Street type',    'S Secondary unit',    'B Built-up area or city',    null,    null,    '1 Region',    'C Country',    'P Postal code',    'P Postal add-on code'  );  element_match varchar2(128);  element_match_code char(1); BEGIN  if address is not null then    dbms_output.put_line ('-ID                 ' || address.ID);    dbms_output.put_line ('- ADDRESSLINES');    if address.addresslines is not null then      for i in 1..address.addresslines.count() loop        dbms_output.put_line ('- ADDRESSLINES['||i||']          ' || address.ADDRESSLINES(i));      end loop;    end if;    dbms_output.put_line ('-PLACENAME           ' ||address.PLACENAME);    dbms_output.put_line ('-STREETNAME          ' ||address.STREETNAME);    dbms_output.put_line ('-INTERSECTSTREET     ' || address.INTERSECTSTREET);    dbms_output.put_line ('-SECUNIT            ' || address.SECUNIT);    dbms_output.put_line ('-SETTLEMENT          ' ||address.SETTLEMENT);    dbms_output.put_line ('-MUNICIPALITY        ' ||address.MUNICIPALITY);    dbms_output.put_line ('-REGION             ' || address.REGION);    dbms_output.put_line ('-COUNTRY            ' || address.COUNTRY);    dbms_output.put_line ('-POSTALCODE          ' ||address.POSTALCODE);    dbms_output.put_line ('-POSTALADDONCODE     ' || address.POSTALADDONCODE);    dbms_output.put_line ('-FULLPOSTALCODE      ' || address.FULLPOSTALCODE);    dbms_output.put_line ('-POBOX              ' || address.POBOX);    dbms_output.put_line ('- HOUSENUMBER        ' || address.HOUSENUMBER);    dbms_output.put_line ('-BASENAME            ' ||address.BASENAME);    dbms_output.put_line ('-STREETTYPE          ' ||address.STREETTYPE);    dbms_output.put_line ('- STREETTYPEBEFORE   ' || address.STREETTYPEBEFORE);    dbms_output.put_line ('- STREETTYPEATTACHED  ' ||address.STREETTYPEATTACHED);    dbms_output.put_line ('-STREETPREFIX        ' ||address.STREETPREFIX);    dbms_output.put_line ('-STREETSUFFIX        ' ||address.STREETSUFFIX);    dbms_output.put_line ('-SIDE               ' || address.SIDE);    dbms_output.put_line ('-PERCENT            ' || address.PERCENT);    dbms_output.put_line ('-EDGEID             ' || address.EDGEID);    dbms_output.put_line ('-ERRORMESSAGE        ' ||address.ERRORMESSAGE);    if address.MATCHVECTOR is not null then      dbms_output.put_line ('-MATCHVECTOR         ' ||address.MATCHVECTOR);      for i in 1..length(address.MATCHVECTOR) loop        if address_elements(i) is not nullthen          if substr(address.matchvector,i,1) = '?' then           element_match_code := 0;          else           element_match_code := substr(address.matchvector,i,1) + 1;          end if;          dbms_output.put_line('-   '|| substr(address.errormessage,i,1)  || ' ' ||            match_names(element_match_code + 1) || ' ' ||           address_elements (i)          );        end if;      end loop;    end if;    if address.MATCHVECTOR is not null then      dbms_output.put_line ('- MATCHCODE          ' || address.MATCHCODE || ' = ' ||        case address.MATCHCODE          when  0 then'Ambiguous'          when  1 then 'Exactmatch'          when  2 then'Street type not matched'          when  3 then 'Housenumber not matched'          when  4 then'Street name not matched'          when 10 then 'Postalcode not matched'          when 11 then 'City notmatched'        end      );    end if;    dbms_output.put_line ('-MATCHMODE           ' ||address.MATCHMODE);    dbms_output.put_line ('-LONGITUDE           ' ||address.LONGITUDE);    dbms_output.put_line ('-LATITUDE            ' ||address.LATITUDE);  else    dbms_output.put_line ('**** NO MATCH ****');  end if;end;/show errors create or replace procedure format_addr_array (  address_list SDO_ADDR_ARRAY) asbegin  if address_list is not null and address_list.count() > 0 then    for i in 1..address_list.count() loop      dbms_output.put_line ('ADDRESS['||i||']');      format_geo_addr (address_list(i));    end loop;  else    dbms_output.put_line ('**** NO MATCH ****');  end if;end;/show errors Now the GEOCODE function can be called again, but this time inconjunction with the format function: execformat_geo_addr (SDO_GCDR.GEOCODE(user, SDO_KEYWORDARRAY('Schiffbauergasse 14','14467 Potsdam'), 'DE', 'DEFAULT')); And the resulting output is now formatted in a manner that is much easierto understand. -ID                 0- ADDRESSLINES- PLACENAME           - STREETNAME          Schiffbauergasse- INTERSECTSTREET     -SECUNIT             - SETTLEMENT          - MUNICIPALITY        Potsdam-REGION             BRANDENBURG-COUNTRY            DE- POSTALCODE          14467- POSTALADDONCODE     - FULLPOSTALCODE      - POBOX               - HOUSENUMBER         14- BASENAME           SCHIFFBAUER- STREETTYPE          GASSE- STREETTYPEBEFORE    F- STREETTYPEATTACHED  F- STREETPREFIX        - STREETSUFFIX        -SIDE               R-PERCENT            .75- EDGEID             748777426- ERRORMESSAGE        ????#ENUT?B281CP?- MATCHVECTOR         ???10101010??400?-   ??             XAddress Point-   ? 1 (ABSENT)    O POI Name-   # 0 (MATCHED)   # House or building number-   E 1 (ABSENT)    E Street prefix-   N 0 (MATCHED)   N Street base name-   U 1 (ABSENT)    U Street suffix-   T 0 (MATCHED)   T Street type-   ? 1 (ABSENT)    S Secondary unit-   B 0 (MATCHED)   B Built-up area or city-   1 4 (SUPPLIED)  1 Region-   C 0 (MATCHED)   C Country-   P 0 (MATCHED)   P Postal code-   ??             PPostal add-on code- MATCHCODE           1 =Exact match- MATCHMODE           DEFAULT- LONGITUDE          13.0745378367008- LATITUDE           52.4041174822031 Note: The reference dataset used for this test with the Oracle Spatialand Graph geocoder was provided by HERE/Nokia.

By Karin Patenge, Principal Sales Consultant, Oracle Germany Many OLTP systems and data warehouse implementations would benefit fromhaving an address cleansing process included which at the same...

Upcoming Webinars: MapViewer at City of Toronto for Public Safety, Customers Achieve 300x Performance Gains with Oracle Spatial and Graph

A note to share information about two upcoming Directions Media webinars on April 23 and May 6.City of Toronto Enhances Public Safety Using Real-time Big Data and Map Rendering with Oracle and AGSI, Wed., April 23, 2:00PM US EDTLearn how the City of Toronto Police Services can search, review and map social media traffic in real time to quickly identify and respond to incidents, improving public safety. See live demos of their system using Oracle MapViewer's HTML5 capabilities, Oracle Spatial, and a social media mapping platform from partner AGSI. Carol Palmer of Oracle will co-present this webinar with Mike Jander of AGSI, and City of Toronto, hosted by Directions Media.Learn more and register for this free webinar -http://www.directionsmag.com/webinars/register/city-of-toronto-enhances-public-safety-using-real-time-big-data-and-ma/389356?DM_webinars_section&utm_medium=web&utm_campaign=389356Learn How Customers Are Experiencing 300x Performance Gains with Oracle Spatial and Graph, Tues., May 6, 2:00PM US EDT (Free Webinar)Nick Salem of Neustar and Steve Pierce of Think Huddle will share their realized performance benchmarks using Oracle Spatial and Graph. With Oracle Spatial and Graph in Database 12c, customers can address the largest geospatial workloads and experience performance increases of 50 to 300 times for vector operations, with minimal configuration changes. Jim Steiner of Oracle will also discuss performance gains from parallel raster processing and Exadata.Learn more and register for this free webinar -http://www.directionsmag.com/webinars/register/learn-how-customers-are-experiencing-300x-performance-gains-with-oracl/390239?DM_webinars_section&utm_medium=web&utm_campaign=390239

A note to share information about two upcoming Directions Media webinars on April 23 and May 6.City of Toronto Enhances Public Safety Using Real-time Big Data and Map Rendering with Oracle and AGSI,...

Data Validation with Esri and Oracle Spatial

By:  Daniel Geringer, Senior Development Manager, Oracle Spatial and Graph One of the things users can run into when using Esri with Oracle Spatial can be problems with data validation when loading data using Esri utilities into Oracle Spatial data types. From my experience, the solution is a relatively straightforward change in the workflow used to load data. Here is an excerpt from a user problem where it sounds like Esri is calling Oracle's validation on load (not Esri's validation). I don't believe Esri's validation would be sensitive to the database version. It seems there are tighter tolerances (or the tolerances work) in 11.2.0.3 and features that were not reporting as invalid in 11.2.0.2 are now reporting as invalid features (mainly self-intersecting polygons) in the 11.2.0.3.  We are assuming that for some reason the SDE tools are crashing when they hit an invalid feature. This sounds more like a workflow defined by Esri than differences in validation.  I think the issue is once Esri encounters an invalid geometry, it stops loading the rest (even if they are valid).  I believe Esri chooses this workflow to ensure all geometries in a layer are valid.  I agree the outcome is optimal, and what Oracle recommends too.  A deficiency in the workflow is: 1. It prevents valid geometries from being loaded after an invalid one is encountered2. Not loading the invalid geometry does not provide a way to inspect it From a product perspective, a change in the Esri workflow would have to be implemented by Esri.  (Maybe the default behavior can remain the same, but enable a switch that populates invalid geometries into a different table for inspection at a later time. This is just one suggestion.  There may be other options.) Currently, the best way to circumvent this is to do what this customer did, and load data with Oracle or third party tools like FME.  Once the data is loaded, Oracle also recommends validation, and removing/isolating invalid geometries until they can be fixed.  I discuss a strategy to do this in a best practices presentation.  http://download.oracle.com/otndocs/products/spatial/pdf/oow2010/spatial/spatialoow10_bestpractices.pdf One thing I keep hearing is the claim that Esri and Oracle validate differently. Both ESRI and Oracle validate by OGC rules.  The only difference I see is when the data is geodetic.  Esri does not consider geodesic paths between vertices for linestrings or polygons, but Oracle does.  There is only one case I have come across that is valid in Esri, but not in Oracle or by OGC standards.  Imagine a box with a triangle void, where the triangle only touches the box at one point.  Esri can model this as a single exterior ring with no voids, and considers it valid.  This does not comply with OGC standards. Oracle and the OGC standard state that this case is valid if it is modeled with two rings, one exterior and one interior, where the interior ring touches the exterior at one point. Other than geodetic data validation, I believe both Esri and Oracle offer comprehensive validation, and for all intents and purposes, the validation offered by both solutions is equivalent.

By:  Daniel Geringer, Senior Development Manager, Oracle Spatial and Graph One of the things users can run into when using Esri with Oracle Spatial can be problems with data validation when loading...

New Point-in-Polygon function in Oracle Spatial and Graph 12c

By: Jim Steiner, Siva Ravada, RickAnderson With the increased adoption of Exadata for spatialworkloads, we have been looking at ways to exploit more and more of thecapabilities of this architecture to address problems faced in large scalespatial analysis. The new point-in-polygon function in Spatial can result in100s of times faster UPDATE and INSERT operations with no degradation in queryperformance for large scale point-in-polygon operations. Mask operations (DISJOINT, TOUCH, INSIDE, ANYINTERACT) canbe performed with the point-in-polygon function. When working with point data and performing point-in-polygonanalysis the existing spatial operators to do a fast query on the data if thereis a Spatial index on the point data. However,in many cases, the data volume is very high, so creating and maintaining theindex becomes very expensive. With 12c, weexploit Exadata smartscan by implementing a different model to take advantageof all the CPUs to do point in polygon operations and not have the overhead ofa Spatial index. Themdsys.PointInPolygon() function returns those rows that reside within a specifiedpolygon geometry. This parallel-enabledPoint-In-Polygon function takes an arbitrary set of rows whose first column isa point's x-coordinate value and the second column is a point's y-coordinatevalue. Themdsys.PointInPolygon() function API is the following: mdsys.sdo_PointInPolygon(curSYS_REFCURSOR,                         geom_obj IN SDO_GEOMETRY,                         tol IN NUMBER,                         params IN VARCHAR2 DEFAULT NULL); The "cur" parameter is used to select an "x" and "y" point coordinate from a  user table. The two columns must be of type NUMBER; this is NOT a geometry  parameter.The "geom_obj" parameter is either a polygon geometry from a table, or a transient instance of a polygon geometry, against which all of the selected points from "cur" will be validated.The "tol" parameter is the desired tolerance value, which must be greater than the value "0.0". The following examples show the performance benefits of this newapproach: Here we select all rows from the "weather_sensor" table and query those rows against a transient polygon geometry instance. Only 1 weather_sensor row (out of 4) resides within the specified polygon.  SQL>SELECT * 2 FROMTABLE(mdsys.sdo_PointInPolygon( 3 CURSOR(select * from weather_sensor), 4 MDSYS.SDO_GEOMETRY( 5 2003, 6 NULL, 7 NULL, 8 MDSYS.SDO_ELEM_INFO_ARRAY(1,1003, 1), 9 MDSYS.SDO_ORDINATE_ARRAY(5, 1,8, 1, 8, 6, 5, 7, 5, 1)), 10 0.05)); Inorder to utilize parallel query servers, you must either specify the "/*+PARALLEL(4) */" optimizer hint, or enable parallel query execution, usingthe command: alter session force parallel query; Belowis the same as above, but uses 4 parallel query servers: SQL>SELECT /*+ PARALLEL(4) */ * 2 FROMTABLE(mdsys.sdo_PointInPolygon( 3 CURSOR(select * from weather_sensor), 4 MDSYS.SDO_GEOMETRY( 5 2003, 6 NULL, 7 NULL, 8 MDSYS.SDO_ELEM_INFO_ARRAY(1,1003, 1), 9 MDSYS.SDO_ORDINATE_ARRAY(5, 1,8, 1, 8, 6, 5, 7, 5, 1)), 10 0.05)); There can be a huge performance benefit to using parallel query servers. The following "worst-case" example queries 1 million rows against a transient polygon geometry instance, using the non-parallel query execution: SQL>-- instead of the actual data... SQL> SQL>-- Test "non-parallel" execution first SQL>timing start "sdo_PointInPolygon()" SQL>SELECT COUNT(*) 2 FROMTABLE(mdsys.sdo_PointInPolygon( 3 CURSOR(select * from pip_data), 4 MDSYS.SDO_GEOMETRY( 5 2003, 6 NULL, 7 NULL, 8 MDSYS.SDO_ELEM_INFO_ARRAY(1,1003, 1), 9 MDSYS.SDO_ORDINATE_ARRAY(5, 1,8, 1, 8, 6, 5, 7, 5, 1)), 10 0.05)); timingfor: sdo_PointInPolygon() Elapsed:00:05:00.73 Enablingthe parallel query servers dramatically reduces the query execution time: SQL>-- Now test using 4 parallel query servers SQL>timing start "sdo_PointInPolygon()" SQL>SELECT /*+ PARALLEL(4) */ COUNT(*) 2 FROMTABLE(mdsys.sdo_PointInPolygon( 3 CURSOR(select * from pip_data), 4 MDSYS.SDO_GEOMETRY( 5 2003, 6 NULL, 7 NULL, 8 MDSYS.SDO_ELEM_INFO_ARRAY(1,1003, 1), 9 MDSYS.SDO_ORDINATE_ARRAY(5, 1,8, 1, 8, 6, 5, 7, 5, 1)), 10 0.05)); SQL>timing stop timingfor: sdo_PointInPolygon() Elapsed:00:02:18.18 For more information about this new feature, link to thedocumentation URL: SDO_PointInPolygon

By: Jim Steiner, Siva Ravada, Rick Anderson With the increased adoption of Exadata for spatial workloads, we have been looking at ways to exploit more and more of thecapabilities of this architecture...