Displaying Spatial Data in SQL Developer

October 23, 2016

oracle, spatial, SQL Developer

There are many ways to display spatial data, but when exploring a new data set in SQL Developer I think the built in Map View is practical. For this demo I used a list of nuclear power stations in the world, found via http://freegisdata.rtwilson.com/ . The Google Fusion Table can be exported to CSV format, and it includes the longitude and latitude which makes it easy to convert to SDO_GEOMETRY. After importing the file with SQL Developer into a table called NUCLEAR, I did this to add native Spatial data:

alter table nuclear add geom sdo_geometry;  
update nuclear set geom=sdo_geometry(2001,  
  8307,  
  sdo_point_type(to_number(substr(location,instr(location,',')+1)),  
    to_number(substr(location,1,instr(location,',')-1)),  
    null),  
  null,  
  null);  

Since I want to try out a spatial operator, I need to add metadata for the column (or layer) and add a spatial index:

insert into user_sdo_geom_metadata(TABLE_NAME,COLUMN_NAME,diminfo,srid)  
  values ('NUCLEAR','GEOM',  
    sdo_dim_array(sdo_dim_element('Longitude',-180,180,3),  
    sdo_dim_element('Latitude',-90,90,3)),8307);  
create index nuclear_si on nuclear(geom) indextype is mdsys.spatial_index  
parameters ('layer_gtype=POINT');  

The parameter is not necessary, but when you use this, Oracle can check that all new data has the same GTYPE and will speed up index creation. Not a big deal here, but useful to know for later.

With this in place I can search for active reactors within a distance of 1000km from my home:

select   name,country  
  ,round(sdo_geom.sdo_distance(geom  
    ,sdo_geometry(2001,8307,sdo_point_type(10.8000994,59.8301125,null)  
      ,null,null)  
  ,10, 'unit=km')) "Distance in km"  
from nuclear  
where sdo_within_distance(geom,  
  sdo_geometry(2001,8307  
    ,sdo_point_type(10.8000994,59.8301125,null),null,null)  
    ,'distance=1000 unit=km') = 'TRUE'  
  and active_reactors>0  
order by 3;  

NAME

COUNTRY

Distance in km

RINGHALS

SWEDEN

297

FORSMARK

SWEDEN

415

OSKARSHAMN

SWEDEN

434

BARSEBECK

SWEDEN

471

OLKILUOTO

FINLAND

604

BRUNSBUETTEL

GERMANY

666

BROKDORF

GERMANY

672

STADE

GERMANY

696

KRUEMMEL

GERMANY

716

UNTERWESER

GERMANY

726

EMSLAND

GERMANY

848

LOVIISA

FINLAND

865

GROHNDE

GERMANY

872

TORNESS

UNITED KINGDOM

891

HARTLEPOOL

UNITED KINGDOM

924

CHAPELCROSS

UNITED KINGDOM

995

A table looks nice, but it sure would be more informative on a map. I added the GEOM column to the query (and removed the others since I don’t need them, and also the ORDER BY). The result is this:

Bring up the menu on the result set (right-click or similar on one of the rows), and select Invoke Map View on result set:

You get something like this:

That is not phenomenal. I had this idea that there should be a map in here. Well, a map with countries and their borders are spatial objects in themselves, and since I had downloaded the data files for the book Pro Oracle Spatial for Oracle Database 11g , I could just display the countries within the same distance:

select geom   
from spatial.world_countries  
where sdo_within_distance(geom,  
  sdo_geometry(2001,8307  
    ,sdo_point_type(10.8000994,59.8301125,null),null,null)  
  ,'distance=1000 unit=km') = 'TRUE';  

and repeat the procedure. I also made this simple query to get an SDO_GEOMETRY object for my own location:

select sdo_geometry(2001,8307,sdo_point_type(10.8000994,59.8301125,null),null,null)   
from dual;  

After changing the order, titles, colors, and zoom, the result look like this:

Also note that I didn’t make any comments about our neighbours ;-)