My experience with oracle Spatial backs to 2012-2013 when I was working as a database developer in a private company called Negasht Rahavard Sharq. I have been involved in a project for municipality called detailed plan of Tehran. The technologies that were used in the project were ASP.NET, C#.NET, oracle Spatial (DB back-end), Oracle maps (as a web map viewer) and ESRI ArcGIS with ArcSDE to edit the data.
Working with large spatial tables with over 1 million parcels is a challenge even with the best database in the world. My main role was to develop and tune highly optimized spatial queries and save them as PLSQL functions/procedures so the C#.NET developer could call them from the application layer. To write highly performable spatial queries I had to take advantage of Oracle advanced features such as table partitioning, parallel query processing, and spatial data clustering.
Spatial Queries and Tuning
Among many spatial operators and predicates available in oracle Spatial, the most useful operations are buffering, intersect and nearest_neighbour. For example: finding the zoning of a parcel which is the intersection of zoning and parcel tables. Various topological errors were present in the spatial tables that must be taken care of while building the queries, e.g. a parcel overlaps with more that one zoning feature.
A special algorithm in this project was to find the adjacent road segment to a selected parcel to extract the road width and name attributes. We avoided using k nearest neighbor queries because we didn’t know the k and the best search distance in advance. After trying many algorithms we ended up with the following procedure to solve the problem:
- buffer the road layer with a radius double the width of the road features (
R = 2 * Road_Width)
- select from the buffered road using intersection of the parcel and the buffered road table
- extract the road width and name from the selected road features
It turned out that this simple algorithm is giving the best answer compared to other implemented approaches.
Map builder is a Java SE application to create and define symbology for themes (layers), creating Base Maps, Tile Layers and etc. Map builder works mainly with the metadata tables of oracle Spatial so care must be taken while backing up the database tables.
- showing the map, themes (e.g. feature layers), tile layers etc
- creating fancy table of contents (TOC)
- enable feature identification of some themes
- export current map extent as jpeg/png
- simple feature creation and update using redlining
- searching based on the attributes of some layers
- securing spatial objects and layers
Here are some screenshots of the final project: