How to query space-time points with index in PostGIS

user_7556979 0
Viewed 133

I got millions of points stored with a PointZM (lon,lat,depth, time epoch) geometry column

ST_SetSRID(st_makepoint(longitude,latitude,-z,date_part('epoch', "timestamp"::timestamp)),4326) as timegeom

I have created a GIST(geometry gist_geometry_ops_nd) index on the column

Now, how to query this in space and time?

I have tried using the &&& operator:

select * from huge_spatial_table 
where timegeom &&& st_3dmakebox(st_setsrid(st_makepoint(0.05278027,29.47846469,0,date_part('epoch', '2013-01-01'::timestamp)),4326), 
                                st_setsrid(st_makepoint(37.50180758,45.37019107,-50,date_part('epoch', '2018-01-01'::timestamp)),4326))

But the time dimension is not filtered when doing this.

When filtering time using st_m(geometry) it works, but the index is not used on the time dimension which is what I want.

select * from huge_spatial_table 
where timegeom &&& st_3dmakebox(st_setsrid(st_makepoint(0.05278027,29.47846469,0,date_part('epoch', '2013-01-01'::timestamp)),4326), 
                                st_setsrid(st_makepoint(37.50180758,45.37019107,-50,date_part('epoch', '2018-01-01'::timestamp)),4326)) 
and st_m(timegeom)>date_part('epoch', '2013-01-01'::timestamp)
and st_m(timegeom)<date_part('epoch', '2018-01-01'::timestamp)

Explain:

"Index Scan using idx_huge_spatial_table  on huge_spatial_table   (cost=0.55..2.78 rows=1 width=184)"
"  Index Cond: (timegeom &&& '010F0000A0E6100000060000000103000080010000000500000045500CFB0306AB3F3DD773A97C7A3D40000000000000000045500CFB0306AB3FEB75C56B62AF46400000000000000000117E143B3BC04240EB75C56B62AF46400000000000000000117E143B3BC042403DD773A97C7A3D40000000000000000045500CFB0306AB3F3DD773A97C7A3D4000000000000000000103000080010000000500000045500CFB0306AB3F3DD773A97C7A3D4000000000000049C0117E143B3BC042403DD773A97C7A3D4000000000000049C0117E143B3BC04240EB75C56B62AF464000000000000049C045500CFB0306AB3FEB75C56B62AF464000000000000049C045500CFB0306AB3F3DD773A97C7A3D4000000000000049C00103000080010000000500000045500CFB0306AB3F3DD773A97C7A3D40000000000000000045500CFB0306AB3F3DD773A97C7A3D4000000000000049C045500CFB0306AB3FEB75C56B62AF464000000000000049C045500CFB0306AB3FEB75C56B62AF4640000000000000000045500CFB0306AB3F3DD773A97C7A3D40000000000000000001030000800100000005000000117E143B3BC042403DD773A97C7A3D400000000000000000117E143B3BC04240EB75C56B62AF46400000000000000000117E143B3BC04240EB75C56B62AF464000000000000049C0117E143B3BC042403DD773A97C7A3D4000000000000049C0117E143B3BC042403DD773A97C7A3D4000000000000000000103000080010000000500000045500CFB0306AB3F3DD773A97C7A3D400000000000000000117E143B3BC042403DD773A97C7A3D400000000000000000117E143B3BC042403DD773A97C7A3D4000000000000049C045500CFB0306AB3F3DD773A97C7A3D4000000000000049C045500CFB0306AB3F3DD773A97C7A3D4000000000000000000103000080010000000500000045500CFB0306AB3FEB75C56B62AF4640000000000000000045500CFB0306AB3FEB75C56B62AF464000000000000049C0117E143B3BC04240EB75C56B62AF464000000000000049C0117E143B3BC04240EB75C56B62AF4640000000000000000045500CFB0306AB3FEB75C56B62AF46400000000000000000'::geometry)"
"  Filter: ((st_m(timegeom) > '1356998400'::double precision) AND (st_m(timegeom) < '1514764800'::double precision))"

Is it even possible to do spatio-temporal queries using an index in PostGIS?

