How can I select points in SQL Server 2012 based on geography if they are text fields

Question

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!

Answer 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)