X

Spatial Visualization - technical tips, best practices, and news from the product team

  • July 18, 2016

Script to add missing metadata to mvdemo (12.2.1.1.0) schema for DTL examples

Jayant Sharma
Sr. Director, Product Mgmt
-- Note: if the quotes (single or double) are converted to smart-quotes on copy-n-paste then the 
-- script may have to be manually edited to fix that
-- execute the following SQL statements in sqlplus or SqlDeveloper as the mvdemo2 (or nedata) user
-- to add the required theme, map, and tile layers for the Dy Tile Layer examples
-- set define off is needed so that the ampersand in > is not treated as a substitution variable
set define off;
insert into user_sdo_themes values ('UTFGRID_THEME_DEMO_CITIES', 'for utfgrid testing', 'CITIES_US', 'LOCATION',
'<?xml version="1.0" standalone="yes"?>
<styling_rules>
<hidden_info>
<field column="CITY" name="City"/>
<field column="POP90" name="Population"/>
</hidden_info>
<rule>
<features style="M.ALL_CITY_L2"> (pop90 between 200000 AND 1000000 ) </features>
<label column="city" style="T.S07_CITIES_L2"> 1 </label>
</rule>
<rule>
<features style="M.ALL_CITY_L3"> (pop90 between 0 and 200000) </features>
<label column="city" style="T.S07_CITIES_L3"> 1 </label>
</rule>
</styling_rules>') ;
insert into user_sdo_themes values ('UTFGRID_THEME_DEMO_COUNTIES', 'for utfgrid testing', 'COUNTIES', 'GEOM',
'<?xml version="1.0" standalone="yes"?>
<styling_rules>
<hidden_info>
<field column="COUNTY" name="County"/>
<field column="POPPSQMI" name="PopulationDensity"/>
<field column="TOTPOP" name="Population"/>
<field column="STATE_ABRV" name="State"/>
</hidden_info>
<rule>
<features style="L.S06_BORDER_STATE"> </features>
</rule>
</styling_rules>') ;
insert into user_sdo_themes values ('UTFGRID_THEME_DEMO_STATES', 'for utfgrid testing', 'STATES', 'GEOM',
'<?xml version="1.0" standalone="yes"?>
<styling_rules>
<hidden_info>
<field column="STATE" name="State"/>
<field column="STATE_ABRV" name="Abrv."/>
<field column="TOTPOP" name="Population"/>
</hidden_info>
<rule>
<features style="C.S02_COUNTRY_AREA"> </features>
<label column="STATE_ABRV" style="T.S02_STATE_ABBREVS"> 1 </label>
</rule>
</styling_rules>') ;
insert into user_sdo_themes values ('UTFGRID_THEME_DEMO_HIGHWAYS', 'for utfgrid testing', 'INTERSTATES', 'GEOM',
'<?xml version="1.0" standalone="yes"?>
<styling_rules>
<hidden_info>
<field column="HIGHWAY" name="Highway"/>
<field column="ROUTEN" name="No."/>
</hidden_info>
<rule>
<features style="L.S04_ROAD_INTERSTATE"> </features>
<label column="routen" style="M.HWY_USA_INTERSTATE_NARROW"> (3-length(routen)) </label>
</rule>
</styling_rules>') ;
insert into user_sdo_themes values ('THEME_US_DYN_HIGHWAYS', 'dynamic theme for utfgrid testing', 'INTERSTATES', 'GEOM',
'<?xml version="1.0" standalone="yes"?>
<styling_rules key_column="ROWID" caching="NORMAL">
<hidden_info>
<field column="HIGHWAY" name="Highway"/>
<field column="ROUTEN" name="Number"/>
</hidden_info>
<rule>
<features style="L.PH"> ROUTEN=:1 </features>
<label column="HIGHWAY" style="T.AIRPORT NAME"> 1 </label>
</rule>
</styling_rules>') ;
insert into user_sdo_themes values ('THEME_US_DYN_COUNTIES', 'dynamic theme for utfgrid testing', 'COUNTIES', 'GEOM',
'<?xml version="1.0" standalone="yes"?>
<styling_rules key_column="ROWID" caching="NORMAL">
<rule>
<features style="C.COUNTIES"> COUNTY=:1 </features>
<label column="COUNTY" style="T.AIRPORT NAME"> 1 </label>
</rule>
<rule>
<features style="C.RED"> TOTPOP&gt;:2 </features>
<label column="COUNTY" style="T.PARK NAME"> 1 </label>
</rule>
</styling_rules>') ;
insert into user_sdo_themes values ('THEME_US_DYN_STATES', 'dynamic theme for utfgrid testing', 'STATES', 'GEOM',
'<?xml version="1.0" standalone="yes"?>
<styling_rules key_column="ROWID" caching="NORMAL">
<rule>
<features style="C.RED"> state_abrv=:1 </features>
<label column="STATE" style="T.PARK NAME"> 1 </label>
</rule>
</styling_rules>') ;
insert into user_sdo_maps values ('COUNTY_UTFGRID_BASEMAP', null,
'<?xml version="1.0" standalone="yes"?>
<map_definition>
<theme name="THEME_DEMO_COUNTY_POPDENSITY"/>
<theme name="THEME_DEMO_STATES_LINE" label_min_scale="Infinity" label_max_scale="1.6E7" scale_mode="RATIO"/>
<theme name="THEME_DEMO_HIGHWAYS_LINE"/>
<theme name="THEME_DEMO_CITIES" min_scale="1.6E7" max_scale="0.0" scale_mode="RATIO"/>
</map_definition>') ;
insert into user_sdo_maps values ('UTFGRID_BASEMAP', null,
'<?xml version="1.0" standalone="yes"?>
<map_definition>
<theme name="UTFGRID_THEME_DEMO_STATES" min_scale="1.0e20" max_scale="0.0" scale_mode="RATIO"/>
<theme name="UTFGRID_THEME_DEMO_HIGHWAYS" min_scale="1.0e20" max_scale="0.0" scale_mode="RATIO"/>
<theme name="UTFGRID_THEME_DEMO_CITIES" min_scale="7.5e6" max_scale="0.0" scale_mode="RATIO"/>
</map_definition>') ;
insert into user_sdo_cached_maps values('UTFGRID_TL', null, null, 'YES', 'YES',
'<?xml version = "1.0" encoding = "UTF-8"?>
<map_tile_layer concurrent_fetching_threads="3" fetch_larger_tiles="false" http_header_expires="168.0" image_format="PNG" name="UTFGRID_TL" utfgrid="true" utfgrid_resolution="4">
<internal_map_source base_map="UTFGRID_BASEMAP" bgcolor="none" data_source="MVDEMO" db_tile_table=""/>
<tile_storage root_path="/temp/MVDEMO.UTFGRID_TL/" xyz_storage_scheme="true"/>
<coordinate_system maxX="180.0" maxY="90.0" minX="-180.0" minY="-90.0" srid="8307"/>
<tile_image height="256" width="256"/>
<tile_dpi value="90.7142857"/>
<tile_meters_per_unit value="111319.49079327358"/>
<zoom_levels levels="19" max_scale="5.590822640287178E8" min_scale="2132.729583849784" min_tile_height="360.0000000566929" min_tile_width="0.0013732910158412662">
<zoom_level description="" level="0" name="" scale="5.590822640287178E8" tile_height="360.0000000566929" tile_width="360.0000000566929"/>
<zoom_level description="" level="1" name="" scale="2.795411320143589E8" tile_height="180.00000002834645" tile_width="180.00000002834645"/>
<zoom_level description="" level="2" name="" scale="1.3977056600717944E8" tile_height="90.00000001417322" tile_width="90.00000001417322"/>
<zoom_level description="" level="3" name="" scale="6.988528300358972E7" tile_height="45.00000000708661" tile_width="45.00000000708661"/>
<zoom_level description="" level="4" name="" scale="3.494264150179486E7" tile_height="22.500000003543306" tile_width="22.500000003543306"/>
<zoom_level description="" level="5" name="" scale="1.747132075089743E7" tile_height="11.250000001771653" tile_width="11.250000001771653"/>
<zoom_level description="" level="6" name="" scale="8735660.375448715" tile_height="5.6250000008858265" tile_width="5.6250000008858265"/>
<zoom_level description="" level="7" name="" scale="4367830.1877243575" tile_height="2.8125000004429133" tile_width="2.8125000004429133"/>
<zoom_level description="" level="8" name="" scale="2183915.0938621787" tile_height="1.4062500002214566" tile_width="1.4062500002214566"/>
<zoom_level description="" level="9" name="" scale="1091957.5469310894" tile_height="0.7031250001107283" tile_width="0.7031250001107283"/>
<zoom_level description="" level="10" name="" scale="545978.7734655447" tile_height="0.35156250005536416" tile_width="0.35156250005536416"/>
<zoom_level description="" level="11" name="" scale="272989.38673277234" tile_height="0.17578125002768208" tile_width="0.17578125002768208"/>
<zoom_level description="" level="12" name="" scale="136494.69336638617" tile_height="0.08789062501384104" tile_width="0.08789062501384104"/>
<zoom_level description="" level="13" name="" scale="68247.34668319309" tile_height="0.04394531250692052" tile_width="0.04394531250692052"/>
<zoom_level description="" level="14" name="" scale="34123.67334159654" tile_height="0.02197265625346026" tile_width="0.02197265625346026"/>
<zoom_level description="" level="15" name="" scale="17061.83667079827" tile_height="0.01098632812673013" tile_width="0.01098632812673013"/>
<zoom_level description="" level="16" name="" scale="8530.918335399136" tile_height="0.005493164063365065" tile_width="0.005493164063365065"/>
<zoom_level description="" level="17" name="" scale="4265.459167699568" tile_height="0.0027465820316825325" tile_width="0.0027465820316825325"/>
<zoom_level description="" level="18" name="" scale="2132.729583849784" tile_height="0.0013732910158412662" tile_width="0.0013732910158412662"/>
</zoom_levels>
</map_tile_layer>', 'UTFGRID_BASEMAP', null);
insert into user_sdo_cached_maps values('COUNTY_UTFGRID_TL', null, null, 'YES', 'YES',
'<?xml version = "1.0" encoding = "UTF-8"?>
<map_tile_layer concurrent_fetching_threads="3" fetch_larger_tiles="false" http_header_expires="168.0" image_format="PNG" name="COUNTY_UTFGRID_TL" utfgrid="true" utfgrid_resolution="4">
<internal_map_source base_map="COUNTY_UTFGRID_BASEMAP" bgcolor="none" data_source="MVDEMO" db_tile_table=""/>
<tile_storage root_path="/temp/MVDEMO.COUNTY_UTFGRID_TL/" xyz_storage_scheme="true"/>
<coordinate_system maxX="180.0" maxY="90.0" minX="-180.0" minY="-90.0" srid="8307"/>
<tile_image height="256" width="256"/>
<tile_dpi value="90.7142857"/>
<tile_meters_per_unit value="111319.49079327358"/>
<zoom_levels levels="12" min_scale="858.0000000000007" max_scale="3.0289566E8" min_tile_width="5.220577239966284E-4" max_tile_width="184.2995557902756">
<zoom_level level="0" name="level0" description="" scale="3.0289566E8" tile_width="184.2995557902756" tile_height="184.2995557902756"/>
<zoom_level level="1" name="level1" description="" scale="9.483028858852641E7" tile_width="57.70033173248195" tile_height="57.70033173248195"/>
<zoom_level level="2" name="level2" description="" scale="2.9689377635134164E7" tile_width="18.064765635285003" tile_height="18.064765635285003"/>
<zoom_level level="3" name="level3" description="" scale="9295122.44960365" tile_width="5.655699848846204" tile_height="5.655699848846204"/>
<zoom_level level="4" name="level4" description="" scale="2910108.2014895976" tile_width="1.770681193768741" tile_height="1.770681193768741"/>
<zoom_level level="5" name="level5" description="" scale="911093.9409666553" tile_width="0.5543632041587065" tile_height="0.5543632041587065"/>
<zoom_level level="6" name="level6" description="" scale="285244.43484309333" tile_width="0.17355951099870603" tile_height="0.17355951099870603"/>
<zoom_level level="7" name="level7" description="" scale="89304.05960402885" tile_width="0.05433784860202624" tile_height="0.05433784860202624"/>
<zoom_level level="8" name="level8" description="" scale="27959.231057906272" tile_width="0.017012042576674106" tile_height="0.017012042576674106"/>
<zoom_level level="9" name="level9" description="" scale="8753.449785099405" tile_width="0.005326114302946117" tile_height="0.005326114302946117"/>
<zoom_level level="10" name="level10" description="" scale="2740.521832720056" tile_width="0.001667494860784266" tile_height="0.001667494860784266"/>
<zoom_level level="11" name="level11" description="" scale="858.0000000000007" tile_width="5.220577239966284E-4" tile_height="5.220577239966284E-4"/>
</zoom_levels>
</map_tile_layer>', 'COUNTY_UTFGRID_BASEMAP', null);
update user_sdo_themes set styling_rules =
'<?xml version="1.0" standalone="yes"?>
<styling_rules key_column="FIPSSTCO" fetch_size="500">
<hidden_info>
<field column="COUNTY" name="County"/>
<field column="STATE_ABRV" name="State"/>
<field column="POPPSQMI" name="Pop_per_sq_mile"/>
<field column="TOTPOP" name="TotalPopulation"/>
</hidden_info>
<rule column="TOTPOP">
<features asis="true" style="V.COUNTY_POP_DENSITY"> </features>
</rule>
</styling_rules>'
where name = 'THEME_DEMO_COUNTY_POPDENSITY';

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha