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!

thatjeffsmith
Author

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

7 Comments

  1. 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-

    • thatjeffsmith

      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.

  2. 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 ?

    • thatjeffsmith

      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.

  3. 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.

    • thatjeffsmith

      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