0

I'm trying to pull the SNOW_ROUTE attribute from the nearest street segments based on an X,Y coordinate and order the results by nearest segment. I'm able to return the snow route information but the distance values are not returning and the results are not returned in order with the nearest at the top.

Here is the query that returns the snow routes but doesn't return distance values or sorted by nearest:

SELECT SNOW_ROUTE, sde.st_distance(shape,SDE.ST_POINT(1362521.32365368, 451903.559144756, 300004)) DISTANCE
FROM SDE.STREETS
WHERE sde.st_intersects(shape, sde.st_buffer(SDE.ST_POINT(1362521.32365368, 451903.559144756, 300004),200)) = 1
ORDER BY DISTANCE;


SNOW_ROUTE   DISTANCE
---------- ----------
2-13R                
2-13R                
2-12P                
2-12P                
2-12P                

5 rows selected.

This is another attempt that runs without error but no rows are selected:

SELECT SNOW_ROUTE, sde.st_distance(shape,SDE.ST_POINT(1362521.32365368, 451903.559144756, 300004)) DISTANCE
FROM SDE.STREETS
WHERE sde.st_intersects(shape, sde.st_buffer(SDE.ST_POINT(1362521.32365368, 451903.559144756, 300004),500)) = 1
and  sde.st_distance(sde.st_geometry(1362521.32365368, 451903.559144756,null,null, 300004),shape) < 500
ORDER BY distance;
  • Is the SRID good /represented in your database? select * from sde.st_coordinate_systems where ID in (3857,300004); etc – cm1 Nov 27 '17 at 15:55
  • Thanks, cm1. The SRID was the issue. I opened the SDE.ST_SPATIAL_REFERENCES table and found the correct SRID. Distances are now returning. – Ken Nov 27 '17 at 20:03
  • Glad to hear it Ken and welcome to Geographic Information Systems StackExchange. If you found my comment useful, you can click the small red up-arrow next to my comment to indicate it was useful. – cm1 Nov 27 '17 at 20:08
  • <-- (about here) You have to 'hover' your mouse cursor just to the left of the comment and the arrows appear : ) Thanks Ken – cm1 Nov 27 '17 at 20:26
  • It may be a popup blocker squashing it. No worries, glad to help anyway. – cm1 Nov 27 '17 at 20:36
0

"Is the SRID good /represented in your database? select * from sde.st_coordinate_systems where ID in (3857,300004); etc – cm1"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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