Spatial data in its raw format is ugly. The shapes and places it represents can be very enlightening. Here’s some raw spatial data followed by their visualization. If you worked with spatial data, what would you prefer? You can probably figure out what you’re looking at, but wouldn’t it be nice if you could get a visual confirmation?

MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(5,1,8,1,8,6,5,7,5,1))
MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(1,1,5,7))
MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1,19,2003,1),MDSYS.SDO_ORDINATE_ARRAY(2,4,4,3,10,3,13,5,13,9,11,13,5,13,2,11,2,4,7,5,7,10,10,10,10,5,7,5))
MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(3,2,4,6,6,4,14,7))
MDSYS.SDO_GEOMETRY(2002,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,4,2,1,2,1,3,2,2),MDSYS.SDO_ORDINATE_ARRAY(10,10,10,14,6,10,14,10))
MDSYS.SDO_GEOMETRY(2003,NULL,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1005,2,1,2,1,5,2,2),MDSYS.SDO_ORDINATE_ARRAY(6,10,10,1,14,10,10,14,6,10))
SDO_GEOMETRY SHAPE Data Visualizations in SQL Developer

To see the shapes, just right-click

Display Geometry Shape translates numbers into pictures!

Map View, What’s that?

Do you remember the kid’s game, Pick Up Sticks? You’d throw a bunch of sticks on the ground and you had to pick them up one at a time following the rules…which I have seemed to have forgotten. Using Oracle SPATIAL I could store each game we play. Here’s how that might look:

I always lost this stupid game anyway.

When you right-click on the data you can also choose to ‘Invoke Map View on result set.’

I know these aren’t really real-world examples, but hopefully it’s good enough to give you an idea of how SPATIAL data representation works in SQL Developer. If you want to load some really cool looking data, try your local government. My county has it’s data available free for the taking.

If you want to use my boring examples, it’s all in the SPATIAL chapter in the Oracle database docs. The lines data came from some free samples published by Simon Greener over at SpatialAdvisors.com.

And here’s the kids game, you can still buy it today!

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

11 Comments

  1. Hello there!
    A couple of suggestions regarding spatial data visualization in SQLDeveloper.

    1) Add Map View as an option in Report Style, so one can have a Report or SubReport as spatial data visualization
    2) Add a “OnMouseOver column” – or something to that effect – so one can show additional info about an object in the Map

    Hope you find these interesting enough for development

    • I believe the spatial team has deprecated the extension you’re seeing. They’ve built a new spatial standalone tool, but. In SQL Developer, there is a 3rd party extension you can look up – GeoRaptor.

    • Jeff, thank you for answering.
      I can still visualize spatial data using SQLDeveloper without any extensions. I hope they don’t rip this from SQLDeveloper, but make it better. It would be awesome to have integrated visualization in reports.

  2. Chris Marx Reply

    Hmm,
    Oddly enough in the latest version (4.1.3.20) the option in the right click context menu to view either individual records or the result set was greyed out. It wasn’t until I manually opened the map view from View->Map View that I could visualize the results. Odd-

    • it’s how java loads jar’s – OSGi – the spatial stuff isn’t avail until you do something for spatial, like open the view > map page.

  3. Hi Jeff,

    In my SQL developer there is no option to view geometry shape but have installed oracle 11 g ..have to enable any options in SQL developer ?

    • What version of SQL Developer are you running? If in version 4, try View > Map View. That will force the library to load, and should make the geometry, right click in the grid to work.

  4. diego weber Reply

    Hello Jeff, congratulations to the blog, sorry for my english but I’ll try. In version 2.1.0 I visualized Spatial data in the following format MDSYS.SDO_GEOMETRY (2001, 29182, NULL, MDSYS.SDO_ELEM_INFO_ARRAY (1,1,1), MDSYS.SDO_ORDINATE_ARRAY (491063.504878304,6688820.29609558)) highlighting colors in elements that compose the geometrical data. In version 3.2.20.09 I visualize spatial data in the following format [MDSYS.SDO_GEOMETRY] without spatial information. How do I set that has syntax highlighting in geometry? Thank you.

    • I’m sure your English is much better than my…Portuguese? I’ll take a look at this first thing Monday morning, US time!

    • diego weber

      Hello Jeff, yeah, Portuguese from Brazil. Thanks for attention.

Write A Comment