Tweeted twitter.com/StackGIS/status/1517110937899130885

occurred Apr 21, 2022 at 12:00

naming

Source Link

edited Apr 21, 2022 at 11:32

20.3k
16
48
65

I've just used osm2pgsql to import osm data to a PostGIS database, using lat/long option to store it in WGS84 projection. I would like to use ST_DwithinST_DWithin to select nodes close to a random point. When I try that directly on the WGS84 data it returns wrong results but works if I reproject it in Web Mercator. What am i missing to avoid the reprojection?

SELECT ST_AsText(way),
       ST_X(ST_AsText(way)) as X,
       ST_Y(ST_AsText(way)) as Y,
       railway
  FROM planet_osm_point
 WHERE railway IS NOT NULL AND
       ST_DWithin(ST_Transform(way, 3785),
                  ST_Transform(ST_SetSRID(ST_MakePoint(-1.631560, 48.107485), 4326), 3785),
                  500)
 LIMIT 1000;

SELECT ST_AsText(way),
       ST_X(ST_AsText(way)) as X, 
       ST_Y(ST_AsText(way)) as Y,
       railway
  FROM planet_osm_point 
 WHERE railway IS NOT NULL AND
       ST_DWithin(way, ST_SetSRID(ST_MakePoint(-1.631560, 48.107485), 4326), 500)
 LIMIT 1000;

As you can see the outputs from WGS84 projection aren't even close to the point I'm lookin at and I can't figure out why when I am using the same geometry type for ST_DWithin. Is there a step I'm missing? (also I am not showing all of the results here, the Web Mercator projection gets me about 154 results, while WGS84 projection goes beyond 1000)

I've just used osm2pgsql to import osm data to a PostGIS database, using lat/long option to store it in WGS84 projection. I would like to use ST_Dwithin to select nodes close to a random point. When I try that directly on the WGS84 data it returns wrong results but works if I reproject it in Web Mercator. What am i missing to avoid the reprojection?

SELECT ST_AsText(way),
       ST_X(ST_AsText(way)) as X,
       ST_Y(ST_AsText(way)) as Y,
       railway
  FROM planet_osm_point
 WHERE railway IS NOT NULL AND
       ST_DWithin(ST_Transform(way, 3785),
                  ST_Transform(ST_SetSRID(ST_MakePoint(-1.631560, 48.107485), 4326), 3785),
                  500)
 LIMIT 1000;

SELECT ST_AsText(way),
       ST_X(ST_AsText(way)) as X, 
       ST_Y(ST_AsText(way)) as Y,
       railway
  FROM planet_osm_point 
 WHERE railway IS NOT NULL AND
       ST_DWithin(way, ST_SetSRID(ST_MakePoint(-1.631560, 48.107485), 4326), 500)
 LIMIT 1000;

As you can see the outputs from WGS84 projection aren't even close to the point I'm lookin at and I can't figure out why when I am using the same geometry type for ST_DWithin. Is there a step I'm missing? (also I am not showing all of the results here, the Web Mercator projection gets me about 154 results, while WGS84 projection goes beyond 1000)

I've just used osm2pgsql to import osm data to a PostGIS database, using lat/long option to store it in WGS84 projection. I would like to use ST_DWithin to select nodes close to a random point. When I try that directly on the WGS84 data it returns wrong results but works if I reproject it in Web Mercator. What am i missing to avoid the reprojection?

SELECT ST_AsText(way),
       ST_X(ST_AsText(way)) as X,
       ST_Y(ST_AsText(way)) as Y,
       railway
  FROM planet_osm_point
 WHERE railway IS NOT NULL AND
       ST_DWithin(ST_Transform(way, 3785),
                  ST_Transform(ST_SetSRID(ST_MakePoint(-1.631560, 48.107485), 4326), 3785),
                  500)
 LIMIT 1000;

SELECT ST_AsText(way),
       ST_X(ST_AsText(way)) as X, 
       ST_Y(ST_AsText(way)) as Y,
       railway
  FROM planet_osm_point 
 WHERE railway IS NOT NULL AND
       ST_DWithin(way, ST_SetSRID(ST_MakePoint(-1.631560, 48.107485), 4326), 500)
 LIMIT 1000;

As you can see the outputs from WGS84 projection aren't even close to the point I'm lookin at and I can't figure out why when I am using the same geometry type for ST_DWithin. Is there a step I'm missing? (also I am not showing all of the results here, the Web Mercator projection gets me about 154 results, while WGS84 projection goes beyond 1000)

Became Hot Network Question

occurred Apr 21, 2022 at 0:50

clean up typos & adjust tags

Source Link

edit approved Apr 20, 2022 at 21:18

hgb

1.2k
8
11

I've just used osm2pgsql to import osm data to a pgsqlPostGIS database, using lat long/long option to store it in WGS84 projection, however iprojection. I would like to use ST_Dwithin to select nodes close to a random point, and for some reason when i. When I try that directly on the WGS84 data it will returnreturns wrong results, but will workworks if iI reproject it in web mercator, whatWeb Mercator. What am i missing to avoid the reprojection?

selectSELECT ST_AsText(way),
       ST_X(ST_AsText(way)) as X, 
       ST_Y(ST_AsText(way)) as Y,
       railway
from planet_osm_point FROM planet_osm_point
where WHERE railway IS NOT NULL AND
and       ST_DWithin(ST_Transform(way, 3785),
                  ST_Transform(ST_SetSRID( ST_MakePoint(-1.631560, 48.107485), 4326), 3785),
                  500)
limit 1000LIMIT 1000;

selectSELECT ST_AsText(way),
       ST_X(ST_AsText(way)) as X, 
       ST_Y(ST_AsText(way)) as Y,
       railway
from  FROM planet_osm_point 
where WHERE railway IS NOT NULL AND
and       ST_DWithin(way, ST_SetSRID( ST_MakePoint(-1.631560, 48.107485), 4326), 500)
limit 1000LIMIT 1000;

As you can see the outputs from WGS84 projection aren't even close to the point i'mI'm lookin at, and iI can't figure out why when iI am using the same geometry type for st_DWIthin, isST_DWithin. Is there a step i'mI'm missing? (also iI am not showing all of the results here, the mercatorWeb Mercator projection gets me about 154 results, while WGS84 projection goes beyond 1000)

I've just used osm2pgsql to import osm data to a pgsql database, using lat long option to store it in WGS84 projection, however i would like to use ST_Dwithin to select nodes close to a random point, and for some reason when i try that directly on the WGS84 data it will return wrong results, but will work if i reproject it in web mercator, what am i missing to avoid the reprojection?

select ST_AsText(way),
ST_X(ST_AsText(way)) as X, 
ST_Y(ST_AsText(way)) as Y,
railway
from planet_osm_point 
where railway IS NOT NULL 
and ST_DWithin(ST_Transform(way,3785),ST_Transform(ST_SetSRID( ST_MakePoint(-1.631560, 48.107485), 4326),3785), 500)
limit 1000

select ST_AsText(way),
ST_X(ST_AsText(way)) as X, 
ST_Y(ST_AsText(way)) as Y,
railway
from planet_osm_point 
where railway IS NOT NULL 
and ST_DWithin(way,ST_SetSRID( ST_MakePoint(-1.631560, 48.107485), 4326), 500)
limit 1000

As you can see the outputs from WGS84 projection aren't even close to the point i'm lookin at, and i can't figure out why when i am using the same geometry type for st_DWIthin, is there a step i'm missing? (also i am not showing all of the results here, the mercator projection gets me about 154 results, while WGS84 projection goes beyond 1000)

I've just used osm2pgsql to import osm data to a PostGIS database, using lat/long option to store it in WGS84 projection. I would like to use ST_Dwithin to select nodes close to a random point. When I try that directly on the WGS84 data it returns wrong results but works if I reproject it in Web Mercator. What am i missing to avoid the reprojection?

SELECT ST_AsText(way),
       ST_X(ST_AsText(way)) as X,
       ST_Y(ST_AsText(way)) as Y,
       railway
  FROM planet_osm_point
 WHERE railway IS NOT NULL AND
       ST_DWithin(ST_Transform(way, 3785),
                  ST_Transform(ST_SetSRID(ST_MakePoint(-1.631560, 48.107485), 4326), 3785),
                  500)
 LIMIT 1000;

SELECT ST_AsText(way),
       ST_X(ST_AsText(way)) as X, 
       ST_Y(ST_AsText(way)) as Y,
       railway
  FROM planet_osm_point 
 WHERE railway IS NOT NULL AND
       ST_DWithin(way, ST_SetSRID(ST_MakePoint(-1.631560, 48.107485), 4326), 500)
 LIMIT 1000;

As you can see the outputs from WGS84 projection aren't even close to the point I'm lookin at and I can't figure out why when I am using the same geometry type for ST_DWithin. Is there a step I'm missing? (also I am not showing all of the results here, the Web Mercator projection gets me about 154 results, while WGS84 projection goes beyond 1000)

Source Link

asked Apr 20, 2022 at 16:50

Bad_deepLearner

41
2

Use ST_DWithin on WGS84 geometry data

I've just used osm2pgsql to import osm data to a pgsql database, using lat long option to store it in WGS84 projection, however i would like to use ST_Dwithin to select nodes close to a random point, and for some reason when i try that directly on the WGS84 data it will return wrong results, but will work if i reproject it in web mercator, what am i missing to avoid the reprojection?

select ST_AsText(way),
ST_X(ST_AsText(way)) as X, 
ST_Y(ST_AsText(way)) as Y,
railway
from planet_osm_point 
where railway IS NOT NULL 
and ST_DWithin(ST_Transform(way,3785),ST_Transform(ST_SetSRID( ST_MakePoint(-1.631560, 48.107485), 4326),3785), 500)
limit 1000

select ST_AsText(way),
ST_X(ST_AsText(way)) as X, 
ST_Y(ST_AsText(way)) as Y,
railway
from planet_osm_point 
where railway IS NOT NULL 
and ST_DWithin(way,ST_SetSRID( ST_MakePoint(-1.631560, 48.107485), 4326), 500)
limit 1000

As you can see the outputs from WGS84 projection aren't even close to the point i'm lookin at, and i can't figure out why when i am using the same geometry type for st_DWIthin, is there a step i'm missing? (also i am not showing all of the results here, the mercator projection gets me about 154 results, while WGS84 projection goes beyond 1000)

openstreetmap wgs84 osm2pgsql mercator

Stack Exchange Network

Return to Question

Use ST_DWithin on WGS84 geometry data