CODEWITHSUNDEEP
sqlsql-server-2012nvarchargeographic-distance
user1591029
user1591029

Reputation: 77

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

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!

Upvotes: 0

Views: 123

Answers (1)

arserbin3
arserbin3

Reputation: 6148

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)

Upvotes: 1

Related Questions