3

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.

  • Please Edit the question to include what you have tried, and what error(s) you have encountered. "Will this product work for me?" is quite broad and may be dependent on what exactly you are doing. – Vince Dec 27 '17 at 12:26
  • 1
    @Vince I add sql quires to make my question is more clear. – user1244932 Dec 28 '17 at 9:30
  • The st_dwithin you specified is true if the point is within 1 degree of the poly. Which is true. But it isn't inside the poly, which you are checking in spatialite. – Lennert De Feyter Dec 28 '17 at 9:42
  • @LennertDeFeyter Why it is not in poly? 50 < 51 < 53 and longitude -179.5 lay between -179 and 179 – user1244932 Dec 28 '17 at 9:47
  • @LennertDeFeyter and st_dwithin for geography type take distance_meters not in degrees, see postgis.net/docs/ST_DWithin.html – user1244932 Dec 28 '17 at 9:50
4

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.

  • I changed to MakePoint(-179.5, 51, 4326), but it still gives 0 instead of 1 – user1244932 Dec 28 '17 at 14:05
  • No wonder because now the point is totally outside your polygon - to the left of the leftmost border. It seems that you did the same wrong thinking even before when you wrote longitude -179.5 lay between -179 and 179. Your polygon does not cross the dateline. – user30184 Dec 28 '17 at 14:07
  • I completely not understand you, why -179.5 not lay between -179 and 179? -180 = 180, so we get sequence -179, -179.5, -180=180, 179.5, 179, where is mistake here? – user1244932 Dec 28 '17 at 14:17
  • 1
    For SpatiaLite -180 != 180. As SpatiaLite geometry your polygon is located between -179 and +179 degrees and -179.5 is less than -179. PostGIS geography goes always the shortest route, see 4.2.3. Geography Advanced FAQ in postgis.net/docs/manual-1.5/ch04.html. – user30184 Dec 28 '17 at 14:22
  • but what the point then in spatialite if it can not handle corner cases like poles and 180 longitude? Without this functionality it is the same as sqlite rtree? – user1244932 Dec 28 '17 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.