wtorek, 22 października 2013

Updating a field in a Geodatabase

I wanted to update in ArcGIS a point feature class with values representing polygons ID's in which the points are located:



I could do this by means of "Join Data" tool based on spatial location (ArcMap > Table of content > local menu > Joins and Relates > Join...). But this tool creates a new feature class which I do not need.

I could use "Spatial Join" tool from the ToolBox but then also a new feature class  is created ("The output must be unique from the input.").

Maybe there's a way in ArcGIS to just update like I wanted but I couldn't find it (some people say there isn't - see:

So I run SQL Server client and typed:

UPDATE Geobaza_08.dbo.PUNKT_ODWIERT
SET OBSZAR_ID = ob.Obszar_ID
FROM Geobaza_08.dbo.PUNKT_ODWIERT po, Geobaza_08.dbo.OBSZAR_BADAN ob
WHERE po.SHAPE.STIntersects(ob.SHAPE) = 1
;


I refreshed the table and I got what I needed:


I tried to make this in ArcGIS without using pure SQL. So I used Python and Arcpy (there should be an edit session opened before executing the following script):


#create update cursor 
uc = arcpy.da.UpdateCursor("PKTO",("OBSZAR_ID","SHAPE@","OBJECTID")) 
#iterate on points
for pkt in uc: 
     #select a polygon containing the point
     arcpy.SelectLayerByLocation_management("OBSZ","CONTAINS",pkt[1]) 
     #create select cursor from the selection
     obs = arcpy.SearchCursor("OBSZ")   
     #iterate on the select cursor 
     for ob in obs: 
          #write the selected polygon id into point field
          pkt[0] = ob.getValue("Obszar_ID")
          #update row in update cursor
          uc.updateRow(pkt)


Hmm... I rather prefer simple SQL update in the DBMS.

środa, 24 lipca 2013

Oracle - spatial join

Regarding spatial operations like SDO_ANYINTERACT and other you should remember: only one parameter can be a layer (spatially indexed geometry column), the other must represent one spatial object.

Let's try:


I want to know which objects from LAYER_1 has "anyinteract" realationship with the object 103 from LAYER_2.

select id
from layer_1
where SDO_ANYINTERACT(
        geom
        ,
        (select geom from layer_2 where id = 103)
        ) = 'TRUE'
;

        ID
----------
         1
         3

As soon as the second parameter represents one object we'll get the result. But if there are more objects (in this case we'll take two: object 102 and 103) – we won't. Let's try:

select id
from layer_1
where SDO_ANYINTERACT(
        geom
        ,
        (select geom from layer_2 where id in (103,102))
        ) = 'TRUE'
;


ORA-29902: error in executing ODCIIndexStart() routine
ORA-01427: single-row subquery returns more than one row
29902. 00000 -  "error in executing ODCIIndexStart() routine"
*Cause:    The execution of ODCIIndexStart routine caused an error.
*Action:   Examine the error messages produced by the indextype code and
           take appropriate action.

Proper way to do it is the spatial join as follows:

select x.id
from layer_1 x
  join layer_2 y on (SDO_ANYINTERACT(x.geom,y.geom) = 'TRUE')
where y.id in (103,102);


        ID
----------
         1
         3
         5

All the 'anyinteract' relationships we obtain by the following SQL:

select x.id LYR_1, y.id LYR_2
from layer_1 x
  join layer_2 y on (SDO_ANYINTERACT(x.geom,y.geom) = 'TRUE')
;

     LYR_1      LYR_2
---------- ----------
         1        103
         2        101
         3        103
         5        102

We can also display what exact type of relationship is this:

select
    x.id LYR_1
  , y.id LYR_2
  ,SDO_GEOM.RELATE(x.geom, 'determine', y.geom, 1e-2) as REL
from layer_1 x
  join layer_2 y on (SDO_ANYINTERACT(x.geom,y.geom) = 'TRUE')
;

    LYR_1      LYR_2         REL                      
---------- ----------------- -------------------------
         1        103        OVERLAPBDYINTERSECT 
         2        101        CONTAINS                
         3        103        OVERLAPBDYINTERSECT     
         5        102        OVERLAPBDYINTERSECT   




wtorek, 25 września 2012

DatabaseError: Invalid handle! while reading LOB in Python/cx_Oracle


I've spend much time on this:


orcl = cx_Oracle.connect(usrpass+'@'+dbase)
c = orcl.cursor()
c.execute(sq)
dane =  c.fetchall()

orcl.close() # before reading LOB to str

wkt = dane[0][0].read()

And I get: DatabaseError: Invalid handle!

But the following code works, because connection is closed after reading LOB to STR:
 
orcl = cx_Oracle.connect(usrpass+'@'+dbase)
c = orcl.cursor()
c.execute(sq)
dane =  c.fetchall()

wkt = dane[0][0].read()

orcl.close() # after reading LOB to str

[http://stackoverflow.com/questions/8646968/how-do-i-read-cx-oracle-lob-data-in-python/12590977#12590977]

piątek, 30 września 2011

Fork/Join under a condition

After an Activity 1 there is an Activity 2 and - under a certain condition - simultaneously Activity 3. After Act.2 and Act.3 are finished or after only Act.2 is finished (if the condition is not fulfilled) there is an Act.4. The problem is how to model this on an Activity diagram? 

Below there are two Act. diagrams showing this situation:


The above diagram comprises a problem. What if the condition is not fulfilled? How long should we wait after Act.2 to start  Act.4?


















This digram shows the solution: we should use decision diamond representing the condition. If the condition is not fulfilled the flow goes to the Join and waits for the end of Act.2 then flow goes to Act.4







czwartek, 7 kwietnia 2011

How to count size od tables in Oracle Spatial?

Generally:

SELECT round((bytes/1024/1024/1024),1) size_GB
FROM dba_segments
WHERE owner = <OWNER_NAME> and segment_name = <TABLE_NAME>;

Use the above select for:
1) Table itself.

2) Indexes for the table (put them as <TABLE_NAME> in the above statement). You can retrieve them as follows:

2.1) Indexes:

SELECT index_name
FROM all_indexes
WHERE table_owner = <OWNER_NAME>and table_name = <TABLE_NAME>;

2.2) Spatial indexes:

SELECT sdo_index_table
FROM all_sdo_index_info
WHERE table_owner = <OWNER_NAME>and table_name = <TABLE_NAME>;

środa, 2 marca 2011

Dictionary iteration - ValueError: too many values to unpack

If you get the following error trying to iterate on a dictionary:
 
ValueError: too many values to unpack
 
you have forgotten add iteritems() method:
 
for k,v in dict.iteritems():
    print k, v