search

FraunhoferIOSB / FROST-Server

A Complete Server implementation of the OGC SensorThings API
https://fraunhoferiosb.github.io/FROST-Server/
GNU Lesser General Public License v3.0
199 stars 74 forks source link

geo distance between two locations using longitude and latitude in meters #18

Closed sudhamani1983 closed 6 years ago

sudhamani1983 commented 7 years ago

Hi,

We are trying to get location within 50 meters from given ‘Point’. I have two points from Google Map and stored this two points in STA LOCATION table. Distance between 2 points is 101.23 meter.

Point 1 : Dolce&Gabbana - 103.831709,1.304389 (longitude, latitude)

image

Point 2 : Sephora - 103.832342,1.30373 (longitude, latitude)

image

STA LOCATION Table data

image

SRID projection: image

Issue : I expect below API call return back location “Sephora” but its return empty JSON.

Request : http://localhost:8080/SensorThingsService/v1.0/Locations?$filter=geo.distance(location,geography'POINT(103.831709 1.304389)') gt 50

Response : { "@iot.count": 0, "value": [] }

What is the unit of geo.distance filter result? Is SensorThing API has any functions to get geo distance in meters?

manuGil commented 7 years ago

The input units expected by geo.distance are the same units of the SRID in the database. By default this implementation of SensorThingsAPI uses 4326, which has as units degrees. Therefore, your call is requesting for locations that fall 50 degrees away from your POINT. Roughly 111 Kms in average for your case. I also can see that your database is using 4326 and 4269, be careful here because requests to the API do not include information about which SRID should be used. The SensorThingsAPI does not specify how to perform re-projections or transformations as part of a request. Therefore, mixing units should be avoided. I recommend you stick to the SRID and units that will fit the best for your case (meters). Some references here: Spatial refernces Or you could use a middle ware to do the conversion before passing the request to the API.

hylkevds commented 7 years ago

It's exactly as @manuGil explained.

You can change the srid of the database tables, and that will change the unit of the geo.distance and geo.length functions. I've not tested it though, and you might hit some bugs.

You might want to keep track of issue https://github.com/opengeospatial/sensorthings/issues/25 on the SenosorThingsAPI github, since that deals with this exact topic.

sudhamani1983 commented 7 years ago

I have modified SRID to 3857 (measure in meters). I get below error while inserting location. as below

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1455)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)

Caused by: org.postgresql.util.PSQLException: ERROR: Geometry SRID (4326) does not match column SRID (3857) at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168) at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:135) at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97) at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97) at com.querydsl.sql.dml.SQLInsertClause.executeWithKeys(SQLInsertClause.java:368) ... 39 common frames omitted

image

SensorthingAPI Request:

Request : http://localhost:8080/SensorThingsService/v1.0/Locations

Request Body : { "name": "Sephora", "encodingType": "application/vnd.geo+json", "description": "Sephora", "location": { "type":"Point", "coordinates": [103.832342,1.30373] }
}

hylkevds commented 7 years ago

That is unfortunate. I had hoped PostgreSQL would automatically convert the object to the correct srid. A Quick hack is to change line 1394 and set the srid to 3857 there. https://github.com/FraunhoferIOSB/SensorThingsServer/blob/62e03068d84b56ed4890aa9fca0dde646afb75b4/SensorThingsServer.SQL/src/main/java/de/fraunhofer/iosb/ilt/sta/persistence/postgres/EntityInserter.java#L1393

Another option would be to add a trigger to the Location and feature of Interest tables, like mentioned in https://gis.stackexchange.com/questions/136137/auto-reprojecting-st-transform-srids-in-postgis/250384

I'll think about how to best solve this issue. Maybe I will make the srid configurable in the context xml file.

sudhamani1983 commented 7 years ago

I tried changing SRID 3857 in de.fraunhofer.iosb.ilt.sta.persistence.postgres.EntityInserter line 1394

clause.set(geomPath, Expressions.template(Geometry.class, "ST_Force2D(ST_Transform(ST_GeomFromGeoJSON({0}), 3857))", geoJson));

POST Location working fine.

But get location using below request has issue in GET API.

http://localhost:8080/SensorThingsService/v1.0/Locations?$filter=geo.distance(Locations/location, geography'POINT (103.831709 1.304389)') gt 50

