Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    6

    Unanswered: how to query this efficiently?

    Hello. I'm not sure if this is the best/correct category for this question, but i am using postgres so I though i should post it here.

    I am pretty new to databases, my question is more about the best way for me to query something.

    I have 2 tables, one of them stores positions of objects (ex: 2, 5, 56, 98) and the other stores start and stop points of other objects (property names: start, stop).

    I need to find all objects from table 1 which are NOT inbetween the start and stop coordinates found in table 2.

    Here is what i'm thinking the query should look like, but i'm not sure if it will work, and if the logic is right, than how the syntax looks like.

    select * from objects where 'object position' > 'coordinates stop' and 'object position' < 'coordinates start of next row'

    ANy help would be greatly appreciated. Thank you.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Is this a rectangular coordinate system?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Jul 2009
    Posts
    6
    What do you mean rectangular coordinate system?

    each object only has a start and stop position. Somebody help me please! i'm still struggling with this!

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    You'll probably get more answers if you supply ready to run DDL (CREATE TABLE) and INSERT scripts to show your tables and sample data.
    And post your expected result as well.

    I for one don't understand your "start of next row" if your object position table already has start and stop in it.

  5. #5
    Join Date
    Jul 2009
    Posts
    6
    Thank you for your response.

    Unfortunately, I do not have any create and instert statements because I am using DataNucleus, which does all this automatically for me.

    lets say i have a table with 2 fields, Position and ID.

    and another table with 2 fields Start and Stop. ex:
    1 5
    8 10
    18 25
    30 34

    Now, what i want is to find all the IDs from the first table above where the Position of that ID is NOT within a start and stop, for example, i want to retrieve all IDS which are between 6-7, 11-17, 26-29, 35+
    thats what i meant by in between the Stop of one row and the Start of the following row.

    thanks.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    OK, this might work.

    The tables I created look like this:
    Code:
    CREATE TABLE object_pos
    (
       id       int4  NOT NULL,
       position int4  NOT NULL
    );
    ALTER TABLE object_pos
       ADD CONSTRAINT object_pos_pkey PRIMARY KEY (id);
    
    CREATE TABLE start_stop
    (
       start int4  NOT NULL,
       stop  int4  NOT NULL
    );
    
    INSERT INTO object_pos (id, position) VALUES (1, 2);
    INSERT INTO object_pos (id, position) VALUES (2, 5);
    INSERT INTO object_pos (id, position) VALUES (3, 56);
    INSERT INTO object_pos (id, position) VALUES (4, 98);
    INSERT INTO object_pos (id, position) VALUES (5, 3);
    INSERT INTO object_pos (id, position) VALUES (6, 31);
    INSERT INTO object_pos (id, position) VALUES (7, 6);
    
    INSERT INTO start_stop (start, stop) VALUES (1, 5);
    INSERT INTO start_stop (start, stop) VALUES (8, 10);
    INSERT INTO start_stop (start, stop) VALUES (18, 25);
    INSERT INTO start_stop (start, stop) VALUES (30, 34);
    
    COMMIT;
    Which is a very simple test case, but I don't feel like creating more data on my own.

    The following statement should return those entries that do not fall in one of the existing intervals defined by start_stop:
    Code:
    select op.id, op.position
    FROM object_pos op 
    CROSS JOIN (
        SELECT last_stop + 1 as start, start - 1 as stop
        FROM (
        SELECT start, 
               lag (stop) over (order by start) as last_stop
        FROM start_stop
        ) rm
        WHERE rm.last_stop + 1 <> rm.start
    ) t
    WHERE op.position >= t.start and op.position <= stop
    UNION ALL
    SELECT op.id, op.position
    FROM object_pos op
    WHERE op.position > (SELECT max(stop) FROM start_stop)
    You will need Postgres 8.4 for this.

  7. #7
    Join Date
    Jul 2009
    Posts
    6
    Shammat, thank you very much!

    i'm gonna give this a try!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •