expandspatial artifact or incorrect call?

[apiszcz]

Performing expandspatial then reviewing the resulting stbox geometry is producing behavior I do not understand. Perhaps it is the ST_AsEWKT, seems unlikely? Typically a polygon has the same x,y for start and end location?

   ST_AsEWKT(transform(expandSpatial(stbox(t1.trip), p.rmeters),4326)::geometry) as ewkt_bb_a,
   ST_AsEWKT(transform(expandSpatial(stbox(t2.trip), p.rmeters),4326)::geometry) as ewkt_bb_b,

Figure 1, note artifact upper left "POLYGON((0.008084837557076 0.010240794238963,0.008084837530246 0.010061131130432,0.010240794238963 0.010061131130432,0.010240794238963 0.008084837530246,0.008084837557076 0.008084837530246))"

Figure 2, note

SRID=4326;POLYGON((0.008983152841195 0.010959446466258,0.008084837530246 0.010061131130432,0.010959446466258 0.010061131130432,0.010959446466258 0.008084837530246,0.008983152841195 0.008084837530246))

[estebanzimanyi]

Many thanks for pointing out this issue!

In order to reproduce it and work on it, do you think is it possible that you give us the data for the two examples you have shown in the map ?

[apiszcz]

WITH T(id,trip) as (
  SELECT  	        1,setsrid(tgeompoint '[Point(1000 1000)@2012-02-01T00:01:01, Point(1020 1010)@2012-02-01T00:01:02, Point(1040 1020)@2012-02-01T00:01:03]',3857)
  union all SELECT	2,setsrid(tgeompoint '[Point(1100 1000)@2012-02-01T00:01:05, Point(1120 1010)@2012-02-01T00:01:06, Point(1110 1020)@2012-02-01T00:01:08]',3857)
  union all SELECT	3,setsrid(tgeompoint '[Point(1200 1000)@2012-02-01T00:01:10, Point(1140 1010)@2012-02-01T00:01:20, Point(1160 1020)@2012-02-01T00:01:40]',3857)
) 
select
  ST_AsEWKT(transform(expandSpatial(stbox(t1.trip), 100),4326)::geometry) as ewkt_bb_a,
  ST_AsEWKT(transform(expandSpatial(stbox(t2.trip), 100),4326)::geometry) as ewkt_bb_b
FROM T t1, T t2
where t1.id < t2.id

[estebanzimanyi]

A simplified version of the query could be as follows

WITH T(id,trip) as ( SELECT 1, tgeompoint 'SRID=3857;[Point(1000 @.:01:01, Point(1020 @.:01:02, Point(1040 @.:01:03]' UNION ALL SELECT 2, tgeompoint 'SRID=3857;[Point(1100 @.:01:05, Point(1120 @.:01:06, Point(1110 @.:01:08]' UNION ALL SELECT 3, tgeompoint 'SRID=3857;[Point(1200 @.:01:10, Point(1140 @.:01:20, Point(1160 @.***:01:40]' ) SELECT ST_AsEWKT((trip::stbox)::geometry), ST_AsEWKT(expandSpatial(trip, 100)::geometry), ST_AsEWKT(transform(expandSpatial(trip, 100), 4326)::geometry) FROM T;

SRID=3857;POLYGON((1000 1000,1000 1020,1040 1020,1040 1000,1000 1000)) | SRID=3857;POLYGON((900 900,900 1120,1140 1120,1140 900,900 900)) | SRID=4326;POLYGON((0.008084837557076 0.008084837530246,0.008084837557076 0.010061131130432, 0.010240794238963 0.010061131130432,0.010240794238963 0.008084837530246,0.008084837557076 0.008084837530246))

SRID=3857;POLYGON((1100 1000,1100 1020,1120 1020,1120 1000,1100 1000)) | SRID=3857;POLYGON((1000 900,1000 1120,1220 1120,1220 900,1000 900)) | SRID=4326;POLYGON((0.008983152841195 0.008084837530246,0.008983152841195 0.010061131130432, 0.010959446466258 0.010061131130432,0.010959446466258 0.008084837530246,0.008983152841195 0.008084837530246))

SRID=3857;POLYGON((1140 1000,1140 1020,1200 1020,1200 1000,1140 1000)) | SRID=3857;POLYGON((1040 900,1040 1120,1300 1120,1300 900,1040 900)) | SRID=4326;POLYGON((0.009342478954843 0.008084837530246,0.009342478954843 0.010061131130432, 0.011678098693554 0.010061131130432,0.011678098693554 0.008084837530246,0.009342478954843 0.008084837530246))

(3 rows)

As far as I can see all polygons are of the form POLYGON((A B,A D,C D,C B,A B))

Is it maybe an issue in the way the geodetic polygons are displayed in QGIS ?

---

Prof. Esteban Zimanyi Department of Computer & Decision Engineering (CoDE) CP 165/15 Universite Libre de Bruxelles Avenue F. D. Roosevelt 50 B-1050 Brussels, Belgium fax: + 32.2.650.47.13 tel: + 32.2.650.31.85 e-mail: @.*** Internet: http://cs.ulb.ac.be/members/esteban/

On Sun, Sep 11, 2022 at 11:11 AM ap @.***> wrote:

WITH T(id,trip) as ( SELECT 1,setsrid(tgeompoint '[Point(1000 @.:01:01, Point(1020 @.:01:02, Point(1040 @.:01:03]',3857) union all SELECT 2,setsrid(tgeompoint '[Point(1100 @.:01:05, Point(1120 @.:01:06, Point(1110 @.:01:08]',3857) union all SELECT 3,setsrid(tgeompoint '[Point(1200 @.:01:10, Point(1140 @.:01:20, Point(1160 @.***:01:40]',3857) ) select ST_AsEWKT(transform(expandSpatial(stbox(t1.trip), 100),4326)::geometry) as ewkt_bb_a, ST_AsEWKT(transform(expandSpatial(stbox(t2.trip), 100),4326)::geometry) as ewkt_bb_b FROM T t1, T t2 where t1.id < t2.id

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.Message ID: @.***>

[apiszcz]

Hi, thanks for the review. I am using 1.0, is it possible this is different with develop?

Using your example the last point is not the same as the first point in my output below.

WITH T(id,trip) as ( SELECT 1,setsrid(tgeompoint '[Point(1000 1000)@2012-02-01T00:01:01, Point(1020 1010)@2012-02-01T00:01:02, Point(1040 1020)@2012-02-01T00:01:03]',3857) union all SELECT 2,setsrid(tgeompoint '[Point(1100 1000)@2012-02-01T00:01:05, Point(1120 1010)@2012-02-01T00:01:06, Point(1110 1020)@2012-02-01T00:01:08]',3857) union all SELECT 3,setsrid(tgeompoint '[Point(1200 1000)@2012-02-01T00:01:10, Point(1140 1010)@2012-02-01T00:01:20, Point(1160 1020)@2012-02-01T00:01:40]',3857) ) select ST_AsEWKT((trip::stbox)::geometry), ST_AsEWKT(expandSpatial(trip, 100)::geometry), ST_AsEWKT(transform(expandSpatial(trip, 100), 4326)::geometry) FROM T t

SRID=3857;POLYGON((1000 1040,1000 1020,1040 1020,1040 1000,1000 1000))
SRID=3857;POLYGON((1100 1120,1000 1020,1120 1020,1120 1000,1100 1000))
SRID=3857;POLYGON((1140 1200,1000 1020,1200 1020,1200 1000,1140 1000))

SRID=3857;POLYGON((900 1140,900 1120,1140 1120,1140 900,900 900))
SRID=3857;POLYGON((1000 1220,900 1120,1220 1120,1220 900,1000 900))
SRID=3857;POLYGON((1040 1300,900 1120,1300 1120,1300 900,1040 900))

SRID=4326;POLYGON((0.008084837557076 0.010240794238963,0.008084837530246 0.010061131130432,0.010240794238963 0.010061131130432,0.010240794238963 0.008084837530246,0.008084837557076 0.008084837530246))
SRID=4326;POLYGON((0.008983152841195 0.010959446466258,0.008084837530246 0.010061131130432,0.010959446466258 0.010061131130432,0.010959446466258 0.008084837530246,0.008983152841195 0.008084837530246))
SRID=4326;POLYGON((0.009342478954843 0.011678098693554,0.008084837530246 0.010061131130432,0.011678098693554 0.010061131130432,0.011678098693554 0.008084837530246,0.009342478954843 0.008084837530246))

[estebanzimanyi]

Hi, thanks for the review. I am using 1.0, is it possible this is different with develop?

We have heavily modified the codebase from 1.0 to 1.1 in order to enable in the near future MEOS and PyMEOS https://www.libmeos.org/ https://github.com/MobilityDB/PyMEOS This is still work in progress but we are gradually finalizing the work.

Is it possible that you may envision using version 1.1 ?

[apiszcz]

Yes, I'll switch to the latest, I do not see mobilitydb 1.1 at https://github.com/MobilityDB/MobilityDB/releases

Should I assume this is 1.1? git clone --branch develop https://github.com/MobilityDB/MobilityDB

Thank you

[estebanzimanyi]

We are not ready yet for the release. However your git command will take you to the working version 1.1.

[apiszcz]

I think i installed 1.1, however. Will review, source, assume the version command is producing 1.1? I may need to review how to uninstall 1.0 before installing 1.1

tmp/MobilityDB/build# ls
CMakeCache.txt         MobilityDB           install_manifest.txt   test_mobilitydb--1.1.0.sql
CMakeFiles             Testing              libMobilityDB-1.1.so   test_mobilitydb--1.1.0.sql.in
CTestTestfile.cmake    cmake_install.cmake  mobilitydb
DartConfiguration.tcl  doc                  mobilitydb--1.1.0.sql
Makefile               doxygen              mobilitydb.control
root@a9fd11dc2a69:/tmp/MobilityDB/build# 

SELECT extname,extversion FROM pg_extension;
postgres=# SELECT extname,extversion FROM pg_extension;
  extname   | extversion 
------------+------------
 plpgsql    | 1.0
 postgis    | 3.2.0
 mobilitydb | 1.0.0

(6 rows)

[estebanzimanyi]

I think i installed 1.1, however. Will review, source, assume the version command is producing 1.1?

Have you created a clean installation ? This requires in particular dropping the previous 1.0 database and creating it again.

In my machine the output of your query is as follows

test=# SELECT extname,extversion FROM pg_extension; extname | extversion ------------+------------ plpgsql | 1.0 postgis | 3.3.0 mobilitydb | 1.1.0 (3 rows)

[apiszcz]

Probably not, it a clean MobilityDB directory in tmp. However can you point me to the steps to uninstall or drop the previous version? Thank you.

DROP EXTENSION — remove an extension

All set, will test issue next

SELECT extname,extversion FROM pg_extension;
postgres=# create extension mobilitydb;
CREATE EXTENSION
postgres=# SELECT extname,extversion FROM pg_extension;
  extname   | extversion 
------------+------------
 plpgsql    | 1.0
 postgis    | 3.2.0
 mobilitydb | 1.1.0

[estebanzimanyi]

Probably not, it a clean MobilityDB directory in tmp. However can you point me to the steps to uninstall or drop the previous version? Thank you.

Normally by cleaning the MobilityDB/build directory and start again. Please notice that the following is DANGEROUS if not in the right directory

cd MobilityDB/build // <------------- important to be in this directory rm -rf * // <--------------- DANGEROUS cmake .. make -j sudo make install pg_ctl restart dropdb createdb psql #create extension mobilitydb cascade;

Please let me know whether this works.

Message ID: @.***>

[apiszcz]

Hi, 1.1 corrects the issue. Here the steps i used to update, including below for future reference. Is there an MobilityDB 1.1 estimated release year/month?

#1 log into running postgres, 

postgres=# drop database mdb;
DROP DATABASE
drop extension mobilitydb
drop database mdb

#2 stop server 
  /usr/lib/postgresql/14/bin/pg_ctl -D /db stop


#3 install new version
    cd /tmp \
    && git clone --branch develop https://github.com/MobilityDB/MobilityDB \
    && mkdir MobilityDB/build \
    && cd MobilityDB/build \
    && cmake .. \
    && make \
    && make install


#5 postgres=# create database mdb;
CREATE DATABASE
create extension postgis
create extension mobilitydb

/usr/lib/postgresql/14/bin/pg_ctl -D /db start

Sample query
WITH T(id,trip) as (
  SELECT  	        1,setsrid(tgeompoint '[Point(1000 1000)@2012-02-01T00:01:01, Point(1020 1010)@2012-02-01T00:01:02, Point(1040 1020)@2012-02-01T00:01:03]',3857)
  union all SELECT	2,setsrid(tgeompoint '[Point(1100 1000)@2012-02-01T00:01:05, Point(1120 1010)@2012-02-01T00:01:06, Point(1110 1020)@2012-02-01T00:01:08]',3857)
  union all SELECT	3,setsrid(tgeompoint '[Point(1200 1000)@2012-02-01T00:01:10, Point(1140 1010)@2012-02-01T00:01:20, Point(1160 1020)@2012-02-01T00:01:40]',3857)
) 
select
  ST_AsEWKT((trip::stbox)::geometry),
  ST_AsEWKT(expandSpatial(trip, 100)::geometry),
  ST_AsEWKT(transform(expandSpatial(trip, 100), 4326)::geometry)
FROM T t

Column 1

Column 1 with 2

[estebanzimanyi]

We have just started the tests with MEOS. We are planning to release both MobilityDB 1.1 and MEOS together in, say, 3 months, after having tested MEOS intensively with real applications.

[apiszcz]

Thank you.

[estebanzimanyi]

The pre-release 1.1 of MobilityDB is out. https://github.com/MobilityDB/MobilityDB/releases/tag/v1.1.0-alpha