Caught PSQLException for select "e1"."ID", "e1"."ID", "e1"."NAME", "e1"."DESCRIPTION", "e1"."ENCODING_TYPE", "e1"."LOCATION", "e1"."GEOM" from "LOCATIONS" "e1" where ST_Distance("e1"."GEOM", ?) > ? order by "e1"."ID" asc limit ? at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50) at com.querydsl.sql.Configuration.translate(Configuration.java:459) at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:502) at de.fraunhofer.iosb.ilt.sta.persistence.postgres.EntityCreator.visit(EntityCreator.java:198) at de.fraunhofer.iosb.ilt.sta.path.EntitySetPathElement.visit(EntitySetPathElement.java:58) at de.fraunhofer.iosb.ilt.sta.persistence.postgres.PostgresPersistenceManager.get(PostgresPersistenceManager.java:439) at de.fraunhofer.iosb.ilt.sta.service.Service.executeGet(Service.java:145) at de.fraunhofer.iosb.ilt.sta.service.Service.execute(Service.java:75) at de.fraunhofer.iosb.ilt.sta.Servlet_1_0.executeService(Servlet_1_0.java:91) at de.fraunhofer.iosb.ilt.sta.Servlet_1_0.processGetRequest(Servlet_1_0.java:67) at de.fraunhofer.iosb.ilt.sta.Servlet_1_0.doGet(Servlet_1_0.java:135) at javax.servlet.http.HttpServlet.service(HttpServlet.java:622) at de.fraunhofer.iosb.ilt.sta.Servlet_1_0.service(Servlet_1_0.java:173) at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) at org.apache.catalina.filters.CorsFilter.handleNonCORS(CorsFilter.java:410) at org.apache.catalina.filters.CorsFilter.doFilter(CorsFilter.java:169) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:108) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:783) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:745) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1455) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:745) Caused by: org.postgresql.util.PSQLException: ERROR: Operation on mixed SRID geometries at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168) at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:116)

hylkevds commented 7 years ago

Can you try changing the line: https://github.com/FraunhoferIOSB/SensorThingsServer/blob/62e03068d84b56ed4890aa9fca0dde646afb75b4/SensorThingsServer.SQL/src/main/java/de/fraunhofer/iosb/ilt/sta/persistence/postgres/expression/ConstantGeometryExpression.java#L38 to return this.transform(3857).accept(v, context);

sudhamani1983 commented 7 years ago

I have done change in "ConstantGeometryExpression" file as per your comments. Its working fine. I tried testing geo.distance using more places from googlemap and attached sample data as well.

Issue : But st_within(geography'POINT(103.832342 1.30375)', location) not return back correct result. Previously (Before changing SRID 3857 ) its working fine.

POINT(103.832342 1.30375) is within ION Orchard polygon, So I expect below request should return back Location - ION Orchard, but response is empty.

Request : POINT(103.832342 1.30375) is within ION Orchard polygon http://localhost:8080/SensorThingsService/v1.0/Locations?$filter=st_within(geography'POINT(103.832342 1.30375)', location)

Polygon location used for testing : { "name": "ION Orchard", "encodingType": "application/vnd.geo+json", "description": "ION Orchard", "location": { "coordinates": [ [ [103.831076,1.304072], [103.831943,1.304477], [103.832699,1.303807], [103.832020,1.303343], [103.831076,1.304072] ] ],"type":"Polygon"} }

Sample Test Data for geo.distance : geo.distance sample data.docx

DistanceDifferent.xlsx

Query :
select ST_Distance("e1"."GEOM", ST_Transform(ST_GeomFromText('SRID=4326;POINT(103.831709 1.304389)'), 3857)), "e1"."NAME" from "LOCATIONS" "e1"

hylkevds commented 7 years ago

Actually, PostGIS is correct here, your point is outside your polygon. See https://codepen.io/anon/pen/vexWag

sudhamani1983 commented 7 years ago

The coordinate I tried is 103.832342 1.30375, its within ION orchard polygon.

hylkevds commented 7 years ago

Hmm, odd, the accept method is only used for the second argument, not the first. using st_contains instead of st_within (with the arguments also the other way around ofcourse) does work... I'll have to look into that in more detail.

hylkevds commented 7 years ago

Can you revert the change to ConstantGeometryExpression And instead change the three occurrences of: https://github.com/FraunhoferIOSB/SensorThingsServer/blob/62e03068d84b56ed4890aa9fca0dde646afb75b4/SensorThingsServer.SQL/src/main/java/de/fraunhofer/iosb/ilt/sta/persistence/postgres/PgExpressionHandler.java#L432 to return new ConstantGeometryExpression(geom).transform(3857); The other two are on line 438 and 444

sudhamani1983 commented 7 years ago

Now st_within working as expected. Thanks for your support.

sudhamani1983 commented 7 years ago

Hi, My understanding is Google Maps uses EPSG 3857 projected coordinate system. We used same SRID 3857. Can you please help me to explain why distance from POSTGIS - ST_Distance has slight difference from GoogleMap distance calculation?

Query : select ST_Distance("e1"."GEOM", ST_Transform(ST_GeomFromText('SRID=4326;POINT(103.831709 1.304389)'), 3857)), "e1"."NAME" from "LOCATIONS" "e1"

Distance Difference : DistanceDifferent.xlsx

hylkevds commented 7 years ago

That is a question best asked on the PostGIS mailing list, since they implemented the function. How do you get the google maps distance calculation?

sudhamani1983 commented 7 years ago

Sorry Typo. I mean Google Map distance.

hylkevds commented 7 years ago

How did you get the exact coordinates of the points for the distance measurement in Google Maps? As far as I can see you can only more-or-less click somewhere, so those inaccuracies of up to 50 cm are to be expected.