GIS for Oracle

walidabdallah · ‎05-12-2014

in my relational data base i have the following table,

Tableau: Clim data

Region_id      --- temperature
SD                  --- 20
SD                  --- 25
SD                  --- 30
SD                  --- 33
SD                  --- 35
.....
Bi                  --- 15
Bi                  --- 19
Bi                  --- 20
......

this table shows the values of temperature for the two provinces(SUD sd, Beirut bi).

on the other side, these two provinces (SUD sd, Beirut bi) has a geographical reference defined in the spatial database that constitutes the
map of Lebanon at provinces level with the same identification (sd,bi) respectively.

the spatial database that constitutes the map of Lebanon at Province level which, by its turn, is digitalized through ArcGis software and saved as LebMap.shape.

my main objective is :

i need to know if there are PLSQL packages, procedures, functions or any Oracle tools through which i achieve two tasks:

1) link the two relational and spatial databases by the identification(sd, bi)

2) select available data in the CLIM table to diffuse or disseminate them on Lebanon map at provincial level (SUD , BEIRUT) represented by
LebMap.shape, and classify them by colors; for example:
   20-30 blue color on the map
   31-40 Red color on the map.

   Etc ...

I thank and appreciate whoever gives me any suggestion.

software that i am using :
           Oracle Enterprise Manager Version 9.2.0.1.0, Forms [32 Bit] Version 9.0.2.9.0 ,
           oracle JInitiator: 1.3.1.9, WebUtil 1.0.2(Beta), windowXp Service pack3 , Internet Explorer 8.

JamesCrandall · ‎05-12-2014

Assuming that your ClimateData table is non-spatial, not registered with ArcSDE --- we utilize the cx_Oracle library for integrating non-spatial Oracle schemas/db's/tables/views/etc...

Basically it goes like:

1. Fill cx_Oracle cursor with a parameterized SQL statement that executes against the table/view and fills the cursor with the results.
2. Convert the cursor to a NumPy Array, then to a table (arcpy.da.NumPyArrayToTable)
3. Join the table to the Feature Class.
4. Create a new layer with the joined attributes.
5. Symbolize the new output FC using the attributes joined.

Also, the majority of this occurs in_memory workspace and just the output FC is written to disk somehwere so it makes things easy to cleanup.

walidabdallah · ‎05-12-2014

thank you Mr for your support your suggestions were very helpful.

about the software that I am using
Oracle Enterprise Manager Version 9.2.0.1.0, Forms [32 Bit] Version 9.0.2.9.0 ,
windowXp Service pack2 , Internet Explorer 8.

I would like to ask you:
can you inform me about the version of cx_Oracle and Python that i should install that are compatible with the above mentioned softwares? also what are the steps that i must execute to access Oracle from Python, and use cx_oracle with python ?? is it obligate to install ArcGIS if yes what is the version that I should install??
thank you very much.

JamesCrandall · ‎05-13-2014

can you inform me about the version of cx_Oracle and Python that i should install that are compatible with the above mentioned softwares?

I have no idea.

also what are the steps that i must execute to access Oracle from Python, and use cx_oracle with python ?? is it obligate to install ArcGIS if yes what is the version that I should install??
thank you very much.

There's tons of examples with a simple google search.

Here's some snipets from an implementation we have (it's been modified for posting here and is for generic example only)

### Build a DSN (can be subsitited for a TNS name)     
dsn = cx_Oracle.makedsn("myconn", instance, schema)     
oradb = cx_Oracle.connect("User", "Password", dsn)     
cursor = oradb.cursor()

_sqlQry = """SELECT table1.Field1, table1.Field2 FROM table1 WHERE table1.Field1 IN (""" + parameter1 + """)""""

cursor.execute(_sqlQry)
datArray = []
  
cxRows = cursor.fetchall()
for cxRow in cxRows:
   datArray.append(cxRow)

#close the conn to ora
cursor.close() 
oradb.close()
del cxRows, cursor

#I also incorporate the Pandas library for doing computations and table operations
DF = DataFrame(datArray, columns=['field1', 'field2'])

#convert pandas DataFrame result into a numpyarray
dfar = DF.to_records()
nmpyar = np.array(dfar, np.dtype([('field1', '|S25'), ('field2', '<f8')]))

##now covert the numpyarray to the gdb table in the default.gdb  
arcpy.da.NumPyArrayToTable(nmpyar, r"H:\Documents\ArcGIS\Default.gdb\numpytab")

walidabdallah · ‎05-15-2014

Hi Mr
when i try to install cx_Oracle-5.1-11g.win-amd64-py3.1 i am getting the following error message :
this installation package is not supported by this processor..

i don't now how to overpass this error, can you help me to overpass it ??
thank you again.

softwares: WindowsXp professional service pack 2, Oracle Enterprise Manager Version 9.2.0.1.0,

JamesCrandall · ‎05-16-2014

https://www.google.com/search?q=this+installation+package+is+not+supported+by+this+processor&rls=com...

http://stackoverflow.com/questions/18275802/cx-oracle-module-installation-error-message