3

I am trying to make a query (using raw query in sequelize orm for a PostgreSQL with PostGIS database) for a radius of a certain 'maxDistance' radius (preferably in meters):

const maxDistance = 100000000

const lat = 4.307662331310191
const lon = 52.09468958820517
const sequelize = hook.app.get('sequelizeClient')

return sequelize.query(`SELECT "id", "created_at", "wkb_geometry", ST_Distance(ST_SetSRID(ST_MakePoint(${parseFloat(lat)}, ${parseFloat(lon)}), 3857), "wkb_geometry") AS distance FROM "points" WHERE ST_Distance(ST_SetSRID(ST_MakePoint(${parseFloat(lat)}, ${parseFloat(lon)}), 3857), "wkb_geometry") < ${maxDistance}`)

When I create another point, i.e.: const HOME_LONLAT = [4.295980701608052, 52.09069818826458] in the database and run the query I get a result with a distance of 0.012344705180102 however the actual distance of these two geopoints is approx 930 meters. What am I doing wrong or how should I convert the distance to get the distance in meters?

EDIT: When I alter the query into:

'SELECT "id", "created_at", "wkb_geometry", ST_Distance(ST_GeogFromText(\'SRID=3857;POINT(4.307662331310191 52.09468958820517)\'), "wkb_geometry") AS distance FROM "points" WHERE ST_Distance(ST_GeogFromText(\'SRID=3857;POINT(4.307662331310191 52.09468958820517)\'), "wkb_geometry") < 100000000'

I get this error:

error: Only lon/lat coordinate systems are supported in geography

EDIT2: In PostGIS I have 3857 CRS: 

SELECT ST_SRID("wkb_geometry") FROM points;
 st_srid 
---------
    3857
Improve this question
17
  • 2
    cast geometry to geography > gis.stackexchange.com/questions/223845/…
    – Mapperz ♦
    Nov 27, 2018 at 19:26
  • 3
    on a side note you build the points using lat-long, but it should be long-lat instead, and these coordinates are not in 3857 but likely in 4326
    – JGH
    Nov 27, 2018 at 19:53
  • 4
    Side note: EPSG:3857 is rather useless for distance and area calculations. Take the geography route or if you work locally use some projected system with less distortion.
    – user30184
    Nov 27, 2018 at 20:31
  • 2
    Because your coordinates look like they are in degrees (0;0 in 3857 is in the ocean), and 4326 is a very popular CRS. Of course it is only a guess and you may want to double check your source CRS
    – JGH
    Nov 27, 2018 at 21:44
  • 2
    While you may be visualizing this in EPSG:3857, the coordinate data looks geodetic. They are most likely EPSG:4326.
    – Mike T
    Nov 27, 2018 at 23:24

1 Answer 1

Reset to default
1

It seems a common practice is to store in SRID 4326 (which enables accurate distance calculations etc.) and use SRID 3857 for mapping (to 2D), so my storage in SRID 3857 is 'working against me'.

After storing in SRID 4326 and making my mapserver serve the conversion to SRID 3857, I could alter my query into:

return sequelize.query(`SELECT "id", "created_at", "wkb_geometry", ST_Distance_Sphere(ST_SetSRID(ST_MakePoint(${parseFloat(lon)}, ${parseFloat(lat)}), 4326), "wkb_geometry") AS distance FROM "points" WHERE ST_Distance_Sphere(ST_SetSRID(ST_MakePoint(${parseFloat(lon)}, ${parseFloat(lat)}), 4326), "wkb_geometry") < ${maxDistance}`)

It gives the distance in meters now and is accurate.

BTW: The exact answer to the original question can not be given as I was storing srid 4326 data in srid 3857 postgis column which results in an undetermined radius calculation.

Improve this answer

Your Answer

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

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