SQLDeveloper and Map Data

In my post Software Used in Spatial, I listed Oracle SQL Developer as a tool I use when working with Maps and Map data.

Very little has been written with regards to MapView within SQL Developer.  The most recent I could find was a post by Jeff Smith in 2011 Visualize Spatial Data with SQL Developer.

I use SQLDeveloper for a number of purposes when working with Maps.

• Viewing the geometry and attribute/metadata columns that came with the map data.
• Adding or removing attribute columns.
• Creating custom geometry via Spatial Functions.

Viewing Attribute columns

Take for instance the Map Shapefiles that are available from the U.S. Census.  Depending on the file you select you could have a lot of Descriptive Attributes.

The TIGER/Line Shapefile indicates that it is very Descriptive

We can see that there a quite a number of attribute columns.

Some that may be helpful in other exercises are the INTPTLAT and INTPTLON which are the center point (Latitude &longitude) of the state.

Where as the Cartographic Boundary Shapefiles indicate it has limited attribute information.

Adding/Removing Attribute Columns

Regardless of the file used many of the columns are not necessary for what we need in working with maps in OBIEE.  In most cases I delete the not needed columns. and will often add other columns for linking to OBIEE data such as State name in all uppercase. 

In the case of County level maps, if you are not going to use the State & County FIPS ID to link to OBIEE data you may need to concatenate the State Name in front of or at the end of the County Name since there are states that have counties with same name.  It really depends on the data you have in OBIEE.

Viewing Maps in SQLDeveloper

SQLDeveloper allow for a developer to view the Geometry data. 

The Geometry for a single row can be viewed by right clicking in the Geometry data cell and selecting Display geometry shape...

If you noticed in the TIGER/Line shape data there were 2 columns - Region and Division.

If a results set has a geometry column another right click option is available.

 

 This will open the Map View and display the results as a map.

Alternately from the View menu select Map View

 

The Map View window will open and a query can be written to display a map.



By Clicking on the green plus sign we get the query window. 

when the query is executed in the Map View we see that Region 1 is the New England states.

Now if 3 more queries are built for the other Regions we can see the rest of the US.

This leaves us to the last item in my list that I use SQLDeveloper with map data.  And that is the topic for another post...