0

I wrote a Spring-Application where I use the com.google.maps.GeocodingApi.GeocodingApi to retreive coordinates for addresses. After that I use the method com.vividsolutions.jts.geom.GeometryFactory.createPoint() to generate a Point I can store in my Oracle Database. I created the GeometryFactory like this: new GeometryFactory(new PrecisionModel(), 3857).

I built a spatial index on the coordinate column in the specific Table with the following code, so that I can run spatial Querys on it:

INSERT INTO USER_SDO_GEOM_METADATA(TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES('ADRESSE', 'KOORDINATE', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', -180, 180, 0.05), SDO_DIM_ELEMENT('Y', -90, 90, 0.05)), 3857);

CREATE INDEX koords ON ADRESSE (KOORDINATE) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('layer_gtype=POINT sdo_max_memory=200000000');

When I just retreive the coordinates and present them on a Google Maps map, everything works as expected. The problem I have is, that the spatial querys I use to filter the data don't seem to work properly. I am using SDO_WITHIN_DISTANCE to get all coordinates that are inbound a 100 km radius from a coordinate I am using as a parameter. After that, I use SDO_GEOM.SDO_DISTANCE to calculate the distance between the coordinates in km. The resulting markers on the map are always in a spherical form instead of a circle and the calculated distances are also totally wrong. I tried many SRID's, but the calculations are always wrong. I suspect that I have to transform the coordinates or that I have to store them with a differnt SRID, so that the calculations made by the Oracle functions give me the expected results, but since now I couldn't make it work.

Improve this question
6
  • Can you show the content of your ADDRESSE table ? Just one row is enough. Google returns coordinates in WGS84 long/lat coordinates. This is SRID 4326, but you specified 3857, which is incorrect - unless you transformed them explicitly to that projection. Commented Jul 15, 2020 at 5:12
  • @AlbertGodfrind : I got the SRID 3857 from [docs.oracle.com/database/121/SPATL/…. Even if I try to calculate the distance manually between two coordinates I took from Google maps, the result is wrong. SELECT SDO_GEOM.SDO_DISTANCE( SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(52.018156, 8.703915, NULL), NULL, NULL), SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(52.0085445, 8.6942913, NULL), NULL, NULL), 0.5, 'unit=KM' ) FROM dual; gives me 1.5km but if I measure the distance directly on Google Maps it's just 1.26km. Commented Jul 16, 2020 at 6:20
  • Just to be clear: all spatial data stores record and process geographical coordinates in longitude/latitude. As written (longitude 52.0, latitude 8.7) that point is in the ocean some 200 km east from the coast of Somalia. Written as (longitude 8.7, latitude 52.0) it is a point some 5 km east from Essen. Commented Jul 16, 2020 at 8:12
  • About SRID 3857: this is the code for a global Mercator projection introduced by google and used in the maps published by Google Maps and used since by the majority of online map producers. Note that it is not designed for storing any data. Note also that it is mathematically incorrect: it us based on a fully spherical representation of the earth. But in reality the earth is slightly flattened at the poles and is more accurately modeled as an ellipsoid. Commented Jul 16, 2020 at 9:09
  • The most common ellipsoid is the WGS84 ellipsoid, used by the GPS system. The coordinates you get from google are in WGS84 decimal degrees. The SRID for those is 4326. Note that other ellipsoids are in use for local projections. But unless specified otherwise, “long/lat” means WGS84 (code 4326). Commented Jul 16, 2020 at 9:11

1 Answer 1

Reset to default
0

As Albert Godfrind mentioned in his comments, I was using the wrong SRID and additionally mixed up the latitude with longitude.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.