01 March 2008

Oracle to PostGres Conversion : Delete Row command is not deleting rows

The topic sounds funny. But yeah, the delete row command was not deleting the row as i expected it to be. Luckily i found the solution for the same.
But first, let me post my problem.
I was doing a simple delete operation over some table..

Say for example,
select count(*) from some_table_x where some_col_name = 'some_value'

This query was returning me a value of 1 which meant is exactly one row that matches this clause. I tried firing a delete query like the one below,
delete from some_table_x where some_col_name = 'some_value'

Ofcourse, the output i expected was that the row to get deleted. But instead, i was informed by postgres as "0 rows deleted. Query executed successfully"

This was very annoying as postgres was not throwing any kind of error and my delete query had the 'where' clause same as the select query i used. The only possible place where the problem might haved occured was the triggers. Even constraints can prevent the row from getting deleted. But the delete operation would have intimated me some kindof error.

So, i concluded only triggers may have this issue. When i went through my trigger, it had a code something like this,
CREATE TRIGGER sample_trig
BEFORE INSERT OR UPDATE OR DELETE
ON some_table_x
FOR EACH ROW
EXECUTE PROCEDURE "TRGR_SAMPLE"();

And the trigger function was something like,
CREATE OR REPLACE FUNCTION "TRGR_SAMPLE"()
RETURNS "trigger" AS
$BODY$
BEGIN
IF TG_OP='DELETE'
THEN
-- some operations
END IF;

IF TG_OP='UPDATE'
THEN
-- some operations
END IF;

IF TG_OP='INSERT'
THEN
-- some operations
END IF;

RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

The trigger was a BEFORE EACH ROW trigger. It will be fired everytime when one of insert or delete or update operation occurs on some_table_x. The bug in this code was that i was returning NEW at the end of the trigger statement. Whereas in oracle, no one need to worry about what a trigger returns, only need to worry about what a trigger does. But not so in Postgres, you have to write a trigger that calls a trigger function which ofcourse should return either NULL or NEW or OLD.
Since a delete operation does not have a NEW RECORD type, returning NEW does not make any sense at all during a delete operation. So, a small addition to the code really solved the issue.
CREATE OR REPLACE FUNCTION "TRGR_SAMPLE"()
RETURNS "trigger" AS
$BODY$
BEGIN
IF TG_OP='DELETE'
THEN
-- some operations
RETURN OLD;
END IF;


IF TG_OP='UPDATE'
THEN
-- some operations
END IF;

IF TG_OP='INSERT'
THEN
-- some operations
END IF;

RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Although the problem and the solution sounded very simple, finding out that sucked up a lot of time as there was no error been thrown by postgres.

06 February 2008

Successfully Emulated Oracle's connect by prior to postgresql using Java

Friends,

Today, I was successfully able to simulate Oracle's "connect by prior" equivalent SQL to postgresql with a little bit of help from Java. I was fed up with finding a decent solution in the net for using a similar query to connect by prior. I found many options. Top of them being Evgen's connect by patch for Postgresql 8.2/8.3. But that patch comes with a tarball which i need to setup/install which i dont want to do. Another one is using some crawl_tree method given some by guy. That never even came close to working. I was really fed up and finally switched over to my home ground : JAVA. Yeah, connect by prior method simply follows the Depth first search algorithm. You can see the example picture given in the side representing some hierarchical arrangement of data. The Depth first search has to follow the order : D L J I H I A E A I J L K G K F K L D C B. Just trace this path and you will know how this works. After Eliminating the redundant values you will get : DLJIHAEKGFCB which is what connect by does finally.

So implementing this algorithm is a piece of cake and i did too. What i did is, selected all the records from the table "select * from (sometable)". Stored all the values in an ArrayList. Then using two main columns (parent/child Node) , i applied this algorithm. It perfectly worked for me. It gave the exact result what oracle would have given. I even cross verified them side by side using Aquastudio connected to both Oracle and PostgresDB. I guess this idea should be enough in implementing the logic. If you even want the full code, i can do that for ya.
Bye for now from,
Braga

Oracle Spatial query windowing technique

This again is one of the problems i faced while coding in oracle 10g.
I will post both the problem and the solution for it..

Dear friends,
The database i'm using (mvdemo - mapviewer demo DB that comes along with Oracle 10g), has only data about united states. Say if i have my query window like this, -180, 0, 180 , 90 (which is the upper half of the earth), i am getting the desired result. ie.,Here is that query,

SELECT count(s.geom) FROM states s WHERE SDO_FILTER(s.geom,SDO_GEOMETRY(2003,8265,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(-180, 0 , 180, 90))) = 'TRUE';
--Returns 55
I'm currently having a problem with my rectangular window query. When i query with a window value of -180, -90, 180, 90 (which covers the whole earth), the number of spatial objects (SDO_GEOM) that is returned is 0. It is not throwing any ORA error. But the expected result is as usual 55.

SELECT count(s.geom) FROM states s WHERE SDO_FILTER(s.geom,SDO_GEOMETRY(2003,8265,NULL,SDO_ELEM_INFO_ARRAY(1,1003,3),SDO_ORDINATE_ARRAY(-180, 0 , 180, 90))) = 'TRUE';
--Returns 0
Can anyone please help me out with this issue??

Solution
I found the solution to my problem. actually the query given here works just fine.wat i was doing was using the sdo_intersection(, ). The query window am using here is the whole earth. But according to spatial rule, there cant be any geometry greater than half the surface of the earth.
Yes.. its pretty true. Imagine yourself trying to calculate the distance from Pearl harbor to japan. For a small kid, if you give the map and ask him to measure, he'l tell the longest route in the conventional map. But it is not so. We have to calculate the shortest distance which ofcourse will only be possible when you see the earth as a sphere. Oracle 10g is no exception. The design is logically correct. So any query window given exceeds half the size of the earth, oracle will throw an error.

