0

Okay I'm kind of stuck with this database, and I'm trying to make a "nearby" page for it.

What I have (unfortunately) is a lat and a lon column that are nvarchar. I can't convert them because they're needed elsewhere as text.

I would like to take the map point center, and put in some dots of places within a mile or so.

Can I somehow join these 2 text fields into one coordinates column to compare STDistance?

To do something like this.. 

SELECT * 
FROM goelocs 
WHERE coords.STDistance(geography::Point(54.1020, -115.12338, 4326)) <=(1609.344)

Or can you recommend a better way?

Thanks in advance!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1591029
  • 77
  • 7

1 Answers1

1

If your NVarchar columns are only the numeric value, like:

  • lat: '54.103'
  • lon: '-115.1'

SQL Server will handle implicitly converting them to a float. The following query will work:

SELECT *
FROM geolocs geo
WHERE
    ISNUMERIC(geo.lat) = 1
    AND ISNUMERIC(geo.lon) = 1
    AND geography::Point(geo.lat, geo.lon, 4326).STDistance(geography::Point(54.1020, -115.12338, 4326)) <= (1609.344)
arserbin3
  • 6,010
  • 8
  • 36
  • 52
  • I went with this.. It was obvious, but not to me lol. THANKS! @arserbin3 SELECT * FROM stops WHERE geography::Point(stop_lat, stop_lon, 4326).STDistance(geography::Point(51.013117,-114.0741555, 4326)) <= (500.0) – user1591029 May 29 '14 at 19:21