spatialite geometry vs PostGIS geography

Question

When I worked with PostGIS I used geography type for geo objects with gps coordinates like latitude and longitude on the WGS-84 ellipsoid. Now I have to use SQLite and libspatialite correspondingly. I wonder, if I create column like this:

Select AddGeometryColumn ('my_table', 'Geometry', 4326, 'POINT', 2)

will queries with ST_Within/ST_Contains(geo_rect, my_table.geometry) find suitable points if geo_rect covers north pole, or 0,180 longitude?

here how it works in postgis:

    geo_demo=# select ST_DWithin(ST_GeographyFromText('POLYGON((-179 53, 179 53, 179 50, -179 50, -179 53))'),  ST_GeographyFromText('POINT(-179 61)'), 1.0, true);
 st_dwithin 
------------
 f
(1 row)

geo_demo=# select ST_DWithin(ST_GeographyFromText('POLYGON((-179 53, 179 53, 179 50, -179 50, -179 53))'),  ST_GeographyFromText('POINT(-179 51)'), 1.0, true);
 st_dwithin 
------------
 t
(1 row)

geo_demo=# 

as you see point with latitude 51,-179.5 inside geo box:

53, -179 <-> 53,179
50, -179 <->  50,179

but

spatialite> select Within(MakePoint(-179.5, 51, 4326), PolyFromText('POLYGON((-179 53, 179 53, 179 50, -179 50, -179 53))', 4326));
0

gives 0, but really point inside geo box.

Answer 1

The geometry model of SpatiaLite assumes a flat Cartesian plane. From https://groups.google.com/forum/#!topic/spatialite-users/5HLZIXzGxQ0

e.g. a LINESTRING(179.9999 0, -179.9999 0) doesn't cross at all the dateline: it simply goes westward for about 359.99 degrees along the equator.

So, each time you have to represent some geometry crossing the dateline you are always required to split such geometry in two halves: one laying at extreme right, the other at extreme left.

There is another thing in your example that adds confusion. The point MakePoint(-179, 51, 4326)) is on top of the outline of POLYGON((-179 53, 179 53, 179 50, -179 50, -179 53)) and therefore is is not within (totally inside) the polygon https://postgis.net/docs/ST_Within.html so SpatiaLite gives correct answer. The equivalent of ST_DWithin in SpatiaLite is "PtDistWithin" http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html.