--

-- Stage 1 GC and Reverse GC

--

-- Convert location information into network representation

-- geocoding

SELECT SDO_GCDR.GEOCODE('here_sf', SDO_KEYWORDARRAY('500 oracle pky','redwood city, CA'), 'US', 'RELAX_BASE_NAME') addr FROM DUAL;

with

part1 as (

SELECT SDO_GCDR.GEOCODE('here_sf', SDO_KEYWORDARRAY('500 oracle pky','redwood city, CA'), 'US', 'RELAX_BASE_NAME') addr FROM DUAL

)

select a.addr.edgeid, a.addr.percent, a.addr.side, a.addr.longitude, a.addr.latitude from part1 a;

-- reverse geocode with longitude,latitude

with

part1 as (

SELECT SDO_GCDR.REVERSE_GEOCODE('here_sf', SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-122.26109, 37.5313, NULL), NULL, NULL),'US') addr FROM DUAL

)

select a.addr.edgeid, a.addr.percent, a.addr.side, a.addr.longitude, a.addr.latitude from part1 a;

-- examples

-- get all geocoded result of a table of streetaddress of buffer centers

select * from sf_stores_streetaddress order by store_id;

with

part1 as (

SELECT b.STORE_ID, SDO_GCDR.GEOCODE('here_sf', SDO_KEYWORDARRAY(b.STREETADDRESS), 'US', 'RELAX_BASE_NAME') addr

FROM SF_STORES_STREETADDRESS b

)

select a.store_id, a.addr.edgeid, a.addr.percent, a.addr.side, a.addr.longitude, a.addr.latitude

from part1 a

order by a.store_id;

-- transfer edgeid, percentage, and side to link_id

with

part1 as (

SELECT b.STORE_ID, SDO_GCDR.GEOCODE('here_sf', SDO_KEYWORDARRAY(b.STREETADDRESS), 'US', 'RELAX_BASE_NAME') addr

FROM SF_STORES_STREETADDRESS b

)

SELECT a.store_id, a.addr.longitude longitude, a.addr.latitude latitude, a.addr.side side,

case

when a.addr.side = 'L' then -a.addr.edgeid

else a.addr.edgeid

end as link_id,

case

when a.addr.side = 'L' then 1-a.addr.percent

else a.addr.percent

end as percent

from part1 a

order by a.store_id;

--

-- Stage 2 , Create Network Buffers

--

-- Generate network buffer tables network buffer prefix: ('SF') in NDM java api

--

-- now create network buffers with NDM Java Code

--

-- 16g RAM linux box

-- ~1s per buffer including analysis and persistence for 10-minute-drive.

-- stoer buffers

-- describe network buffer tables

-- metadata table

-- buffer id, radius and direction

select * from sf_nbr$ order by buffer_id;

-- metadata table

-- buffer network representation

select * from SF_NBCL$ order by buffer_id;

-- coverage table:

-- network buffer covered link table schema

select * from SF_NBL$ where rownum < 20;

-- number of covered links of each buffer (10-minute-drive)

select buffer_id, count(*) from SF_nbl$ group by buffer_id order by buffer_id;

-- 10 min. path buffer used in the second demo:

-- metadata

-- buffer_id, radius, direction

select * from path_10m_nbr$ order by buffer_id;

-- buffer centers for each buffer

select buffer_id,count(*) from PATH_10M_NBcL$ group by buffer_id order by buffer_id;

-- no of covered links for each 10-minute buffer

select buffer_id, count(*) from PATH_10M_NBL$ group by buffer_id order by buffer_id;

--

-- Stage 3, Query Network Buffers

--

-- queries on network buffer table

-- get cost to one specific buffer centers of a given location (link_id, percentage)

SELECT buffer_id, MIN(start_cost+(0.95-start_percentage)*(end_cost-start_cost)/(end_percentage-start_percentage)) cost

FROM SF_NBl$

WHERE link_id=945669955

AND buffer_id = 1

AND (0.95-start_percentage)*(end_percentage-start_percentage)>=0

GROUP BY buffer_id;

-- get costs to all buffer centers of a given location (link_id, percentage)

SELECT buffer_id, MIN(start_cost+(0.95-start_percentage)*(end_cost-start_cost)/(end_percentage-start_percentage)) cost

FROM SF_NBl$

WHERE link_id=945669955

AND (0.95-start_percentage)*(end_percentage-start_percentage)>=0

GROUP BY buffer_id

ORDER BY COST;

