[postgis-devel] PostGIS 3 and MobilityDB

[Esteban Zimanyi]

The geography functions that could be useful for PostGIS can be tested,
e.g., on a docker version of MobilityDB
https://github.com/MobilityDB/MobilityDB-docker

An example of usage of the functions is as follows.

------------------------------------------------------
drop table if exists brussels_moscow;
create table brussels_moscow(line) as
select geography 'Linestring(4.35 50.85, 37.617222 55.755833)';

drop table if exists berlin;
create table berlin(point) as select geography 'Point(13.405 52.52)';

drop table if exists dusseldorf;
create table dusseldorf(point) as select geography 'Point(6.783333
51.233333)';

select ST_AsText(ST_ClosestPoint(line, point)) from brussels_moscow,
dusseldorf;
-- POINT(6.56089044607685 51.5289887332848)

select ST_AsText(ST_ClosestPoint(line, point)) from brussels_moscow, berlin;
-- POINT(13.0094047494337 53.1773032048536)

select ST_AsText(ST_ShortestLine(line, point)) from brussels_moscow, berlin;
-- LINESTRING(13.0094047494337 53.1773032048536,13.405 52.52)

select ST_Length(ST_Transform(ST_ShortestLine(line, point)::geometry,
5676)) from brussels_moscow, dusseldorf;
-- 36353.42804126981

select ST_Length(ST_Transform(ST_ShortestLine(line, point)::geometry,
5676)) from brussels_moscow, berlin;
-- 78066.92633442582

select ST_LineLocatePoint(line, ST_ClosestPoint(line, point)) from
brussels_moscow, dusseldorf;
-- 0.07611238023414664

select ST_LineLocatePoint(line, ST_ClosestPoint(line, point)) from
brussels_moscow, berlin;
-- 0.28667310815692687

select ST_AsText(ST_LineSubstring(line, 0.07611238023414664,
0.28667310815692687)) from brussels_moscow;
-- LINESTRING(6.52323436157496 51.5179359226452,12.9480926557099
53.1638122041852)

select ST_Length(ST_Transform(ST_LineSubstring(line, 0.07611238023414664,
0.28667310815692687)::geometry, 5676)) from brussels_moscow;
-- 474822.8892579055

select ST_AsText(ST_LineInterpolatePoint(line, 0.5, true)) from
brussels_moscow;
-- POINT(19.9998576749004 54.4649673133545)

select ST_AsText(ST_LineInterpolatePoints(line, 0.2, true, true)) from
brussels_moscow;
--  MULTIPOINT(10.2325875123559 52.5254342648845,16.6347759217365
53.9056793854838,23.4500503284172 54.9283329391853,30.512938399544
55.5502632631239,37.617222 55.755833)
------------------------------------------------------

Since you mention geodetic functions, we implemented a year ago some
> geography functions to be submitted as PR to PostGIS but we haven't had
> time to do it. These functions are geography_closestpoint,
> geography_shortestline, geography_line_substring,
> geography_line_interpolate_point, and geography_line_locate_point. You can
> find these functions at the following address
>
> https://github.com/MobilityDB/MobilityDB/blob/develop/point/src/geography_functions.c
>
> It may be useful to have such functionality in PostGIS.
>
> Regards
>
> Esteban
>
> On Fri, Jul 9, 2021 at 5:54 PM Paul Ramsey <pramsey at cleverelephant.ca>
> wrote:
>
>> I really wonder whether just copying over the liblwgeom functions they
>> really really need into their own code base isn't the most straight-forward
>> thing. Once that's done the only real point-of-integration is the
>> serialization, and that only changes once every 10 years, so... keeping an
>> eye on the postgis serialization code to watch for changes once a year is a
>> relatively small price to pay.
>>
>> Everything else is just "useful functionality that happens to be in
>> postgis", and can probably just be copied across and given a new function
>> name prefix to avoid collisions. At that point they are stuck maintaining
>> their copy, but it's not like postgis is some massively churning code base
>> at this point. Core lwgeom stuff changes infrequently at most. You might
>> see some re-writes in geodetic from me, but that wouldn't invalidate the
>> current code if they had a working copy.
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20210711/5788183b/attachment.html>