Getting Geometry of Intersection of Road SQL and Inserting Into Table -


i need find intersection of various roads each road divided multiple segments there many records 1 road. have query can use find geometries each shows null. need have 1 geometry can insert 1 record. 

select sdo_geom.sdo_intersection(coll_a.geometry, coll_b.geometry, 0.05) ottawacollectors coll_a, ottawacollectors coll_b coll_a.road_name = 'kent' , coll_b.road_name = 'metcalfe';

the efficient approach sort of problem use sdo_join() filter. designed efficiently match spatially many objects many others using spatial indexes.

i assuming table looks this:

create table ottawacollectors (   road_id number,   segment_id number,   road_name varchar2(30),   geometry sdo_geometry,   primary key (road_id, segment_id) );

it contains road segments. each road segment identified road identifier , segment identifier.

the following creates new table intersections contain 1 row each intersection, i.e. whenever 2 road segments interact. intersection computed geometric point. each row contains identifier of each segment (road identifier , segment identifier) name of each road.

create table intersections select a.road_id road_id_1, a.segment_id segment_id_1, a.road_name road_name_1,        b.road_id road_id_2, b.segment_id segment_id_2, b.road_name road_name_2,               sdo_geom.sdo_intersection (          a.geometry, b.geometry, 0.05        ) intersection_point  ottawacollectors a,       ottawacollectors b,       table (         sdo_join(           'ottawacollectors','geometry',           'ottawacollectors','geometry',           'mask=anyinteract'         )       ) j   a.rowid = j.rowid1 ,   b.rowid = j.rowid2 ,   j.rowid1 > j.rowid2;

some explanations:

  • sdo_join() "table" function. takes names of 2 input tables (table name , name of geometry column) , match criteria - here " anyinteract", meaning kind of interaction: segments may cross or touch each other.
  • it returns varray of elements, each element contains pair of rowids (physical identifiers of rows in table) called rowid1 , rowid2, point couples of interacting road segments.
  • the table() constructor casts array make regular table, making easy embed in relational query. "virtual" table called j in query.
  • the query reads ottawacollectors table twice (like in example). joins them table() result: j.rowid1=a.rowid , j.rowid2=b.rowid
  • the j.rowid1>j.rowid2 filter there eliminate unwanted results. road segments , b intersect. sdo_join return 4 combinations: (a,b), (b,a) , (a,a) , (b,b) since segment intersects itself! purpose of comparing rowids retain 1 of (a,b) or (b,a).
  • the select list includes full identifiers of 2 segments, name , intersection point (computed using sdo_geom.sdo_intersection() in example).
  • finally result written table

note query not return results instantly: may take minutes complete, depending on number of road segments need process, , of course on hardware run on. if run on oracle 12c (12.1.0.1 or 12.1.0.2) , own proper license oracle spatial, make sure have vector performance accelerator option turned on.


Comments

Post a Comment

Popular posts from this blog

powershell Start-Process exit code -1073741502 when used with Credential from a windows service environment -

i'm running strange behavior powershell start-process call. here call: $process = start-process ` "c:\somepath\mybinary.exe" ` -passthru ` -credential $defaultcredential ` -wait ` -workingdirectory "c:\somepath" ` -loaduserprofile if ($process.exitcode -ne 0) { #do } this call return exit code - 1073741502 . after quick search, exit code seems related generic error when program not load required dll (aka. status_dll_init_failed ). when run without -credential $credential program runs correctly. in order isolate problem, manually launched some.exe in prompt target credential , runs smoothly. so problem seems come way start-process cmdlet launch process. i found potential solutions problem tried apply no luck : link , link . would have idea of what's going on here ? edit 1: run proc mon monitoring program activities when launched directly or via powershell script. problem seems occur when loading kernelbase
Read more

twig - Using Twigbridge in a Laravel 5.1 Package -

i'm trying create laravel 5.1 package uses twigbridge instead of .php views. can normal .php views display fine, when try make use of .twig templates i'm met error. possible use twigbridge templates when creating views laravel package or pretty stuck blade , php ? edit 1: loading twig template without extending works. it's when start using {% extends 'file' %} error comes out. i error error loading /virtual/laravel/packages/username/mypackage/src/views/sample.twig: template "templates.master" not defined () in "/virtual/laravel/packages/username/mypackage/src/views/sample.twig" @ line 1. my folders username |_ mypackage |_ src |_ controllers - samplecontroller.php |_ views |_ templates - master.twig - sample.twig twig template sample.twig {% extends 'templates.master' %} {% block content %} {{ parent()
Read more

c# - LINQ join Entities from HashSet's, Join vs Dictionary vs HashSet performance -

i have hashset's each stores t, have written test application compares different relation algorithms can think of, i'm not pleased results getting. does there exist more efficient ways of achieving object relations, once testing? using system; using system.collections.generic; using system.linq; using system.text; using system.threading.tasks; using system.diagnostics; namespace linqtests { class program { static void main(string[] args) { hashset<user> usertable = new hashset<user>(); hashset<userproperty> userpropertytable = new hashset<userproperty>(); #region lets create dummy data. console.writeline("please wait while creating dummy data, can take while..."); console.writeline(""); int rows = 1000000; for(int x = 0; x < rows; x++) { random rnd = new random(); // ad
Read more