[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Non-linear time cost; please suggest a better way to structure the query
From: Florian Nadler <florian.nadler () cybertec ! at>
Date: 2022-02-26 11:56:56
Message-ID: f6732b67-85d1-261a-bdf4-84658155b629 () cybertec ! at
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Hi Rory,
I recommend to start with the latest MobilityDB extension (docker image
seems to be outdated) and import and partition the Berlin Workshop data,
as mentioned in
https://docs.mobilitydb.com/MobilityDB-BerlinMOD/master/mobilitydb-berlinmod.pdf.
The query below should give a good starting point for your analysis -
for a given period, output the average time cars remain in regions.
select carid,
regionid,
avg(duration(
(atgeometry((atPeriod(T.Trip,'[2007-05-28 00:00:00+00, 2007-05-29 \
00:00:00+00]'::period)), R.geom))::tgeompoint))
FROM Trips T,
Regions R WHERE T.Trip && stbox(R.Geom,'[2007-05-28 00:00:00+00, 2007-05-29 \
00:00:00+00]'::period) AND st_intersects(trajectory(atPeriod(T.Trip,'[2007-05-28 \
00:00:00+00, 2007-05-29 00:00:00+00]'::period)), R.Geom)
group by carid,regionid
Just let me know if this works out for.
Florian
Am 24.02.2022 um 08:53 schrieb Rory Meyer:
> I haven't looked at MobilityDB (although I will now). In addition to
> PostGIS I'm using TimeScaleDB to build "continuous aggregates" of the
> GPS data in order to get things like the latest position per hour, the
> average speed per day etc. It's pretty handy for that but is unable to
> use more complex aggregates with window functions, distinct,
> trajectory creation etc.
>
> I suppose the main part of my query would be portion that distributes
> the difference between the lead/lag and current event_times (time
> delta) over the grids that the lead/lag created line pass over:
> sum(((st_length(st_intersection(traj.traj, grid.geom)) *
> traj.time_delta) / traj.traj_dist))
> Does MobilityDB have a function that could help with this?
>
> I tried to stay away from using the trajectory data type since it is
> only (x,y,t) and what I really need is (x,y,t,m1,m2,m3....) so that I
> can distribute the speed/bearing/dataX over the hexagon weighted with
> the time associated with each vertex.
>
> Regards,
> Rory
>
> ------------------------------------------------------------------------
> *From:* Florian Nadler <florian.nadler@cybertec.at>
> *Sent:* 24 February 2022 07:45
> *To:* PostGIS Users Discussion <postgis-users@lists.osgeo.org>; Rory
> Meyer <rory.meyer@VLIZ.be>
> *Subject:* Re: [postgis-users] Non-linear time cost; please suggest a
> better way to structure the query
>
> Hi,
>
>
> apart from Paul advice did you ever take into consideration to use
> MobilityDB for this kind of spatial questions?
>
>
> This will imply creating trajectories out of gps points too, but might
> simplify query design and processing time as this extension is
> developed for this kind of queries.
>
>
> Checkout
> https://docs.mobilitydb.com/MobilityDB-BerlinMOD/master/mobilitydb-berlinmod.pdf
> <https://docs.mobilitydb.com/MobilityDB-BerlinMOD/master/mobilitydb-berlinmod.pdf>, \
> chapter 3.3 which deals with quite simliar analysis.
>
>
> Florian
>
>
> Am 23.02.2022 um 15:14 schrieb Rory Meyer:
> > Afternoon all,
> >
> > I've got a database full of GPS points (along with associated data
> > like speed, bearing, time, GPS_ID, class etc) and I'm trying to do
> > complex aggregations with the data. I'm trying to build up a
> > "heatmap" of the data by first creating a grid of polygons
> > using ST_HexagonGrid and then using a window function to overlay the
> > lines (created from a lead/lag window of each GPS point and the next
> > one from the same GPS_ID) over the grid. I'd like to get the the
> > number of seconds that gps carrying vehicles spend in each hex cell,
> > grouped by class, speed, date, direction etc etc. The end goal would
> > be to query a lon/lat and get a bunch of aggregated data for
> > different classes, speed/bearing distributions.
> >
> > Here's a simplified look at the SQL (sorry, it's not really simple...):
> >
> > '''
> > SELECT
> > grid.gid,
> > grid.geom,
> > avg(traj.bearing, 511.0) AS avg_bearing,
> > avg(traj.time_delta) AS avg_time_delta,
> > sum(((st_length(st_intersection(traj.traj, grid.geom)) *
> > traj.time_delta) / traj.traj_dist)) AS cum_time_in_grid
> > FROM (
> > (my_hex_grid AS grid LEFT JOIN ( SELECT
> > subquery.gps_id,
> > subquery.event_date,
> > subquery.bearing,
> > subquery.time_delta,
> > st_makeline(subquery.pos, subquery.pos2) AS traj,
> > st_distance(subquery.pos, subquery.pos2) AS traj_dist
> > FROM (
> > SELECT
> > gps.mmsi,
> > date_part('epoch'::text, (lead(gps.event_time) OVER time_order -
> > gps.event_time)) AS time_delta,
> > gps.geom,
> > gps.bearing,
> > lead(gps.geom) OVER time_order AS geom2
> > FROM gps
> > WHERE ((gps.event_time >= '<Start Time>') AND (gps.event_time <=
> > '<End Time>'))
> > WINDOW time_order AS (PARTITION BY gps.gps_id ORDER BY
> > gps.event_time)) as subquery
> > ON (st_intersects(gps.traj,
> > grid.geom)))
> > GROUP BY grid.gid, grid.geom
> > '''
> >
> > My issue is that I've got a non-linear increase in time that the
> > query takes to complete. If <Start Time> to <End Time> is a couple of
> > hours then it's takes a couple of seconds to run. If it's for a day,
> > it takes a couple minutes to run. If it's for a week it takes a
> > couple of hours.
> >
> > I'd like to run this for over a year of data but that won't be
> > feasible at this rate.
> >
> > Is there some way to avoid this non-linear increase in time or would
> > it be best to just write some python code to loop through smaller
> > chunks of data and write the results somewhere?
> >
> > Regards,
> > Rory
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
> > https://lists.osgeo.org/mailman/listinfo/postgis-users \
> > <https://lists.osgeo.org/mailman/listinfo/postgis-users>
> --
> CYBERTEC PostgreSQL International GmbH
> Römerstraße 19, A-2752 Wöllersdorf
> Web:https://www.cybertec-postgresql.com <https://www.cybertec-postgresql.com>
--
CYBERTEC PostgreSQL International GmbH
Römerstraße 19, A-2752 Wöllersdorf
Web:https://www.cybertec-postgresql.com
[Attachment #5 (text/html)]
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi Rory,</p>
<p><br>
</p>
<p>I recommend to start with the latest MobilityDB extension (docker
image seems to be outdated) and import and partition the Berlin
Workshop data, as mentioned in
<a class="moz-txt-link-freetext" \
href="https://docs.mobilitydb.com/MobilityDB-BerlinMOD/master/mobilitydb-berlinmod.pdf \
">https://docs.mobilitydb.com/MobilityDB-BerlinMOD/master/mobilitydb-berlinmod.pdf</a>.</p>
<p>The query below should give a good starting point for your
analysis - for a given period, output the average time cars remain
in regions. <br>
</p>
<pre style="background-color:#ffffff;color:#080808;font-family:'JetBrains \
Mono',monospace;font-size:9,0pt;"><span style="color:#0033b3;">select </span><span \
style="color:#871094;">carid</span>, <span style="color:#871094;">regionid</span>,
<span style="font-style:italic;">avg</span>(<span \
style="color:#00627a;font-style:italic;">duration</span>( (<span \
style="color:#00627a;font-style:italic;">atgeometry</span>((<span \
style="color:#00627a;font-style:italic;">atPeriod</span>(<span \
style="color:#000000;">T</span>.<span style="color:#871094;">Trip</span>, <span \
style="color:#067d17;">'[2007-05-28 00:00:00+00, 2007-05-29 \
00:00:00+00]'</span>::<span style="color:#0033b3;">period</span>)),
<span style="color:#000000;">R</span>.<span \
style="color:#871094;">geom</span>))::<span \
style="color:#0033b3;">tgeompoint</span>)) <span style="color:#0033b3;">FROM \
</span><span style="color:#000000;">Trips T</span>, <span \
style="color:#000000;">Regions R </span><span style="color:#0033b3;">WHERE \
</span><span style="color:#000000;">T</span>.<span style="color:#871094;">Trip \
</span><span style="color:#000000;">&& </span><span \
style="color:#00627a;font-style:italic;">stbox</span>(<span \
style="color:#000000;">R</span>.<span style="color:#871094;">Geom</span>, <span \
style="color:#067d17;">'[2007-05-28 00:00:00+00, 2007-05-29 \
00:00:00+00]'</span>::<span style="color:#0033b3;">period</span>) <span \
style="color:#0033b3;">AND </span><span \
style="color:#00627a;font-style:italic;">st_intersects</span>(<span \
style="color:#00627a;font-style:italic;">trajectory</span>(<span \
style="color:#00627a;font-style:italic;">atPeriod</span>(<span \
style="color:#000000;">T</span>.<span style="color:#871094;">Trip</span>, <span \
style="color:#067d17;">'[2007-05-28 00:00:00+00, 2007-05-29 \
00:00:00+00]'</span>::<span style="color:#0033b3;">period</span>)),
<span style="color:#000000;">R</span>.<span \
style="color:#871094;">Geom</span>) <span style="color:#0033b3;">group by \
</span><span style="color:#871094;">carid</span>, <span \
style="color:#871094;">regionid</span><span style="color:#871094;"></span></pre> \
<p>Just let me know if this works out for. <br> </p>
<p>Florian<br>
</p>
<p><br>
</p>
<div class="moz-cite-prefix">Am 24.02.2022 um 08:53 schrieb Rory
Meyer:<br>
</div>
<blockquote type="cite"
cite="mid:DB9PR05MB8026C130DCBE0AEBD0DC7A06843D9@DB9PR05MB8026.eurprd05.prod.outlook.com">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<style type="text/css" style="display:none;">P \
{margin-top:0;margin-bottom:0;}</style> <div style="font-family: Calibri, Arial, \
Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0); background-color: \
rgb(255, 255, 255);">
<span style="color: rgb(0, 0, 0); font-family: "Segoe
UI", "Segoe UI Web (West European)",
"Segoe UI", -apple-system, BlinkMacSystemFont,
Roboto, "Helvetica Neue", sans-serif; font-size:
14px; background-color: rgb(255, 255, 255); display: inline
!important;">I haven't looked at MobilityDB (although I will
now). In addition to PostGIS I'm using TimeScaleDB to build
"continuous aggregates" of the GPS data in order to get things
like the latest position per hour, the average speed per day
etc. It's pretty handy for that but is unable to use more
complex aggregates with window functions, distinct, trajectory
creation etc. </span><br>
</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0); background-color: rgb(255,
255, 255);">
<span style="color: rgb(0, 0, 0); font-family: "Segoe
UI", "Segoe UI Web (West European)",
"Segoe UI", -apple-system, BlinkMacSystemFont,
Roboto, "Helvetica Neue", sans-serif; font-size:
14px; background-color: rgb(255, 255, 255); display: inline
!important;"><br>
</span></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0); background-color: rgb(255,
255, 255);">
<span style="color: rgb(0, 0, 0); font-family: "Segoe
UI", "Segoe UI Web (West European)",
"Segoe UI", -apple-system, BlinkMacSystemFont,
Roboto, "Helvetica Neue", sans-serif; font-size:
14px; background-color: rgb(255, 255, 255); display: inline
!important;">I suppose the main part of my query would be
portion that distributes the difference between the lead/lag
and current event_times (time delta) over the grids that the
lead/lag created line pass over:</span></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0); background-color: rgb(255,
255, 255);">
<span style="color: rgb(0, 0, 0); font-family: "Segoe
UI", "Segoe UI Web (West European)",
"Segoe UI", -apple-system, BlinkMacSystemFont,
Roboto, "Helvetica Neue", sans-serif; font-size:
14px; background-color: rgb(255, 255, 255); display: inline
!important;"> sum(((st_length(st_intersection(traj.traj,
grid.geom)) * traj.time_delta) / traj.traj_dist))<br>
</span></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0); background-color: rgb(255,
255, 255);">
<span style="color: rgb(0, 0, 0); font-family: "Segoe
UI", "Segoe UI Web (West European)",
"Segoe UI", -apple-system, BlinkMacSystemFont,
Roboto, "Helvetica Neue", sans-serif; font-size:
14px; background-color: rgb(255, 255, 255); display: inline
!important;">Does MobilityDB have a function that could help
with this? </span></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0); background-color: rgb(255,
255, 255);">
<span style="color: rgb(0, 0, 0); font-family: "Segoe
UI", "Segoe UI Web (West European)",
"Segoe UI", -apple-system, BlinkMacSystemFont,
Roboto, "Helvetica Neue", sans-serif; font-size:
14px; background-color: rgb(255, 255, 255); display: inline
!important;"><br>
</span></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0); background-color: rgb(255,
255, 255);">
<span style="color: rgb(0, 0, 0); font-family: "Segoe
UI", "Segoe UI Web (West European)",
"Segoe UI", -apple-system, BlinkMacSystemFont,
Roboto, "Helvetica Neue", sans-serif; font-size:
14px; background-color: rgb(255, 255, 255); display: inline
!important;">I tried to stay away from using the trajectory
data type since it is only (x,y,t) and what I really need is
(x,y,t,m1,m2,m3....) so that I can distribute the
speed/bearing/dataX over the hexagon weighted with the time
associated with each vertex.</span></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0); background-color: rgb(255,
255, 255);">
<span style="color: rgb(0, 0, 0); font-family: "Segoe
UI", "Segoe UI Web (West European)",
"Segoe UI", -apple-system, BlinkMacSystemFont,
Roboto, "Helvetica Neue", sans-serif; font-size:
14px; background-color: rgb(255, 255, 255); display: inline
!important;"><br>
</span></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0); background-color: rgb(255,
255, 255);">
<span style="color: rgb(0, 0, 0); font-family: "Segoe
UI", "Segoe UI Web (West European)",
"Segoe UI", -apple-system, BlinkMacSystemFont,
Roboto, "Helvetica Neue", sans-serif; font-size:
14px; background-color: rgb(255, 255, 255); display: inline
!important;">Regards,</span></div>
<div style="color: rgb(0, 0, 0); background-color: rgb(255, 255,
255);"><span style="font-size: 14px;">Rory</span></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif;
font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="divRplyFwdMsg" dir="ltr"><font style="font-size: 11pt;"
face="Calibri, sans-serif" color="#000000"><b>From:</b>
Florian Nadler <a class="moz-txt-link-rfc2396E" \
href="mailto:florian.nadler@cybertec.at"><florian.nadler@cybertec.at></a><br> \
<b>Sent:</b> 24 February 2022 07:45<br> <b>To:</b> PostGIS Users Discussion
<a class="moz-txt-link-rfc2396E" \
href="mailto:postgis-users@lists.osgeo.org"><postgis-users@lists.osgeo.org></a>; \
Rory Meyer
<a class="moz-txt-link-rfc2396E" \
href="mailto:rory.meyer@VLIZ.be"><rory.meyer@VLIZ.be></a><br> <b>Subject:</b> \
Re: [postgis-users] Non-linear time cost; please suggest a better way to structure \
the query</font> <div> </div>
</div>
<div>
<p style="margin-top: 0px; margin-bottom: 0px;">Hi, <br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;">apart from Paul
advice did you ever take into consideration to use MobilityDB
for this kind of spatial questions?</p>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
This will imply creating trajectories out of gps points too,
but might simplify query design and processing time as this
extension is developed for this kind of queries.
<br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;">Checkout <a
class="x_moz-txt-link-freetext moz-txt-link-freetext"
href="https://docs.mobilitydb.com/MobilityDB-BerlinMOD/master/mobilitydb-berlinmod.pdf"
moz-do-not-send="true">
https://docs.mobilitydb.com/MobilityDB-BerlinMOD/master/mobilitydb-berlinmod.pdf</a>,
chapter 3.3 which deals with quite simliar analysis.<br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;">Florian<br>
</p>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
</p>
<div class="x_moz-cite-prefix">Am 23.02.2022 um 15:14 schrieb
Rory Meyer:<br>
</div>
<blockquote type="cite">
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
Afternoon all,</div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
I've got a database full of GPS points (along with
associated data like speed, bearing, time, GPS_ID, class
etc) and I'm trying to do complex aggregations with the
data. I'm trying to build up a "heatmap" of the data by
first creating a grid of polygons using ST_HexagonGrid and
then using a window function to overlay the lines (created
from a lead/lag window of each GPS point and the next one
from the same GPS_ID) over the grid. I'd like to get the the
number of seconds that gps carrying vehicles spend in each
hex cell, grouped by class, speed, date, direction etc etc.
The end goal would be to query a lon/lat and get a bunch of
aggregated data for different classes, speed/bearing
distributions. </div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
Here's a simplified look at the SQL (sorry, it's not really
simple...):</div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
'''</div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
SELECT
<div> grid.gid,</div>
<div> grid.geom,</div>
<div> avg(traj.bearing, 511.0) AS avg_bearing,</div>
<div> avg(traj.time_delta) AS avg_time_delta,</div>
<div> sum(((st_length(st_intersection(traj.traj,
grid.geom)) * traj.time_delta) / traj.traj_dist)) AS
cum_time_in_grid</div>
<div>FROM (</div>
<div>(my_hex_grid AS grid LEFT JOIN ( SELECT</div>
<div>
subquery.gps_id,</div>
<div><span style="color: rgb(0, 0, 0); background-color:
rgb(255, 255, 255); display: inline !important;">
\
</span>subquery.event_date,</div> <div><span style="color: rgb(0, 0, 0); \
background-color: rgb(255, 255, 255); display: inline !important;">
\
</span>subquery.bearing, </div>
<div><span style="color: rgb(0, 0, 0); background-color:
rgb(255, 255, 255); display: inline !important;">
\
</span>subquery.time_delta,</div> <div><span style="color: rgb(0, 0, 0); \
background-color: rgb(255, 255, 255); display: inline !important;">
\
</span>st_makeline(subquery.pos, subquery.pos2) AS traj,</div>
<div><span style="color: rgb(0, 0, 0); background-color:
rgb(255, 255, 255); display: inline !important;">
\
</span>st_distance(subquery.pos, subquery.pos2) AS traj_dist</div>
<div><span style="color: rgb(0, 0, 0); background-color:
rgb(255, 255, 255); display: inline !important;">
</span>FROM
( </div>
<div><span style="color: rgb(0, 0, 0); background-color:
rgb(255, 255, 255); display: inline !important;">
</span>SELECT </div>
<div><span style="color: rgb(0, 0, 0); background-color:
rgb(255, 255, 255); display: inline !important;">
\
</span>gps.mmsi,</div> <div><span style="color: rgb(0, 0, 0); background-color:
rgb(255, 255, 255); display: inline !important;">
\
</span>date_part('epoch'::text, (lead(gps.event_time) OVER time_order - \
gps.event_time)) AS
<span style="color: rgb(0, 0, 0); background-color:
rgb(255, 255, 255); display: inline !important;">
</span>time_delta,</div>
<div><span style="color: rgb(0, 0, 0); background-color:
rgb(255, 255, 255); display: inline !important;">
\
</span>gps.geom,</div> <div><span style="color: rgb(0, 0, 0); background-color:
rgb(255, 255, 255); display: inline !important;">
\
</span>gps.bearing,</div> <div><span style="color: rgb(0, 0, 0); background-color:
rgb(255, 255, 255); display: inline !important;">
\
</span>lead(gps.geom) OVER time_order AS geom2</div>
<div><span style="color: rgb(0, 0, 0); background-color:
rgb(255, 255, 255); display: inline !important;">
</span> FROM
gps</div>
<div><span style="color: rgb(0, 0, 0); background-color:
rgb(255, 255, 255); display: inline !important;">
</span>WHERE
((gps.event_time >= '<Start Time>') AND
(gps.event_time <= '<End Time>'))</div>
<div><span style="color: rgb(0, 0, 0); background-color:
rgb(255, 255, 255); display: inline !important;">
</span>WINDOW
time_order AS (PARTITION BY gps.gps_id ORDER BY
gps.event_time)) as subquery</div>
<div> ON
(st_intersects(gps.traj, grid.geom)))</div>
GROUP BY grid.gid, grid.geom</div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
'''</div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
My issue is that I've got a non-linear increase in time that
the query takes to complete. If <Start Time> to
<End Time> is a couple of hours then it's takes a
couple of seconds to run. If it's for a day, it takes a
couple minutes to run. If it's for a week it takes a couple
of hours. </div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
I'd like to run this for over a year of data but that won't
be feasible at this rate. </div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
Is there some way to avoid this non-linear increase in time
or would it be best to just write some python code to loop
through smaller chunks of data and write the results
somewhere?</div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
<br>
</div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
Regards,</div>
<div style="font-family: Calibri, Arial, Helvetica,
sans-serif; font-size: 12pt; color: rgb(0, 0, 0);
background-color: rgb(255, 255, 255);">
Rory</div>
<br>
<fieldset class="x_moz-mime-attachment-header"></fieldset>
<pre class="x_moz-quote-pre">_______________________________________________
postgis-users mailing list
<a class="x_moz-txt-link-abbreviated moz-txt-link-freetext" \
href="mailto:postgis-users@lists.osgeo.org" \
moz-do-not-send="true">postgis-users@lists.osgeo.org</a> <a \
class="x_moz-txt-link-freetext moz-txt-link-freetext" \
href="https://lists.osgeo.org/mailman/listinfo/postgis-users" \
moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/postgis-users</a> \
</pre> </blockquote>
<pre class="x_moz-signature" cols="72">--
CYBERTEC PostgreSQL International GmbH
Römerstraße 19, A-2752 Wöllersdorf
Web: <a class="x_moz-txt-link-freetext moz-txt-link-freetext" \
href="https://www.cybertec-postgresql.com" \
moz-do-not-send="true">https://www.cybertec-postgresql.com</a></pre> </div>
</blockquote>
<pre class="moz-signature" cols="72">--
CYBERTEC PostgreSQL International GmbH
Römerstraße 19, A-2752 Wöllersdorf
Web: <a class="moz-txt-link-freetext" \
href="https://www.cybertec-postgresql.com">https://www.cybertec-postgresql.com</a></pre>
</body>
</html>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic