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 ;-)