Re: Update with last known location? - Mailing list pgsql-novice

Hi Erik / all,

I don't think that will work, as what happens if one of the people has
two missing periods of time within their day?

I've made a self-contained example of my problem below. Would you mind
trying to produce in the code below what you think I should do?  Or if
anyone else fancies having a go then please do.

I very much appreciate your help by the way. Thank you. I'm really at
a loss with this. :-(

James
--------------------------------------

DROP TABLE test_data;

CREATE TABLE test_data(
ppid integer,
point_time timestamp without time zone,
the_geom integer);

INSERT INTO test_data VALUES
('1', '2012-01-01 07:00', '1'),
('1', '2012-01-01 07:01', '1'),
('1', '2012-01-01 07:02', '1'),
('1', '2012-01-01 07:03', NULL), -- null should be replaced with 1
('1', '2012-01-01 07:04', NULL), -- null should be replaced with 1
('1', '2012-01-01 07:05', '5'),
('1', '2012-01-01 07:06', '5'),
('1', '2012-01-01 07:07', '5'),
('1', '2012-01-01 07:08', NULL), -- null should be replaced with 5
('1', '2012-01-01 07:09', NULL), -- null should be replaced with 5
('1', '2012-01-01 07:10', NULL), -- null should be replaced with 5
('1', '2012-01-01 07:11', NULL), -- null should be replaced with 5
('2', '2013-05-02 07:12', '24'),
('2', '2013-05-02 07:13', '24'),
('2', '2013-05-02 07:14', '24'),
('2', '2013-05-02 07:15', NULL), -- null should be replaced with 24
('2', '2013-05-02 07:16', NULL), -- null should be replaced with 24
('2', '2013-05-02 07:17', '44'),
('2', '2013-05-02 07:18', '44'),
('2', '2013-05-02 07:19', NULL), -- null should be replaced with 44
('2', '2013-05-02 07:20', '4'),
('2', '2013-05-02 07:21', '4'),
('2', '2013-05-02 07:22', '4');

WITH         missing_geoms AS (
SELECT        ppid,
        point_time,
        the_geom
FROM        test_data
WHERE        the_geom IS NULL)
---
        ,filled_geoms AS (
SELECT        ppid,
        point_time,
        the_geom
FROM        test_data
WHERE        the_geom IS NOT NULL)
---
        ,partitioned AS (
SELECT        missing_geoms.ppid as missing_geoms_ppid,
        missing_geoms.point_time as missing_geoms_point_time,
        missing_geoms.the_geom as missing_geoms_the_geom,
        filled_geoms.ppid as filled_geoms_ppid,
        filled_geoms.point_time as filled_geoms_point_time,
        filled_geoms.the_geom as filled_geoms_the_geom
FROM        missing_geoms
LEFT JOIN    filled_geoms
ON        filled_geoms.point_time < missing_geoms.point_time
AND        filled_geoms.ppid = missing_geoms.ppid
ORDER BY    missing_geoms_ppid,
        missing_geoms_point_time)
---
SELECT        *
FROM        partitioned;





On 29 January 2014 22:10, Erik Darling <edarling80@gmail.com> wrote:
> Hi James,
>
> Yeah, that looks like the right place to me.
>
>
> On Wed, Jan 29, 2014 at 1:14 PM, James David Smith
> <james.david.smith@gmail.com> wrote:
>>
>> Hi Erik,
>>
>> Do you mean in this section of the SQL?
>> .....
>> filled_geoms AS (
>> SELECT
>> ppid,
>> point_time,
>> the_geom
>> FROM
>> hybrid_location
>> WHERE
>> the_geom IS NOT NULL)
>> ...
>>
>> Thanks
>>
>> James
>>
>> On 29 January 2014 17:57, Erik Darling <edarling80@gmail.com> wrote:
>> > Hi James,
>> >
>> > I think you're still stuck with sort of unnecessary ('too much' ) data
>> > coming from the right side of your left join. If so, one option I would
>> > consider is using DENSE_RANK() the way you use ROW_NUMBER(), in the
>> > filled_geoms table. If you partition by id and order by date descending,
>> > you
>> > can do an additional d_rank = 1 filter to only get the most recent
>> > activity.
>> > I believe this is what you want to set your NULL values to, no?
>> >
>> >
>> >
>> >
>> > On Wed, Jan 29, 2014 at 12:41 PM, James David Smith
>> > <james.david.smith@gmail.com> wrote:
>> >>
>> >> On 29 January 2014 16:02, Erik Darling <edarling80@gmail.com> wrote:
>> >> > I would re-suggest using a CTE to contain each dataset to ensure your
>> >> > selects are distilling them correctly, and then using a final query
>> >> > to
>> >> > join
>> >> > them. You can then either update your data directly through the
>> >> > CTE(s),
>> >> > or
>> >> > insert the results to another table to do some further testing. I
>> >> > think
>> >> > you'll find this method presents the data a bit more ergonomically
>> >> > for
>> >> > analysis.
>> >> >
>> >> > http://www.postgresql.org/docs/9.3/static/queries-with.html
>> >> >
>> >> >
>> >> >
>> >> > On Wed, Jan 29, 2014 at 10:45 AM, James David Smith
>> >> > <james.david.smith@gmail.com> wrote:
>> >> >>
>> >> >> Hi Erik/all,
>> >> >>
>> >> >> I just tried that, but it's tricky. The 'extra' data is indeed
>> >> >> coming
>> >> >> from the right side of the join, but it's hard to select only the
>> >> >> max
>> >> >> from it. Maybe it's possible but I've not managed to do it. Here is
>> >> >> where I am, which is so very close.
>> >> >>
>> >> >> SELECT
>> >> >> DISTINCT(a.ppid, a.point_time, a.the_geom) as
>> >> >> row_that_needs_geom_updating,
>> >> >> max(b.point_time) OVER (PARTITION BY a.ppid, a.point_time) as
>> >> >> last_known_position_time
>> >> >> FROM
>> >> >> test a
>> >> >> INNER JOIN
>> >> >> (SELECT    ppid,
>> >> >> point_time,
>> >> >> the_geom
>> >> >> FROM    test
>> >> >> WHERE    the_geom IS NOT NULL) b
>> >> >> ON b.point_time < a.point_time
>> >> >> AND a.ppid = b.ppid
>> >> >> WHERE a.the_geom IS NULL;
>> >> >>
>> >> >> If you see attached screen-print, the output is the rows that I
>> >> >> want.
>> >> >> However I've had to use DISTINCT to stop the duplication. Also I've
>> >> >> not managed to pull through 'the_geom' from the JOIN. I'm not sure
>> >> >> how. Anyone?
>> >> >>
>> >> >> But it's kind of working. :-)
>> >> >>
>> >> >> Worst case if I can't figure out how to solve this in one query I'll
>> >> >> have to store the result of the above, and then use it as a basis
>> >> >> for
>> >> >> another query I think.
>> >> >>
>> >> >> Thanks
>> >> >>
>> >> >> James
>> >> >>
>> >> >>
>> >> >>
>> >> >> On 29 January 2014 12:56, Erik Darling <edarling80@gmail.com> wrote:
>> >> >> > I would try partitioning the second time you call row_number,
>> >> >> > perhaps
>> >> >> > by
>> >> >> > ID,
>> >> >> > and then selecting the MAX() from that, since I think the too much
>> >> >> > data
>> >> >> > you're referring to is coming from the right side of your join.
>> >> >> >
>> >> >> > On Jan 29, 2014 7:23 AM, "James David Smith"
>> >> >> > <james.david.smith@gmail.com>
>> >> >> > wrote:
>> >> >> >>
>> >> >> >> On 28 January 2014 23:15, Gavin Flower
>> >> >> >> <GavinFlower@archidevsys.co.nz>
>> >> >> >> wrote:
>> >> >> >> > On 29/01/14 11:00, Kevin Grittner wrote:
>> >> >> >> >>
>> >> >> >> >> James David Smith <james.david.smith@gmail.com> wrote:
>> >> >> >> >>
>> >> >> >> >>> Given the data is so large I don't want to be taking the data
>> >> >> >> >>> out
>> >> >> >> >>> to a CSV or whatever and then loading it back in. I'd like to
>> >> >> >> >>> do
>> >> >> >> >>> this within the database using SQL. I thought I would be able
>> >> >> >> >>> to
>> >> >> >> >>> do this using a LOOP to be honest.
>> >> >> >> >>
>> >> >> >> >> I would be amazed if you couldn't do this with  a single
>> >> >> >> >> UPDATE
>> >> >> >> >> statement.  I've generally found declarative forms of such
>> >> >> >> >> work
>> >> >> >> >> to
>> >> >> >> >> be at least one order of magnitude faster than going to either
>> >> >> >> >> a
>> >> >> >> >> PL
>> >> >> >> >> or a script approach.  I would start by putting together a
>> >> >> >> >> SELECT
>> >> >> >> >> query using window functions and maybe a CTE or two to list
>> >> >> >> >> all
>> >> >> >> >> the
>> >> >> >> >> primary keys which need updating and the new values they
>> >> >> >> >> should
>> >> >> >> >> have.  Once that SELECT was looking good, I would put it in
>> >> >> >> >> the
>> >> >> >> >> FROM clause of an UPDATE statement.
>> >> >> >> >>
>> >> >> >> >> That should work, but if you are updating a large percentage
>> >> >> >> >> of
>> >> >> >> >> the
>> >> >> >> >> table, I would go one step further before running this against
>> >> >> >> >> the
>> >> >> >> >> production tables.  I would put a LIMIT on the above-mentioned
>> >> >> >> >> SELECT of something like 10000 rows, and script a loop that
>> >> >> >> >> alternates between the UPDATE and a VACUUM ANALYZE on the
>> >> >> >> >> table.
>> >> >> >> >>
>> >> >> >> >> --
>> >> >> >> >> Kevin Grittner
>> >> >> >> >> EDB: http://www.enterprisedb.com
>> >> >> >> >> The Enterprise PostgreSQL Company
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> > James, you might consider dropping as many indexes on the table
>> >> >> >> > as
>> >> >> >> > you
>> >> >> >> > safely can, and rebuilding them after the mass update.  If you
>> >> >> >> > have
>> >> >> >> > lots
>> >> >> >> > of
>> >> >> >> > such indexes, you will find this apprtoach to be a lot faster.
>> >> >> >> >
>> >> >> >> >
>> >> >> >> > Cheers,
>> >> >> >> > Gavin
>> >> >> >>
>> >> >> >> Hi all,
>> >> >> >>
>> >> >> >> Thanks for your help and assistance. I think that window
>> >> >> >> functions,
>> >> >> >> and inparticular the PARTITION function, is 100% the way to go.
>> >> >> >> I've
>> >> >> >> been concentrating on a SELECT statement for now and am close but
>> >> >> >> not
>> >> >> >> quite close enough. The below query gets all the data I want, but
>> >> >> >> *too* much. What I've essentially done is:
>> >> >> >>
>> >> >> >> - Select all the rows that don't have any geom information
>> >> >> >> - Join them with all rows before this point that *do* have geom
>> >> >> >> information.
>> >> >> >> - Before doing this join, use partition to generate row numbers.
>> >> >> >>
>> >> >> >> The attached screen grab shows the result of my query below.
>> >> >> >> Unfortunately this is generating alot of joins that I don't want.
>> >> >> >> This
>> >> >> >> won't be practical when doing it with 75,000 people.
>> >> >> >>
>> >> >> >> Thoughts and code suggestions very much appreciated... if needed
>> >> >> >> I
>> >> >> >> could put together some SQL to create an example table?
>> >> >> >>
>> >> >> >> Thanks
>> >> >> >>
>> >> >> >> SELECT     row_number() OVER (PARTITION BY test.point_time ORDER
>> >> >> >> BY
>> >> >> >> test.point_time) as test_row,
>> >> >> >>     test.ppid as test_ppid,
>> >> >> >>     test.point_time as test_point_time,
>> >> >> >>     test.the_geom as test_the_geom,
>> >> >> >>     a.ppid as a_ppid,
>> >> >> >>     a.point_time as a_point_time,
>> >> >> >>     a.the_geom as a_the_geom,
>> >> >> >>     a.a_row
>> >> >> >> FROM     test
>> >> >> >>     LEFT JOIN (
>> >> >> >>         SELECT    the_geom,
>> >> >> >>             ppid,
>> >> >> >>             point_time,
>> >> >> >>             row_number() OVER (ORDER BY ppid, point_time) as
>> >> >> >> a_row
>> >> >> >>         FROM     test
>> >> >> >>         WHERE     the_geom IS NOT NULL) a
>> >> >> >> ON    a.point_time < test.point_time
>> >> >> >> AND    a.ppid = test.ppid
>> >> >> >> WHERE    test.the_geom IS NULL
>> >> >> >> ORDER BY test.point_time)
>> >> >> >>
>> >>
>> >>
>> >> Hi Erik / all,
>> >>
>> >> So I think I've managed to re-write my queries using CTEs. The below
>> >> code now does get me the data that I want from this. But to do so it
>> >> is going to create a frankly huge table in the bit of the SQL where it
>> >> makes the table called 'partitioned'. My rough guess is that it'll
>> >> have to make a table of about 100 billion rows in order to get data I
>> >> need ( about 108 million rows).
>> >>
>> >> Could someone please glance through it for me and suggest how to write
>> >> it more efficiently?
>> >>
>> >> Thanks
>> >>
>> >> James
>> >>
>> >> WITH         missing_geoms AS (
>> >> SELECT        ppid,
>> >>         point_time,
>> >>         the_geom
>> >> FROM        hybrid_location
>> >> WHERE        the_geom IS NULL)
>> >> -----------------
>> >>         ,filled_geoms AS (
>> >> SELECT        ppid,
>> >>         point_time,
>> >>         the_geom
>> >> FROM        hybrid_location
>> >> WHERE        the_geom IS NOT NULL)
>> >> ----------------
>> >>         ,partitioned AS (
>> >> SELECT        missing_geoms.ppid,
>> >>         missing_geoms.point_time,
>> >>         missing_geoms.the_geom,
>> >>         filled_geoms.ppid,
>> >>         filled_geoms.point_time,
>> >>         filled_geoms.the_geom,
>> >>         row_number() OVER (    PARTITION BY     missing_geoms.ppid,
>> >>                             missing_geoms.point_time
>> >>                     ORDER BY    missing_geoms.ppid,
>> >>                             missing_geoms.point_time,
>> >>                             filled_geoms.ppid,
>> >>                             filled_geoms.point_time DESC)
>> >> FROM        missing_geoms
>> >> LEFT JOIN    filled_geoms
>> >> ON        filled_geoms.point_time < missing_geoms.point_time
>> >> AND        filled_geoms.ppid = missing_geoms.ppid)
>> >> --------------
>> >> SELECT         *
>> >> FROM        partitioned
>> >> WHERE        row_number = 1;
>> >>
>> >> James
>> >
>> >
>
>