11 Geodb Extension (Database Manager)

11 Geodb Extension (Database Manager)

11geoDBExtension(databasemanager)

Thisextensionallowsuserseasy,standardisedaccesstogeographicdatabasesfromdifferentproviders.Atpresent,gvSIGsupportsthefollowingdatabasemanagementsystems:

  • PostGIS
  • MySQL
  • HSQLDB
  • OracleSpatial(SDOGeometry)

gvSIGstoresthedifferentconnectionsmadeduringthevarioussessions.Thus,usersdonotneedtoinputtheparametersofeveryservertheyconnectto.Likewise,ifaprojectfileisopenedwhich has a database connection, the user will only be required to enter thepassword.

Theextensionhastwouserinterfaces,onetomanagethedatasourcesandanothertoaddthelayerstotheview.

11.1Thespatialdatabaseconnectionmanager

SelectthemenuSee–Spatialdatabaseconnectionmanager(figure1)toopenthedialogueboxwhichallowsyoutoadd,remove,connectanddisconnecttheconnectionstothedifferenttypesofdatabasescontaininggeographicinformation.IfyouhavealreadyusedthismanagerinanearliergvSIGsession,thepreviousconnectionswillappear(figure2).Ifnot,thedialogueboxwillbeempty.

Figure1.AccesstothegeoDBconnectionFigure2.Theconnectionmanagermanager

ClickonAddtointroducetheparametersofanewconnection(figure3).

NB:FromgvSIGversion1.1onwards,itshouldbenotedthatthenameofthedatabasemustbewrittencorrectlyandthatitiscasesensitive.IfyouwishtoopenaprojectsavedinaversionpriortogvSIG1.1whichincludeslayersbelongingtoadatabasewhoseconnectionshavenottakenthisfactorintoconsideration,thedatamustberecoveredbyreconnectingtotheoriginaldatabase.

Youcaneitherconnectthereandthenorremainoffline.Openconnectionsappearwithalinkandwith“[C]”beforetheirname(figure4).Ifyouwishtoopenaconnection,selectitandclickonConnect.Youwillbeaskedtoenterthepassword(figure5)andtheconnectionwillthenbemade.

Figure3.AddinganewconnectionFigure4.Theconnectionhasbeenmade

Figure5.Passwordrequest

11.2AddingageoDBlayertotheview

IntheProjectManager,createanewviewandopenitusingtheNewandOpenbuttons.UsetheAddlayericon to add a layer to the view. Go to theGeoDBtabinthedialogueboxtoaddanewlayerofthistype(figure6).

Youmustchooseaconnection(ifyouselectonewhichisdisconnected,youwillthenbeaskedenterthepassword),select one or more tables and the attributes you wish todownloadfromeachlayerand,optionally,setanalphanumericrestrictionandanareaofinterest.Youcangiveeachlayeradifferentnametothatofthetable.ClickonOktoviewthetable’sgeometriesintheview.

Thiswindowalsoallowsyoutospecifyanewconnectionifthedatabaseisnotregisteredinthedatasourcecatalogue.

Any alphanumeric restriction must be introduced by means of a valid SQL expression which is attached as a WHERE clause to each call to the database.

Giventhatthetablemaytakeseveralsecondstoload,asmalliconappearsnexttothenameofthetableindicatingthatthisprocessisunderway.Whenthetablehasbeenloaded,thesmallblueicondisappearsandthegvSIGviewisautomaticallyrefreshedtoallowthegeometriestobeviewed.

Figure6.AddingageoDBlayerFigure7.Viewswithgeometriesfromageographicdatabase

Figure8.Miniiconshowinglayersareloading

11.3ExportingagvSIGlayertoaspatialdatabase

ThisfunctionallowsnewtablestobecreatedinthespatialdatabasefromanyvectorialsourceingvSIG.Thesetablescanbecreatedasfollows:

  1. Create a vectorial layer of any type, for instance by opening an SHP file using theAdd layer button (figure 9).
  2. Selectthelayerbyclickingonitsnameintheleft­handsideofthescreen(figure10).
  3. In the Layer – Export to menu, select the type of database you wish to export thelayer to. The example shows an Oracle database (figure 11).
  4. Youwillthenbeaskedtointroducethenameofthetablewhichwillbecreatedinthedatabase(Oracle)andwhetherornotyouwishtoincludethenewly­createdlayerinthecurrentview.

