1

I've been playing around with H2GIS (http://www.h2gis.org/) for a project at work and am struggling to get the H2 functions to operate in spherical space (lat/ lon), rather than cartesian space.

The problem is basically this. I want to store points in a geometry column of an H2 table using the WKT format, where the coordinates represent a lat/ lon pair (i.e: 'POINT (0 0)' would be the point where the prime-meridian crosses the equator).

So I create my table and insert a point into it like:

CREATE TABLE GeoTable(location GEOMETRY);
INSERT INTO GeoTable (location) VALUES ('POINT (45 45)'); 

I did some calculations by hand to figure out how far 'POINT (45 45)' would be from 'POINT (0 0)' in spherical and cartesian space using the Haversine and Euclidean distance methods respectively to devise a test that should fail in Cartesian space but succeed in Spherical space. The numbers I used work out to a Cartesian distance of 7076.77 km (assuming a distance of 111.2km between degrees of latitude, which I understand is only accurate at the equator). The distance computed using the Haversine formula (calculated using http://andrew.hedges.name/experiments/haversine/) is roughly 6673.79 km. When measuring the distance on Google Earth, the haversine distance is within 20km, however the cartesian distance is more than 400km off, so given a difference of 20x in the error over large distances we want to make sure predicates supported by H2GIS can operate in Spherical space.

The test I've devised to confirm we can use H2GIS predicates in spherical space uses the ST_DWithin method to select all points within 6700km of 'POINT (0 0)', which should return 'POINT (45 45)' in Spherical space, but not in Cartesian space.

CREATE TABLE IF NOT EXISTS GeoTable(location GEOMETRY);
INSERT INTO GeoTable (location) VALUES ('POINT (45 45)'); 
SELECT * FROM GeoTable WHERE ST_DWITHIN(ST_GeomFromText('POINT (0 0)', 4326), ST_GeomFromText(location, 4326), 63);

If that final select returns 'POINT (45 45)', then I know that the distance between the points is being calculated in spherical space, as the required distance in cartesian space is > 69 degrees (calculated by dividing the cartesian distance in km between the points by the number of km per degree, again using 111.2km/degree). If we are in spherical space, then the distance should be ~ 60 degrees. I can't get this test to pass, regardless of how many ST_Transforms into WGS-84 (using SRID=4326) I apply to location or the input POINT, which leads (finally) to my question: How can I modify/ transform geometry values in H2 so H2GIS predicates function treat coordinate pairs as lat/ lon pairs and compute distances in spherical space?

I've seen that this is supported by tools like PostGIS (with a geography type that is differentiated from geometry types). Is this something that H2GIS simply doesn't support?

  • I think that you are right and H2GIS does not have support for that. – user30184 Jun 5 '16 at 7:50
  • In cartesian space distance from (0,0) to (45,45) is by Pythagoras: square root of (sqr(45)+sqr(45)) = 63.63961. But calculating distances in degrees on the surface of sphere is rather useless. – user30184 Jun 5 '16 at 8:40
  • Right, approximating the distance between degrees latitude in km when calculating the euclidean distance doesn't work because the distance between degrees of latitude is not constant. It appears that the way H2GIS computes distance between geometries, even when given WGS-84 encoded coordinate points, is to compute the euclidean distance, which results in an answer in the same unit as the input coordinate points, so degrees in this case, which as you pointed out is useless (and trying to convert to km using the number of km/ degree at the equator introduces the error described above). – Jake Jun 6 '16 at 16:31
  • The challenge boils down to storing coordinate points in a column using lat/ lon encoding, but then be able to support finding points within a distance that is provided in meters/ km. – Jake Jun 6 '16 at 16:32
  • An example of a DB that supports this would be mongo: docs.mongodb.com/manual/reference/operator/query/nearSphere/… – Jake Jun 6 '16 at 16:34

Your Answer

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

Browse other questions tagged or ask your own question.