-- get links of the shortest path from a given location to a specific buffer center

select buffer_id, link_id, prev_link_id from sf_nbl$

where buffer_id = 1

start with link_id = 799415310

connect by prior prev_link_id = link_id and

prior start_cost = end_cost and

prior buffer_id = buffer_id

order by start_cost;

-- get path geometry from a given location to a buffer center

with

part1 as( -- get links of the path

select link_id from sf_nbl$

where buffer_id = 1

start with link_id = 799415310

connect by prior prev_link_id = link_id and

prior start_cost = end_cost and

prior buffer_id = buffer_id

order by start_cost

),

part2 as( -- get geometry of each link of the path

select a.link_id, b.geometry

from part1 a, here_sf_net_link$ b

where a.link_id = b.link_id

)

select SDO_AGGR_CONCAT_LINES(b.geometry) path -- create path geometry

from part2 b;

-- get path geometry vertices from a given location to a buffer center

with

part1 as( -- get links of the path

select link_id from sf_nbl$

where buffer_id = 1

start with link_id = 799415310

connect by prior prev_link_id = link_id and

prior start_cost = end_cost and

prior buffer_id = buffer_id

order by start_cost

),

part2 as( -- get geometry of each link of the path

select a.link_id, b.geometry

from part1 a, here_sf_net_link$ b

where a.link_id = b.link_id

),

part3 as (

select SDO_AGGR_CONCAT_LINES(b.geometry) path -- create path geometry

from part2 b

)

select t.x,t.y,t.id from

part3 a, table(sdo_util.getvertices(a.path)) t -- create path geometry

order by t.id ;

package lod.networkbuffer;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.text.DecimalFormat;

import java.text.NumberFormat;

import oracle.spatial.network.lod.NetworkAnalyst;

import oracle.spatial.network.lod.LODNetworkManager;

import oracle.spatial.network.lod.LinkCostCalculator;

import oracle.spatial.network.lod.NetworkIO;

import oracle.spatial.network.lod.PointOnNet;

import oracle.spatial.network.lod.util.PrintUtility;

import oracle.spatial.util.Logger;

import oracle.spatial.network.lod.NetworkBuffer;

import oracle.spatial.network.lod.NodeCostCalculator;

import oracle.spatial.network.lod.config.ConfigManager;

import oracle.spatial.network.lod.config.LODConfig;

public class PersistentNetworkBuffer