Ifallgoeswell,thenewvectorialgeoDBlayerwillappearintheviewandyouwillbeabletoworkwithitintheusualway.

Figure9.AddingavectoriallayerFigure10.Selectingthelayertoexport

Figure11.ExportingtoOracleSpatialFigure12.Exportprogressbar

11.4OracleSpatial

ThesenotessupplementthedocumentationforthegeoDBextensionwithregardtothe driverforOracleSpatial.

ThisdriverallowsaccesstoanytablefromaninstallationofbothOracleSpatialandOracleLocator(inbothcasesfromversion9ionwards)which has a column that storesSDO­typegeometries.

11.4.1Metadata

ThedriveronlyliststableswhichhavetheirgeographicmetadataintheUSER_SDO_GEOM_METADATAview.

Giventhateachtable’smetadataisavailable,theinterfacemakes use of that data and automatically presents the column (or columns) of geometries. Likewise, ROWID, which isauniquedescriptorforeachrowusedinternallybyOracle,isusedandthisensuresthatidentificationiscorrect.

11.4.2Datatypes

Twoandthree­dimensionaldataofthefollowingtypesaresupported:

  1. Pointandmultipoint
  1. Lineandmultiline
  1. Polygonandmultipolygon
  1. Collection

Atpresent,layersinLRSformat(LinearReferencingSystem)are not supported.

11.4.3CoordinatesystemsinOracle

Oraclehasitsownsystemforcataloguingcoordinateandreferencesystems.MiguelÁngelManso,onbehalfofthePolytechnicUniversityofMadrid,hasprovidedalistofequivalentvaluesfortheOraclesystemandtheEPSGsystemandthisisincludedinthedriverasaDBFfile.

ConversionsfromonecoordinatesystemtoanotherarecarriedoutbygvSIGsinceitsperformancehasprovedtobesuperior.

11.4.4Notesonreadinggeometries

The driver constantly performs geometric requests (in other words constantly calculates which geometries intersect with the current gvSIG view) and it is therefore essential that the database has a spatial index linked to the column in question. If this index does not exist, an error window appears (figure 1) and the table or view cannot be added to the gvSIG view.

Inaddition,thedriverneedstosetauniqueidentifierfortherecordsofthetableorview,andthisisnotpossibleforcertaintypesofviews.Ifsuchaproblemoccurs,itwillbedetectedbythedriverandanerrormessagewillalsoappear(figure2).Asaresult,theviewcannotbeloadedtogvSIGfromthedatabase.

Figure1.Warningregardingthelackofaspatialindex

Figure2.WarningregardingthefactthataROWIDcouldnotbeobtained

11.4.5TransferringalayerfromgvSIGtoOracle

IfyouwishtoexportalayertoanOracledatabase,youwillalsobeaskedifyouwishtoincludetheview’scurrentcoordinatesysteminthetableattheendoftheprocessdescribedinthemanual.Thismaybeusefulincaseswherewedonotwishtoincludesuchinformationinthetableforreasonsofcompatibilitywithotherapplicationsorinformationsystems.

ToworkwithtwoOraclegeometries(themostcommoncaseisanintersection),thetwogeometriesmusthavethesamecoordinatesystem.EachgeometryhasanSRIDfieldwhichcanhavethevalueNULL.

Forinstance,ifwehaveatablewithgeometriesinEPSG:4326(Oraclecode8307) and anotherwithgeometriesinEPSG:4230(Oraclecode8223),itwillnotbepossibletocarryoutSQLinstructionstoperformcalculationsdirectlybetweenthegeometriesofonetableandanother.However,ifthesetables’geometriesdonothaveacoordinatesystem(i.e.SRIDisNULL),thenoperationscanbeperformedbetweenthegeometriesofthesetables,bearinginmindtheerrorsinvolvedincarryingoutintersectionsbetweendifferentcoordinatesystems.

WhenreadingatablewhosegeometrieshaveacoordinatesystemsetatNULL,itisunderstoodthattheuserwillmakesurethatthegeometriesareappropriateforthecurrentview,sincenoreprojectionispossible(thismaychangewiththenewgvSIGextensionfortheadvanceduseofcoordinatesystems).

Inshort,notstoringthecoordinatesystemallowsforamoreflexibleuseofgeometries.

Top View