Btw, I am using postgres 9.6 and PostGIS 2.3.2

postgis postgresql sql
1 Answers

Welcome to SO.

You could create an extra index containing only the M dimension of your geometries, e.g. this will improve query time:

CREATE INDEX idx_t_geom_m ON huge_spatial_table (ST_M(geom));
Query plan without the index (100k random points):
Bitmap Heap Scan on huge_spatial_table  (cost=4.36..291.45 rows=1 width=32) (actual time=0.011..0.011 rows=0 loops=1)
  Filter: ((st_m(timegeom) > '1356998400'::double precision) AND (st_m(timegeom) < '1514764800'::double precision) AND st_contains(timegeom, '0103000000010000002100000000000000008046400000000000003D407CA67EB86767464008BF479B910C3B4013E65FD8901E46408AB5F495542C394042496AF647A84540437FF97DBD71374059E103C018094540523DF87FCEED354060400341214744407E6D2B1370AF34403DA505B5D5694340DC33404FDEC233407E205C32B77942400AB3028F303133400200000000804140000000000000334086DFA3CD4886404008B3028F303133408EB5F495542C3F40D833404FDEC23340467FF97DBD713D407A6D2B1370AF3440543DF87FCEED3B404C3DF87FCEED3540816D2B1370AF3A403C7FF97DBD713740DD33404FDEC2394081B5F495542C39400AB3028F30313940FFBE479B910C3B400000000000003940F7FFFFFFFFFF3C4007B3028F30313940EF40B8646EF33E40D633404FDEC2394037A505B5D5694040776D2B1370AF3A405B40034121474140483DF87FCEED3B4054E103C018094240377FF97DBD713D403E496AF647A842407EB5F495542C3F4011E65FD8901E43407DDFA3CD488640407AA67EB867674340F9FFFFFFFF7F4140000000000080434075205C32B77942407DA67EB86767434035A505B5D569434016E65FD8901E4340584003412147444046496AF647A8424052E103C0180945405EE103C0180942403C496AF647A84540674003412147414010E65FD8901E464044A505B5D56940407AA67EB8676746400B41B8646EF33E4000000000008046400000000000003D40'::geometry))
  ->  Bitmap Index Scan on idx_t_geom  (cost=0.00..4.36 rows=10 width=0) (actual time=0.009..0.009 rows=0 loops=1)
        Index Cond: (timegeom ~~ '0103000000010000002100000000000000008046400000000000003D407CA67EB86767464008BF479B910C3B4013E65FD8901E46408AB5F495542C394042496AF647A84540437FF97DBD71374059E103C018094540523DF87FCEED354060400341214744407E6D2B1370AF34403DA505B5D5694340DC33404FDEC233407E205C32B77942400AB3028F303133400200000000804140000000000000334086DFA3CD4886404008B3028F303133408EB5F495542C3F40D833404FDEC23340467FF97DBD713D407A6D2B1370AF3440543DF87FCEED3B404C3DF87FCEED3540816D2B1370AF3A403C7FF97DBD713740DD33404FDEC2394081B5F495542C39400AB3028F30313940FFBE479B910C3B400000000000003940F7FFFFFFFFFF3C4007B3028F30313940EF40B8646EF33E40D633404FDEC2394037A505B5D5694040776D2B1370AF3A405B40034121474140483DF87FCEED3B4054E103C018094240377FF97DBD713D403E496AF647A842407EB5F495542C3F4011E65FD8901E43407DDFA3CD488640407AA67EB867674340F9FFFFFFFF7F4140000000000080434075205C32B77942407DA67EB86767434035A505B5D569434016E65FD8901E4340584003412147444046496AF647A8424052E103C0180945405EE103C0180942403C496AF647A84540674003412147414010E65FD8901E464044A505B5D56940407AA67EB8676746400B41B8646EF33E4000000000008046400000000000003D40'::geometry)
