Sunday, October 23, 2016

Displaying Spatial Data in 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 ;-)

No comments :