The requirement here is to Query Spatial Data, I want to know which Latitude and Longitude inside one VERTICE(An Array).
-->tmp_notam_tfr-> I have all vertices Latitude and Longitude Data per NOTAM_ID in one row.--VERTICE DATA
-->EXPORT_TABLE -> I have Tower's Data with each Tower's Latitude and Longitude.--TOWER DATA
Challenge is I have VERTICE DATA in tmp_notam_tfr, from that I want to Query only that TOWER from "EXPORT_TABLE" which is INSIDE THIS WHOLE VERTICE Data in "tmp_notam_tfr".
So technically I have Tower's Latitude and Longitude and I have whole Areas VERTICE's Latitude and Longitude and out of that Entire List of Vertice, I Want to know which Tower is falling. We can call it AFFECTED_TOWER 'Y' if it falls inside,'N' if it does not fall inside.
--DB VERSION-------------------------------
select* from v$version;
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
"CORE 12.1.0.2.0 Production" 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
------------------------------------------------------------------------------
-----Main Table Create Script
create table tmp_notam_tfr
(NOTAM_ID varchar2(200),
FACILITY_ACCOUNTABLE varchar2(200),
DATE_ISSUED varchar2(200),
DATE_EFFECTIVE varchar2(200),
DATE_EXPIRES varchar2(200),
FACILITY_TYPE varchar2(200),
CITY varchar2(200),
STATE varchar2(200),
ENTITY_NAME varchar2(200),
ENTITY_PHONE varchar2(200),
AIRSPACE_ID varchar2(200),
AIRSPACE_TYPE varchar2(200),
AIRSPACE_NAME varchar2(200),
VERTICES clob,--set of Vertices
DESCRIPTION_1 varchar2(4000),
DESCRIPTION_2 varchar2(4000),
INSERT_DATE date,
UPDATE_DATE date);
insert into tmp_notam_tfr (
notam_id,facility_accountable,date_issued,date_effective,date_expires,facility_type,city,
state,entity_name,entity_phone,airspace_id,airspace_type,airspace_name,
vertices,
description_1,description_2,insert_date,update_date
) values (
'1/2923','FDC','2021-07-01T01:40:00','2021-07-01T03:00:00','2021-09-01T03:00:00','ARTCC','15NM N OF MT. SHASTA',
'CALIFORNIA','UNITED STATES FOREST SERVICE SHASTA TRINITY TEL','530-226-2499','358305','RAS','Hazard Area1',
'[[41.63194444,-122.415],[41.62888889,-122.23138889],[41.45444444,-122.19888889],[41.31138889,-122.22277778],[41.47888889,-122.41027778],[41.63194444,-122.415]]',
'!FDC 1/2923 ZSE CA..AIRSPACE 15NM N OF MT. SHASTA, CA..TEMPORARY FLIGHT RESTRICTIONS WI AN AREA BOUNDED BY 413755N1222454W (FJS039020.7) TO 413744N1221353W (FJS048028.1) TO 412716N1221156W (FJS070027.4) TO 411841N1221322W (FJS088027.6) TO 412844N1222437W (FJS065018.0) TO POINT OF ORIGIN SFC-12000FT. TO PROVIDE A SAFE ENVIRONMENT FOR FIRE FIGHTING ACFT OPS. PURSUANT TO 14 CFR SECTION 91.137(A)(2) TEMPORARY FLIGHT RESTRICTIONS ARE IN EFFECT. UNITED STATES FOREST SERVICE SHASTA TRINITY TEL 530-226-2499 OR FREQ 120.025/THE LAVA FIRE IS IN CHARGE OF THE OPS. SEATTLE /ZSE/ ARTCC TEL 253-351-3698 IS THE FAA CDN FACILITY. 2107010300-2109010300EST','!FDC 1/2923 ZSE CA..AIRSPACE 15NM N OF MT. SHASTA, CA..TEMPORARY FLIGHT RESTRICTION. PURSUANT TO 14 CFR SECTION 91.137(A)(2), TEMPORARY FLIGHT RESTRICTIONS ARE IN EFFECT WITHIN AN AREA DEFINED AS 413755N1222454W (FJS039020.7) TO 413744N1221353W (FJS048028.1) TO 412716N1221156W (FJS070027.4) TO 411841N1221322W (FJS088027.6) TO 412844N1222437W (FJS065018) TO THE POINT OF ORIGIN SFC-12000FT MSL EFFECTIVE 2107010300 UTC UNTIL 2109010300 UTC. TO PROVIDE A SAFE ENVIRONMENT FOR FIRE FIGHTING ACFT OPS UNITED STATES FOREST SERVICE SHASTA TRINITY TEL, PHONE 530-226-2499, /FREQ 120.025 IS IN CHARGE OF ON SCENE EMERGENCY RESPONSE ACTIVITIES. 2107010300-2109010300',
to_date('07-JUL-2021 07:23:40','DD-MON-YYYY HH:MI:SS'),to_date('07-JUL-2021 07:23:40','DD-MON-YYYY HH:MI:SS')
);
--------------------------------------------------------------------
---DIMENSION TABLE Create Script
CREATE TABLE EXPORT_TABLE
( ASSET_KEY NUMBER(22,0) NOT NULL ENABLE,
TOWER_NUMBER VARCHAR2(20 BYTE),
ASSET_NUMBER VARCHAR2(100 BYTE),
ASSET_NAME VARCHAR2(400 BYTE),
ASSET_SOURCE VARCHAR2(120 BYTE),
ASSET_TYPE VARCHAR2(120 BYTE),
ASSET_CLASS VARCHAR2(200 BYTE),
ASSET_STATUS VARCHAR2(120 BYTE),
TOWER_LATITUDE NUMBER(22,10),
TOWER_LONGITUDE NUMBER(22,10)
);
--INSERT DATA in to
SET DEFINE OFF;
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046175,'344699','344699','WESTFIELD BROWARD IB FL','Built','Managed Networks','Indoor Network','Active',26.117721,-80.254769);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046176,null,'4164-MN1','La Grange',null,'Monitoring',null,'Inactive',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046177,'412106','412106','Warsaw (SB) IN','Acquired','Tower','Wireless','Active',41.183333,-85.835278);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046178,'15379','15379','ERIE EAST PA','Built','Tower','Wireless','Active',42.13317778,-80.03993333);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046179,null,'276537-LN1','NORTH ZARAGOZA G TX',null,'Land',null,'Active',31.73516,-106.29393);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046180,null,'282693-LL5','Level 5',null,'Lighting Level',null,'Inactive',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046181,null,'374207-LN1','WYAV - Burgess 1',null,'Land',null,'Active',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046182,null,'8627-LL1','Level 1',null,'Lighting Level',null,'Inactive',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046183,null,'85416-MN1','MIDLAND VALLEY SC',null,'Monitoring',null,'Inactive',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046184,'339729','339729','Blanchard House','Acquired','Managed Networks','Rooftop','Inactive',41.06388889,-83.6417);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046167,'204190','204190','CEDAR BLUFF','Acquired','Tower','Wireless','Active',34.221472,-85.609083);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046168,'97034','97034-LS1','GEORGIANA (COVINGTON)',null,'Lighting System',null,'Inactive',31.61605556,-86.73386111);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046169,'419789','419789','Racetrack LA','Acquired','Tower','Wireless','Active',30.3186,-92.020553);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046170,'391153','391153','Edison - Plainfield Road - IP','Acquired','Tower','Wireless','Inactive',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046171,null,'383422-LN1','Quebec Center Center',null,'Land',null,'Inactive',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046172,'413780','413780-LS1','Denman Lands OH',null,'Lighting System',null,'Active',41.417056,-80.711028);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046173,'319334','319334','INNSBROOK CENTRE RT VA','Acquired','Managed Networks','Rooftop','Inactive',37.660917,-77.573952);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046174,'85540','85540','CANNON ROAD SC','Acquired','Tower','Wireless','Active',33.69771111,-78.90253889);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046374,null,'204147-LL3','Level 1',null,'Lighting Level',null,'Active',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (151232360,'89507','89507-LS2','LEVAN UT2',null,'Lighting System',null,'Active',39.6255,111.9088055);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046375,'412191','412191','Cosmos Nursery FL','Acquired','Tower','Wireless','Active',30.063833,-81.927861);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046376,null,'424649-LN1','Atlanta Industrial Building 2 - Graham',null,'Land',null,'Inactive',33.558858,-84.544772);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046377,'392146','392146','The Addison','Acquired','Managed Networks','Rooftop','Active',40.689294,-73.98487);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046378,'81245','81245-LS1','MIDLAND MI',null,'Lighting System',null,'Inactive',43.63611221,-84.29833221);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046379,null,'390214-LN1','St. Louis - Page Service Dr',null,'Land',null,'Inactive',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046380,null,'319506-LN1','DOGWOOD RT NC',null,'Land',null,'Inactive',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046381,'389972','389972','Blaine - Hwy 65 NE','Acquired','Managed Networks','Rooftop','Inactive',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046382,'312778','312778','AKR-COPLEY BR OH','Acquired','Managed Networks','Rooftop','Active',41.111463,-81.65056);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046383,'83306','83306','DELEVAN CA','Acquired','Tower','Wireless','Active',39.28044444,-122.17747222);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046357,'16171','16171','HADAD','Built','Tower','Wireless','Active',37.44669724,-77.397995);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046358,null,'373262-UT1','Stoney Point Battle Mountain',null,'Utilities',null,'Active',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046359,'205050','205050','STRELO Pickens SC SC','Built','Tower','Wireless','In Development',34.88435833,-82.70202611);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046360,'311298','311298','LOVEJOY WV','Acquired','Tower','Wireless','Active',38.3411,-82.06563611);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046361,null,'417090-LL2','Level 1',null,'Lighting Level',null,'Inactive',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046362,'338755','338755','400 Greenbrae Drive','Acquired','Managed Networks','Rooftop','Active',39.54766111,-119.776);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046363,'418238','418238-LS1','Tyler North TX',null,'Lighting System',null,'Inactive',32.390389,-95.247778);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046364,null,'302683-LL1','Level 1',null,'Lighting Level',null,'Active',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046365,null,'392515-LN1','175 Great Neck Rd',null,'Land',null,'Inactive',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046366,'346671','346671','SOUTHWIND OFFICE CTR D IB TN','Built','Managed Networks','Indoor Network','Inactive',35.057505,-89.786179);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046367,null,'4145-LL2','Level 2',null,'Lighting Level',null,'Inactive',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046368,null,'24780-LN1','Allen Creek II',null,'Land',null,'Inactive',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046369,null,'311159-MN1','Greenbo Lake KY',null,'Monitoring',null,'Active',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046370,null,'308029-TS1','Draper Lake ATS1',null,'Transfer Switch',null,'Active',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046371,'308393','308393','Pennypack','Acquired','Tower','Wireless','Active',40.03798333,-75.01704722);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046372,'90303','90303','NACOGDOCHES (OLD TYLER RD)','Acquired','Tower','Wireless','Active',31.62913333,-94.67903333);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046373,'382866','382866','San Jose - Felipe Ave','Acquired','Managed Networks','Rooftop','Inactive',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046663,'304252','304252','Pontiac IL 7','Built','Tower','Wireless','Active',40.90305111,-88.662);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046664,null,'385890-LN1','5409 Buford Highwa',null,'Land',null,'Inactive',0,0);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046665,'383920','383920','Orlando - Maitland - Summit Tower Blvd','Acquired','Managed Networks','Rooftop','Inactive',28.63405555,-81.40286388);
Insert into EXPORT_TABLE (ASSET_KEY,TOWER_NUMBER,ASSET_NUMBER,ASSET_NAME,ASSET_SOURCE,ASSET_TYPE,ASSET_CLASS,ASSET_STATUS,TOWER_LATITUDE,TOWER_LONGITUDE) values (79046666,null,'370806-SH1','Hudson FL',null,'ATC Shelter',null,'Active',0,0);
--------------------------------------------------------------
---DESIRED OUTPUT--this is DUMMY RESULT I HAVE MADE and NOT AN ACTUAL OUTPUT, IT MAY VARY with ACTUAL DATA. SO Row#2 is not actual, you may get NOT AFFECTED also. It will be CROSS JOIN on DUMMY Column like 1=1 and I need to get all Possible Combinations which is inside of VERTICES.
TOWER_NUMBER AFFECTED_TOWER notam_id vertice_LATITUDE vertice_LONGITUDE TOWER_LATITUDE TOWER_LONGITUDE
79046663 N NULL(if Not Affected) NULL(if Not Affected) NULL(if Not Affected) 40.90305111 -88.662
79046665 Y 1/2923 41.63194444 -122.415 28.63405555 -81.40286388
---I was trying the below function but not succeed as it requires an Array and I need to pass the Vertices column Dynamically here
TABLE(sdo_PointInPolygon(
CURSOR(select TOWER_LATITUDE as X, TOWER_LONGITUDE as Y,ASSET_NUMBER,TOWER_NUMBER from dim_asset),
SDO_GEOMETRY(
2003,
NULL,
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1),
MDSYS.SDO_ORDINATE_ARRAY(26, -80,25, -80,40,-80,40,-82,26,-80)),--need to pass vetcies column dynamically
0.05)) )I am also trying to use SDE_INSIDE like below:
select notam_id, asset_number,tower_number,tower_latitude, tower_longitude,
replace((replace(vertices,'[','')),']','') as new_vertices
from tmp_notam_tfr tfr_notam_vertices, dim_asset
where sdo_inside(
sdo_geometry(2001,4326,sdo_point_type(TOWER_LONGITUDE,TOWER_LATITUDE,null),null,null),--convertion of LANG & LAT to SDO_GEOMETRY
sdo_geometry(2003, null, null,
sdo_elem_info_array(1,1003,3),
sdo_ordinate_array(26, -80,25, -80,40,-80,40,-82,26,-80))--not able to convert Entire Array to SDE_GEOMETRY
) = 'TRUE'; --but not succeed