ST_DWithin function gives the same value, while trying to join two tables-CodePudding

I'm trying to join two tables based on their geometries, and getting the same values for columns:

select poly.block_code, ST_X(points.geom),  ST_X(points.geom)
from public.us_blocks10_coordinates poly
join public.usgs_2 points
on St_DWithin(poly.geom, ST_Transform(ST_SetSRID(points.geom, 102008), 4269), 0) 
limit 10

enter image description here

CodePudding user response：

First, you are printing st_x twice so it is expected that the values would be the same.

Second, we see that when printing st_x(point.geom), the coordinate really looks like being degrees. However, in the st_dwithin part, there is the statement ST_Transform(ST_SetSRID(points.geom, 102008), 4269) which implies that the points are in CRS 102008, whose unit is in meters, and that you then transform to 4269 (degrees). Both statements are incompatible, and it seems that the set_srid statement is wrong and therefore the result of st_transform is also wrong and so is the result of st_dwithin, and you end up with the ID of whatever polygon is located over the wrong points, near -96;40, the projection center coordinate.

Maybe you would just need St_DWithin(poly.geom, ST_SetSRID(points.geom,4269), 0)

Let's note that you could use st_intersects here instead of st_dwithin