{

private static final NumberFormat formatter = new DecimalFormat("#.######");

private static NetworkAnalyst analyst;

private static NetworkIO networkIO;

private static void setLogLevel(String logLevel)

{

if("FATAL".equalsIgnoreCase(logLevel))

Logger.setGlobalLevel(Logger.LEVEL_FATAL);

else if("ERROR".equalsIgnoreCase(logLevel))

Logger.setGlobalLevel(Logger.LEVEL_ERROR);

else if("WARN".equalsIgnoreCase(logLevel))

Logger.setGlobalLevel(Logger.LEVEL_WARN);

else if("INFO".equalsIgnoreCase(logLevel))

Logger.setGlobalLevel(Logger.LEVEL_INFO);

else if("DEBUG".equalsIgnoreCase(logLevel))

Logger.setGlobalLevel(Logger.LEVEL_DEBUG);

else if("FINEST".equalsIgnoreCase(logLevel))

Logger.setGlobalLevel(Logger.LEVEL_FINEST);

else //default: set to ERROR

Logger.setGlobalLevel(Logger.LEVEL_ERROR);

}

private static boolean tableExists(Connection conn, String tableName)

{

boolean result = false;

try {

Statement stmt = conn.createStatement();

String sqlStr = "SELECT COUNT(*) FROM TAB WHERE TNAME = '" + tableName.toUpperCase() + "'";

ResultSet rs = stmt.executeQuery(sqlStr);

if ( rs.next() ) {

int no = rs.getInt(1);

if ( no != 0 )

result = true;

}

rs.close();

stmt.close();

}

catch (Exception e) {

e.printStackTrace();

}

return result;

}

public static void main(String[] args) throws Exception

{

System.out.println("\n\ncreate reaching buffer\n\n");

String configXmlFile = "lod/networkbuffer/LODConfigs.xml";

String logLevel = "ERROR";

String dbUrl = ""; // jdbc url

String dbUser = "";

String dbPassword = "";

String networkName = "HERE_SF_NET";

long startNodeId = 48523065;

long linkId = 947224640;

double percent = 0;

long[] linkIds = {915260080, 711576509, -23618421, -127806843, 23618590, 23595880, 23594646, 23748128, 23611433, -127806839, 23612874, -916623909};

double[] percents = {0.2, 0.48, 0.27, 0.72, 0.63, 0.14, 0.1, 1, 0.29, 0.14, 0, 0};

int linkLevel = 1; //default link level

// double cost = 10*1600; // 10 miles converted to meters

double cost = 10*60; // 10 minutes converted to seconds

String tableNamePrefix = "SF";

Connection conn = null;

//get input parameters

for(int i=0; i<args.length; i++)

{

if(args[i].equalsIgnoreCase("-dbUrl"))

dbUrl = args[i+1];

else if(args[i].equalsIgnoreCase("-dbUser"))

dbUser = args[i+1];

else if(args[i].equalsIgnoreCase("-dbPassword"))

dbPassword = args[i+1];

else if(args[i].equalsIgnoreCase("-networkName") && args[i+1]!=null)

networkName = args[i+1].toUpperCase();

else if(args[i].equalsIgnoreCase("-linkLevel"))

linkLevel = Integer.parseInt(args[i+1]);

else if(args[i].equalsIgnoreCase("-startNodeId"))

startNodeId = Long.parseLong(args[i+1]);

else if(args[i].equalsIgnoreCase("-cost:"))

cost = Double.parseDouble(args[i]);

else if(args[i].equalsIgnoreCase("-tableNamePrefix"))

tableNamePrefix = args[i+1];

else if(args[i].equalsIgnoreCase("-configXmlFile"))

configXmlFile = args[i+1];

else if(args[i].equalsIgnoreCase("-logLevel"))

logLevel = args[i+1];

}

// opening connection

conn = LODNetworkManager.getConnection(dbUrl, dbUser, dbPassword);

Statement stmt = conn.createStatement();

System.out.println("Network analysis for "+networkName);

setLogLevel(logLevel);

//load user specified LOD configuration (optional),

//otherwise default configuration will be used

InputStream config = ClassLoader.getSystemResourceAsStream(configXmlFile);

LODNetworkManager.getConfigManager().loadConfig(config);

LODConfig c = LODNetworkManager.getConfigManager().getConfig(networkName);

//get network input/output object

networkIO = LODNetworkManager.getCachedNetworkIO(

conn, networkName, networkName, null);

//get network analyst

analyst = LODNetworkManager.getNetworkAnalyst(networkIO);

LinkCostCalculator[] oldlccs = analyst.getLinkCostCalculators();

LinkCostCalculator[] lccs = {new LinkTravelTimeCalculator()};

try

{

analyst.setLinkCostCalculators(lccs);

for (int i = 0; i < linkIds.length; i++) {

int bufferId = i + 1;

System.out.println("***** BEGIN: Network Buffer " + bufferId + " *****");

linkId = linkIds[i];

percent = percents[i];

PointOnNet[] startPoint = {new PointOnNet(linkId, percent)};

long startTime = System.currentTimeMillis();

NetworkBuffer buffer = analyst.reachingNetworkBuffer(startPoint, cost, null);

long t1 = System.currentTimeMillis();

networkIO.writeNetworkBuffer(buffer, bufferId, tableNamePrefix);

long endTime = System.currentTimeMillis();

System.out.println("Run times : "+" Total = "+(endTime-startTime) +" msec."+

" Analysis = "+(t1-startTime)+" msec."+" Persistence = "+

(endTime-t1)+" msec.");

System.out.println("***** END: Network Buffer" + bufferId + " *****");

}

}

catch (Exception e)

{

e.printStackTrace();

}

if(conn!=null)

try{conn.close();} catch(Exception ignore){}

}

}

Java Example and SQL scripts for [AskTom session on Oct 20, 2020](https://asktom.oracle.com/pls/apex/asktom.search?oh=10082)

The Java example illustrates the generation and persistence of network buffers in the database with the Network Data Model (NDM) Java API.

The SQL queries illustrate the workflow with network buffers in the tutorial.

PersistentNetworkBuffers.java: Java example for network buffer generation and persistence to the database

AskTom_scripts.sql: SQL scripts for geocoding, reverse geocoding, queries of the min. cost and shortest path geometry with network buffers

Spatial Features Homepage: https://oracle.com/goto/spatial

NDM Demo Homepage: https://oracle.com/downloads/samplecode/ndm-graph-samplecode-downloads.html