05 February 2008

SWT / Java2D setTransform() problems!!!!

I will post one of the problems that i faced while i was in the design phase and was really confused about the strange features of RCP. That time, i wasnt knowing the solution and only few may have come across one. Eventually, i found the answer.



I may not know how many have you have come across this bug or error or impotentness..
Its between Java2d and swt..

Here is the snippet of java2d version

public void paint(Graphics g){
AffineTransform transform = new AffineTransform();
transform.scale(10,10);//any scale value am specifying
transform.translate(100,100);//any translation value

((Graphics2D)g).setStroke(new BasicStroke(0.0001f));
((Graphics2D)g).setTransform(transform);

g.draw( );//say a general path
}


Here is the swt version for the same
public void paintControl(PaintEvent e){
Transform transform = new Transform(e.getDevice());
transform.scale(10,10);//any scale value am specifying
transform.translate(100,100);//any translation value

e.gc.setLineWidth(1);//This is the least am able to specify
e.gc.draw(path);//Here path is org.eclipse.swt.graphics.Path
}


Both code looks almost the same. The basic problem here is this. In the former case, say i am giving a state boundary for the path and i am scaling the same. It is working just fine. (State boundary is the boundary of say Florida which might have latitude and longitude values like -83.45645631, 22.45646413)

But not so in SWT. In swt, the value of path variables are rounded off or casted to int and then the scale is applied which is a serious problem!!!!. Whereas, i need a mechanism where the scale is first applied and then it is casted to int. Moerover, the setLineWidth() method is taking a value of minimum 1 which when scaled to say 10 times, the thickness also is proportionately increasing.

This is killing me for the past one week and i cannot make any progress in this issue. If someone knows the internals or how to overcome this issue, kindly reply!!!!

Thanks in advance.

Braga




The solution to the above problem was simple : There is no solution.

Yes, At the time i was trying to create the application in RCP, it wont support Vector graphic features, which means you will not be able to draw images on the screen with double precision values. This was a huge setback for me. I thought of designing my full application in RCP and had a thorough understanding of the beautiful eclipse framework until i saw an IEEE paper presented by some college grads claiming the cons of RCP being its lack supportive towards Eclipse's RCP.

An application from the scratch

I have designed an application purely using java that has lots of spatial features. I had doubt of whether to use good old java swings or some other UI such as RCP. Well, I started my application in RCP first. The one good thing I did while creating it was the framework. I made it independent of the front end (doesn’t matter what UI you are gonna use, the core logic remains the same). What I mean to say is that, there are these map viewing/editing functionalities which do not depend on what UI being used. For example, take the simple zoom functionality. If I am going to zoom to some part of my map in the front end, all that has to happen in the background is to query for the newly zoomed area and send it back to the front end. Now, it can be sent to anywhere. It may be a swing front end or Eclipse RCP or even a C++ UI or you can even send it as a response to a HTTP request. That’s how flexible your app should be. I will provide as many details about the one I have done.

04 February 2008

Kick off

When I initially launched this website I thought I would fully focus on geo spatial applications but it was not a big hit. The following article was the first one I wrote regarding it and unfortunately it was the last one too as the ship turned direction towards DSA and Java. However, for memoir, I am keeping it.

Let me kick off with the basic understaing needed about a spatial application.

Your spatial application may be a,
1) Basic standalone version dealing with basic map viewing features such as zoom, pan etc which can be used for basic viewing purposes alone.
2) Advanced standalone version which combines the basic features along with area marking, advanced searching (searching with buffers), cartographic features etc.,
3) Web application which has a common server that serves a variety of clients who need to work on a particular region/area. It can even include ajax and web 2.0 capabilites.
Requirements are endless.

But why people really need all these? Why do they need a map viewing application?
Well, the answer is pretty straight forward. Cost cutting. There are lots of map viewing applications that are available in market which has a lot of features and cool tekniques that come with ready made frameworks. But they all have the following disadvantages,
1) As said earlier, cost is a major factor.
2) Just buying enough licenses from these spatial companies is never going to be enough. You need to have a in-depth understanding of your needs and a proper design has to be followed. Without that, whatever support or assistance you are going to get from the third party company will go into waste.
3) Limit to your imagination. If you want to develop a spatial application that need to address your expectation (mainly performance), then you should definitely ponder over your decision whether or not should i go for a third party application for my maps.
4) All major companies using geo spatial applications may at times blindly fall in trap for these geo spatial vendors without exploring enough on the beautiful open source communities (eg., www.opensourcegis.org ) available in the web and the range of free products they give.

I guess i will stop here for a kick-off. Will be back with more information. Till then, its bye from,
Bragaadeesh.

Funny name

Hi folks,

This website is mainly intended to help people gain both basic and advanced knowledge in data structures and algorithms. Things have gotten very hot these days and the expectation levels of an engineer has been on the raise. To be on the top layer we need to make sure that learning never stops.

This is the main aim for this website being online. I will try to cover as much as data structures as possible. Plus you will get some free goodies, some of the projects that I did during my UG and some coding problems that I solved in some of the interviews I attended. Keep an eye for off-topic section. This website should be an arsenal for interview preparation thats for sure.

Regarding the website name, its not a word in dictionary at all. Its just a combination of technical and apocalypto as I like that movie very much. :)

Cheers,
Bragaadeesh.