Planning Time: 1.393 ms
Execution Time: 0.181 ms
The same query after creating the new index:
Bitmap Heap Scan on huge_spatial_table  (cost=17.90..46.92 rows=1 width=32) (actual time=0.011..0.012 rows=0 loops=1)
  Recheck Cond: ((st_m(timegeom) > '1356998400'::double precision) AND (st_m(timegeom) < '1514764800'::double precision))
  Filter: st_contains(timegeom, '0103000000010000002100000000000000008046400000000000003D407CA67EB86767464008BF479B910C3B4013E65FD8901E46408AB5F495542C394042496AF647A84540437FF97DBD71374059E103C018094540523DF87FCEED354060400341214744407E6D2B1370AF34403DA505B5D5694340DC33404FDEC233407E205C32B77942400AB3028F303133400200000000804140000000000000334086DFA3CD4886404008B3028F303133408EB5F495542C3F40D833404FDEC23340467FF97DBD713D407A6D2B1370AF3440543DF87FCEED3B404C3DF87FCEED3540816D2B1370AF3A403C7FF97DBD713740DD33404FDEC2394081B5F495542C39400AB3028F30313940FFBE479B910C3B400000000000003940F7FFFFFFFFFF3C4007B3028F30313940EF40B8646EF33E40D633404FDEC2394037A505B5D5694040776D2B1370AF3A405B40034121474140483DF87FCEED3B4054E103C018094240377FF97DBD713D403E496AF647A842407EB5F495542C3F4011E65FD8901E43407DDFA3CD488640407AA67EB867674340F9FFFFFFFF7F4140000000000080434075205C32B77942407DA67EB86767434035A505B5D569434016E65FD8901E4340584003412147444046496AF647A8424052E103C0180945405EE103C0180942403C496AF647A84540674003412147414010E65FD8901E464044A505B5D56940407AA67EB8676746400B41B8646EF33E4000000000008046400000000000003D40'::geometry)
  ->  BitmapAnd  (cost=17.90..17.90 rows=1 width=0) (actual time=0.009..0.010 rows=0 loops=1)
        ->  Bitmap Index Scan on idx_t_geom  (cost=0.00..4.36 rows=10 width=0) (actual time=0.009..0.009 rows=0 loops=1)
              Index Cond: (timegeom ~~ '0103000000010000002100000000000000008046400000000000003D407CA67EB86767464008BF479B910C3B4013E65FD8901E46408AB5F495542C394042496AF647A84540437FF97DBD71374059E103C018094540523DF87FCEED354060400341214744407E6D2B1370AF34403DA505B5D5694340DC33404FDEC233407E205C32B77942400AB3028F303133400200000000804140000000000000334086DFA3CD4886404008B3028F303133408EB5F495542C3F40D833404FDEC23340467FF97DBD713D407A6D2B1370AF3440543DF87FCEED3B404C3DF87FCEED3540816D2B1370AF3A403C7FF97DBD713740DD33404FDEC2394081B5F495542C39400AB3028F30313940FFBE479B910C3B400000000000003940F7FFFFFFFFFF3C4007B3028F30313940EF40B8646EF33E40D633404FDEC2394037A505B5D5694040776D2B1370AF3A405B40034121474140483DF87FCEED3B4054E103C018094240377FF97DBD713D403E496AF647A842407EB5F495542C3F4011E65FD8901E43407DDFA3CD488640407AA67EB867674340F9FFFFFFFF7F4140000000000080434075205C32B77942407DA67EB86767434035A505B5D569434016E65FD8901E4340584003412147444046496AF647A8424052E103C0180945405EE103C0180942403C496AF647A84540674003412147414010E65FD8901E464044A505B5D56940407AA67EB8676746400B41B8646EF33E4000000000008046400000000000003D40'::geometry)
        ->  Bitmap Index Scan on idx_t_geom_m  (cost=0.00..13.29 rows=500 width=0) (never executed)
              Index Cond: ((st_m(timegeom) > '1356998400'::double precision) AND (st_m(timegeom) < '1514764800'::double precision))
Planning Time: 0.621 ms
Execution Time: 0.044 ms

Demo 1: db<>fiddle

Demo 2: db<>fiddle

Note: PostgreSQL 9.6 will reach EOL in 5 months, so I strongly suggest you to upgrade your system (currently 13). You're missing tons of really kickass features.

user_22753880
Related