3

I have a working POSTGIS database, loaded Dutch CBS data using shp2pgsql and now I want to map lat,lon coordinates to multipolygons.

All examples I see use lon lat coordinates but if I do so I get huge distance to my geom objects... If I use coordinates from http://epsg.io/28992/map# it works...

SELECT gm_naam, 
st_distance(geom, ST_SetSRID(ST_MakePoint(5.112472, 52.008417),28992) ) as dist,
ST_Distance(geom, ST_SetSRID(ST_Point(134989.82, 448191.66),28992) ) as dist2
FROM nlstaging.gem_2013
order by dist2 Asc

The result is like this:

gm_naam dist    dist2
Nieuwegein  465085.92145570647  0.0
IJsselstein 464133.17518803617  2018.4612972214297
Houten  463571.9453450817   2037.1431117615386
Vianen  459472.14358348626  2102.6589540861714
Utrecht 467845.0647365362   2308.3854923630483

So Nieuwegein is the right answer, dist2 is coorect calculation but the coordinates are NOT lon,lat ...

Question: How can I specify a point using lon lat and have correct distance?

1 Answer 1

6

Your table seems to be an Amersfoort oblique stereographic (EPSG:28992). To use a lon/lat point as input, you need to create it as a lon/lat point, then transform it, so your distance function would look something like this:

ST_Distance(geom, ST_Transform(ST_SetSRID(ST_MakePoint(5.112472, 52.008417),4326),28992)) 

Your Answer

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

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