Hi All,
Oracle Database Enterprise Edition 19c.
Assume I have the following road:
which can be modelled in the database using the SQL as follows:
--Creating a table
create table test ( name varchar2(100), geom sdo_geometry);
--Inserting a road representing the google maps red line
insert into test (name, geom) values ('Norton Lane', sdo_geometry(2002, 4326, null, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(-2.586813108015,51.3808549254081,-2.5868127,51.3808598,-2.5869086,51.3811122,-2.5869411,51.3813172,-2.586954,51.3815145,-2.5869584,51.3815817,-2.5869455,51.3817394,-2.5868997,51.3819006,-2.5870091,51.3821976,-2.5870058,51.3822332,-2.5869574,51.3822558,-2.5868817,51.3822598,-2.586674,51.3822819,-2.5865763,51.3823113,-2.586414,51.3824154,-2.5862885,51.3824753,-2.586165,51.3825015,-2.5859554,51.3825066,-2.5855202,51.3824983,-2.5851934,51.3825017,-2.5850528,51.3825143,-2.5848683,51.3825418,-2.5847853,51.3825647,-2.5847518,51.382574,-2.5845103,51.3826657,-2.583885,51.3829478,-2.5833352,51.3832229,-2.5831319,51.3833526,-2.5830289,51.3834466,-2.582864,51.3836591,-2.582347,51.3842495,-2.5822872,51.3843014,-2.5822406,51.3843419,-2.5820869,51.3844062,-2.5819112,51.3844496,-2.5815568,51.384498,-2.5795077,51.3846764,-2.579294,51.3847022,-2.5791073,51.3847401,-2.5789013,51.3848067,-2.5779534,51.3852535,-2.5773884,51.3855199,-2.5772221,51.3855835,-2.5769894,51.3856524,-2.5762469,51.3858335,-2.5754996,51.3859669,-2.575394,51.3859965,-2.5751614,51.3860618,-2.5749246,51.3861355,-2.5743944,51.3863588,-2.5740893,51.3865044,-2.5739407,51.3865674,-2.5738672,51.38659)));
--Saving the road
commit;
--Verifying that lat/lng are properly loaded into the database
set serveroutput on
begin
for i in (select geom FROM test) loop
for j in (SELECT t.y lat, t.x lng FROM TABLE(sdo_util.getvertices(i.geom)) t) loop
dbms_output.put_line('{lat: '||j.lat||', lng: '||j.lng||'},');
end loop;
end loop;
end;
I want to do the following tasks using SQL statements if possible:
Finding the centre of the road. The query shall return SDO_POINT containing the latitude and longitude of the centre of the road.
Dividing the road into segments based on certain criteria say a distance of 10 meters. The query shall return several SDO_GEOMETRY objects representing these segments.
I don't have a definitive answer for result verification but I hope you shall be able to work from here.
Does anyone know how to use SQL queries to achieve the aforementioned tasks?
Your help will be. greatly appreciated.
Many Thanks and
Kind Regards,
Bilal